json_each / json_tree în SQLite

SELECT ... FROM tabel, json_each(tabel.coloana [, cale]);
SELECT key, value, type, atom, fullkey, path FROM json_tree(json [, cale]);

Ce face json_each / json_tree

json_each și json_tree sunt funcții-tabel: le scrii în FROM, iar ele întorc rânduri. json_each întoarce câte un rând pentru fiecare element de pe primul nivel al unui tablou sau al unui obiect. json_tree merge recursiv prin tot documentul și întoarce câte un rând pentru fiecare valoare, la orice adâncime.

Coloanele cele mai folosite sunt key (cheia din obiect sau indicele din tablou), value (valoarea), type (text, integer, real, true, false, null, object, array), atom (valoarea pentru tipurile simple, NULL pentru obiecte și tablouri) și fullkey (calea completă spre element, de exemplu $.db.port).

Scrisă după un tabel, json_each(tabel.coloana) se calculează pentru fiecare rând al tabelului, ca un JOIN. Așa „desfaci” o listă de etichete păstrată într-o coloană JSON în rânduri separate. Al doilea argument, opțional, este o cale: atunci se parcurge doar acea parte a documentului.

Parametri

Parametru	Descriere
json	Textul JSON parcurs; de obicei o coloană din tabelul de dinainte din FROM.
cale	Opțional: partea din document de la care începe parcurgerea.

Ce returnează

Un tabel cu coloanele key, value, type, atom, id, parent, fullkey și path.

Exemple

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

CREATE TABLE articole (
  id        INTEGER PRIMARY KEY,
  titlu     TEXT NOT NULL,
  etichete  TEXT NOT NULL    -- tablou JSON de texte
);
INSERT INTO articole (titlu, etichete) VALUES
  ('Primii pași în SQLite',     '["sqlite", "baze-de-date", "incepatori"]'),
  ('JSON în baze de date',      '["sqlite", "json"]'),
  ('Python pentru începători',  '["python", "incepatori"]');

Exemplul 1: Fiecare etichetă pe un rând

Virgula din FROM articole a, json_each(a.etichete) e leagă fiecare articol de elementele propriului tablou. key este poziția etichetei în tablou.

SELECT a.titlu, e.key AS pozitie, e.value AS eticheta
FROM articole a, json_each(a.etichete) e
ORDER BY a.id, e.key;

Rezultat

╭──────────────────────────┬─────────┬──────────────╮
│          titlu           │ pozitie │   eticheta   │
╞══════════════════════════╪═════════╪══════════════╡
│ Primii pași în SQLite    │       0 │ sqlite       │
│ Primii pași în SQLite    │       1 │ baze-de-date │
│ Primii pași în SQLite    │       2 │ incepatori   │
│ JSON în baze de date     │       0 │ sqlite       │
│ JSON în baze de date     │       1 │ json         │
│ Python pentru începători │       0 │ python       │
│ Python pentru începători │       1 │ incepatori   │
╰──────────────────────────┴─────────┴──────────────╯

Exemplul 2: Câte articole are fiecare etichetă

După ce etichetele sunt rânduri, le poți grupa și filtra ca pe o coloană obișnuită. A doua interogare găsește articolele care au eticheta „incepatori”.

SELECT e.value AS eticheta, COUNT(*) AS articole
FROM articole a, json_each(a.etichete) e
GROUP BY e.value
ORDER BY articole DESC, eticheta;

SELECT titlu FROM articole
WHERE EXISTS (SELECT 1 FROM json_each(etichete) WHERE value = 'incepatori')
ORDER BY titlu;

Rezultat

╭──────────────┬──────────╮
│   eticheta   │ articole │
╞══════════════╪══════════╡
│ incepatori   │        2 │
│ sqlite       │        2 │
│ baze-de-date │        1 │
│ json         │        1 │
│ python       │        1 │
╰──────────────┴──────────╯
╭──────────────────────────╮
│          titlu           │
╞══════════════════════════╡
│ Primii pași în SQLite    │
│ Python pentru începători │
╰──────────────────────────╯

Exemplul 3: Un obiect cu json_each, tot documentul cu json_tree

Pe un obiect, json_each dă câte un rând pentru fiecare cheie. json_tree coboară și în obiectul db și în tabloul limbi; condiția atom IS NOT NULL păstrează doar valorile simple.

SELECT key, value, type
FROM json_each('{"oras": "Iași", "cod": 700001, "activ": true, "taguri": ["a", "b"]}');

SELECT fullkey, value, type
FROM json_tree('{"site": "dexblog", "db": {"host": "localhost", "port": 5432}, "limbi": ["ro", "en"]}')
WHERE atom IS NOT NULL;

Rezultat

╭────────┬───────────┬─────────╮
│  key   │   value   │  type   │
╞════════╪═══════════╪═════════╡
│ oras   │ Iași      │ text    │
│ cod    │    700001 │ integer │
│ activ  │         1 │ true    │
│ taguri │ ["a","b"] │ array   │
╰────────┴───────────┴─────────╯
╭────────────┬───────────┬─────────╮
│  fullkey   │   value   │  type   │
╞════════════╪═══════════╪═════════╡
│ $.site     │ dexblog   │ text    │
│ $.db.host  │ localhost │ text    │
│ $.db.port  │      5432 │ integer │
│ $.limbi[0] │ ro        │ text    │
│ $.limbi[1] │ en        │ text    │
╰────────────┴───────────┴─────────╯

Greșeli frecvente

• Uiți că true și false devin 1 și 0 în coloana value. Ca să deosebești un 1 adevărat de valoarea true, uită-te la coloana type.
• Te bazezi pe ordinea rândurilor din json_each fără ORDER BY. În practică vin în ordinea din document, dar dacă ordinea contează, sortează după key (pentru tablouri) sau după id.
• Pui o listă de valori în JSON doar ca să o cauți des. Dacă interoghezi mereu elementele, un tabel separat (articol–etichetă) cu index este mai simplu și mai rapid.

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.
• WITH RECURSIVE: Un CTE care se folosește pe el însuși: generează serii de numere sau de date și parcurge ierarhii (șef–subordonat, categorii).