Анализ и преобразование данных JSON с помощью OPENJSON

  • Статья

Область применения: SQL Server 2016 (13.x) и более поздних версий База данных SQL Azure Управляемый экземпляр SQL Azure Azure Synapse Analytics

Функция OPENJSON набора строк преобразует текст JSON в набор строк и столбцов. После преобразования коллекции JSON в набор OPENJSONстрок можно запустить любой SQL-запрос к возвращаемым данным или вставить его в таблицу SQL Server. Дополнительные сведения о работе с данными JSON в ядро СУБД SQL Server см. в статье JSON data in SQL Server.

Функция OPENJSON принимает один объект JSON или коллекцию объектов JSON и преобразует их в одну или несколько строк. По умолчанию OPENJSON функция возвращает следующие данные:

  • Из объекта JSON функция возвращает все пары "ключ-значение", которые она находит на первом уровне.
  • Из массива JSON она возвращает все элементы массива вместе с их индексами.

Можно добавить необязательное WITH предложение, чтобы предоставить схему, которая явно определяет структуру выходных данных.

OPENJSON с выходными данными по умолчанию

При использовании OPENJSON функции без предоставления явной схемы для результатов ( то есть WITH без предложения после OPENJSON - функция возвращает таблицу со следующими тремя столбцами:

  1. Свойство name входного объекта (или индекс элемента в входном массиве).
  2. Свойство value или элемент массива.
  3. ( type например, строка, число, логическое значение, массив или объект).

OPENJSON возвращает каждое свойство объекта JSON или каждый элемент массива в виде отдельной строки.

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

DECLARE @json NVARCHAR(MAX);

SET @json='{ "name": "John", "surname": "Doe", "age": 45, "skills": [ "SQL", "C#", "MVC" ]}';

SELECT *
FROM OPENJSON(@json);

Результирующий набор:

key значение type
name John 1
surname Doe 1
age 45 2
skills [ "SQL" ,"C#" ,"MVC" ] 4

Дополнительные сведения и примеры см. в разделе "Использование OPENJSON" с схемой по умолчанию.

Сведения о синтаксисе и использовании см. в разделе OPENJSON.

Выходные данные OPENJSON с явной структурой

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

В следующем примере используется OPENJSON схема выходных данных, явно указанных в предложении WITH .

DECLARE @json NVARCHAR(MAX);

SET @json = N'[
    {
        "Order": {
            "Number": "SO43659",
            "Date": "2024-05-31T00:00:00"
        },
        "AccountNumber": "AW29825",
        "Item": {
            "Price": 2024.9940,
            "Quantity": 1
        }
    },
    {
        "Order": {
            "Number": "SO43661",
            "Date": "2024-06-01T00:00:00"
        },
        "AccountNumber": "AW73565",
        "Item": {
            "Price": 2024.9940,
            "Quantity": 3
        }
    }
]';

SELECT *
FROM OPENJSON(@json) WITH (
    Number VARCHAR(200) '$.Order.Number',
    DATE DATETIME '$.Order.Date',
    Customer VARCHAR(200) '$.AccountNumber',
    Quantity INT '$.Item.Quantity'
);

Результирующий набор:

Число Дата Клиент Количество
SO43659 2024-05-31T00:00:00 AW29825 1
SO43661 2024-06-01T00:00:00 AW73565 3

Эта функция возвращает и форматирует элементы массива JSON.

  • Для каждого элемента в массиве OPENJSON JSON создается новая строка в выходной таблице. Два элемента в массиве JSON конвертируются в две строки в таблице результатов.

  • Для каждого столбца, указанного с помощью синтаксиса colName type json_path , OPENJSON преобразует значение, найденное в каждом элементе массива по указанному пути к указанному типу. В этом примере значения для столбца Date берутся из каждого элемента в пути $.Order.Date и преобразуются в значения даты и времени.

Дополнительные сведения и примеры см. в разделе "Использование OPENJSON" с явной схемой (SQL Server).

Сведения о синтаксисе и использовании см. в разделе OPENJSON.

OPENJSON необходим уровень совместимости 130

Функция OPENJSON доступна только на уровне 130 совместимости и больше. Если уровень совместимости базы данных ниже130, SQL Server не может найти и запустить функциюOPENJSON. Другие встроенные функции JSON доступны на всех уровнях совместимости.

Уровень совместимости можно проверка в sys.databases представлении или в свойствах базы данных, а также изменить уровень совместимости базы данных с помощью следующей команды:

ALTER DATABASE <DatabaseName> SET COMPATIBILITY_LEVEL = 130;

Связанный контент