Fetching JSON from SQL when a column is empty

Question

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 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 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

An empty string `''` is not valid JSON. You should instead use `NULL` or `'[]'` — Charlieface, Aug 15 '23 at 01:38

score 1 · Accepted Answer · answered Aug 14 '23 at 20:53

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

`ISJSON` is probably a more reliable indicator for valid JSON. — Charlieface, Aug 15 '23 at 01:43

score 0 · Answer 2 · answered Aug 15 '23 at 01:42

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

Fetching JSON from SQL when a column is empty

2 Answers2