JSON_EXTRACT

Extrae un valor de un documento JSON según una ruta.

MySQL 8.4

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

[ ]parte opcional
...se puede repetir

json_doc: Documento JSON o cadena con JSON
JSON
path: Ruta al valor: $ es todo el documento, $.key una clave, $[0] un elemento de un array
VARCHAR

Valor devuelto

Valor JSON; NULL si la ruta no existe en el documento o si un argumento es NULL; con varias rutas, un array con los valores encontrados

Ejemplos

Elemento de un array anidado y clave inexistente

MySQL 8.4

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>

Código promocional en los metadatos de un evento

MySQL 8.4

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

null de JSON y NULL de SQL

MySQL 8.4

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

Detalles

El resultado es un valor JSON: las cadenas conservan las comillas, "SUMMER20". La forma abreviada para una columna es column->'$.key', y column->>'$.key' además quita las comillas, como JSON_UNQUOTE. Estos operadores solo funcionan con una columna, no con un literal.

Una clave que no existe da NULL, pero una clave con el valor null da el null de JSON, que no es igual al NULL de SQL: IS NULL es falso para él, y ->> devuelve la cadena null. Una cadena con un JSON no válido provoca un error. En PostgreSQL, para esto se usan los operadores -> y ->> con el tipo jsonb, y la ruta se escribe de otra forma.

Véase también

COALESCE CAST