Що таке SQL/JSON path і JSON_TABLE у PostgreSQL?

Question

Accepted Answer

SQL/JSON path (PostgreSQL 12+) - мова запитів до вмісту JSON за стандартом SQL, схожа на JSONPath: шляхи, фільтри, арифметика.
-- order.data = {"items": [{"sku": "A1", "qty": 2, "price": 100}, {"sku": "B2", "qty": 1, "price": 900}]}

SELECT jsonb_path_query_array(data, '$.items[*] ? (@.price > 500).sku') FROM orders;
-- ["B2"]

SELECT * FROM orders WHERE data @? '$.items[*] ? (@.qty > 1)';   -- є позиція з кількістю > 1
SELECT * FROM orders WHERE data @@ '$.items.size() > 3';         -- предикат

$ - корінь документа, @ - поточний елемент у фільтрі ? (...).
@? - чи повертає шлях хоч щось; @@ - результат предиката. Обидва прискорює GIN-індекс.
Режими lax (за замовчуванням, прощає відсутні ключі) і strict (помилка на відсутніх).

JSON_TABLE (PostgreSQL 17+) перетворює JSON на звичайні рядки й колонки - без ручного jsonb_array_elements і приведень:
SELECT o.id, items.*
FROM orders o,
     JSON_TABLE(o.data, '$.items[*]'
         COLUMNS (
             sku   text    PATH '$.sku',
             qty   int     PATH '$.qty',
             price numeric PATH '$.price'
         )
     ) AS items
WHERE items.qty > 1;

Результат можна з'єднувати, групувати й фільтрувати, як будь-яку таблицю.
Де корисно: розбір відповідей зовнішніх API, збережених у JSON; імпорт вкладених документів у реляційні таблиці; звіти по даних, що зберігаються як JSON.
PostgreSQL 17 також додав функції стандарту JSON_EXISTS, JSON_QUERY, JSON_VALUE - їх зручно використовувати, якщо запити мають бути переносимими між СУБД.
Застереження: якщо такі запити - щоденна робота, а не виняток, дані, ймовірно, варто зберігати в таблицях, а не в JSON.
Докладніше в документації: Мова SQL/JSON path

Що таке SQL/JSON path і JSON_TABLE у PostgreSQL?

Схожі питання