Блог

Главная - Вопросы по программированию - ВЫБЕРИТЕ динамический json, хранящийся в SQL, как часть запроса select

ВЫБЕРИТЕ динамический json, хранящийся в SQL, как часть запроса select

#sql #json #sql-server #tsql

#sql #json #sql-сервер #tsql

Вопрос:

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

Моя таблица базы данных выглядит следующим образом:

 App ID    |    ID     |     JSONData      |     URL      |     CreatedOn
---------- ----------- ------------------- -------------- -----------------
5b5cd8    |    1      | {"F":"B", "S":"D"}| http://local | Mar 19 2018 13:04
5b5cd8    |    2      | {"F":"C", "S":"K"}| http://remote| Mar 29 2018 09:34
6b9df0    |    3      | {"T":"N", "D":"S"}| http://site  | Apr 04 2018 16:12

App ID столбец может иметь разные значения, однако структура JSONData (* должна быть) одинаковой для одного и того же App ID .

Могу ли я в любом случае разделить JSONData данные и получить такой результат?

 App ID    |    ID     |  F  |  S  |     URL      |     CreatedOn
---------- ----------- ----- ----- -------------- -------------------------
5b5cd8    |    1      |  B  |  D  | http://local | Mar 19 2018 13:04
5b5cd8    |    2      |  C  |  K  | http://remote| Mar 29 2018 09:34

Для следующего App ID это выглядит следующим образом

 App ID    |    ID     |  T  |  D  |     URL      |     CreatedOn
---------- ----------- ----- ----- -------------- -------------------------
6b9df0    |    3      |  N  |  S  | http://site  | Apr 04 2018 16:12

Примечание: Данные в JSONData поле будут в основном одноуровневыми, т. е. все данные будут строковыми и никаких дополнительных объектов.

Решение, которое я нашел в большинстве случаев, подобных этому, заключалось либо в использовании имен статических ключей JSON для разделения, либо в создании временной таблицы, которая вызовет проблемы с производительностью.

Комментарии:

1. В результате запроса не может быть произвольных столбцов. Вы не можете иметь столбец с именем T в одной строке и переименовывать его в F в другой.

2. @PanagiotisKanavos, запросы будут разными и будут зависеть от App ID столбца как части предложения where. Таким образом, мы можем предположить, что у нас не будет случая, когда нам понадобится иметь столбец F и T , т.е. App ID s 6b9df0 amp; 5b5cd8 , как часть того же запроса select.

Ответ №1:

Вам уже говорили, что имена столбцов результирующего набора должны быть известны заранее.

Единственным обходным путем был динамический SQL (создание инструкции в виде строки и EXEC() получение ее результата). Но у этого есть некоторые серьезные недостатки (и некоторые преимущества)…

Вы могли бы использовать что-то в этом роде (требуется SQL-Server 2016 ):

Макетный стол

 DECLARE @tbl TABLE(AppID VARCHAR(100),ID INT,JSONData NVARCHAR(MAX));
INSERT INTO @tbl VALUES
 ('5b5cd8',1,N'{"F":"B", "S":"D"}')
,('5b5cd8',2,N'{"F":"C", "S":"K"}')
,('6b9df0',3,N'{"T":"N", "D":"S"}');

—Этот запрос извлекает значения с помощью JSON_VALUE
—Вам нужно будет создать одну инструкцию для каждого возможного списка столбцов
— Применить WHERE для фильтрации соответствующих строк

 SELECT t.AppID
      ,t.ID
      ,JSON_VALUE(t.JSONData,'$.F') AS F
      ,JSON_VALUE(t.JSONData,'$.S') AS S
FROM @tbl t
WHERE t.AppID='5b5cd8'

—Вы могли бы включить все возможные столбцы
—Это работает без фильтра, но вернет много нулей

 SELECT t.AppID
      ,t.ID
      ,JSON_VALUE(t.JSONData,'$.F') AS F
      ,JSON_VALUE(t.JSONData,'$.S') AS S
      ,JSON_VALUE(t.JSONData,'$.T') AS T
      ,JSON_VALUE(t.JSONData,'$.D') AS D
FROM @tbl t

— Немного чище / лучше для чтения было OPENJSON() в связи с WITH предложением 

 SELECT t.AppID
      ,t.ID
      ,JsonColumns.*
FROM @tbl t
CROSS APPLY OPENJSON(t.JSONData) WITH(F CHAR(1)
                                     ,S CHAR(1)
                                     ,T CHAR(1)
                                     ,D CHAR(1)) JsonColumns

Мое предложение: создайте последний в виде VIEW или (возможно, лучше) iTVF и используйте для этого специальные инструкции, по одной для каждого типа структуры.