JSON EXTRACT: примеры (SQL)

Примеры использования функции

Базовые примеры извлечения данных из JSON-строки:

DECLARE @json NVARCHAR(MAX) = N'{ "name": "Иван", "age": 30, "city": "Москва" }';

SELECT 
    JSON_EXTRACT(@json, '$.name') as Name,
    JSON_EXTRACT(@json, '$.age') as Age,
    JSON_EXTRACT(@json, '$.city') as City;

Name        | Age | City
----------- | --- | ------
"Иван"     | 30  | "Москва"

Извлечение элемента массива:

DECLARE @json NVARCHAR(MAX) = N'{ "skills": ["SQL", "C#", "JavaScript"] }';

SELECT 
    JSON_EXTRACT(@json, '$.skills') as AllSkills,
    JSON_EXTRACT(@json, '$.skills[0]') as FirstSkill,
    JSON_EXTRACT(@json, '$.skills[2]') as ThirdSkill;

AllSkills               | FirstSkill | ThirdSkill
----------------------- | ---------- | --------------
["SQL","C#","JavaScript"] | "SQL"      | "JavaScript"

Извлечение из вложенной структуры:

DECLARE @json NVARCHAR(MAX) = N'{ "person": { "name": "Мария", "contacts": { "email": "test@mail.com" } } }';

SELECT 
    JSON_EXTRACT(@json, '$.person.name') as PersonName,
    JSON_EXTRACT(@json, '$.person.contacts.email') as Email;

PersonName | Email
---------- | -----------------
"Мария"   | "test@mail.com"

Похожие функции в MS SQL

SQL Server предлагает несколько функций для работы с JSON, каждая из которых решает специфические задачи:

JSON_VALUE извлекает скалярное значение из JSON-документа и возвращает его как SQL-тип данных. В отличие от JSON_EXTRACT, которая всегда возвращает JSON-фрагмент, JSON_VALUE преобразует значение в типы SQL Server (int, nvarchar и т.д.).

JSON_QUERY извлекает объект или массив из JSON-документа. Эта функция аналогична JSON_EXTRACT при работе с нескалярными значениями, но не может извлекать скалярные значения.

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

Выбор функции зависит от задачи: для получения скалярных значений с преобразованием в SQL-типы используют JSON_VALUE; для извлечения объектов или массивов в виде JSON-строк - JSON_EXTRACT или JSON_QUERY; для табличного представления JSON-данных - OPENJSON.

Типичные ошибки при использовании

Ошибка при неправильном формате JSON:

DECLARE @invalid_json NVARCHAR(MAX) = N'{ name: "Иван" }'; -- Нет кавычек у ключа

SELECT JSON_EXTRACT(@invalid_json, '$.name');

NULL

Ошибка при неправильном пути:

DECLARE @json NVARCHAR(MAX) = N'{ "name": "Иван" }';

SELECT JSON_EXTRACT(@json, '$.age'); -- Свойство age отсутствует

NULL

Попытка использовать функцию с не-JSON данными:

DECLARE @text NVARCHAR(MAX) = N'Простой текст';

SELECT JSON_EXTRACT(@text, '$');

NULL

Неверный синтаксис пути:

DECLARE @json NVARCHAR(MAX) = N'{ "data": [1,2,3] }';

SELECT JSON_EXTRACT(@json, '$.data(0)'); -- Неправильные скобки

NULL

Изменения в последних версиях

Функция JSON_EXTRACT появилась в SQL Server 2016 вместе с начальной поддержкой JSON. В последующих версиях существенных изменений в синтаксис или поведение этой функции не вносилось.

Однако в SQL Server 2022 улучшена общая производительность работы с JSON, оптимизировано использование индексов при фильтрации по JSON-полям. Также добавлены новые функции для работы с JSON, такие как JSON_PATH_EXISTS для проверки существования пути, что может использоваться в комбинации с JSON_EXTRACT.

Важное замечание: в документации Microsoft рекомендуется использовать для скалярных значений функцию JSON_VALUE, а для объектов и массивов - JSON_QUERY, так как это обеспечивает лучшую ясность кода и согласованность с другими JSON-функциями.

Расширенные примеры использования

Извлечение данных из массива объектов:

DECLARE @json NVARCHAR(MAX) = N'{
  "employees": [
    {"id": 1, "name": "Анна"},
    {"id": 2, "name": "Петр"},
    {"id": 3, "name": "Сергей"}
  ]
}';

SELECT 
    JSON_EXTRACT(@json, '$.employees[0]') as FirstEmployee,
    JSON_EXTRACT(@json, '$.employees[1].name') as SecondEmployeeName;

FirstEmployee               | SecondEmployeeName
-------------------------- | -------------------
{"id":1,"name":"Анна"}   | "Петр"

Использование в запросе к таблице:

CREATE TABLE #Users (Id INT, JsonData NVARCHAR(MAX));
INSERT INTO #Users VALUES 
(1, N'{"profile": {"name": "Иван", "premium": true}}'),
(2, N'{"profile": {"name": "Ольга", "premium": false}}');

SELECT 
    Id,
    JSON_EXTRACT(JsonData, '$.profile.name') as UserName,
    JSON_EXTRACT(JsonData, '$.profile') as FullProfile
FROM #Users
WHERE JSON_VALUE(JsonData, '$.profile.premium') = 'true';

Id | UserName  | FullProfile
---| --------- | ---------------------------
1  | "Иван"    | {"name":"Иван","premium":true}

Комбинирование с другими JSON-функциями:

DECLARE @json NVARCHAR(MAX) = N'{"orders": [{"id": 101, "items": ["item1", "item2"]}]}';

SELECT 
    JSON_EXTRACT(@json, '$.orders[0].items') as ItemsArray,
    JSON_QUERY(@json, '$.orders[0].items') as ItemsArrayViaQuery,
    JSON_VALUE(JSON_EXTRACT(@json, '$.orders[0].items'), '$[0]') as FirstItemValue;

ItemsArray      | ItemsArrayViaQuery | FirstItemValue
--------------- | ------------------ | ---------------
["item1","item2"] | ["item1","item2"]  | item1

Работа с вложенными структурами:

DECLARE @complex_json NVARCHAR(MAX) = N'{
  "department": {
    "name": "IT",
    "teams": [
      {
        "name": "Backend",
        "members": 5,
        "tools": ["C#", "SQL", "Docker"]
      },
      {
        "name": "Frontend",
        "members": 3
      }
    ]
  }
}';

SELECT
    JSON_EXTRACT(@complex_json, '$.department.name') as DepartmentName,
    JSON_EXTRACT(@complex_json, '$.department.teams[0].tools') as BackendTools,
    JSON_EXTRACT(@complex_json, '$.department.teams[0].tools[2]') as SpecificTool;

DepartmentName | BackendTools       | SpecificTool
-------------- | ------------------ | -------------
"IT"           | ["C#","SQL","Docker"] | "Docker"

Аналоги в других СУБД и языках

Работа с JSON реализована во многих современных СУБД, но с различиями в синтаксисе и возможностях:

MySQL: функция JSON_EXTRACT() имеет аналогичный синтаксис, но поддерживает дополнительные операторы пути, например, $.* для всех элементов. Пример: SELECT JSON_EXTRACT('{"a":1}', '$.a') возвращает 1.

PostgreSQL: операторы -> и ->> для получения JSON-объекта или текстового значения. Пример: SELECT '{"a":1}'::json->'a' возвращает 1 как JSON-значение.

Oracle: метод JSON_VALUE с синтаксисом, похожим на SQL Server. Пример: JSON_VALUE('{"a":1}', '$.a') возвращает 1.

SQLite: функция json_extract() работает аналогично MySQL. Пример: SELECT json_extract('{"a":1}', '$.a') возвращает 1.

JavaScript: точечная нотация или метод JSON.parse(). Пример: JSON.parse('{"a":1}').a возвращает 1.

Главное отличие MS SQL - необходимость явного указания корня документа через $ и строгая типизация через отдельные функции для скалярных и нескалярных значений.