Функция SQL Server OPENJSON()

В этом руководстве вы узнаете, как использовать функцию SQL Server OPENJSON() для анализа текста JSON и преобразования его элементов в строки и столбцы.

Введение в функцию SQL Server OPENJSON()

Функция OPENJSON() — это табличная функция, которая анализирует строку JSON и возвращает значения из входного JSON в виде строк и столбцов. Другими словами, функция OPENJSON() преобразует строку JSON в строки и столбцы.

Вот базовый синтаксис функции OPENJSON():

OPENJSON(json_expression [, path])
  [WITH(
    option1 [,...optionN]
  )]

В этом синтаксисе:

• json_expression: Это документ JSON, который вы хотите проанализировать.
• path: Этот путь JSON указывает элемент JSON для извлечения. Путь необязателен. Если вы его опустите, функция OPENJSON() проанализирует весь документ JSON.
• option1, option2, …: Это необязательные параметры, которые определяют дополнительные параметры, такие как преобразование типа данных, определение схемы и обработка ошибок.

Примеры функции SQL Server OPENJSON()

Давайте рассмотрим несколько примеров использования функции OPENJSON().

1) Пример базовой функции OPENJSON()

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

{
  "name": "John",
  "age": 25,
  "skills": [
    "SQL Server",
    "C#",
    ".NET"
  ],
  "address": null,
  "active": true,
  "phone": {
    "work": "(408)-123-4567",
    "home": "(408)-789-1234"
  }
}

В следующем примере функция OPENJSON() используется для анализа и преобразования документа JSON в строки и столбцы:

SELECT
  * 
FROM
  OPENJSON(
    '{"name": "John", "age": 25, "skills" : ["SQL Server","C#", ".NET"], "address":null, "active": true, "phone": {"work": "(408)-123-4567", "home": "(408)-789-1234"}}'
  ) 
ORDER BY
  type;

Выход:

key     | value                                                | type
-----------------------------------------------------------------------
address | None                                                 | 0
name    | John                                                 | 1
age     | 25                                                   | 2
active  | true                                                 | 3
skills  | ["SQL Server","C#", ".NET"]                          | 4
phone   | {"work": "(408)-123-4567", "home": "(408)-789-1234"} | 5

Вывод состоит из трех столбцов: ключ, значение и тип.

Ключи и значения в результирующем наборе соответствуют ключам и значениям в документе JSON. Значение столбца type определяет тип данных соответствующего значения в результате:

2) Использование функции OPENJSON() с явной структурой

Предложение WITH в функции OPENJSON() позволяет указать схему для набора результатов. Функция OPENJSON() вернет набор результатов только со столбцами, которые вы определили в предложении WITH.

В следующем примере функция OPENJSON() используется для анализа и преобразования элементов строки JSON в определенную схему:

SELECT
  * 
FROM
  OPENJSON(
    '{"name": "John", "age": 25, "skills" : ["SQL Server","C#", ".NET"], "address":null, "active": true, "phone": {"work": "(408)-123-4567", "home": "(408)-789-1234"}}'
  ) 
WITH(
 name VARCHAR(100)  '$.name',
 age INT  '$.age',
 active BIT '$.active',
 work_phone VARCHAR(20) '$.phone.work',
 home_phone VARCHAR(25) '$.phone.home',
 address VARCHAR(255) '$.address'
);

Выход:

name | age | active | work_phone     | home_phone     | address
----------------------------------------------------------------
John | 25  | True   |(408)-123-4567 |(408)-789-1234 | NULL

Краткое содержание

• Используйте функцию OPENJSON() для анализа текста JSON и преобразования его элементов в строки и столбцы.