Microsoft SQL Server
OPENJSON

Ottieni chiave: coppie di valori dal testo JSON

La funzione OPENJSON analizza il testo JSON e restituisce tutte le chiavi: coppie di valori al primo livello di JSON:

declare @json NVARCHAR(4000) = N'{"Name":"Joe","age":27,"skills":["C#","SQL"]}';
SELECT * FROM OPENJSON(@json);

Il tipo di colonna descrive il tipo di valore, ad esempio null (0), string (1), number (2), boolean (3), array (4) e object (5).

Trasforma l'array JSON in un insieme di righe

La funzione OPENJSON analizza la raccolta di oggetti JSON e restituisce valori dal testo JSON come set di righe.

declare @json nvarchar(4000) = N'[
  {"Number":"SO43659","Date":"2011-05-31T00:00:00","Customer": "MSFT","Price":59.99,"Quantity":1},
  {"Number":"SO43661","Date":"2011-06-01T00:00:00","Customer":"Nokia","Price":24.99,"Quantity":3}
]'

SELECT    * 
FROM OPENJSON (@json)
    WITH (
          Number   varchar(200),
          Date     datetime,
          Customer varchar(200),
          Quantity int
  )

Nella clausola WITH viene specificato lo schema di restituzione della funzione OPENJSON. Le chiavi negli oggetti JSON sono recuperate per nome di colonna. Se alcune chiavi in ​​JSON non sono specificate nella clausola WITH (ad es. Price in questo esempio) verranno ignorate. I valori vengono automaticamente convertiti in tipi specificati.

Trasforma i campi JSON nidificati in un insieme di righe

La funzione OPENJSON analizza la raccolta di oggetti JSON e restituisce valori dal testo JSON come set di righe. Se i valori nell'oggetto di input sono nidificati, è possibile specificare un parametro di mapping aggiuntivo in ogni colonna nella clausola WITH:

declare @json nvarchar(4000) = N'[
  {"data":{"num":"SO43659","date":"2011-05-31T00:00:00"},"info":{"customer":"MSFT","Price":59.99,"qty":1}},
  {"data":{"number":"SO43661","date":"2011-06-01T00:00:00"},"info":{"customer":"Nokia","Price":24.99,"qty":3}}
]'

SELECT    * 
FROM OPENJSON (@json)
    WITH (
          Number   varchar(200) '$.data.num',
          Date     datetime '$.data.date',
          Customer varchar(200) '$.info.customer',
          Quantity int '$.info.qty',
  )

Nella clausola WITH viene specificato lo schema di restituzione della funzione OPENJSON. Dopo il tipo viene specificato il percorso dei nodi JSON in cui deve essere trovato il valore restituito. Le chiavi negli oggetti JSON vengono recuperate da questi percorsi. I valori vengono automaticamente convertiti in tipi specificati.

Estrazione di oggetti secondari JSON interni

OPENJSON può estrarre frammenti di oggetti JSON all'interno del testo JSON. Nella definizione della colonna che fa riferimento all'oggetto secondario JSON, impostare il tipo nvarchar (max) e l'opzione AS JSON:

declare @json nvarchar(4000) = N'[
  {"Number":"SO43659","Date":"2011-05-31T00:00:00","info":{"customer":"MSFT","Price":59.99,"qty":1}},
  {"Number":"SO43661","Date":"2011-06-01T00:00:00","info":{"customer":"Nokia","Price":24.99,"qty":3}}
]'

SELECT    * 
FROM OPENJSON (@json)
    WITH (
          Number   varchar(200),
          Date     datetime,
          Info nvarchar(max) '$.info' AS JSON
  )

La colonna Info verrà mappata sull'oggetto "Info". I risultati saranno:

Lavorare con i sotto-array JSON nidificati

JSON può avere una struttura complessa con array interni. In questo esempio, abbiamo una matrice di ordini con sub array nidificato di OrderItems.

declare @json nvarchar(4000) = N'[
  {"Number":"SO43659","Date":"2011-05-31T00:00:00",
    "Items":[{"Price":11.99,"Quantity":1},{"Price":12.99,"Quantity":5}]},
  {"Number":"SO43661","Date":"2011-06-01T00:00:00",
    "Items":[{"Price":21.99,"Quantity":3},{"Price":22.99,"Quantity":2},{"Price":23.99,"Quantity":2}]}
]'

Possiamo analizzare le proprietà del livello root usando OPENJSON che restituirà il frammento AS JSON dell'array Items. Quindi possiamo applicare nuovamente OPENJSON sull'array Items e aprire la tabella JSON interna. La tabella del primo livello e la tabella interna saranno "uniti" come nel JOIN tra le tabelle standard:

SELECT    * 
FROM
    OPENJSON (@json)
    WITH (  Number varchar(200), Date datetime,
            Items nvarchar(max) AS JSON )
        CROSS APPLY 
            OPENJSON (Items)
            WITH ( Price float, Quantity int)

risultati: