Урок 3.6 · Время чтения: ~8 мин

JSON-функции позволяют запросу заглянуть внутрь JSON-документа, хранящегося в столбце: прочитать значение по пути, отобрать по нему строки, посчитать элементы массива или превратить строки в JSON для приложения. В этом уроке вы будете читать значения через JSON_VALUE, JSON_EXTRACT и оператор ->>, фильтровать по ним, работать с массивами и собирать JSON с помощью JSON_OBJECT и JSON_ARRAYAGG. Примеры выполняются в нескольких базах данных сайта, потому что в каждой СУБД эти функции называются немного по-разному.

JSON-функции в SQL

JSON в столбце таблицы

Бывает, что у строк есть атрибуты, которые отличаются от строки к строке, и таблица хранит их одним JSON-документом. Так устроена таблица machine в базе SQLite:

В MySQL и MariaDB есть тип JSON, в PostgreSQL — json и jsonb, SQLite и SQL Server хранят JSON как текст. Во всех них JSON-функции работают с документом как со структурированными данными, а не как с обычной строкой.

Чтение значения по пути

Значение внутри документа задаётся путём: $ — весь документ, $.acquired — ключ acquired, $[0] — первый элемент массива, $[0].source — ключ source этого элемента.

SQLite:

SELECT name, json_extract(details, '$.acquired') AS acquired
FROM machine;

Результат: WY401 — 2023-05-01, Inphormex — 2021-07-15, AutoPlate 9000 — NULL. Отсутствующий ключ даёт NULL, а не ошибку.

В SQLite и MySQL есть и краткая форма — оператор ->>: details ->> '$.acquired' возвращает те же значения.

В MySQL и MariaDB есть две функции, и разница между ними важна:

SELECT JSON_EXTRACT('{ "title": "ACADEMY DINOSAUR", "year": 2006}', '$.title') AS extracted,
       JSON_VALUE('{ "title": "ACADEMY DINOSAUR", "year": 2006}', '$.title') AS value;

Результат: в extracted — "ACADEMY DINOSAUR" с кавычками, это всё ещё фрагмент JSON; в value — просто ACADEMY DINOSAUR.

JSON_EXTRACT возвращает JSON, поэтому для сравнения со строкой его оборачивают в JSON_UNQUOTE. JSON_VALUE сразу возвращает обычное значение; в SQL Server используется та же функция.

Отбор строк по значению внутри JSON

JSON-функцию можно использовать в WHERE, как любое другое выражение. Раз отсутствующий ключ даёт NULL, IS NULL и IS NOT NULL находят документы без ключа и с ним.

SQLite:

SELECT name, json_extract(details, '$.refurbished') AS refurbished
FROM machine
WHERE json_extract(details, '$.refurbished') IS NOT NULL;

Результат: одна машина, Inphormex, восстановлена 2023-10-22.

JSON-массивы

В базе University (MariaDB) столбец research_projects.funding хранит массив грантов: [{ "source": "Private Foundation", "amount": 244542.96, ...}] или [] у проекта без финансирования. JSON_LENGTH считает элементы, а путь с индексом читает один из них:

SELECT project_id, JSON_LENGTH(funding) AS grants,
       JSON_UNQUOTE(JSON_EXTRACT(funding, '$[0].source')) AS first_source
FROM research_projects
ORDER BY project_id
LIMIT 3;

Результат: у проекта 1 один грант от Private Foundation; у проектов 2 и 3 грантов 0, а первый источник — NULL.

Чтобы проверить, есть ли значение хоть в одном элементе массива, в MySQL и MariaDB есть JSON_SEARCH: он возвращает путь к найденному значению или NULL.

SELECT COUNT(*) AS nasa_projects
FROM research_projects
WHERE JSON_SEARCH(funding, 'one', 'NASA', NULL, '$[*].source') IS NOT NULL;

Результат: у 30 проектов есть грант от NASA. `$[].source` означает «source каждого элемента».*

PostgreSQL: операторы -> и ->>

PostgreSQL читает JSON операторами, а не путями: -> возвращает значение JSON, ->> — текст. В базе Bookings названия аэропортов хранятся на нескольких языках в виде jsonb, например { "en": "Yakutsk Airport", "ru": "Якутск"}:

SELECT airport_code, airport_name ->> 'en' AS name, city ->> 'en' AS city
FROM airports_data
ORDER BY airport_code
LIMIT 3;

Результат: AAQ — Anapa Vityazevo Airport, Anapa; ABA — Abakan Airport, Abakan; AER — Sochi International Airport, Sochi.

Сборка JSON из строк

Обратная задача — вернуть результат в виде JSON, например для веб-приложения. JSON_OBJECT собирает объект из пар «ключ, значение».

MySQL, Sakila:

SELECT JSON_OBJECT('id', film_id, 'title', title, 'rating', rating) AS film
FROM film
ORDER BY film_id
LIMIT 2;

Результат: { "id": 1, "title": "ACADEMY DINOSAUR", "rating": "PG"} и { "id": 2, "title": "ACE GOLDFINGER", "rating": "G"}.

JSON_ARRAYAGG — агрегатная функция: она собирает значения многих строк в один массив.

SELECT JSON_ARRAYAGG(name) AS categories
FROM category;

Результат: одна строка с массивом всех 16 категорий: ["Action", "Animation", "Children", ...].

В PostgreSQL то же самое делают json_build_object и json_agg:

SELECT json_build_object('code', airport_code, 'city', city ->> 'en') AS airport
FROM airports_data
ORDER BY airport_code
LIMIT 2;

Результат: { "code" : "AAQ", "city" : "Anapa"} и { "code" : "ABA", "city" : "Abakan"}.

JSON-функции в разных СУБД

Когда хранить JSON

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

Ключевые выводы:

• Путь задаёт значение внутри JSON: $.key, $[0], $[0].key, $[*].key.
• JSON_VALUE (MySQL, MariaDB, SQL Server), json_extract и ->> (SQLite, MySQL) и ->> (PostgreSQL) читают значение как обычное значение.
• JSON_EXTRACT в MySQL и MariaDB возвращает JSON: строка приходит в кавычках, и нужен JSON_UNQUOTE.
• Отсутствующий ключ даёт NULL, поэтому IS NULL и IS NOT NULL отбирают документы по наличию ключа.
• JSON_OBJECT собирает объект из столбцов, JSON_ARRAYAGG собирает строки в массив; в PostgreSQL для этого есть json_build_object и json_agg.

Часто задаваемые вопросы

Чем JSON_EXTRACT отличается от JSON_VALUE?

JSON_EXTRACT возвращает фрагмент JSON: строка приходит с кавычками, объект или массив — как JSON-текст. JSON_VALUE возвращает одно скалярное значение обычным текстом, готовым для сравнения или вывода.

Что возвращает JSON-функция, если ключа нет?

NULL. Поэтому WHERE json_extract(details, '$.refurbished') IS NULL находит документы без этого ключа.

Чем -> отличается от ->> в PostgreSQL?

-> возвращает значение JSON, ->> — текст. -> используют, чтобы пройти глубже в документ, а ->> — чтобы получить итоговое значение.

Вопросы на собеседовании

Когда хранить данные в JSON-столбце, а не в отдельных столбцах?

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

Как найти строки, в JSON-массиве которых есть значение?

В MySQL и MariaDB — через JSON_SEARCH(doc, 'one', 'value', NULL, '$[*].key') IS NOT NULL или JSON_CONTAINS; в PostgreSQL — оператором вхождения @> для jsonb.

Как вернуть результат запроса в виде JSON?

Собрать объекты через JSON_OBJECT (json_build_object в PostgreSQL) и собрать их в массив через JSON_ARRAYAGG (json_agg); в SQL Server к запросу добавляют FOR JSON PATH.