Tot el curs ha donat per fet que saps quines columnes necessites. I gairebé sempre és cert: una comanda té client, data, estat i ports, i això no canvia. Però hi ha dades que no tenen forma fixa. Un oli d'oliva té acidesa, varietat d'oliva i mètode d'extracció; una crema té ingredients, tipus de pell i volum; un raspall de bambú no té cap d'aquestes coses i sí que té tipus de cerra. Afegir una columna per atribut donaria una taula de vuitanta columnes gairebé totes nul·les, i muntar una taula de parells clau-valor —el clàssic EAV— converteix qualsevol consulta en un trencaclosques d'autounions.

Per a això PostgreSQL té JSONB: una columna que desa un document amb estructura pròpia, indexable i consultable amb SQL. Aquí es tanca la promesa de 08-03 (índexs GIN sobre JSON) i es cobreix el cas de la resposta d'una API, les preferències d'usuari o el registre d'esdeveniments. Veuràs com construir documents, com llegir-los, com modificar-los, com tornar-los a convertir en files per continuar fent servir tot el SQL del curs, i —el més important de la lliçó— què no ha d'anar mai dins d'un JSON.

1. Quan un esquema fix no basta
2. JSON enfront de JSONB
3. Construir documents
4. La columna productes.atributs de BotigaVerda
5. Accedir: ->, ->>, #>, #>>
6. Buscar: contenció, existència i JSONPath
7. Modificar documents
8. Expandir a files: el pont de tornada al SQL
9. Indexació amb GIN
10. La discussió de disseny: què NO va en JSON
11. Errors habituals i consells
12. Exercicis
13. Conclusió del mòdul

Quatre situacions en què el model relacional pur incomoda:

I les tres sortides clàssiques, amb el seu preu:

PostgreSQL té dos tipus, i triar malament es paga.

La regla pràctica: fes servir JSONB tret que necessitis conservar el text exacte. I aquest "tret que" és molt estret: bàsicament, desar la resposta literal d'un servei extern perquè cal verificar una signatura digital o reproduir-la byte a byte. Per a tota la resta, JSONB. La diferència es veu a l'instant:

El json desa el disbarat tal qual —clau a repetida inclosa—; el jsonb normalitza, ordena i es queda amb l'últim valor d'a.

El cas estrella —retornar una comanda sencera amb les seves línies imbricades en una sola fila, que és el que una API necessita— combina els tres últims:

Una fila, una columna, la comanda 1 completa amb els seus 42,10 € en tres línies. Fixa't en dues coses: jsonb_agg admet el seu propi ORDER BY dins dels parèntesis, i les claus surten desordenades respecte de com les vas escriure, perquè és jsonb. Això estalvia a l'aplicació la feina de reassemblar files planes en un objecte imbricat, i és la raó per la qual moltes API modernes retornen directament el que produeix la base de dades — l'enllaç amb 11-05.

I per a un resum compacte, jsonb_object_agg:

Les cinc categories amb vendes i les seves xifres canòniques, en un sol valor.

A partir d'aquí treballem amb una columna nova. No és canònica: és l'exemple d'aquesta lliçó i cap altre mòdul no la fa servir.

Sis productes amb atributs; els altres catorze tenen atributs a NULL, cosa normal en aquest tipus de columna i que les consultes hauran de tenir en compte.

Quatre operadors, i la diferència entre ells és la font d'errors número u amb JSON a PostgreSQL:

La regla mnemotècnica: la fletxa doble >> treu el valor "en cru", com a text. La simple continua retornant JSON, i això permet encadenar: atributs -> 'certificacions' ->> 0 baixa a l'array amb -> i en treu el primer element com a text amb ->>.

Tres coses que cal veure en aquesta taula. La primera: "Espanya" amb cometes no és Espanya; comparar atributs -> 'origen' = 'Espanya' falla perquè a l'esquerra hi ha un jsonb i a la dreta un text — cal fer servir ->>, o comparar contra '"Espanya"'::jsonb. La segona: tot el que surt de ->> és text i cal convertir-ho (::int, ::numeric) per comparar-ho com a número; si no, '200' < '50' és cert perquè compara alfabèticament. La tercera: una clau que no existeix retorna NULL, no un error — còmode, i perillós, perquè una clau mal escrita no es queixa.

Els operadors anteriors extreuen; aquests filtren, i són els que aprofiten l'índex GIN.

Quatre productes vegans. Fixa't en la potència de @>: busca dins d'un array sense desmuntar-lo i sense saber en quina posició és l'element. I SELECT id FROM productes WHERE atributs ? 'volum_ml' retorna 2 files —els productes 6 i 8, els únics amb aquella clau—, que és com es pregunta "quins productes tenen definit aquest atribut".

Per a condicions que @> no pot expressar —comparacions numèriques, filtres dins d'arrays, expressions— PostgreSQL 12 va afegir JSONPath, un llenguatge de rutes a l'estil XPath:

La primera retorna una fila, el producte 8 (oli corporal, 200 ml): el 6 en té 50 i la resta no té la clau. La segona en retorna cinc: cosmos-organic, ecologic-ue, sense-gluten, vega i —en afegir qualsevol producte nou— el que sigui que porti. Llegeix $.volum_ml ? (@ > 100) així: $ és l'arrel del document, .volum_ml baixa a aquella clau, ? (...) és un filtre i @ és el valor actual. Amb [*] es recorren tots els elements d'un array.

I aquí el detall de rendiment que cal interioritzar: un UPDATE sobre una columna JSONB reescriu el document sencer. No existeix "actualitzar una clau"; PostgreSQL crea una versió nova de la fila completa amb el document complet (és MVCC, 09-02). Canviar un booleà en un document de 200 KB escriu 200 KB. La conseqüència pràctica: JSONB és per escriure poc i llegir molt. Si un camp s'actualitza constantment, aquell camp vol ser una columna.

Una nota fina: jsonb_set amb un NULL de SQL en qualsevol argument retorna NULL, no el document sense tocar — i com que és un UPDATE, esborra el document sencer sense avisar. Fes servir COALESCE o jsonb_set(COALESCE(atributs, '{}'::jsonb), ...) quan la columna pugui ser nul·la.

Aquest apartat és el que connecta JSON amb tota la resta del curs: convertir un document en files per poder agrupar, unir, ordenar i aplicar-hi funcions de finestra.

I aquí ja no hi ha res de JSON: és un GROUP BY del mòdul 4 sobre files normals, amb el LATERAL de 07-04 fent de pont. Aquest és exactament el punt: una vegada expandit, un document és una taula més, i tot el que has après en onze lliçons torna a aplicar-se. jsonb_to_recordset va un pas més enllà i produeix columnes tipades directament:

És la forma canònica de rebre una llista de línies de comanda des d'una API i convertir-la en files inseribles amb un sol INSERT ... SELECT — molt més net que els dos arrays paral·lels de sp_confirmar_comanda a 10-04.

Aquí es tanca la promesa de 08-03. Sense índex, cada consulta amb @> recorre la taula sencera i analitza cada document. Amb un índex GIN, no.

El criteri: si només consultes per contenció (@>), jsonb_path_ops, que és més petit i més ràpid. Si necessites preguntar per existència de claus (?), no queda més remei que jsonb_ops. I recorda de 08-03 que un GIN es construeix i es manté a poc a poc: és l'índex d'"escriu poc, llegeix molt", que casualment és també el perfil d'una columna JSONB.

I la tercera via, que moltes vegades és la millor: un B-tree sobre una expressió quan la consulta va sempre per la mateixa clau.

Retorna els productes 8 i 15. Un B-tree sobre (atributs ->> 'origen') és minúscul comparat amb un GIN, suporta rangs i ordenació, i és el correcte quan una clau concreta es consulta molt. Els parèntesis dobles són obligatoris: és un índex d'expressió, dels de 08-02 — i funciona perquè ->> és IMMUTABLE, la condició que 10-04 va explicar.

El més important de la lliçó. JSONB és tan còmode que convida a ficar-ho tot a dins, i aquesta és una decisió que es paga durant anys.

El que NO ha d'anar en un JSON:

• El que es filtra o s'uneix constantment. categoria_id dins d'un JSON converteix un JOIN d'índex en una consulta que cal reescriure amb ->> i convertir a cada ús.
• El que té integritat referencial. Una clau forana no pot apuntar dins d'un document. Si deses {"proveidor_id": 7} i esborren el proveïdor 7, ningú no t'avisa: acabes de perdre la garantia que 01-05 et donava de franc.
• El que en realitat és una taula. Un array de mil comandes dins del document d'un client és una taula comandes disfressada, sense índexs propis, sense poder consultar-se per si sola i reescrita sencera amb cada compra.
• El que té regles. No hi ha NOT NULL, ni UNIQUE, ni CHECK dins d'un JSON: un preu en JSON pot arribar a ser "car" i res no ho impedirà. I el que s'actualitza constantment, perquè cada UPDATE reescriu el document sencer (apartat 7).

L'antipatró: fer servir la base de dades relacional com a magatzem de documents per mandra de modelar. Es reconeix per una taula amb id i una columna dades JSONB que ho conté tot. Funciona el primer mes, i a partir del sisè cada consulta és un castell de ->> i ::numeric, res no té índex, res no té integritat i ningú no sap quines claus existeixen. Si saps quins camps hi ha, són columnes. Si de debò necessites un magatzem de documents, hi ha bases de dades que fan això molt millor.

I la taula que resol el dubte concret:

A BotigaVerda, aplicat: preu, stock i categoria_id són columnes, sense discussió; les línies de comanda són una taula, no un array dins de comandes; i l'acidesa de l'oli o el tipus de pell d'una crema són JSON, perquè cada categoria té els seus i només es mostren a la fitxa del producte.

Nota de dialecte: MySQL 8 té un tipus JSON binari amb -> i ->> (amb la mateixa semàntica de PostgreSQL), JSON_EXTRACT, JSON_TABLE per expandir a files, i no té índexs sobre JSON: s'indexen columnes generades. SQLite porta l'extensió JSON1 compilada per omissió: json_extract(), json_each(), l'operador ->> des de la 3.38, i tot desat com a text. SQL Server emmagatzema JSON en NVARCHAR i el consulta amb JSON_VALUE, JSON_QUERY i OPENJSON per expandir a files, amb índexs sobre columnes calculades. Oracle té un tipus JSON natiu des de la 21c i suporta JSONPath àmpliament. L'estàndard SQL:2016 defineix JSONPath, i per això jsonb_path_query s'assembla tant entre motors; la resta és dialecte pur.

• Confondre -> amb ->>. El primer retorna jsonb ("Espanya", amb cometes) i el segon text (Espanya). atributs -> 'origen' = 'Espanya' no casa mai.
• Comparar números sense convertir. atributs ->> 'volum_ml' > '100' compara text: '50' és més gran que '100'. Converteix sempre: (atributs ->> 'volum_ml')::int.
• Escriure malament una clau. No dona error: retorna NULL i la fila desapareix en silenci. Comprova amb ? quines claus existeixen de debò. I fer servir JSON en lloc de JSONB: sense operadors de contenció, sense índexs GIN i reanalitzant el text a cada accés. JSONB tret que necessitis el text literal exacte.
• Oblidar que un UPDATE reescriu el document sencer. En documents grans i actualitzacions freqüents, això és bloat i feina de VACUUM (09-02).
• jsonb_set sobre una columna NULL. Retorna NULL i esborra el document. COALESCE(atributs, '{}'::jsonb).
• Indexar amb GIN per defecte i no mesurar. Si només fas servir @>, jsonb_path_ops ocupa força menys; i si sempre consultes la mateixa clau, un B-tree sobre l'expressió guanya a tots dos.
• Ficar en JSON alguna cosa que té clau forana. No hi ha integritat referencial dins d'un document, i no n'hi haurà mai. Consell: documenta les claus esperades. Un JSON sense esquema documentat és un camp de text lliure. Si el conjunt de claus és tancat, valida'l amb un CHECK (atributs ?& ARRAY['origen']) o amb l'extensió de validació d'esquemes.
• Consell: comença per columnes i mou a JSON només el que en sobri. A l'inrevés no funciona: treure d'un JSON tres anys de dades a columnes tipades és una migració cara.
• Consell: jsonb_pretty(atributs) per llegir un document a psql. I \x per al mode expandit.

Amb la columna atributs carregada com a l'apartat 4: (1) Llista els productes d'origen espanyol amb la seva acidesa, si en tenen. (2) Compta quants productes hi ha per origen, ordenats de més a menys. (3) Troba tots els que tenen àloe vera entre els seus ingredients, fent servir @>. (4) Afegeix la clau "revisat": false a tots els productes que tinguin atributs, sense trepitjar res del que ja hi ha.

Màrqueting vol el catàleg en JSON per al web: un array amb un objecte per categoria, i dins de cadascun, el nom de la categoria i l'array dels seus productes actius amb id, nom i preu. Escriu-ho amb jsonb_agg i jsonb_build_object, i digues quants elements té l'array exterior.

Decideix, amb la taula de l'apartat 10, on va cada dada i justifica-ho en una frase:

1. La valoració mitjana d'un producte, que es mostra al llistat i permet ordenar.
2. Les dimensions de l'embalatge (alt, ample, fons), que només coneixen alguns productes i només es fan servir en calcular els ports.
3. L'historial de canvis de preu d'un producte.
4. La resposta completa de la passarel·la de pagament en cobrar una comanda.
5. El proveïdor d'un producte.

Solució 1

1 retorna dues files: l'oli (id 1) amb acidesa 0.25 —la que va deixar el jsonb_set de l'apartat 7— i l'arròs (id 2) amb NULL, perquè no té aquella clau. 2 retorna Espanya 2, Portugal 2, Alemanya 1 i França 1: quatre orígens per als sis productes amb atributs. 3 retorna una fila, la crema facial (id 6), i funciona perquè @> busca dins de l'array sense importar la posició. 4 afecta 6 files, i l'ordre del || és la clau: '{"revisat": false}' || atributs fa que guanyi atributs si la clau ja existís, mentre que atributs || '{"revisat": false}' la trepitjaria. És la diferència entre "afegeix si falta" i "força el valor".

Solució 2

L'array exterior té 6 elements, un per categoria, inclosa Complements — que hi apareix amb "productes": [] perquè el seu únic producte, les Càpsules d'espirulina, està descatalogat (actiu = FALSE). Aquest COALESCE(..., '[]'::jsonb) no és cap adorn: sense ell, jsonb_agg sobre zero files retorna NULL i la clau sortiria com a null en lloc de com a array buit, cosa que trencaria qualsevol codi que recorri la llista. És exactament el problema de 04-04 amb SUM sobre el conjunt buit, ara en JSON.

Solució 3

Les cinc respostes surten d'aplicar tres preguntes: es filtra o s'ordena per això? (columna), té vida pròpia? (taula), és opcional, variable i de només lectura conjunta? (JSON).

Tanques el mòdul amb l'última peça de la caixa d'eines:

• JSONB enfront de JSON: binari descompost enfront de text literal. JSONB normalitza claus, elimina duplicats, accedeix ràpid i és l'únic indexable i amb operadors de contenció. La regla: JSONB tret que necessitis el text exacte.
• Construir: to_jsonb, jsonb_build_object, jsonb_build_array, i sobretot els agregats jsonb_agg i jsonb_object_agg, que retornen la comanda 1 completa amb les seves tres línies i els seus 42,10 € en una sola fila — el cas estrella per a una API (11-05).
• Accedir: -> i #> retornen jsonb; ->> i #>> retornen text. Aquesta diferència és la font d'errors número u, juntament amb comparar números sense convertir-los i amb les claus inexistents que retornen NULL en silenci.
• Buscar: @> (contenció, que entra dins dels arrays), ?, ?| i ?& (existència de claus), i JSONPath amb @?, @@ i jsonb_path_query per al que la contenció no pot expressar, com ara $.volum_ml ? (@ > 100).
• Modificar: || fusiona, - esborra, jsonb_set fixa i jsonb_insert afegeix — recordant que un UPDATE reescriu el document sencer, i que per això JSONB és per escriure poc i llegir molt.
• Expandir a files amb jsonb_array_elements, jsonb_each i jsonb_to_recordset és el pont de tornada: una vegada expandit, un document és una taula més i torna a servir tot el SQL del curs — com el recompte de certificacions, amb vega en 4 productes i ecologic-ue en 3.
• Indexar, tancant 08-03: GIN amb jsonb_ops suporta tots els operadors i ocupa més; jsonb_path_ops només @>, @? i @@, però és més petit i més ràpid; i un B-tree sobre (atributs ->> 'clau') guanya a tots dos quan sempre consultes la mateixa clau.
• I la discussió de disseny: fora del JSON tot el que es filtri o s'uneixi, tot el que tingui integritat referencial, tot el que sigui en realitat una taula, tot el que necessiti regles i tot el que s'actualitzi constantment. L'antipatró —fer servir una base relacional com a magatzem de documents per mandra de modelar— es paga amb interessos. Si saps quins camps hi ha, són columnes.

I amb això es tanca el mòdul 10. En sis lliçons has muntat la caixa d'eines completa: vistes que donen nom a una consulta i encapsulen una mètrica, amb les materialitzades per als informes cars; CTE que converteixen tres nivells de subconsultes en passos llegibles, i WITH RECURSIVE per recórrer l'organigrama sencer i la cadena de referits de tres salts; funcions de finestra que agreguen sense col·lapsar i resolen rànquings, acumulats, mitjanes mòbils i top N per grup; procediments on viu per fi la confirmació de comanda amb la seva atomicitat garantida; disparadors que apliquen sols les regles que un CHECK no abasta; i JSON per al que no cap en un esquema fix. Ja no estàs aprenent SQL: l'estàs fent servir.

El que falta no són més funcions, sinó context. Un sistema real no és una consulta ben escrita: és un conjunt de decisions sobre com s'organitza la feina, qui pot veure què, què es mesura i des d'on es crida. Al mòdul 11, Pràctica: casos d'ús reals, veuràs SQL en el seu entorn: els casos d'ús que apareixen una vegada i una altra en qualsevol projecte; les bones pràctiques d'escriptura, nomenclatura i manteniment que separen una base amb la qual es pot treballar d'una que fa por tocar; la seguretat —la injecció SQL i com s'evita de debò, els permisos i els rols que aquest mòdul ha anat remetent lliçó rere lliçó—; el SQL per a l'anàlisi de dades, on les funcions de finestra de 10-03 es converteixen en informes complets; i el SQL en el desenvolupament web, amb els ORM, el pool de connexions i el problema N+1. La caixa d'eines ja és plena; queda aprendre l'ofici.