SQLearnAcademy
←Довідник
JSONлише PostgreSQL

JSONB_PATH_QUERY

Дістає з JSON значення за виразом SQL/JSON path — зокрема з умовами.

Синтаксис

JSONB_PATH_QUERY(<json>, '<шлях>')
JSONB_PATH_QUERY_FIRST(<json>, '<шлях>')
JSONB_PATH_EXISTS(<json>, '<шлях>')

Шлях починається з $ — це весь документ. Далі .<ключ> переходить до поля, [*] — до всіх елементів масиву, а ? (@.price > 100) відбирає за умовою. Кожне знайдене значення стає окремим рядком результату. JSONB_PATH_QUERY_FIRST повертає лише перше, а JSONB_PATH_EXISTS відповідає, чи знайшлося щось.

Поле за шляхом
SELECT JSONB_PATH_QUERY('{"customer": {"name": "Emma", "city": "London"}}', '$.customer.city') AS city;
city
London
Усі елементи масиву
SELECT JSONB_PATH_QUERY('{"items": [{"name": "Mouse", "price": 25}, {"name": "Monitor", "price": 240}]}', '$.items[*].name') AS item;
item
Mouse
Monitor
З умовоюСкладніше
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

@ — поточний елемент: з товарів лишаються ті, де ціна більша за 100.

Зверни увагу

Результат — jsonb, тож порівняння з текстом ламається: JSONB_PATH_QUERY(…) = 'London' дасть invalid input syntax for type json. Перетвори на текст: JSONB_PATH_QUERY(…) #>> '{}'.

Шлях без збігу дає нуль рядків, а не NULL, тож рядок таблиці зникне з результату. Одне значення або NULL повертає JSONB_PATH_QUERY_FIRST.

Ключ із пробілом беруть у подвійні лапки: '$."order id"'. А зі словом strict на початку шляху відсутній ключ — уже помилка: JSON object does not contain key.