Есть ли способ вернуть строку или встроенный JSON, используя FOR JSON?

У меня есть столбец nvarchar, который я хотел бы вернуть, встроенный в мои результаты JSON, если содержимое является допустимым JSON, или как строка в противном случае.

Вот что я попробовал:

select
  (
    case when IsJson(Arguments) = 1 then 
      Json_Query(Arguments) 
    else 
      Arguments 
    end
  ) Results
  from Unit
  for json path

Это всегда помещает результаты в строку.

Следующее работает, но только если атрибут содержит допустимый JSON:

select
    (
      Json_Query(
        case when IsJson(Arguments) = 1 then 
          Arguments 
        else 
          '"' + String_escape(IsNull(Arguments, ''), 'json') + '"' end
      )
    ) Results
    from Unit
    for json path

Если аргументы не содержат объект JSON, возникает ошибка во время выполнения.

Обновление: Пример данных:

Arguments
---------
{ "a": "b" }
Some text

Обновление: подойдет любая версия SQL Server. Я бы даже был рад узнать, что это будет бета или что-то в этом роде.

Источник

2 ответа

Решение

Когда вы говорите, что ваше утверждение "... всегда помещает результаты в строку"., вы, вероятно, имеете в виду, что когда JSON хранится в текстовом столбце, FOR JSON ускользает от этого текста. Конечно, если вы хотите вернуть без спасения JSON текст, вам нужно использовать JSON_QUERY функция только для вашего действительного JSON текст.

Далее небольшой обходной путь (основанный на FOR JSON и манипуляции со строками), которые могут помочь решить вашу проблему.

Таблица:

CREATE TABLE #Data (
   Arguments nvarchar(max)
)
INSERT INTO #Data 
   (Arguments)
VALUES
   ('{"a": "b"}'),
   ('Some text'),
   ('{"c": "d"}'),
   ('{"e": "f"}'),
   ('More[]text')

Утверждение:

SELECT CONCAT(N'[', j1.JsonOutput, N',', j2.JsonOutput, N']')
FROM 
(
   SELECT JSON_QUERY(Arguments) AS Results
   FROM #Data
   WHERE ISJSON(Arguments) = 1
   FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
) j1 (JsonOutput),
(
   SELECT STRING_ESCAPE(ISNULL(Arguments, ''), 'json') AS Results
   FROM #Data
   WHERE ISJSON(Arguments) = 0
   FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
) j2 (JsonOutput)

Выход:

[{"Results":{"a": "b"}},{"Results":{"c": "d"}},{"Results":{"e": "f"}},{"Results":"Some text"},{"Results":"More[]text"}]

Примечания:

Одним из недостатков здесь является то, что порядок элементов в сгенерированном выводе не такой, как в таблице.

Источник

Я не нашел хорошего решения и был бы счастлив, если бы кто-то нашел лучшее, чем этот хак:

DECLARE @tbl TABLE(ID INT IDENTITY,Arguments NVARCHAR(MAX));
INSERT INTO @tbl VALUES
 (NULL)
,('plain text')
,('[{"id":"1"},{"id":"2"}]');

SELECT t1.ID
      ,(SELECT Arguments FROM @tbl t2 WHERE t2.ID=t1.ID AND ISJSON(Arguments)=0) Arguments
      ,(SELECT JSON_QUERY(Arguments) FROM @tbl t2 WHERE t2.ID=t1.ID AND ISJSON(Arguments)=1) ArgumentsJSON
FROM @tbl t1 
FOR JSON PATH;

Поскольку значения NULL опущены, вы всегда найдете eiter Arguments или же ArgumentsJSON в вашем конечном результате. Рассматривая этот JSON как NVARCHAR(MAX), вы можете использовать REPLACE переименовать все в одно и то же Arguments,

Кажется, проблема в том, что вы не можете включить в свой SELECT два столбца с одинаковым именем, но каждый столбец должен иметь предсказуемый тип. Это зависит от порядка, который вы используете в CASE (или COALESCE). Если движок думает "Хорошо, вот текст", все будет рассматриваться как текст, и ваш JSON будет экранирован. Но если движок думает "Хорошо, немного JSON", все обрабатывается как JSON и сломается, если этот JSON недопустим.

С участием FOR XML PATH Есть некоторые трюки с колонкой namig (такие как [*], [node()] или даже дважды в одном запросе), но FOR JSON PATH не такой мощный...

Источник
Другие вопросы по тегам