Fișă de referință PostgreSQL JSON și JSONB

PostgreSQL oferă suport nativ pentru date JSON prin json (stochează textul ca atare) și jsonb (format binar descompus ce suportă indexare GIN și interogări rapide).

Operatori de extragere

Săgeata simplă (->, #>) returnează json/jsonb. Săgeata dublă (->>, #>>) returnează text.

SELECT * FROM users WHERE data->>'name' = 'John';
SELECT data->>'name' AS name FROM users;

Exemple de interogări de cale

-- Extract nested data.address.city as text
SELECT data#>>'{address,city}' FROM customers;

-- Extract second element in items array
SELECT order_info->'items'->>1 FROM orders;

---

Operatori booleeni și de incluziune (doar jsonb)

-- Check if JSON object contains a specific top-level key
SELECT * FROM users WHERE data ? 'email';

-- Check if JSON contains all keys in array
SELECT * FROM users WHERE data ?& array['name', 'email'];

-- Check if JSON contains any key in array
SELECT * FROM users WHERE data ?| array['mobile', 'email'];

-- Containment: Left contains Right
SELECT * FROM users WHERE data @> '{"role": "admin"}'::jsonb;

-- Contained by: Left is contained within Right
SELECT * FROM users WHERE data <@ '{"role": "admin", "active": true}'::jsonb;

---

Modificarea și actualizarea datelor JSONB

Concatenare și ștergere chei

-- Append or update key in jsonb object
UPDATE users SET data = data || '{"active": true}'::jsonb WHERE id = 1;

-- Delete key from object
UPDATE users SET data = data - 'temporary_token' WHERE id = 1;

-- Delete element from array by index
UPDATE users SET tags = tags - 1 WHERE id = 1;

-- Delete nested path
UPDATE users SET data = data #- '{profile,avatar}' WHERE id = 1;

Funcția jsonb_set

-- Syntax: jsonb_set(target, path, new_value, create_missing)
UPDATE users
SET data = jsonb_set(data, '{contact,phone}', '"555-0199"', true)
WHERE id = 1;

---

Funcții de agregare și construire

-- Convert rows to JSON array
SELECT json_agg(u) FROM (SELECT id, name FROM users) u;

-- Build JSON object from key-value pairs
SELECT json_build_object('id', id, 'username', name) FROM users;

-- Expand JSON object to table rows
SELECT * FROM json_each('{"a": 1, "b": 2}');
-- Returns columns: key (text), value (json)

---

Referințe

• Documentația funcțiilor JSON din PostgreSQL