معاملات المسار: -> ->> @>

عائلات العوامل الثلاث

-> يعيد 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;

مراجعة

تحقق سريع