Aide-mémoire PostgreSQL JSON et JSONB

PostgreSQL prend en charge nativement les données JSON via json (stocke le texte brut) et jsonb (format binaire décomposé supportant l'indexation GIN et les recherches rapides).

Opérateurs d'extraction

La flèche simple (->, #>) renvoie json/jsonb. La double flèche (->>, #>>) renvoie text.

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

Exemples de requêtes par chemin

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

---

Opérateurs booléens et d'inclusion (jsonb uniquement)

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

---

Modification et mise à jour de données JSONB

Concaténation et suppression de clés

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

Fonction 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;

---

Fonctions d'agrégation et de construction

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

---

Références

• Documentation des fonctions JSON de PostgreSQL