Question 230235 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Fetching a Json with from SQL when a columns is empty

I have a table in SQL SERVER to keep all of the users app reviews.
When the user reviews the app with 4 or 5 stars I recieve a Json like this one

{
    "Calificacion": 5,
    "Cuestionario": "",
    "Comentarios": "",
    "Application": "APLIC004"
}

and when te user gives us a 3 stars or lower review, FrontEnd sends me a Json like this one

{
  "Calificacion": 0,
  "Cuestionario": [
    {
      "Order": 1,
      "Question": "string",
      "Answer": true
    }
    {
      "Order": 2,
      "Question": "string",
      "Answer": true
    }
    {
      "Order": 3,
      "Question": "string",
      "Answer": true
    }
    {
      "Order": 4,
      "Question": "string",
      "Answer": true
    }
  ],
  "Comentarios": "string",
  "Application": "string"
}

the problem is that when I try to query the most recent record from a user, I get this message when the recent record has no data for the column Cuestionario

Msg 13609, Level 16, State 2, Line 114 JSON text is not properly
formatted. Unexpected character ‘.’ is found at position 0.

The query I’m using to fetch the data is the following

select TOP 1
  UserId,
  Fecha,
  Calificacion,
  JSON_QUERY(Cuestionario) Cuestionario,
  Comentarios,
  [Application]
from AppScores
where UserId = '00000000-0000-0000-0000-000000000000'
order by Fecha desc
for json path, WITHOUT_ARRAY_WRAPPER
Tags:

2

Answers


  1. 0

    The error message you’re encountering occurs because SQL Server expects the value in the Cuestionario column to be a well-formatted JSON object, but you’re getting an empty string when the rating is 4 or 5 stars.

    CREATE TABLE AppScores (
    UserId UNIQUEIDENTIFIER,
    Fecha DATETIME,
    Calificacion INT,
    Cuestionario NVARCHAR(MAX),
    Comentarios NVARCHAR(MAX),
    [Application] NVARCHAR(50)
);

INSERT INTO AppScores (UserId, Fecha, Calificacion, Cuestionario, Comentarios, [Application])
VALUES ('00000000-0000-0000-0000-000000000000', GETDATE(), 5, '', '', 'APLIC004');

INSERT INTO AppScores (UserId, Fecha, Calificacion, Cuestionario, Comentarios, [Application])
VALUES ('00000000-0000-0000-0000-000000000000', DATEADD(DAY, -1, GETDATE()), 3, '[
        {
          "Order": 1,
          "Question": "string",
          "Answer": true
        },
        {
          "Order": 2,
          "Question": "string",
          "Answer": true
        },
        {
          "Order": 3,
          "Question": "string",
          "Answer": true
        },
        {
          "Order": 4,
          "Question": "string",
          "Answer": true
        }
      ]', 'string', 'string');


    2 rows affected

    SELECT TOP 1
       UserId,
       Fecha,
       Calificacion,
       JSON_QUERY(CASE
                     WHEN LEN(Cuestionario) = 0 THEN '[]'
                     ELSE Cuestionario
                  END) AS Cuestionario,
       Comentarios,
       [Application]
FROM AppScores
WHERE UserId = '00000000-0000-0000-0000-000000000000'
ORDER BY Fecha DESC
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;
    JSON_F52E2B61-18A1-11d1-B105-00805F49916B
    {"UserId":"00000000-0000-0000-0000-000000000000","Fecha":"2023-08-14T20:52:25.167","Calificacion":5,"Cuestionario":[],"Comentarios":"","Application":"APLIC004"}

    fiddle

    Login or Signup to reply.
  2. 0

    Using JSON_QUERY with a single parameter basically tells the compiler "this is definitely valid JSON, do not escape it". But '' is not valid JSON, nor is '.'. You should really use NULL or '[]' for empty values.

    You can use the ISJSON function in a CASE or IIF to verify if the value is actually valid JSON.

    JSON_QUERY(
  IIF(ISJSON(Cuestionario) = 1, Cuestionario, N'[]')
) AS Cuestionario
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search