Операторы путей: -> ->> @>

Три семейства операторов

-> возвращает JSONB

SELECT data->'user_id' FROM events;
-- returns JSONB like  42

SELECT data->0 FROM events;
-- first element of a JSONB array

->> возвращает текст

SELECT data->>'user_id'  FROM events;
-- '42'  (TEXT)

SELECT (data->>'user_id')::BIGINT FROM events;
-- 42    (BIGINT)

Извлечение по пути #> (JSONB)

SELECT data #> '{user, address, city}' FROM events;
-- nested JSONB value

Извлечение по пути #>> (TEXT)

SELECT data #>> '{user, address, city}' FROM events;
-- 'Berlin'

Вхождение @>

SELECT * FROM events
WHERE data @> '{"type":"login"}';

-- Works for nested matching too:
WHERE data @> '{"user":{"plan":"pro"}}'

Существование ключа ?

SELECT * FROM events WHERE data ? 'error';
SELECT * FROM events WHERE data ?| ARRAY['a','b'];    -- any of these keys
SELECT * FROM events WHERE data ?& ARRAY['a','b'];    -- all of these keys

Запросы по путям JSONB (jsonb_path_query)

SELECT jsonb_path_query(data, '$.user.tags[*]') FROM events;
SELECT jsonb_path_exists(data, '$.user.tags[*] ? (@ == "admin")') FROM events;

Комбинирование с индексами

CREATE INDEX events_data_gin ON events USING GIN (data);
-- supports @>, ?, ?|, ?&

CREATE INDEX events_data_path_gin ON events USING GIN (data jsonb_path_ops);
-- smaller, faster — but only supports @>

Запросы к массивам внутри JSONB

SELECT * FROM events
WHERE data->'tags' @> '["admin"]'::JSONB;

-- Or via path query:
SELECT * FROM events
WHERE jsonb_path_exists(data, '$.tags[*] ? (@ == "admin")');

Обновление JSONB

UPDATE events
SET data = jsonb_set(data, '{user,plan}', '"pro"')
WHERE id = 1;

UPDATE events SET data = data || '{"new_field":true}'::JSONB;
UPDATE events SET data = data - 'old_field';

Агрегирование JSONB

SELECT jsonb_agg(data) FROM events WHERE user_id = 42;
SELECT jsonb_object_agg(name, value) FROM settings;

Итоги

Быстрая проверка