Leggere un JSON con SQL su IBM i

Un campo di un file che contiene un JSON si legge con SQL, senza scrivere un programma che lo scomponga. Le funzioni sono già nel database: JSON_VALUE estrae un valore, JSON_TABLE ne estrae tanti insieme e trasforma gli elenchi in righe.

L'articolo è diviso in quattro livelli. Ognuno parte da dove finisce il precedente, e ci si può fermare quando si ha quello che serve.

Il file dell'esempio

In tutto l'articolo il file si chiama ORDINI_WEB e ha due campi: ID, un numero, e DOC, il JSON di un ordine arrivato dal sito. Nomi e valori sono inventati. I record sono due.

ID 1:

{"ordine":"W-1001",
 "data":"2026-09-28",
 "cliente":{"codice":"C0042"},
 "righe":[{"articolo":"A-17","qta":2,"prezzo":12.50},
          {"articolo":"B-03","qta":1,"prezzo":99.00}]}

ID 2:

{"ordine":"W-1002",
 "data":"2026-09-29",
 "cliente":{"codice":"C0007"}}

Il secondo ordine non ha righe: serve più avanti.

Livello 1: per cominciare

Il path

Per dire a una funzione quale valore si vuole si scrive un path: una stringa che parte da $, il documento intero, e scende di chiave in chiave con il punto.

Le chiavi si scrivono esattamente come nel JSON, maiuscole e minuscole comprese: $.Ordine non trova "ordine".

Leggere un valore

SELECT ID,
       JSON_VALUE(DOC, '$.ordine'         RETURNING VARCHAR(10)) AS ORDINE,
       JSON_VALUE(DOC, '$.cliente.codice' RETURNING VARCHAR(10)) AS CLIENTE
  FROM ORDINI_WEB;

JSON_VALUE riceve il campo e il path. RETURNING dice in che tipo si vuole il risultato: VARCHAR, INTEGER, DECIMAL, DATE e gli altri tipi SQL.

RETURNING va scritto sempre. Senza, il risultato è un CLOB da 2 GB.

Un numero si chiede come numero e una data come data:

SELECT ID,
       JSON_VALUE(DOC, '$.data'             RETURNING DATE)          AS DATA,
       JSON_VALUE(DOC, '$.righe[0].prezzo' RETURNING DECIMAL(9, 2)) AS PRIMO_PREZZO
  FROM ORDINI_WEB;

Il secondo ordine non ha righe, e il prezzo è NULL: un valore che non c'è non è un errore.

Livello 2: intermedio

Tanti valori con JSON_TABLE

Con JSON_VALUE ogni valore è una chiamata. Quando i valori sono più di uno, IBM consiglia JSON_TABLE, che scompone il documento una volta sola e restituisce una tabella con le colonne che si chiedono.

SELECT O.ID, J.*
  FROM ORDINI_WEB O,
       JSON_TABLE(O.DOC, '$'
         COLUMNS (
           ORDINE   VARCHAR(10)  PATH '$.ordine',
           DATA     DATE         PATH '$.data',
           CLIENTE  VARCHAR(10)  PATH '$.cliente.codice'
         )) AS J;

Come si legge la query:

• JSON_TABLE sta nella FROM accanto al file, e riceve il campo O.DOC della riga che si sta leggendo.
• '$' è il punto di partenza di ogni riga del risultato: qui il documento intero, quindi una riga per ordine.
• In COLUMNS ogni colonna ha un nome, un tipo e un path, che parte dal punto di partenza.
• I campi normali del file, come ID, si mettono accanto.

Il PATH va scritto sempre, anche quando la chiave ha lo stesso nome della colonna. Senza, JSON_TABLE cerca la chiave con il nome della colonna in maiuscolo: ordine VARCHAR(10) cerca "ORDINE", e la colonna resta NULL.

Gli elenchi: una riga per elemento

Per avere una riga per ogni riga d'ordine si aggiunge NESTED PATH, che entra nell'elenco righe. [*] vuol dire «tutti gli elementi».

SELECT O.ID, J.*
  FROM ORDINI_WEB O,
       JSON_TABLE(O.DOC, '$'
         COLUMNS (
           ORDINE   VARCHAR(10)  PATH '$.ordine',
           NESTED PATH '$.righe[*]' COLUMNS (
             RIGA      FOR ORDINALITY,
             ARTICOLO  VARCHAR(10)   PATH '$.articolo',
             QTA       INTEGER       PATH '$.qta',
             PREZZO    DECIMAL(9, 2) PATH '$.prezzo'
           )
         )) AS J;

• I dati dell'ordine si ripetono su ogni sua riga.
• RIGA FOR ORDINALITY numera gli elementi dell'elenco, da 1, e riparte a ogni ordine.
• L'ordine W-1002 non ha righe e compare lo stesso, una volta, con i campi della riga a NULL. Per escluderlo si scrive WHERE J.ARTICOLO IS NOT NULL. Il filtro va sull'articolo e non su RIGA, che lì vale 1.

Usare il risultato

Il risultato di JSON_TABLE si usa come una tabella qualunque: WHERE, ORDER BY, GROUP BY. Quando servono solo le righe, il punto di partenza può essere l'elenco stesso. Il totale per articolo:

SELECT J.ARTICOLO, SUM(J.QTA * J.PREZZO) AS TOTALE
  FROM ORDINI_WEB O,
       JSON_TABLE(O.DOC, '$.righe[*]'
         COLUMNS (
           ARTICOLO  VARCHAR(10)   PATH '$.articolo',
           QTA       INTEGER       PATH '$.qta',
           PREZZO    DECIMAL(9, 2) PATH '$.prezzo'
         )) AS J
 GROUP BY J.ARTICOLO;

Qui l'ordine senza righe non produce niente: il punto di partenza è l'elenco, e un elenco che non c'è non dà righe.

Leggerlo in modo efficiente

• Un JSON_TABLE invece di tanti JSON_VALUE. Il documento viene scomposto una volta sola.
• Prima i campi normali. JSON_TABLE viene chiamata una volta per ogni riga del file che si legge. Se la selezione si può fare su un campo normale del file, per esempio una data di caricamento o uno stato, conviene farla lì: i documenti da scomporre diventano meno.
• Estrarre una volta, leggere tante. Se gli stessi valori servono tutti i giorni, conviene copiarli una volta in un file normale, con un INSERT ... SELECT sulla stessa query, e da lì leggerli con gli indici, invece di scomporre il JSON a ogni lettura.

Livello 3: avanzato

Gli errori non fermano la query

Con le impostazioni di serie le funzioni JSON non danno errore. Un valore che non trovano, o che non riescono a convertire nel tipo chiesto, diventa NULL, e un documento scritto male non produce nessuna riga. È comodo, ma un dato sbagliato si presenta come un dato mancante.

Le sezioni che seguono spiegano i casi uno per uno.

Trovare i documenti non validi

SELECT ID
  FROM ORDINI_WEB
 WHERE DOC IS NOT JSON;

Restituisce i record il cui JSON non è valido: sono quelli che mancano nel risultato di JSON_TABLE. Un campo DOC a NULL non compare, né qui né fra quelli validi.

Far diventare errori gli errori

ERROR ON ERROR, scritto dopo COLUMNS (...), fa fallire la query invece di restituire NULL o nessuna riga:

SELECT O.ID, J.*
  FROM ORDINI_WEB O,
       JSON_TABLE(O.DOC, '$'
         COLUMNS (
           ORDINE  VARCHAR(10)  PATH '$.ordine',
           DATA    DATE         PATH '$.data'
         )
         ERROR ON ERROR) AS J;

Una chiave che manca resta NULL anche così: per farla diventare un errore si scrive ERROR ON EMPTY sulla colonna.

Quando invece serve distinguere un dato mancante da uno sbagliato senza fermare la query, si mette un valore di ripiego sulla colonna:

QTA INTEGER PATH '$.qta' NULL ON EMPTY DEFAULT -1 ON ERROR

NULL vuol dire che la chiave non c'è, -1 che c'è ma non è un intero.

Le date

JSON_TABLE e JSON_VALUE accettano quattro formati di data: ISO yyyy-mm-dd, USA mm/dd/yyyy, EUR dd.mm.yyyy e JIS. Il formato italiano con le barre non c'è, e ha la stessa forma di quello USA:

Le date con l'ora nel formato ISO-8601 hanno più varianti, e non tutte passano:

La Z finale, che vuol dire UTC, è molto comune. Una data così si legge come stringa e si converte a parte:

SELECT TIMESTAMP(REPLACE(REPLACE(
         JSON_VALUE('{"t":"2026-09-30T10:15:00Z"}', '$.t' RETURNING VARCHAR(40)),
         'T', ' '), 'Z', ''))
  FROM SYSIBM.SYSDUMMY1;

Restituisce 2026-09-30 10:15:00, l'ora UTC. In JSON_TABLE la colonna si dichiara VARCHAR(40) e la stessa espressione si applica nella SELECT. Prima di confrontarla con l'ora del sistema va portata al suo fuso.

I numeri

• I decimali in più vengono troncati, non arrotondati: 12.345 letto in DECIMAL(9, 2) vale 12.34.
• I numeri con la parte decimale si fermano a 15 cifre significative. Un importo come 1234567.89 arriva esatto, ma 1234567890.123456789 letto in DECIMAL(31, 9) diventa 1234567890.123460000, e 99999999999999.99, che di cifre ne ha 16, diventa 100000000000000.00. Gli interi arrivano esatti, anche a venti cifre, e un decimale scritto fra virgolette nel JSON, come "1234567890.123456789", arriva intero.
• Un numero che non ci sta nel tipo è un errore, quindi di serie NULL.
• true e false in una colonna numerica valgono 1 e 0, in una colonna di caratteri sono le stringhe true e false.

Il path che trova due valori

Un path si può scrivere in due modi: lax, che è quello di serie, e strict. La parola si mette in testa al path, in minuscolo: 'LAX $.ordine' non dà errore, dà NULL.

lax perdona. Se una chiave viene applicata a un elenco, la applica a ogni elemento. Sul primo ordine lax $.righe.articolo trova quindi due articoli, JSON_VALUE ne può restituire uno solo, e il risultato è NULL senza spiegazioni.

strict non perdona, e insieme a ERROR ON ERROR dice che cosa non va:

SELECT JSON_VALUE(DOC, 'strict $.righe.articolo'
                  RETURNING VARCHAR(10) ERROR ON ERROR)
  FROM ORDINI_WEB;

La risposta è SQLSTATE 2203A, SQ16410 SQL/JSON member not found.: la chiave articolo viene cercata sull'elenco, che non ne ha. Il path giusto è $.righe[0].articolo per il primo articolo, oppure JSON_TABLE con NESTED PATH '$.righe[*]' per averli tutti.

Livello 4: esperto

Le cose che seguono servono meno spesso. Sono curiosità, provate sul sistema, che si scoprono lavorandoci.

Il JSON in un file dell'IFS

Se il JSON è un file dell'IFS e non un campo di un file del database, lo si legge con IFS_READ_UTF8 e lo si passa a JSON_TABLE:

SELECT J.*
  FROM TABLE(QSYS2.IFS_READ_UTF8(PATH_NAME   => '/scambi/ordini.json',
                                 END_OF_LINE => 'NONE')) AS F,
       JSON_TABLE(F.LINE, '$.ordini[*]'
         COLUMNS (
           ORDINE  VARCHAR(10)  PATH '$.ordine',
           DATA    DATE         PATH '$.data'
         )) AS J;

• END_OF_LINE => 'NONE' è indispensabile. Senza, IFS_READ_UTF8 restituisce una riga per ogni riga del file: un JSON scritto su più righe arriva a pezzi, nessuno valido, e JSON_TABLE risponde con zero righe.
• IFS_READ_UTF8 non converte i caratteri nel CCSID del job. IFS_READ e GET_CLOB_FROM_FILE invece sì, e GET_CLOB_FROM_FILE deve girare sotto controllo di commit.

• Il BOM rompe la lettura. Alcuni editor mettono in testa ai file UTF-8 tre byte, EF BB BF. Con quei tre byte il JSON non è valido e non esce niente. Si vedono così:

  SELECT HEX(SUBSTR(F.LINE, 1, 3))
    FROM TABLE(QSYS2.IFS_READ_BINARY(PATH_NAME => '/scambi/ordini.json')) AS F;
  

  Se il risultato è EFBBBF, il file si legge in binario saltando i tre byte, e si dichiara FORMAT JSON perché un valore binario verrebbe letto come BSON:

  SELECT JSON_VALUE(CASE WHEN HEX(SUBSTR(F.LINE, 1, 3)) = 'EFBBBF'
                         THEN SUBSTR(F.LINE, 4) ELSE F.LINE END FORMAT JSON,
                    '$.ordini[0].ordine' RETURNING VARCHAR(10))
    FROM TABLE(QSYS2.IFS_READ_BINARY(PATH_NAME => '/scambi/ordini.json')) AS F;
  

  La stessa espressione, con il suo FORMAT JSON, va come primo argomento di JSON_TABLE.

Il JSON da un servizio web

QSYS2.HTTP_GET chiama un indirizzo e restituisce la risposta, che si passa direttamente a JSON_TABLE:

SELECT J.*
  FROM JSON_TABLE(
         QSYS2.HTTP_GET('https://api.example.com/ordini?dal=2026-09-01',
                        '{"headers":{"Accept":"application/json"},
                          "signalErrors":"true",
                          "connectTimeout":"10",
                          "ioTimeout":"30"}'),
         '$.ordini[*]'
         COLUMNS (ORDINE VARCHAR(10) PATH '$.ordine')) AS J;

Il secondo parametro sono le opzioni, scritte a loro volta come JSON. Tre valori di serie vanno cambiati quasi sempre:

Altre cose che si scoprono alla prima chiamata:

• Servono le opzioni 3 e 34 di 5770SS1. Le funzioni di QSYS2 non avviano una JVM, a differenza di quelle di SYSTOOLS come HTTPGETCLOB.
• Per HTTPS l'archivio dei certificati di serie è /QIBM/USERDATA/ICSS/CERT/SERVER/DEFAULT.KDB, che di serie non esiste: si crea con Digital Certificate Manager, oppure se ne indica un altro con sslCertificateStoreFile.
• HTTP_GET_BLOB restituisce un binario: per leggerlo come JSON serve FORMAT JSON.
• Per inviare un JSON con HTTP_POST va impostata l'intestazione Content-Type: senza, parte text/xml.
• Chi può usare queste funzioni lo decidono i diritti sul programma di servizio QSYS/QSQAXISC.

Curiosità del path

E tre cose che non danno errore:

• I filtri non esistono. Un path come $.righe[*]?(@.qta > 1), che altri strumenti accettano, qui restituisce NULL. Il filtro si scrive nel WHERE.
• Le chiavi doppie passano. Se un oggetto ha due volte la stessa chiave ne viene letto un valore solo; nella prova è uscito il secondo, ma IBM non garantisce quale. IS JSON WITH UNIQUE KEYS le trova.
• IS JSON accetta una virgola in coda, come {"a":[1,2], }, che lo standard JSON non ammette. Un documento che supera questo controllo può essere rifiutato da un altro sistema.

Una lista di valori in un parametro

Un JSON può anche essere solo un elenco, come ["C0042","C0007","C0113"]. Da qui un modo di passare a un programma una lista di lunghezza variabile in una variabile sola:

SELECT ID
  FROM ORDINI_WEB
 WHERE JSON_VALUE(DOC, '$.cliente.codice' RETURNING VARCHAR(10))
       IN (SELECT C
             FROM JSON_TABLE(:LISTA, '$[*]'
                    COLUMNS (C VARCHAR(10) PATH '$')) AS J);

Al posto di un IN costruito concatenando stringhe c'è un'istruzione sola, con i valori passati come dato.

Due elenchi allo stesso livello

Se ogni ordine avesse anche un elenco note, letto con un secondo NESTED PATH accanto a quello delle righe, il risultato non sarebbe il prodotto dei due: IBM li unisce con una UNION. Ogni riga ha le colonne delle righe d'ordine oppure quelle delle note, mai entrambe.

JSON_QUERY: un pezzo di JSON

JSON_VALUE restituisce solo valori semplici. Per avere un oggetto o un elenco intero, come testo JSON, c'è JSON_QUERY:

SELECT JSON_QUERY(DOC, '$.cliente' RETURNING VARCHAR(200))
  FROM ORDINI_WEB;

Restituisce {"codice":"C0042"} sul primo ordine. Le stringhe arrivano con le virgolette, e OMIT QUOTES le toglie; se il path trova più valori serve WITH ARRAY WRAPPER, che li mette in un elenco.

Conservare il JSON in BSON

Un tipo di dato JSON non esiste: il JSON sta in un campo di caratteri, con qualunque CCSID tranne 65535. IBM propone anche un'altra forma, il BSON, cioè il JSON in binario, in un campo VARBINARY o BLOB, convertito con JSON_TO_BSON. Le funzioni JSON lavorano internamente su BSON, quindi un documento già in quella forma risparmia una conversione a ogni lettura. Tre avvertenze: non occupa meno spazio, un decimale convertito in BSON si ferma a 15 cifre, e non va mai in un campo binario a lunghezza fissa.

Su quali versioni funziona

JSON_TABLE, JSON_VALUE, JSON_QUERY, IS JSON, IFS_READ_UTF8 e HTTP_GET sono documentate per IBM i 7.4, 7.5 e 7.6. Le pagine non dicono con quale Technology Refresh sono arrivate; per i servizi di QSYS2 lo dice il catalogo del sistema:

SELECT ROUTINE_NAME
  FROM QSYS2.SYSROUTINES
 WHERE ROUTINE_SCHEMA = 'QSYS2'
   AND ROUTINE_NAME IN ('IFS_READ_UTF8', 'HTTP_GET');

Un nome che manca è un servizio che su quel sistema, a quel livello di PTF, non c'è.

Fonti

• JSON concepts, IBM i 7.6
• Using JSON_TABLE, IBM i 7.6
• JSON_TABLE, IBM i 7.6
• JSON_VALUE, IBM i 7.6
• JSON_QUERY, IBM i 7.6
• IS JSON predicate, IBM i 7.6
• sql-json-path-expression, IBM i 7.6
• JSON_TO_BSON, IBM i 7.6
• GET_CLOB_FROM_FILE, IBM i 7.6
• IFS_READ, IFS_READ_BINARY e IFS_READ_UTF8, IBM i 7.6
• HTTP functions overview, IBM i 7.6
• HTTP_GET e HTTP_GET_BLOB, con la tabella delle opzioni, IBM i 7.6