Operator Jalur: -> ->> @>

Tiga Keluarga Operator

-> Mengembalikan JSONB

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

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

->> Mengembalikan Teks

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

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

Ekstraksi Jalur #> (JSONB)

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

Ekstraksi Jalur #>> (TEXT)

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

@> Pencakupan

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

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

? Keberadaan Kunci

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

Kueri Jalur JSONB (jsonb_path_query)

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

Menggabungkan dengan Indeks

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

Mengerkueri Larik di Dalam JSONB

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

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

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

Mengagregasikan JSONB

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

Ringkasan

Pemeriksaan Singkat