JSON_EXTRACT

Извлекает значение из JSON-документа по пути.

MySQL 8.1

JSON_EXTRACT(json_doc, path[, ...])

Параметры

json_docJSON: JSON-документ или строка с JSON
pathVARCHAR: Путь к значению: $ — весь документ, $.key — ключ, $[0] — элемент массива

Возвращаемое значение

Значение JSON; NULL, если пути нет в документе или аргумент равен NULL; при нескольких путях — массив найденных значений

Примеры

Элемент вложенного массива и отсутствующий ключ

MySQL 8.1

SELECT JSON_EXTRACT('{"a": {"b": [10, 20]}}', '$.a.b[1]') AS second_item,
	JSON_EXTRACT('{"a": 1}', '$.x') AS missing_key;

second_item	missing_key
20	<NULL>

Промокод из метаданных события

MySQL 8.1

SELECT event_id,
	JSON_EXTRACT(metadata, '$.promo_code') AS promo_json,
	metadata->>'$.promo_code' AS promo
FROM events
WHERE JSON_EXTRACT(metadata, '$.promo_code') IS NOT NULL
LIMIT 5;

event_id	promo_json	promo
1	"SUMMER20"	SUMMER20
3	"NEWYEAR"	NEWYEAR
5	"SUMMER20"	SUMMER20
6	"WELCOME10"	WELCOME10
8	"WELCOME10"	WELCOME10

JSON null и SQL NULL

MySQL 8.1

SELECT event_id,
	metadata,
	JSON_EXTRACT(metadata, '$.error_msg') IS NULL AS is_sql_null,
	metadata->>'$.error_msg' AS error_msg
FROM events
WHERE event_id IN (1, 2);

event_id	metadata	is_sql_null	error_msg
1	{"utm_source": "tiktok", "promo_code": "SUMMER20"}	1	<NULL>
2	{"utm_src": "email", "error_msg": null}	0	null

Подробности

Результат — значение JSON: строки остаются в кавычках, "SUMMER20". Короткая запись для столбца — column->'$.key', а column->>'$.key' ещё и снимает кавычки, как JSON_UNQUOTE. Эти операторы работают только со столбцом, а не с литералом.

Отсутствующий ключ даёт NULL, а ключ со значением null — JSON null, который не равен SQL NULL: IS NULL для него ложно, а ->> вернёт строку null. Строка с некорректным JSON вызывает ошибку. В PostgreSQL для этого используют операторы -> и ->> с типом jsonb, а путь пишут иначе.

Смотрите также

COALESCE CAST