SQLearnAcademy
←Referencia de funciones
JSONsolo PostgreSQL

JSONB_PATH_QUERY

Saca valores de un JSON con una expresión SQL/JSON path, también con condiciones.

Sintaxis

JSONB_PATH_QUERY(<json>, '<ruta>')
JSONB_PATH_QUERY_FIRST(<json>, '<ruta>')
JSONB_PATH_EXISTS(<json>, '<ruta>')

La ruta empieza por $, que es todo el documento. Después, .<clave> pasa a un campo; [*], a todos los elementos de un array, y ? (@.price > 100) filtra por una condición. Cada valor encontrado pasa a ser una fila aparte del resultado. JSONB_PATH_QUERY_FIRST devuelve solo el primero, y JSONB_PATH_EXISTS dice si se ha encontrado algo.

Un campo por su ruta
SELECT JSONB_PATH_QUERY('{"customer": {"name": "Emma", "city": "London"}}', '$.customer.city') AS city;
city
London
Todos los elementos de un array
SELECT JSONB_PATH_QUERY('{"items": [{"name": "Mouse", "price": 25}, {"name": "Monitor", "price": 240}]}', '$.items[*].name') AS item;
item
Mouse
Monitor
Con una condiciónAvanzado
SELECT JSONB_PATH_QUERY('{"items": [{"name": "Mouse", "price": 25}, {"name": "Monitor", "price": 240}]}', '$.items[*] ? (@.price > 100).name') AS expensive_item,
JSONB_PATH_EXISTS('{"items": [{"name": "Mouse", "price": 25}, {"name": "Monitor", "price": 240}]}', '$.items[*] ? (@.price > 1000)') AS has_over_1000;
expensive_itemhas_over_1000
Monitorfalse

@ es el elemento actual: de los productos quedan los que tienen un precio mayor que 100.

Ten en cuenta

El resultado es jsonb, así que la comparación con texto se rompe: JSONB_PATH_QUERY(…) = 'London' da invalid input syntax for type json. Conviértelo en texto: JSONB_PATH_QUERY(…) #>> '{}'.

Una ruta sin coincidencias da cero filas, no NULL, así que la fila de la tabla desaparece del resultado. Un solo valor o NULL lo devuelve JSONB_PATH_QUERY_FIRST.

Una clave con espacio va entre comillas dobles: '$."order id"'. Y con la palabra strict al principio de la ruta, una clave que falta ya es un error: JSON object does not contain key.