SQL

Функция OPENJSON в SQL Server

Время прочтения: 8 мин.

Основные обозначения JSON:

” – Имя и Значения
: - Разделитель между Именами и Значениями
{} - Объект
[,] – Массив данных
[{},{}] – Массив объектов

Наглядно, использование обозначений, лучше изучить на примере кода:

Более детальная информация на сайте разработчиковhttp://json.org/

OPENJSON — функция с табличным представлением данных, позволяющая выполнить синтаксический анализ текста JSON. Возвращает объекты и свойства из входных данных JSON в виде строк и столбцов. Из-за возможности функции возвращать набор строк, её можно встраивать в SQL-запрос раздела FROM и обращаться к ней так же, как к таблице или преобразовывать данные в реляционный формат, для служб и приложений, которые напрямую не могут использовать формат JSON.

Функция OPENJSON по умолчанию возвращает три столбца, содержащих Ключ, Значение и Тип {ключ: значение}, но также, есть возможность явно указать схему результирующего набора, который возвращает OPENJSON.

Простой путь выполнения функции и последующего запроса выглядит так:

Давайте рассмотрим примеры запросов с возвращением ответов по умолчанию и явным объявлением схемы.

1. Запросы, с объявлением схемы по умолчанию.

1.1. Выполним стандартный запрос со схемой по умолчанию

SELECT * FROM OPENJSON('{"Имя":"Иван","Фамилия":"Петров"}')

Результат:

Из результата видно, что функция OPENJSON со схемой по умолчанию, возвращает 3 столбца:

Key: имя ключа или индекс
Value: значение свойства
Type: тип значения (целое число)

Ниже мы более подробно разберём возможные Типы значений.

1.2. Пример с возможными типами значений функции OPENJSON со схемой по умолчанию

DECLARE @json_string NVARCHAR(MAX) = N'
    {"Имя":"Иван","Id":1,"Permanent":true,
         "Увлечения":["Теннис","Чтение"],
        "Адрес":{"Область":"МОСКОВСКАЯ","Страна":"Россия"}}'
SELECT *
FROM OPENJSON(@json_string)

Результат:

Разберём результат ответа по возможным типам:

  1. значение STRING
  2. значение INT
  3. значение BOOLEAN
  4. значение типа JSON ARRAY
  5. значение типа JSON OBJECT

1.3. Выборка со столбцами key и value

DECLARE @json_string NVARCHAR(MAX) = N'
    {"Id":1,"Имя":"Иван",
        "Адрес":{"Область":"МОСКОВСКАЯ","Страна":"Россия"}}'
SELECT [key],value
FROM OPENJSON(@json_string)

Результат:

1.4. Определение функции OPENJSON через json_path, указывающий на объект «АДРЕС» во входной строке

DECLARE @json_string NVARCHAR(MAX) = N'
  {"Id":1,"Имя":"Иван",
   "Адрес":{"Область":"МОСКОВСКАЯ","Страна":"Россия"}}'
SELECT [key],value
FROM OPENJSON(@json_string, '$."Адрес"')

Результат:

1.5. Определение функции OPENJSON через один атрибут на корневом уровне, указывающий на входную строку

DECLARE @json_string NVARCHAR(MAX) = N'
  {"Customer":{"Id":1,"Имя":"Иван",
   "Адрес":{"Область":"МОСКОВСКАЯ","Страна":"Россия"}}}'
SELECT [key],value
FROM OPENJSON(@json_string)

Результат:

1.6. Входная строка интерпретируется как массив строк JSON

DECLARE @json_string NVARCHAR(MAX) = '["Теннис","Чтение"]'
SELECT [key],value
FROM OPENJSON(@json_string)

Результат:

1.7. Входная строка интерпретируется как массив целых чисел JSON

DECLARE @json_string NVARCHAR(MAX) = '[1,2,3,4]'
SELECT [key],value
FROM OPENJSON(@json_string)

Результат:

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

1.8. Определение функции OPENJSON через json_path, указывающий на массив «УВЛЕЧЕНИЯ» во входной строке

DECLARE @json_string NVARCHAR(MAX) = N'
    {"Id":1,"Имя":"Иван",
        "Увлечения":["Теннис","Чтение"]}'
SELECT [key],value
FROM OPENJSON(@json_string,'$."Увлечения"')

Результат:

1.9. Входная строка интерпретируется как массив вложенных объектов JSON

DECLARE @json_string NVARCHAR(MAX) = 
 N'{"Clients":
    [{"Id":1,"Имя":"Иван",
            "Адрес":{"Область":"МОСКОВСКАЯ","Страна":"Россия"}},
     {"Id":2,"Имя":"Елена",
            "Адрес":{"Область":"РОСТОВСКАЯ","Страна":"Россия"}}
    ]
 }'
SELECT *
FROM OPENJSON(@json_string)

Результат:

А теперь выполним тот же запрос, но с параметром json_path, который указывает на массив объектов JSON

DECLARE @json_string NVARCHAR(MAX) = 
 N'{"Clients":
    [{"Id":1,"Имя":"Иван",
            "Адрес":{"Область":"МОСКОВСКАЯ","Страна":"Россия"}},
     {"Id":2,"Имя":"Елена",
            "Адрес":{"Область":"РОСТОВСКАЯ","Страна":"Россия"}}
    ]
 }'
SELECT *
FROM OPENJSON(@json_string,'$.Clients')

Результат:

Как видим это даёт возможность, получать строчный результат с корректным логическим распределением данных.

2. Запросы, с явным объявлением схемы

2.1. Разберём пример функции OPENJSON с явным объявлением схемы

DECLARE @json_string NVARCHAR(MAX) = 
 N'{"Clients":
    [{"Id":1,"Имя":"Иван",
      "Адрес":{"Область":"МОСКОВСКАЯ","Страна":"Россия"}},
     {"Id":2,"Имя":"Елена",
      "Адрес":{"Область":"РОСТОВСКАЯ","Страна":"Россия"}}
    ]
 }'
SELECT *
FROM OPENJSON(@json_string,'$.Clients')
WITH(Id INT, Имя NVarchar(100))

Результат:

В этом примере json_path в функции OPENJSON указывает на массив объектов JSON. В значениях столбцов объявления схемы путь json не упоминается, в таких случаях он пытается сопоставить Имя свойства JSON по Имени столбца и возвращает соответствующее ему значение свойства JSON.

2.2. Давайте расширим пример 2.1 и получим «Страну» клиента из вложенного объекта JSON «Адрес», кроме «Id» и «Имя»

DECLARE @json_string NVARCHAR(MAX) = 
 N'{"Clients":
    [{"Id":1,"Имя":"Иван",
            "Адрес":{"Область":"МОСКОВСКАЯ","Страна":"Россия"}},
     {"Id":2,"Имя":"Елена",
            "Адрес":{"Область":"РОСТОВСКАЯ","Страна":"Россия"}}
    ]
 }'
SELECT *
FROM OPENJSON(@json_string,'$.Clients')
WITH("Id" INT, "Имя" Varchar(100), [Адрес.Страна] NVarchar(50))

Результат:

т.к. значения столбца не упоминается, поэтому он идет по Имени столбца, т.о. Имя столбца [Адрес.Страна] подразумевает, что функция OPEJSON возвращает значение свойства «Страна» из объекта «Адрес» JSON.

2.3. В этом примере указан путь json для значения столбца «ID Сотрудника». Другие столбцы (например, «Имя» и «Адрес.Страна») идут по Имени столбца во входном тексте json для его значения, как было наглядно показано в примере 2.2.

DECLARE @json_string NVARCHAR(MAX) = 
 N'{"Clients":
    [{"Id":1,"Имя":"Иван",
      "Адрес":{"Область":"МОСКОВСКАЯ","Страна":"Россия"}},
     {"Id":2,"Имя":"Елена",
      "Адрес":{"Область":"РОСТОВСКАЯ","Страна":"Россия"}}
    ]
 }'
SELECT *
FROM OPENJSON(@json_string,'$.Clients')
WITH("ID Сотрудника" INT '$.Id', "Имя" Varchar(100), 
     "Адрес.Страна" NVarchar(50))

Результат:

Если указан путь JSON к значению столбца, то он переопределяет метод извлечения Имении столбца. Т.о. значение столбца «ID Сотрудника» извлекается с помощью значения столбца json_path, то есть ‘$.Id’, если бы оно проходило по Имени столбца, мы не получили бы никакого значения для этого столбца, потому что не было бы свойств во входной строке JSON с Именем ключа «ID Сотрудника».

2.4. Теперь мы определим для всех столбцов путь JSON значения столбца

DECLARE @json_string NVARCHAR(MAX) = 
 N'{"Clients":
    [{"Id":1,"Имя":"Иван",
            "Адрес":{"Область":"МОСКОВСКАЯ","Страна":"Россия"}},
     {"Id":2,"Имя":"Елена",
            "Адрес":{"Область":"РОСТОВСКАЯ","Страна":"Россия"}}
    ]
 }'
SELECT *
FROM OPENJSON(@json_string,'$.Clients')
WITH("Сотрудник Id" INT '$."Id"', "Имя Сотрудника" Varchar(100) '$."Имя"', 
        "Страна Сотрудника" NVarchar(50) '$."Адрес"."Страна"')

Результат:

3. Разница влияний на результат, в зависимости от типа запроса

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

3.1. В приведенном ниже примере json_path ‘$.City’, упомянутый в функции OPENJSON, не существует во входной строке JSON. И в этом json_path режим пути JSON явно не указан, поэтому он идет со слабым режимом пути JSON по умолчанию

DECLARE @json_string NVARCHAR(MAX) = N'
{"Id":1,"Имя":"Иван",
  "Адрес":{"Область":"МОСКОВСКАЯ","Страна":"Россия"}}'
SELECT [key],value
FROM OPENJSON(@json_string, '$.City')

Результат:

Давайте выполним этот пример, указав режим пути JSON как строгий

DECLARE @json_string NVARCHAR(MAX) = 
    N'{"Id":1,"Имя":"Иван",
       "Адрес":{"Область":"МОСКОВСКАЯ","Страна":"Россия"}}'
SELECT [key],value
FROM OPENJSON(@json_string, 'strict$.City')

Результат:

Сообщение 13608, уровень 16, состояние 3, строка 4
Property cannot be found on the specified Json_path.

Давайте выполним этот пример, явно указав режим пути JSON по умолчанию lax

DECLARE @json_string NVARCHAR(MAX) = 
    N'{"Id":1,"Имя":"Иван",
       "Адрес":{"Область":"МОСКОВСКАЯ","Страна":"Россия"}}'
SELECT [key],value
FROM OPENJSON(@json_string, 'lax$.City')

Результат:

3.2. В приведенной ниже функции OPENJSON с явным объявлением схемы значение столбца DOB Путь JSON «$.Dob» не существует во входной строке JSON. В json_path режим пути JSON не указан явно, поэтому он идет с режимом пути JSON по умолчанию, который является слабым

DECLARE @json_string NVARCHAR(MAX) = 
 N'{"Clients":
    [{"Id":1,"Имя":"Иван",
            "Адрес":{"Область":"МОСКОВСКАЯ","Страна":"Россия"}},
     {"Id":2,"Имя":"Елена",
            "Адрес":{"Область":"РОСТОВСКАЯ","Страна":"Россия"}}
    ]
 }'
SELECT *
FROM OPENJSON(@json_string,'$.Clients')
WITH("ID Сотрудника" INT '$.Id', "Имя Сотрудника" Varchar(100) '$."Имя"',
        DOB Varchar(10) '$.Dob')

Результат:

Выполним предыдущий запрос с указанием режима пути JSON как strict, т.е. ‘strict $.Dob’

DECLARE @json_string NVARCHAR(MAX) = N'
{"Clients":
    [{"Id":1,"Имя":"Иван",
      "Адрес":{"Область":"МОСКОВСКАЯ","Страна":"Россия"}},
     {"Id":2,"Имя":"Елена",
      "Адрес":{"Область":"РОСТОВСКАЯ","Страна":"Россия"}}
    ]
 }'
SELECT *
FROM OPENJSON(@json_string,'$.Clients')
WITH("Id Сотрудника" INT '$.Id', "Имя Сотрудника" Varchar(100) '$."Имя"',
        DOB Varchar(10) 'strict$.Dob')

Результат:

Сообщение 13608, уровень 16, состояние 6, строка 9
Property cannot be found on the specified Json_path.

А теперь явно укажем режим пути JSON по умолчанию ‘lax $.Dob’

DECLARE @json_string NVARCHAR(MAX) = N'
{"Clients":
    [{"Id":1,"Имя":"Иван",
      "Адрес":{"Область":"МОСКОВСКАЯ","Страна":"Россия"}},
     {"Id":2,"Имя":"Елена",
      "Адрес":{"Область":"РОСТОВСКАЯ","Страна":"Россия"}}
    ]
 }'
SELECT *
FROM OPENJSON(@json_string,'$.Clients')
WITH("Id Сотрудника" INT '$.Id', "Имя Сотрудника" Varchar(100) '$."Имя"',
        DOB Varchar(10) 'lax$.Dob')

Результат:

Не смотря на управляемость процесса, явное объявление схемы не всегда применимо на практике, всё зависит от конкретной задачи, которая стоит перед специалистом. При малом наборе типов выводимых данных, функционал OPENJSON достаточен, для большинства решаемых задач, где необходимо применить вывод дополнительных строковых значений.

Подробное описание функции JSON представлено в справочном разделе SQL Server на сайте Microsoft

Советуем почитать