Convalidare, eseguire query e modificare i dati JSON con funzioni predefinite (SQL Server)

  • Articolo

Si applica a: SQL Server 2016 (13.x) e versioni successive Database SQL diIstanza gestita di SQL di Azure

Il supporto integrato per JSON include le funzioni predefinite seguenti descritte brevemente in questo articolo.

  • ISJSON verifica se una stringa include contenuto JSON valido.
  • JSON_VALUE estrae un valore scalare da una stringa JSON.
  • JSON_QUERY estrae un oggetto o una matrice da una stringa JSON.
  • JSON_MODIFY aggiorna il valore di una proprietà in una stringa JSON e restituisce la stringa JSON aggiornata.

Questo articolo richiede il AdventureWorks2022 database di esempio AdventureWorks, che è possibile scaricare dalla home page del sito relativo a progetti della community ed esempi per Microsoft SQL Server.

Testo JSON per gli esempi in questa pagina

Gli esempi in questa pagina usano il testo JSON simile al contenuto illustrato nell'esempio seguente:

{
    "id": "DesaiFamily",
    "parents": [
        { "familyName": "Desai", "givenName": "Prashanth" },
        { "familyName": "Miller", "givenName": "Helen" }
    ],
    "children": [
        {
            "familyName": "Desai",
            "givenName": "Jesse",
            "gender": "female",
            "grade": 1,
            "pets": [
                { "givenName": "Goofy" },
                { "givenName": "Shadow" }
            ]
        },
        {
            "familyName": "Desai",
            "givenName": "Lisa",
            "gender": "female",
            "grade": 8
        }
    ],
    "address": {
        "state": "NY",
        "county": "Manhattan",
        "city": "NY"
    },
    "creationDate": 1431620462,
    "isRegistered": false
}

Questo documento JSON, che contiene elementi complessi annidati, viene archiviato nella tabella di esempio seguente:

CREATE TABLE Families (
    id INT identity CONSTRAINT PK_JSON_ID PRIMARY KEY,
    doc NVARCHAR(MAX)
);

Convalidare il testo JSON tramite la funzione ISJSON

La funzione ISJSON verifica se una stringa include contenuto JSON valido.

L'esempio seguente restituisce le righe in cui la colonna JSON include testo JSON valido. Senza un vincolo JSON esplicito, è possibile immettere qualsiasi testo nella colonna NVARCHAR:

SELECT *
FROM Families
WHERE ISJSON(doc) > 0;

Per altre informazioni, vedere ISJSON (Transact-SQL).

Estrarre un valore dal testo JSON tramite la funzione JSON_VALUE

La funzione JSON_VALUE estrae un valore scalare da una stringa JSON. La query seguente restituisce i documenti in cui il campo JSON id corrisponde al valore DesaiFamily, ordinati in base ai campi JSON city e state:

SELECT JSON_VALUE(f.doc, '$.id') AS Name,
    JSON_VALUE(f.doc, '$.address.city') AS City,
    JSON_VALUE(f.doc, '$.address.county') AS County
FROM Families f
WHERE JSON_VALUE(f.doc, '$.id') = N'DesaiFamily'
ORDER BY JSON_VALUE(f.doc, '$.address.city') DESC,
    JSON_VALUE(f.doc, '$.address.state') ASC

I risultati di questa query sono riportati nella tabella seguente:

Nome Città Contea
DesaiFamily NY Manhattan

Per altre informazioni, vedere JSON_VALUE (Transact-SQL).

Estrarre un oggetto o una matrice dal testo JSON tramite la funzione JSON_QUERY

La funzione JSON_QUERY estrae un oggetto o una matrice da una stringa JSON. Nell'esempio seguente viene illustrato come restituire un frammento JSON nei risultati della query.

SELECT JSON_QUERY(f.doc, '$.address') AS Address,
    JSON_QUERY(f.doc, '$.parents') AS Parents,
    JSON_QUERY(f.doc, '$.parents[0]') AS Parent0
FROM Families f
WHERE JSON_VALUE(f.doc, '$.id') = N'DesaiFamily';

I risultati di questa query sono riportati nella tabella seguente:

Address Principali Parent0
{ "state": "NY", "county": "Manhattan", "city": "NY" } [ { "familyName": "Desai", "givenName": "Prashanth" }, { "familyName": "Miller", "givenName": "Helen" } ] { "familyName": "Desai", "givenName": "Prashanth" }

Per altre informazioni, vedere JSON_QUERY (Transact-SQL).

Analizzare raccolte JSON annidate

La funzione OPENJSON consente di trasformare la sottomatrice JSON nel set di righe e quindi di unirla in join all'elemento padre. È ad esempio possibile restituire tutti i documenti della famiglia e "unirli in join" ai relativi oggetti children archiviati come una matrice JSON interna:

SELECT JSON_VALUE(f.doc, '$.id') AS Name,
    JSON_VALUE(f.doc, '$.address.city') AS City,
    c.givenName,
    c.grade
FROM Families f
CROSS APPLY OPENJSON(f.doc, '$.children') WITH (
    grade INT,
    givenName NVARCHAR(100)
) c

I risultati di questa query sono riportati nella tabella seguente:

Nome Città givenName grade
DesaiFamily NY Jesse 1
DesaiFamily NY Lisa 8

Si ottengono due righe come risultato perché una riga padre viene unita in join con due righe figlio generate analizzando due elementi della sottomatrice degli elementi figlio. La funzione OPENJSON analizza il frammento children dalla colonna doc e restituisce grade e givenName da ogni elemento come set di righe. Questo set di righe può essere unito in join al documento padre.

Eseguire query in sottomatrici JSON gerarchiche annidate

È possibile applicare più chiamate CROSS APPLY OPENJSON per eseguire query in strutture JSON annidate. Il documento JSON usato in questo esempio include una matrice annidata denominata children, in cui ogni elemento figlio ha una matrice annidata di pets. La query seguente analizza gli elementi figlio di ogni documento, restituirà ogni oggetto della matrice come riga e quindi analizzerà la matrice pets:

SELECT c.familyName,
    c.givenName AS childGivenName,
    p.givenName AS petName
FROM Families f
CROSS APPLY OPENJSON(f.doc) WITH (
    familyName NVARCHAR(100),
    children NVARCHAR(MAX) AS JSON
) AS a
CROSS APPLY OPENJSON(children) WITH (
    familyName NVARCHAR(100),
    givenName NVARCHAR(100),
    pets NVARCHAR(max) AS JSON
) AS c
OUTER APPLY OPENJSON(pets) WITH (givenName NVARCHAR(100)) AS p;

La prima chiamata di OPENJSON restituisce un frammento della matrice childrenusando la clausola AS JSON. Questo frammento di matrice viene fornito alla seconda funzione OPENJSON che restituisce givenName, firstName di ogni figlio, nonché la matrice di pets. La matrice di pets viene fornita alla terza funzione OPENJSON che restituisce givenName l'animale domestico.

I risultati di questa query sono riportati nella tabella seguente:

familyName childGivenName petName
Desai Jesse Goofy
Desai Jesse Shadow
Desai Lisa NULL

Il documento radice viene unito in join alle due righe children restituite dalla prima chiamata di OPENJSON(children) che crea due righe (o tuple). Ogni riga viene quindi unita in join alle nuove righe generate da OPENJSON(pets) tramite l'operatore OUTER APPLY. Jesse ha due animali domestici, quindi (Desai, Jesse) viene unito in join alle due righe generate per Goofy e Shadow. Lisa non ha animali domestici, quindi non sono presenti righe restituite da OPENJSON(pets) per questa tupla. Tuttavia, dal momento che si usa OUTER APPLY, si ottiene NULL nella colonna. Specificando CROSS APPLY invece di OUTER APPLY, Lisa non verrebbe inclusa nel risultato perché non sono presenti righe di animali domestici che possono essere unite in join con questa tupla.

Confronto tra JSON_VALUE e JSON_QUERY

La differenza principale tra JSON_VALUE e JSON_QUERY consiste nel fatto che JSON_VALUE restituisce un valore scalare, mentre JSON_QUERY restituisce un oggetto o una matrice.

Si consideri il testo JSON di esempio seguente.

{
    "a": "[1,2]",
    "b": [1, 2],
    "c": "hi"
}

In questo testo JSON di esempio i membri dati "a" e "c" sono valori stringa, mentre il membro dati "b" è una matrice. JSON_VALUE e JSON_QUERY restituiscono i risultati seguenti:

Percorso Valori restituiti JSON_VALUE Valori restituiti JSON_QUERY
$ NULL o errore { "a": "[1,2]", "b": [1, 2], "c": "hi" }
$.a [1,2] NULL o errore
$.b NULL o errore [1,2]
$.b[0] 1 NULL o errore
$.c hi NULL o errore

Test di JSON_VALUE e JSON_QUERY con il database di esempio AdventureWorks

Testare le funzioni predefinite descritte in questo argomento eseguendo gli esempi seguenti con il database di esempio AdventureWorks2022. Per maggiori informazioni su come aggiungere i dati JSON per il testing eseguendo uno script, vedere Test drive del supporto JSON integrato.

Negli esempi seguenti la colonna Info nella tabella SalesOrder_json contiene testo JSON.

Esempio 1: restituire colonne standard e dati JSON

La query seguente restituisce valori sia dalle colonne relazionali standard sia da una colonna JSON.

SELECT SalesOrderNumber,
    OrderDate,
    Status,
    ShipDate,
    AccountNumber,
    TotalDue,
    JSON_QUERY(Info, '$.ShippingInfo') ShippingInfo,
    JSON_QUERY(Info, '$.BillingInfo') BillingInfo,
    JSON_VALUE(Info, '$.SalesPerson.Name') SalesPerson,
    JSON_VALUE(Info, '$.ShippingInfo.City') City,
    JSON_VALUE(Info, '$.Customer.Name') Customer,
    JSON_QUERY(OrderItems, '$') OrderItems
FROM Sales.SalesOrder_json
WHERE ISJSON(Info) > 0;

Esempio 2: aggregare e filtrare valori JSON

La query seguente aggrega i subtotali in base al nome del cliente (archiviato in JSON) e in base allo stato (archiviato in una colonna normale), quindi filtra i risultati in base alla città (archiviata in JSON) e in base al valore OrderDate (archiviato in una colonna normale).

DECLARE @territoryid INT;
DECLARE @city NVARCHAR(32);

SET @territoryid = 3;
SET @city = N'Seattle';

SELECT JSON_VALUE(Info, '$.Customer.Name') AS Customer,
    Status,
    SUM(SubTotal) AS Total
FROM Sales.SalesOrder_json
WHERE TerritoryID = @territoryid
    AND JSON_VALUE(Info, '$.ShippingInfo.City') = @city
    AND OrderDate > '1/1/2015'
GROUP BY JSON_VALUE(Info, '$.Customer.Name'),
    Status
HAVING SUM(SubTotal) > 1000;

Aggiornare i valori delle proprietà in testo JSON tramite la funzione JSON_MODIFY

La funzione JSON_MODIFY aggiorna il valore di una proprietà in una stringa JSON e restituisce la stringa JSON aggiornata.

Nell'esempio seguente viene aggiornato il valore di una proprietà JSON in una variabile che include contenuto JSON.

SET @info = JSON_MODIFY(@jsonInfo, "$.info.address[0].town", 'London');

Per altre informazioni, vedere JSON_MODIFY (Transact-SQL).

Contenuto correlato