Operatory ścieżek: -> ->> @>

Trzy rodziny operatorów

-> zwraca JSONB

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

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

->> zwraca tekst

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

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

#> wyodrębnianie ścieżki (JSONB)

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

#>> wyodrębnianie ścieżki (TEXT)

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

@> zawieranie

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

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

? istnienie klucza

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

Zapytania ścieżek JSONB (jsonb_path_query)

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

Łączenie z indeksami

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 @>

Odpytywanie tablic wewnątrz JSONB

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

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

Aktualizowanie 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';

Agregowanie JSONB

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

Podsumowanie

Szybkie sprawdzenie