OPENJSON (Transact-SQL)

Si applica a: SQL Server 2016 (13.x) e versioni successive di Istanza gestita di SQL diAzure Istanza gestita di SQL diAzure Azure Synapse Analyticsin Microsoft FabricWarehouse nel database SQL diMicrosoft Fabricin Microsoft Fabric

La OPENJSON funzione con valori di tabella analizza il testo JSON e restituisce oggetti e proprietà dall'input JSON come righe e colonne. In altre parole, OPENJSON fornisce una visualizzazione set di righe su un documento JSON. È possibile specificare in modo esplicito le colonne nel set di righe e i percorsi delle proprietà JSON usate per popolare le colonne. Poiché OPENJSON restituisce un set di righe, è possibile usare OPENJSON nella FROM clausola di un'istruzione Transact-SQL esattamente come è possibile usare qualsiasi altra tabella, vista o funzione con valori di tabella.

Usare OPENJSON per importare dati JSON in SQL Server o per convertire i dati JSON in formato relazionale per un'app o un servizio che non può utilizzare direttamente JSON.

Note

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

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

ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 130

Convenzioni relative alla sintassi Transact-SQL

Syntax

OPENJSON( jsonExpression [ , path ] )  [ <with_clause> ]

<with_clause> ::= WITH ( { colName type [ column_path ] [ AS JSON ] } [ ,...n ] )

La OPENJSON funzione con valori di tabella analizza jsonExpression fornito come primo argomento e restituisce una o più righe contenenti dati dagli oggetti JSON nell'espressione. jsonExpression può contenere oggetti secondari annidati. Se si vuole analizzare un sottooggetto dall'interno di jsonExpression, è possibile specificare un parametro path per il sottooggetto JSON.

openjson

Per impostazione predefinita, la OPENJSON funzione con valori di tabella restituisce tre colonne, che contengono il nome della chiave, il valore e il tipo di ogni key:value coppia trovata in jsonExpression. In alternativa, è possibile specificare in modo esplicito lo schema del set di risultati restituito OPENJSON fornendo with_clause.

with_clause

Il with_clause contiene un elenco di colonne con i relativi tipi da OPENJSON restituire. Per impostazione predefinita, OPENJSON corrisponde alle chiavi in jsonExpression con i nomi di colonna in with_clause (in questo caso, corrisponde alle chiavi implica la distinzione tra maiuscole e minuscole). Se un nome di colonna non corrisponde a un nome di chiave, è possibile fornire un column_path facoltativo, ovvero un'espressione di percorso JSON che fa riferimento a una chiave all'interno di jsonExpression.

Arguments

jsonExpression

Espressione di caratteri Unicode contenente testo JSON.

OPENJSON esegue l'iterazione sugli elementi della matrice o sulle proprietà dell'oggetto nell'espressione JSON e restituisce una riga per ogni elemento o proprietà. L'esempio seguente restituisce ogni proprietà dell'oggetto fornito come jsonExpression:

DECLARE @json NVARCHAR(2048) = N'{
   "String_value": "John",
   "DoublePrecisionFloatingPoint_value": 45,
   "DoublePrecisionFloatingPoint_value": 2.3456,
   "BooleanTrue_value": true,
   "BooleanFalse_value": false,
   "Null_value": null,
   "Array_value": ["a","r","r","a","y"],
   "Object_value": {"obj":"ect"}
}';

SELECT * FROM OpenJson(@json);

Results:

key	value	type
String_value	John	1
DoublePrecisionFloatingPoint_value	45	2
DoublePrecisionFloatingPoint_value	2.3456	2
BooleanTrue_value	true	3
BooleanFalse_value	false	3
Null_value	NULL	0
Array_value	["a","r","r","a","y"]	4
Object_value	{"obj":"ect"}	5

• DoublePrecisionFloatingPoint_value è conforme a IEEE-754.

path

Espressione di percorso JSON facoltativa che fa riferimento a un oggetto o a una matrice all'interno di jsonExpression. OPENJSON cerca nel testo JSON nella posizione specificata e analizza solo il frammento a cui si fa riferimento. Per altre informazioni, vedere Espressioni di percorso JSON.

È possibile specificare una variabile come valore di path. Questa opzione non è supportata in SQL Server 2016 (13.x) e versioni precedenti.

Nell'esempio seguente viene restituito un oggetto annidato specificando il percorso:

DECLARE @json NVARCHAR(4000) = N'{  
      "path": {  
            "to":{  
                 "sub-object":["en-GB", "en-UK","de-AT","es-AR","sr-Cyrl"]  
                 }  
              }  
 }';

SELECT [key], value
FROM OPENJSON(@json,'$.path.to."sub-object"')

Results

Key	Value
0	en-GB
1	en-UK
2	de-AT
3	es-AR
4	sr-Cyrl

Quando OPENJSON si analizza una matrice JSON, la funzione restituisce gli indici degli elementi nel testo JSON come chiavi.

Il confronto usato per la ricerca delle corrispondenze dei passaggi di percorso con le proprietà dell'espressione JSON fa distinzione tra maiuscole e minuscole e non considera le regole di confronto (ovvero è un confronto BIN2).

Valore Identity dell'elemento della matrice

La funzione OPENJSON nel pool SQL serverless di Azure Synapse Analytics può generare automaticamente il valore Identity di ogni riga restituita come risultato. La colonna Identity viene specificata usando l'espressione $.sql:identity() nel percorso JSON dopo la definizione di colonna. La colonna con questo valore nell'espressione del percorso JSON genererà un numero univoco in base 0 per ogni elemento della matrice JSON analizzato dalla funzione. Il valore Identity rappresenta la posizione/indice dell'elemento della matrice.

DECLARE @array VARCHAR(MAX);
SET @array = '[{"month":"Jan", "temp":10},{"month":"Feb", "temp":12},{"month":"Mar", "temp":15},
               {"month":"Apr", "temp":17},{"month":"May", "temp":23},{"month":"Jun", "temp":27}
              ]';

SELECT * FROM OPENJSON(@array)
        WITH (  month VARCHAR(3),
                temp int,
                month_id tinyint '$.sql:identity()') as months

Results

month	temp	month_id
Jan	10	0
Feb	12	1
Mar	15	2
Apr	17	3
May	23	4
Jun	27	5

Il valore Identity è disponibile solo nel pool SQL serverless di Synapse Analytics.

with_clause

Definisce in modo esplicito lo schema di output per la OPENJSON funzione da restituire. Il with_clause facoltativo può contenere gli elementi seguenti:

colName

Nome della colonna di output.

Per impostazione predefinita, OPENJSON usa il nome della colonna per trovare una corrispondenza con una proprietà nel testo JSON. Ad esempio, se si specifica la colonna name nello schema, OPENJSON tenta di popolare questa colonna con la proprietà "name" nel testo JSON. È possibile eseguire l'override di questo mapping predefinito usando l'argomento column_path .

type

Tipo di dati per la colonna di output.

Note

Se si usa anche l'opzione AS JSON , il tipo di dati della colonna deve essere nvarchar(MAX).

column_path

Percorso JSON che specifica la proprietà da restituire nella colonna specificata. Per altre info, vedi la descrizione del parametro path in precedenza in questo argomento.

Usare column_path per eseguire l'override delle regole di mapping predefinite quando il nome di una colonna di output non corrisponde al nome della proprietà.

Il confronto usato per la ricerca delle corrispondenze dei passaggi di percorso con le proprietà dell'espressione JSON fa distinzione tra maiuscole e minuscole e non considera le regole di confronto (ovvero è un confronto BIN2).

Per altre informazioni sui percorsi, vedere Espressioni di percorso JSON.

COME JSON

Usare l'opzione AS JSON in una definizione di colonna per specificare che la proprietà a cui si fa riferimento contiene un oggetto JSON interno o una matrice. Se si specifica l'opzione AS JSON , il tipo della colonna deve essere nvarchar(MAX).

• Se non si specifica AS JSON per una colonna, la funzione restituisce un valore scalare (ad esempio, int, string, true, false) dalla proprietà JSON specificata nel percorso specificato. Se il percorso rappresenta un oggetto o una matrice e la proprietà non può essere trovata nel percorso specificato, la funzione restituisce NULL in lax modalità o restituisce un errore in strict modalità. Questo comportamento è simile al comportamento della JSON_VALUE funzione.

• Se si specifica AS JSON per una colonna, la funzione restituisce un frammento JSON dalla proprietà JSON specificata nel percorso specificato. Se il percorso rappresenta un valore scalare e la proprietà non può essere trovata nel percorso specificato, la funzione restituisce NULL in lax modalità o restituisce un errore in strict modalità. Questo comportamento è simile al comportamento della JSON_QUERY funzione.

Note

Se si vuole restituire un frammento JSON annidato da una proprietà JSON, è necessario fornire il AS JSON flag . Senza questa opzione, se non è possibile trovare la proprietà, OPENJSON restituisce un NULL valore anziché l'oggetto JSON o la matrice a cui si fa riferimento oppure restituisce un errore di runtime in strict modalità .

Ad esempio, la query seguente restituisce e formatta gli elementi di una matrice:

DECLARE @json NVARCHAR(MAX) = N'[  
  {  
    "Order": {  
      "Number":"SO43659",  
      "Date":"2011-05-31T00:00:00"  
    },  
    "AccountNumber":"AW29825",  
    "Item": {  
      "Price":2024.9940,  
      "Quantity":1  
    }  
  },  
  {  
    "Order": {  
      "Number":"SO43661",  
      "Date":"2011-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',  
              [Order]  NVARCHAR(MAX)  AS JSON  
 )

Results

Number	Date	Customer	Quantity	Order
SO43659	2011-05-31T00:00:00	AW29825	1	{"Number":"SO43659","Date":"2011-05-31T00:00:00"}
SO43661	2011-06-01T00:00:00	AW73565	3	{"Number":"SO43661","Date":"2011-06-01T00:00:00"}

Valore restituito

Le colonne restituite dalla OPENJSON funzione dipendono dall'opzione WITH .

• Quando si chiama OPENJSON con lo schema predefinito, ovvero quando non si specifica uno schema esplicito nella WITH clausola , la funzione restituisce una tabella con le colonne seguenti:

  • Key. Valore nvarchar(4000) che contiene il nome della proprietà specificata o l'indice dell'elemento nella matrice specificata. La key colonna ha regole di confronto BIN2.

  • Value. Valore nvarchar(MAX) che contiene il valore della proprietà . La value colonna eredita le regole di confronto da jsonExpression.

  • Type. Valore int che contiene il tipo del valore. La Type colonna viene restituita solo quando si usa OPENJSON con lo schema predefinito. La type colonna ha uno dei valori seguenti:

    Valore della colonna Type	Tipo di dati JSON
    0	null
    1	string
    2	number
    3	true/false
    4	array
    5	object

  Vengono restituite solo le proprietà di primo livello. L'istruzione ha esito negativo se il testo JSON non è formattato correttamente.

• Quando si chiama OPENJSON e si specifica uno schema esplicito nella WITH clausola , la funzione restituisce una tabella con lo schema definito nella WITH clausola .

Note

Le Keycolonne , Valuee Type vengono restituite solo quando si usa OPENJSON con lo schema predefinito e non sono disponibili con uno schema esplicito.

Remarks

json_path usato nel secondo argomento di OPENJSON o in with_clause può iniziare con la lax parola chiave o strict .

• In lax modalità non OPENJSON genera un errore se non è possibile trovare l'oggetto o il valore nel percorso specificato. Se non è possibile trovare il percorso, OPENJSON restituisce un set di risultati vuoto o un NULL valore.
• In strictmodalità OPENJSON restituisce un errore se il percorso non è stato trovato.

Alcuni esempi in questa pagina specificano in modo esplicito la modalità lax percorso o strict. La modalità di percorso è facoltativa. Se non si specifica in modo esplicito una modalità percorso, lax la modalità è quella predefinita. Per altre informazioni sulla modalità percorso e sulle espressioni di percorso, vedere Espressioni di percorso JSON.

I nomi di colonna in with_clause vengono confrontati con le chiavi nel testo JSON. Se si specifica il nome di colonna [Address.Country], viene cercata la corrispondenza con la chiave Address.Country. Se si vuole fare riferimento a una chiave nidificata Country all'interno dell'oggetto Address, è necessario specificare il percorso $.Address.Country nel percorso di colonna.

json_path può contenere chiavi con caratteri alfanumerici. Escape del nome della chiave in json_path con virgolette doppie se sono presenti caratteri speciali nelle chiavi. Ad esempio, $."my key $1".regularKey."key with . dot" corrisponde al valore 1 nel testo JSON seguente:

{
  "my key $1": {
    "regularKey":{
      "key with . dot": 1
    }
  }
}

Examples

Esempio 1: convertire una matrice JSON in una tabella temporanea

L'esempio seguente visualizza un elenco di identificatori come matrice JSON di numeri. La query converte la matrice JSON in una tabella di identificatori e filtra tutti i prodotti con gli ID specificati.

DECLARE @pSearchOptions NVARCHAR(4000) = N'[1,2,3,4]'

SELECT *
FROM products
INNER JOIN OPENJSON(@pSearchOptions) AS productTypes
 ON product.productTypeID = productTypes.value

La query corrisponde all'esempio seguente. Tuttavia, nell'esempio seguente, è necessario incorporare i numeri nella query anziché passarli come parametri.

SELECT *
FROM products
WHERE product.productTypeID IN (1,2,3,4)

Esempio 2: unire le proprietà di due oggetti JSON

L'esempio seguente seleziona un'unione di tutte le proprietà di due oggetti JSON. I due oggetti hanno una proprietà name duplicata. L'esempio usa il valore di chiave per escludere la riga duplicata dai risultati.

DECLARE @json1 NVARCHAR(MAX),@json2 NVARCHAR(MAX)

SET @json1=N'{"name": "John", "surname":"Doe"}'

SET @json2=N'{"name": "John", "age":45}'

SELECT *
FROM OPENJSON(@json1)
UNION ALL
SELECT *
FROM OPENJSON(@json2)
WHERE [key] NOT IN (SELECT [key] FROM OPENJSON(@json1))

Esempio 3: unire in join le righe con i dati JSON memorizzati nelle celle di tabella usando CROSS APPLY

Nell'esempio seguente la tabella SalesOrderHeader ha una colonna di testo SalesReason che contiene una matrice di SalesOrderReasons in formato JSON. Gli SalesOrderReasons oggetti contengono proprietà come Quality e Manufacturer. L'esempio crea un report che unisce ogni riga dell'ordine di vendita ai motivi di vendita correlati. L'operatore OPENJSON espande la matrice JSON di motivi di vendita come se i motivi fossero archiviati in una tabella figlio separata. L'operatore unisce quindi ogni riga dell'ordine CROSS APPLY di vendita alle righe restituite dalla OPENJSON funzione con valori di tabella.

SELECT SalesOrderID,OrderDate,value AS Reason
FROM Sales.SalesOrderHeader
CROSS APPLY OPENJSON(SalesReasons)

Tip

Quando è necessario espandere matrici JSON archiviate in singoli campi e aggiungerle alle righe padre, in genere si usa l'operatore Transact-SQL CROSS APPLY . Per altre informazioni su CROSS APPLY, vedere clausola FROM.

La stessa query può essere riscritta usando OPENJSON con uno schema definito in modo esplicito delle righe da restituire:

SELECT SalesOrderID, OrderDate, value AS Reason  
FROM Sales.SalesOrderHeader  
     CROSS APPLY OPENJSON (SalesReasons) WITH (value NVARCHAR(100) '$')

In questo esempio il percorso $ fa riferimento a ogni elemento nella matrice. Se si vuole eseguire in modo esplicito il cast del valore restituito, è possibile usare questo tipo di query.

Esempio 4: combinare le righe relazionali e gli elementi JSON con CROSS APPLY

La query seguente combina le righe relazionali e gli elementi JSON nei risultati riportati nella tabella seguente.

SELECT store.title, location.street, location.lat, location.long  
FROM store  
CROSS APPLY OPENJSON(store.jsonCol, 'lax $.location')   
     WITH (street VARCHAR(500) ,  postcode VARCHAR(500) '$.postcode' ,  
     lon int '$.geo.longitude', lat int '$.geo.latitude')  
     AS location

Results

title	street	postcode	lon	lat
Mercati alimentari interi	17991 Redmond Way	WA 98052	47.666124	-122.10155
Sears	148th Ave NE	WA 98052	47.63024	-122.141246,17

Esempio 5: importare dati JSON in SQL Server

Nell'esempio seguente viene caricato un intero oggetto JSON in una tabella di SQL Server.

DECLARE @json NVARCHAR(max)  = N'{  
  "id" : 2,  
  "firstName": "John",  
  "lastName": "Smith",  
  "isAlive": true,  
  "age": 25,  
  "dateOfBirth": "2015-03-25T12:00:00",  
  "spouse": null  
  }';  

  INSERT INTO Person  
  SELECT *   
  FROM OPENJSON(@json)  
  WITH (id INT,  
        firstName NVARCHAR(50), lastName NVARCHAR(50),   
        isAlive BIT, age INT,  
        dateOfBirth DATETIME, spouse NVARCHAR(50))

Esempio 6: esempio semplice con contenuto JSON

--simple cross apply example
DECLARE @JSON NVARCHAR(MAX) = N'[
{
"OrderNumber":"SO43659",
"OrderDate":"2011-05-31T00:00:00",
"AccountNumber":"AW29825",
"ItemPrice":2024.9940,
"ItemQuantity":1
},
{
"OrderNumber":"SO43661",
"OrderDate":"2011-06-01T00:00:00",
"AccountNumber":"AW73565",
"ItemPrice":2024.9940,
"ItemQuantity":3
}
]'

SELECT root.[key] AS [Order],TheValues.[key], TheValues.[value]
FROM OPENJSON ( @JSON ) AS root
CROSS APPLY OPENJSON ( root.value) AS TheValues

Contenuti correlati

• Espressioni di percorso JSON
• Analizzare e trasformare i dati JSON con OPENJSON
• Usare OPENJSON con lo schema predefinito
• Usare OPENJSON con uno schema esplicito