当列为空时从 SQL 获取 JSON

出现您遇到的错误消息是因为SQL Server希望Cuestionario列中的值是格式良好的JSON对象，但当评级为4星或5星时，您得到的是空字符串.

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;

fiddle个