json_set / json_insert / json_replace / json_remove în SQLite

json_set(json, cale, valoare [, cale, valoare ...])
json_insert(json, cale, valoare [, ...])
json_replace(json, cale, valoare [, ...])
json_remove(json, cale [, cale ...])

Ce face json_set / json_insert / json_replace / json_remove

Funcțiile acestea nu modifică nimic singure: primesc un text JSON și întorc un text JSON nou. Ca să schimbi coloana, le folosești într-un UPDATE ... SET coloana = json_set(coloana, ...). Poți da mai multe perechi cale–valoare într-un singur apel.

Diferența dintre ele este ce fac când cheia există sau nu: json_set creează cheia dacă lipsește și o înlocuiește dacă există. json_insert doar creează (nu atinge valorile existente). json_replace doar înlocuiește (nu creează chei noi). json_remove șterge cheile sau elementele date. Calea '$[#]' înseamnă „după ultimul element” și adaugă la sfârșitul unui tablou.

Valoarea dată este transformată în JSON: un număr rămâne număr, iar un text devine text JSON, între ghilimele. Dacă vrei să pui un obiect sau un tablou, folosește json('...'), json_object() sau json_array(), altfel obiectul ajunge în document ca text.

Parametri

Parametru	Descriere
json	Documentul de pornire.
cale	Unde se face modificarea: '$.cheie', '$.tablou[0]', '$.tablou[#]' (la final).
valoare	Valoarea nouă; pentru obiecte și tablouri, trece-o prin json().

Ce returnează

Documentul JSON modificat, ca text.

Exemple

Exemplele de pe această pagină pornesc de la datele de mai jos. Rulează mai întâi acest cod, apoi exemplul.

CREATE TABLE profiluri (
  utilizator TEXT PRIMARY KEY,
  setari     TEXT NOT NULL
);
INSERT INTO profiluri VALUES
  ('ana',  '{"tema": "luminos", "limba": "ro", "notificari": true}'),
  ('vlad', '{"tema": "intunecat", "limba": "en"}');

Exemplul 1: Ce face fiecare funcție pe același document

Documentul are deja a, dar nu are b. json_insert adaugă doar b, json_replace schimbă doar a, json_set le face pe amândouă.

SELECT json_insert('{"a": 1}', '$.a', 2, '$.b', 3)  AS cu_insert,
       json_replace('{"a": 1}', '$.a', 2, '$.b', 3) AS cu_replace,
       json_set('{"a": 1}', '$.a', 2, '$.b', 3)     AS cu_set,
       json_remove('{"a": 1, "b": 2}', '$.b')       AS cu_remove;

Rezultat

╭───────────────┬────────────┬───────────────┬───────────╮
│   cu_insert   │ cu_replace │    cu_set     │ cu_remove │
╞═══════════════╪════════════╪═══════════════╪═══════════╡
│ {"a":1,"b":3} │ {"a":2}    │ {"a":2,"b":3} │ {"a":1}   │
╰───────────────┴────────────┴───────────────┴───────────╯

Exemplul 2: UPDATE pe o coloană JSON

Ana trece pe tema întunecată și primește o cheie nouă, font; lui Vlad i se șterge limba. Valoarea true din documentul Anei apare ca true, pentru că documentul este rescris ca JSON compact.

UPDATE profiluri
  SET setari = json_set(setari, '$.tema', 'intunecat', '$.font', 16)
  WHERE utilizator = 'ana';
UPDATE profiluri
  SET setari = json_remove(setari, '$.limba')
  WHERE utilizator = 'vlad';

SELECT utilizator, setari FROM profiluri ORDER BY utilizator;

Rezultat

╭────────────┬───────────────────────────────────────────────────────────────╮
│ utilizator │                            setari                             │
╞════════════╪═══════════════════════════════════════════════════════════════╡
│ ana        │ {"tema":"intunecat","limba":"ro","notificari":true,"font":16} │
│ vlad       │ {"tema":"intunecat"}                                          │
╰────────────┴───────────────────────────────────────────────────────────────╯

Exemplul 3: Adăugare la tablou și obiecte imbricate

'$[#]' adaugă la sfârșitul tabloului. În al doilea rând se vede diferența: textul '{"oras": "Iași"}' este pus ca text (cu ghilimele escapate), iar json('...') îl pune ca obiect.

SELECT json_set('["sqlite", "json"]', '$[#]', 'nou') AS tablou_nou;

SELECT json_set('{}', '$.adresa', '{"oras": "Iași"}')       AS ca_text,
       json_set('{}', '$.adresa', json('{"oras": "Iași"}')) AS ca_obiect;

Rezultat

╭─────────────────────────╮
│       tablou_nou        │
╞═════════════════════════╡
│ ["sqlite","json","nou"] │
╰─────────────────────────╯
╭───────────────────────────────────┬────────────────────────────╮
│              ca_text              │         ca_obiect          │
╞═══════════════════════════════════╪════════════════════════════╡
│ {"adresa":"{\"oras\": \"Iași\"}"} │ {"adresa":{"oras":"Iași"}} │
╰───────────────────────────────────┴────────────────────────────╯

Greșeli frecvente

• Apelezi SELECT json_set(setari, ...) FROM profiluri și te aștepți ca tabelul să se schimbe. Funcția doar întoarce documentul nou; pentru a-l salva ai nevoie de UPDATE ... SET setari = json_set(...).
• Folosești json_insert ca să schimbi o valoare existentă. Nu se întâmplă nimic, pentru că json_insert nu suprascrie; folosește json_set sau json_replace.
• Pui un obiect scris ca text fără json(). Rezultatul are \" peste tot și nu mai poate fi citit cu căi de tipul $.adresa.oras.

Comenzi înrudite

• json_extract: Citește o valoare dintr-un text JSON după o cale de forma $.livrare.cost sau $.produse[0].nume.
• json_group_array / json_group_object: Funcții de agregare care adună valorile dintr-un grup de rânduri într-un tablou sau obiect JSON, gata de trimis unui API.
• Operatorii -> și ->>: Prescurtări pentru citirea din JSON: -> întoarce o bucată de JSON, ->> întoarce valoarea SQL, fără ghilimele.