JSON, array e semi-structured analytics

Il tracciamento eventi cambia più velocemente dello schema: un giorno plan è una stringa, il giorno dopo vive dentro properties, e alcune integrazioni inviano array di prodotti nello stesso payload. JSON, array e semi-structured analytics insegna a interrogare questi dati senza fingere che siano già tabelle perfette.

Una scena da cui partire

Leggi la lezione pensando a una pipeline di eventi reale. Estrarre una chiave JSON o fare UNNEST non è un gesto tecnico neutro: decide quali eventi diventano righe, quali valori nulli sono accettabili e quale schema implicito puoi promettere al team.

• Contesto: Quale payload semi-strutturato contiene il segnale utile?
• Metodo: Quale estrazione rende esplicito lo schema implicito?
• Applicazione: Come proteggeresti il report da chiavi mancanti o array vuoti?

Perché i dati semi-strutturati dominano

Il 73% dei dati raccolti dalle aziende oggi arriva in formato semi-strutturato (JSON, Avro, Parquet, Protobuf). I motivi sono tre:

1. Event streaming. Kafka, Kinesis, Pub/Sub inviano eventi serializzati. Ogni evento è un oggetto JSON con schema flessibile — aggiungere un campo non rompe i consumer esistenti.

2. API esterne. Stripe, Salesforce, Shopify espongono API REST che restituiscono JSON. Salvare il payload grezzo è più veloce e meno fragile che mapparlo in tabelle relazionali che si rompono a ogni cambio di API.

3. Data lakes. Su S3/GCS, i dati vivono come file Parquet o JSON. I query engine moderni (Presto, Trino, Athena, ClickHouse) leggono direttamente i formati semi-strutturati senza ETL preventivo.

SQL, da standard progettato per dati tabellari, si è evoluto per gestire questo mondo. Tre famiglie di funzioni sono essenziali: JSON extraction, array unnesting, e struct/object navigation.

Estrazione da JSON: i dialetti a confronto

Ogni database ha la sua sintassi, ma i concetti sono universali:

Operazione	PostgreSQL	ClickHouse	BigQuery	Snowflake
Estrai campo	payload->>'field' o payload->'field'	JSONExtractString(payload, 'field')	JSON_EXTRACT_SCALAR(payload, '$.field')	payload:field::string o PARSE_JSON()
Estrai nested	payload->'a'->>'b'	JSONExtractString(payload, 'a', 'b')	JSON_EXTRACT(payload, '$.a.b')	payload:a.b::string
Controlla esistenza	payload ? 'field'	JSONHas(payload, 'field')	payload.field IS NOT NULL	IS_NULL_VALUE(payload:field)
Array length	jsonb_array_length(payload->'arr')	JSONLength(payload, 'arr')	ARRAY_LENGTH(JSON_EXTRACT_ARRAY(payload, '$.arr'))	ARRAY_SIZE(payload:arr)

La scelta del dialetto dipende dal data warehouse. Ma la strategia è universale: estrarre colonne dai JSON in una CTE di staging, poi fare analisi relazionale sulle colonne estratte.

WITH parsed_events AS (
  SELECT
    event_id,
    event_time,
    payload->>'user_id' AS user_id,
    payload->>'event' AS event_type,
    (payload->>'revenue')::numeric AS revenue,
    payload->'items' AS items_array
  FROM raw_events
)
SELECT event_type, COUNT(*), SUM(revenue)
FROM parsed_events
GROUP BY event_type;

Array unnesting: da una riga a molte righe

L’operazione più potente e meno intuitiva per i dati semi-strutturati è l’unnesting: esplodere un array in righe separate.

Caso reale: Deliveroo e l’analisi degli ordini

Un ordine Deliveroo contiene un array di items: ogni item ha name, quantity, price. L’ordine è una riga nella tabella orders, ma per l’analisi di prodotto servono metriche per singolo item.

SELECT
  o.order_id,
  o.order_time,
  item->>'name' AS item_name,
  (item->>'quantity')::int AS quantity,
  (item->>'price')::numeric AS price
FROM orders o
CROSS JOIN LATERAL jsonb_array_elements(o.items) AS item;

In PostgreSQL, jsonb_array_elements() esplode l’array JSON in un set di righe, e CROSS JOIN LATERAL le unisce alla riga dell’ordine. Il risultato: ogni item diventa una riga.

In ClickHouse, la sintassi equivalente è ARRAY JOIN:

SELECT
  order_id,
  order_time,
  JSONExtractString(array_join(items), 'name') AS item_name
FROM orders;

Deliveroo ha documentato che l’unnesting degli ordini in items ha permesso di scoprire che il 12% degli item più ordinati rappresentava il 56% del revenue di ristorante — un insight che senza array unnesting richiedeva un processo ETL separato.

Navigazione di oggetti annidati

I dati semi-strutturati hanno spesso profondità > 2 livelli. La sintassi a percorso (path notation) permette di navigarli senza funzioni annidate.

PostgreSQL (operatori freccia):

SELECT
  payload->'device'->>'os' AS os,
  payload->'device'->>'version' AS os_version,
  payload->'user'->'profile'->>'plan' AS subscription_plan
FROM events;

Snowflake (dot notation con casting):

SELECT
  payload:device:os::string AS os,
  payload:device:version::string AS os_version,
  payload:user:profile:plan::string AS subscription_plan
FROM events;

BigQuery (JSONPath-like):

SELECT
  JSON_EXTRACT_SCALAR(payload, '$.device.os') AS os,
  JSON_EXTRACT_SCALAR(payload, '$.user.profile.plan') AS plan
FROM events;

Caso reale: Segment e la pipeline di eventi

Segment (acquisita da Twilio per 3.2 miliardi nel 2020) è nata proprio per risolvere il problema dei dati semi-strutturati. La loro architettura: ogni evento (page view, identify, track) viene serializzato in JSON, inviato a un collector, e distribuito a centinaia di destinazioni (Google Analytics, Mixpanel, database, S3).

Internamente, Segment usa PostgreSQL per l’analisi dei propri dati operativi. Una query tipica del team di data engineering analizza la latenza di delivery per destinazione:

SELECT
  payload->>'destination' AS destination,
  COUNT(*) AS events,
  AVG((payload->>'delivery_latency_ms')::numeric) AS avg_latency_ms
FROM deliveries
WHERE payload->>'status' = 'success'
  AND (payload->>'delivery_latency_ms')::numeric > 1000
GROUP BY destination
ORDER BY avg_latency_ms DESC;

Il vantaggio di salvare il payload JSON grezzo: quando una nuova destinazione viene aggiunta, nessuno schema change è necessario. Nuovi campi nel payload sono automaticamente disponibili per le query, anche retroattivamente sui dati storici.

Laboratorio pratico

Dataset: api_responses con 100.000 payload JSON da un’API di pagamenti.

🖥️ Prova in Sandbox

[SQL]Prova tu

Esplora la struttura delle tabelle nel database: elenca tutte le colonne di 'utenti' e 'ordini'. Usa questa informazione per capire cosa puoi queryare.

-- Esplora la struttura: PRAGMA table_info(utenti);

EseguiCtrl+Enter per eseguireSuggerimentoSoluzione

[SQL]Prova tu

Trova gli utenti che hanno fatto più acquisti (usa COUNT e GROUP BY) e calcola la percentuale di ordini che rappresentano sul totale.

SELECT u.nome, COUNT(o.id) AS num_ordini FROM utenti u JOIN ordini o ON u.id = o.utente_id GROUP BY u.id ORDER BY num_ordini DESC

EseguiCtrl+Enter per eseguireSuggerimentoSoluzione

Ogni payload ha:

{
  "transaction_id": "txn_123",
  "amount": 99.99,
  "currency": "USD",
  "customer": {"email": "...", "country": "US"},
  "line_items": [
    {"sku": "ABC-123", "quantity": 2, "price": 30.00},
    {"sku": "XYZ-789", "quantity": 1, "price": 39.99}
  ],
  "metadata": {"source": "app", "campaign_id": "summer2024"}
}

Livello 1 — Estrazione semplice: Estrai transaction_id, amount, customer.email, metadata.campaign_id dall’array.

Livello 2 — Item analysis: Esplodi line_items e calcola il revenue per SKU più un flag per transazioni con più di 2 item.

Livello 3 — Missing data: Identifica i payload dove metadata.campaign_id è assente o nullo. Calcola la percentuale sul totale.

Checkpoint operativi

1. Quali sono le tre ragioni principali per cui i dati semi-strutturati sono oggi dominanti?

2. Cosa fa l’unnesting di un array e quando è necessario?

3. Perché salvare il payload JSON grezzo (come fa Segment) è vantaggioso rispetto a normalizzare in tabelle relazionali?

---

Riferimenti accademici:

• Melnik, S. et al. (2007). “Dremel: Interactive Analysis of Web-Scale Datasets.” Proceedings of VLDB 2010.
• Stonebraker, M. & Cetintemel, U. (2005). “One Size Fits All: An Idea Whose Time Has Come and Gone.” Proceedings of ICDE 2005.
• PostgreSQL Documentation. (2024). “8.14. JSON Types.” PostgreSQL 16 Manual.

Problema reale

Nel dominio di advanced SQL, JSON, array e semi-structured analytics serve a risolvere questo problema: scrivere query analitiche corrette anche quando grain, finestre, coorti e casi limite diventano complessi. La lezione non va trattata come teoria isolata, ma come un modo per migliorare una scelta concreta con dati, assunzioni esplicite e controlli minimi.

Obiettivo operativo: Comprendere il problema analitico e il contesto decisionale; Applicare esempi, metriche e controlli a casi reali. Se alla fine non sai indicare quale decisione cambia, quale dato osservi e quale errore vuoi evitare, la lezione non è ancora diventata competenza applicata.

Modello concettuale

Fase	Cosa chiarire	Output
Domanda	Quale scelta reale deve migliorare?	Decisione da prendere
Misura	Quale segnale osservabile rappresenta il problema?	Metrica o dato sorgente
Controllo	Quale baseline rende il risultato interpretabile?	Confronto credibile
Azione	Che cosa cambia dopo l’analisi?	Prossimo passo operativo

Il modello concettuale è intenzionalmente semplice: decisione, dato, controllo, azione. Ogni approfondimento tecnico deve rafforzare almeno uno di questi quattro punti.

Formalizzazione rigorosa

Per rendere JSON, array e semi-structured analytics analizzabile, definisci prima l’unità di lavoro: riga, partizione, finestra, join, coorte o metrica temporale. Poi collega questa unità a una metrica osservabile: correttezza, performance, duplicati, grain e stabilità del risultato. Infine dichiara la decisione attesa: query, modello intermedio, test SQL o pattern riusabile.

Elemento	Specifica richiesta
Unità di analisi	riga, partizione, finestra, join, coorte o metrica temporale
Segnale principale	correttezza, performance, duplicati, grain e stabilità del risultato
Baseline	Periodo precedente, gruppo comparabile, benchmark o scenario controfattuale
Decisione	query, modello intermedio, test SQL o pattern riusabile
Rischio	Scambiare un numero disponibile per una prova sufficiente

La formalizzazione e solida quando un altro analista può riprodurre la logica, criticare le assunzioni e ottenere la stessa decisione partendo dagli stessi dati.

Esempio o caso studio

Gli eventi prodotto arrivano con payload JSON diversi per piattaforma e versione dell’app. Prima di aggregare, il team deve estrarre campi, normalizzare array, gestire chiavi mancanti e documentare quali proprietà sono affidabili per analisi ricorrenti.

Evidenza osservata	Lettura prudente	Azione consigliata
Il numero migliora	Potrebbe essere effetto reale o variazione normale	Cercare confronto e segmento
Un segmento cambia più degli altri	La media aggregata nasconde una differenza	Separare coorti o casi d’uso
Il costo cresce insieme al risultato	L’impatto va letto sul margine	Stimare trade-off e sostenibilità

Lab / esercizio

Livello base

Scrivi una scheda di una pagina per JSON, array e semi-structured analytics: decisione da supportare, metrica primaria, baseline, rischio principale e azione se il segnale e confermato.

Livello intermedio

Costruisci una tabella con tre segmenti, periodi o scenari. Per ciascuno indica cosa cambia, quale spiegazione alternativa e plausibile e quale controllo useresti prima di raccomandare un azione.

Livello research-grade

Prepara un decision memo: ipotesi, dati richiesti, criteri di esclusione, controlli di qualità, soglia decisionale, rischio residuo e piano di monitoraggio dopo la decisione.

Dataset e materiali consigliati

Usa warehouse SQL, dati ordini, eventi, sessioni, coorti, revenue e tabelle calendario. Se non hai accesso a dati reali, crea un dataset sintetico con almeno 200 righe, una dimensione temporale, una dimensione segmento e una metrica di outcome.

Errore tipico da evitare

L’errore più comune e usare JSON, array e semi-structured analytics come etichetta invece che come processo. Succede quando il team mostra un grafico senza decisione, una metrica senza baseline, o una conclusione senza indicare quale assunzione potrebbe invalidarla.

La domanda di controllo è: se questo risultato fosse instabile, quale scelta sbaglierei? Se la risposta non è concreta, manca ancora il collegamento tra analisi e azione.

Quiz o checkpoint

1. Quale decisione concreta dovrebbe migliorare questa lezione?
2. Quale unità di analisi rende il problema misurabile?
3. Quale baseline useresti per evitare una lettura ingenua?
4. Quale errore tipico potrebbe cambiare la conclusione?
5. Quale output consegneresti a uno stakeholder non tecnico?

Riepilogo operativo

JSON, array e semi-structured analytics diventa utile quando produce una decisione più chiara, non quando aggiunge terminologia. Usa il framework problema, modello, formalizzazione, esempio, lab e checkpoint per trasformare la lezione in pratica verificabile. Categoria: Decisione. Difficoltà: advanced. Tempo stimato: 18 min.