Які основні оператори є для роботи з jsonb?

Question

Accepted Answer

-- settings = '{"theme": "dark", "notify": {"email": true}, "tags": ["php", "sql"]}'

settings -> 'notify'                 -- {"email": true}   (результат - jsonb)
settings ->> 'theme'                 -- dark              (результат - text)
settings -> 'notify' ->> 'email'     -- true
settings #>> '{notify,email}'        -- true (шлях масивом)
settings -> 'tags' -> 0              -- "php" (елемент масиву за індексом з нуля)

settings @> '{"theme": "dark"}'      -- містить цю пару
settings ? 'theme'                   -- є ключ верхнього рівня
settings ?| array['theme', 'lang']   -- є хоча б один з ключів
settings ?& array['theme', 'lang']   -- є всі ключі

Головна різниця, на якій помиляються: -> повертає jsonb, ->> - text. Для порівняння з рядком чи числом потрібен ->> і приведення типу:
WHERE (data ->> 'price')::numeric > 100
WHERE data ->> 'status' = 'active'

@> (містить) - найкорисніший оператор для фільтрів: його прискорює GIN-індекс, на відміну від порівняння через ->>.
Пастка в PHP: оператори ?, ?|, ?& конфліктують із заповнювачами параметрів PDO - драйвер прийме ? за параметр запиту. Варіанти:

функції-аналоги: jsonb_exists(settings, 'theme'), jsonb_exists_any(), jsonb_exists_all();
подвоєний знак ?? (PDO з PHP 7.4 розуміє його як буквальний ?);
оператор @> там, де він підходить за змістом.

У Laravel запити до JSON-полів пишуть через стрілки в імені колонки - Query Builder перетворює їх на оператори PostgreSQL:
User::where('settings->theme', 'dark')->get();
User::whereJsonContains('settings->tags', 'php')->get();

Докладніше в документації: Оператори jsonb

Які основні оператори є для роботи з jsonb?

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