Поиск…


Получить ключ: пары значений из текста JSON

Функция OPENJSON анализирует текст JSON и возвращает все пары ключ: значение на первом уровне JSON:

declare @json NVARCHAR(4000) = N'{"Name":"Joe","age":27,"skills":["C#","SQL"]}';
SELECT * FROM OPENJSON(@json);
ключ значение тип
название Джо 1
возраст 27 2
навыки [ "C #", "SQL"] 4

Тип столбца описывает тип значения, то есть null (0), строку (1), число (2), логическое (3), массив (4) и объект (5).

Преобразование массива JSON в множество строк

Функция OPENJSON анализирует набор объектов JSON и возвращает значения из текста JSON в виде набора строк.

declare @json nvarchar(4000) = N'[
  {"Number":"SO43659","Date":"2011-05-31T00:00:00","Customer": "MSFT","Price":59.99,"Quantity":1},
  {"Number":"SO43661","Date":"2011-06-01T00:00:00","Customer":"Nokia","Price":24.99,"Quantity":3}
]'

SELECT    * 
FROM OPENJSON (@json)
    WITH (
          Number   varchar(200),
          Date     datetime,
          Customer varchar(200),
          Quantity int
  )

В предложении WITH указывается схема возврата функции OPENJSON. Ключи в объектах JSON извлекаются по именам столбцов. Если какой-либо ключ в JSON не указан в предложении WITH (например, цена в этом примере), он будет проигнорирован. Значения автоматически преобразуются в определенные типы.

Число Дата Покупатель Количество
SO43659 2011-05-31T00: 00: 00 MSFT 1
SO43661 2011-06-01T00: 00: 00 Nokia 3

Преобразование вложенных полей JSON в набор строк

Функция OPENJSON анализирует набор объектов JSON и возвращает значения из текста JSON в виде набора строк. Если значения в входном объекте вложены, в каждом столбце в предложении WITH может быть указан дополнительный параметр сопоставления:

declare @json nvarchar(4000) = N'[
  {"data":{"num":"SO43659","date":"2011-05-31T00:00:00"},"info":{"customer":"MSFT","Price":59.99,"qty":1}},
  {"data":{"number":"SO43661","date":"2011-06-01T00:00:00"},"info":{"customer":"Nokia","Price":24.99,"qty":3}}
]'

SELECT    * 
FROM OPENJSON (@json)
    WITH (
          Number   varchar(200) '$.data.num',
          Date     datetime '$.data.date',
          Customer varchar(200) '$.info.customer',
          Quantity int '$.info.qty',
  )

В предложении WITH указывается схема возврата функции OPENJSON. После того, как тип указан путь к узлам JSON, где должно быть найдено возвращаемое значение. Ключи в объектах JSON извлекаются этими путями. Значения автоматически преобразуются в определенные типы.

Число Дата Покупатель Количество
SO43659 2011-05-31T00: 00: 00 MSFT 1
SO43661 2011-06-01T00: 00: 00 Nokia 3

Извлечение внутренних подсайтов JSON

OPENJSON может извлекать фрагменты объектов JSON внутри текста JSON. В определении столбца, которое ссылается на под-объект JSON, задается опция nvarchar (max) и AS JSON:

declare @json nvarchar(4000) = N'[
  {"Number":"SO43659","Date":"2011-05-31T00:00:00","info":{"customer":"MSFT","Price":59.99,"qty":1}},
  {"Number":"SO43661","Date":"2011-06-01T00:00:00","info":{"customer":"Nokia","Price":24.99,"qty":3}}
]'

SELECT    * 
FROM OPENJSON (@json)
    WITH (
          Number   varchar(200),
          Date     datetime,
          Info nvarchar(max) '$.info' AS JSON
  )

Информационный столбец будет сопоставлен с объектом «Информация». Результатом будет:

Число Дата Информация
SO43659 2011-05-31T00: 00: 00 { "Клиент": "MSFT", "Цена": 59,99, "кол-во": 1}
SO43661 2011-06-01T00: 00: 00 { "Клиент": "Nokia", "Цена": 24,99, "кол-во": 3}

Работа с вложенными подматрицами JSON

JSON может иметь сложную структуру с внутренними массивами. В этом примере у нас есть массив заказов с вложенным под массивом OrderItems.

declare @json nvarchar(4000) = N'[
  {"Number":"SO43659","Date":"2011-05-31T00:00:00",
    "Items":[{"Price":11.99,"Quantity":1},{"Price":12.99,"Quantity":5}]},
  {"Number":"SO43661","Date":"2011-06-01T00:00:00",
    "Items":[{"Price":21.99,"Quantity":3},{"Price":22.99,"Quantity":2},{"Price":23.99,"Quantity":2}]}
]'

Мы можем проанализировать свойства корневого уровня с помощью OPENJSON, который будет возвращать массив элементов AS JSON. Затем мы можем снова применить OPENJSON в массиве Items и открыть внутреннюю таблицу JSON. Таблица первого уровня и внутренняя таблица будут «объединены», как в JOIN между стандартными таблицами:

SELECT    * 
FROM
    OPENJSON (@json)
    WITH (  Number varchar(200), Date datetime,
            Items nvarchar(max) AS JSON )
        CROSS APPLY 
            OPENJSON (Items)
            WITH ( Price float, Quantity int)

Результаты:

Число Дата Предметы Цена Количество
SO43659 2011-05-31 00: 00: 00.000 [{ "Цена": 11,99, "Количество": 1}, { "Цена": 12,99, "Количество": 5}] 11,99 1
SO43659 2011-05-31 00: 00: 00.000 [{ "Цена": 11,99, "Количество": 1}, { "Цена": 12,99, "Количество": 5}] 12,99 5
SO43661 2011-06-01 00: 00: 00.000 [{ "Цена": 21.99, "Количество": 3}, { "Цена": 22.99, "Количество": 2}, { "Цена": 23.99, "Количество": 2}] 21,99 3
SO43661 2011-06-01 00: 00: 00.000 [{ "Цена": 21.99, "Количество": 3}, { "Цена": 22.99, "Количество": 2}, { "Цена": 23.99, "Количество": 2}] 22,99 2
SO43661 2011-06-01 00: 00: 00.000 [{ "Цена": 21.99, "Количество": 3}, { "Цена": 22.99, "Количество": 2}, { "Цена": 23.99, "Количество": 2}] 23,99 2


Modified text is an extract of the original Stack Overflow Documentation
Лицензировано согласно CC BY-SA 3.0
Не связан с Stack Overflow