Analizzare e trasformare dati JSON con OPENJSON

  • Articolo

Si applica a: SQL Server 2016 (13.x) e versioni successive Database SQL di AzureIstanza gestita di SQL di AzureAzure Synapse Analytics

La funzione per i set di righe OPENJSON consente di convertire testo JSON in un set di righe e colonne. Dopo aver trasformato una raccolta JSON in un set di righe con OPENJSON, è possibile eseguire qualsiasi query SQL sui dati restituiti o inserirli in una tabella di SQL Server. Per altre informazioni sull'uso dei dati JSON nel motore di database di SQL Server, vedere Dati JSON in SQL Server.

La funzione OPENJSON accetta un singolo oggetto JSON o una raccolta di oggetti JSON e li trasforma in una o più righe. Per impostazione predefinita la funzione OPENJSON restituisce i dati seguenti:

  • Da un oggetto JSON la funzione restituisce tutte le coppie chiave-valore trovate al primo livello.
  • Da una matrice JSON la funzione restituisce tutti gli elementi della matrice con i rispettivi indici.

È possibile aggiungere una clausola WITH facoltativa per specificare uno schema che definisce in modo esplicito la struttura dell'output.

OPENJSON con l'output predefinito

Quando si usa la funzione OPENJSON senza specificare uno schema esplicito per i risultati, ovvero senza una clausola WITH dopo OPENJSON, la funzione restituisce una tabella con le tre colonne seguenti:

  1. Il name della proprietà nell'oggetto di input o l'indice dell'elemento nella matrice di input.
  2. Il value della proprietà o l'elemento di matrice.
  3. Il type, ad esempio stringa, numero, valore booleano, matrice o oggetto.

OPENJSON restituisce ogni proprietà dell'oggetto JSON o ogni elemento della matrice come riga separata.

L’esempio che segue usa OPENJSON con lo schema predefinito, ovvero senza la clausola WITH facoltativa, e restituisce una riga per ogni proprietà dell'oggetto JSON.

DECLARE @json NVARCHAR(MAX);

SET @json='{ "name": "John", "surname": "Doe", "age": 45, "skills": [ "SQL", "C#", "MVC" ]}';

SELECT *
FROM OPENJSON(@json);

Questo è il set di risultati.

key value type
name John 1
surname Doe 1
age 45 2
skills [ "SQL" ,"C#" ,"MVC" ] 4

Per altre informazioni ed esempi, vedere Usare OPENJSON con lo schema predefinito.

Per la sintassi e l'utilizzo, vedere OPENJSON.

Output di OPENJSON con una struttura esplicita

Quando si specifica uno schema per i risultati tramite la clausola WITH della funzione OPENJSON, la funzione restituisce una tabella che include solo le colonne definite nella clausola WITH. Nella clausola WITH facoltativa specificare un set di colonne di output, i relativi tipi e i percorsi delle proprietà di origine JSON per ogni valore di output. OPENJSON esegue l'iterazione della matrice di oggetti JSON, legge il valore nel percorso specificato per ogni colonna e converte il valore nel tipo specificato.

Nell'esempio seguente OPENJSON viene usato con uno schema per l'output specificato in modo esplicito nella clausola WITH.

DECLARE @json NVARCHAR(MAX);

SET @json = N'[
    {
        "Order": {
            "Number": "SO43659",
            "Date": "2024-05-31T00:00:00"
        },
        "AccountNumber": "AW29825",
        "Item": {
            "Price": 2024.9940,
            "Quantity": 1
        }
    },
    {
        "Order": {
            "Number": "SO43661",
            "Date": "2024-06-01T00:00:00"
        },
        "AccountNumber": "AW73565",
        "Item": {
            "Price": 2024.9940,
            "Quantity": 3
        }
    }
]';

SELECT *
FROM OPENJSON(@json) WITH (
    Number VARCHAR(200) '$.Order.Number',
    DATE DATETIME '$.Order.Date',
    Customer VARCHAR(200) '$.AccountNumber',
    Quantity INT '$.Item.Quantity'
);

Questo è il set di risultati.

Numero Data Customer Quantità
SO43659 2024-05-31T00:00:00 AW29825 1
SO43661 2024-06-01T00:00:00 AW73565 3

Questa funzione restituisce e formatta gli elementi di una matrice JSON.

  • Per ogni elemento nella matrice JSON la funzione OPENJSON genera una nuova riga nella tabella di output. I due elementi nella matrice JSON vengono convertiti in due righe nella tabella restituita.

  • Per ogni colonna, specificata usando la sintassi colName type json_path, OPENJSON converte il valore trovato in ogni elemento della matrice nel percorso specificato del tipo specificato. In questo esempio i valori per la colonna Date sono ricavati da ogni elemento nel percorso $.Order.Date e convertiti in valori datetime.

Per altre informazioni ed esempi, vedere Usare OPENJSON con uno schema esplicito (SQL Server).

Per la sintassi e l'utilizzo, vedere OPENJSON.

OPENJSON richiede il livello di compatibilità 130

La funzione OPENJSON è disponibile solo nel livello di compatibilità 130 e superiore. Se il livello di compatibilità del database è inferiore a 130, SQL Server non riesce a trovare e a eseguire la funzione OPENJSON. Altre funzioni JSON predefinite sono disponibili in tutti i livelli di compatibilità.

È possibile controllare il livello di compatibilità nella vista sys.databases o nelle proprietà del database e modificare il livello di compatibilità di un database usando il comando seguente:

ALTER DATABASE <DatabaseName> SET COMPATIBILITY_LEVEL = 130;

Contenuto correlato