Microsoft SQL Server
JSON in SQL Server

Sintassi

• JSON_VALUE (espressione, percorso): estrae un valore scalare da una stringa JSON.
• JSON_QUERY (espressione [, percorso]): estrae un oggetto o una matrice da una stringa JSON.
• OPENJSON (jsonExpression [, path]) - funzione valore di tabella che analizza il testo JSON e restituisce oggetti e proprietà in JSON come righe e colonne.
• ISJSON (espressione): verifica se una stringa contiene JSON valido.
• JSON_MODIFY (expression, path, newValue) - Aggiorna il valore di una proprietà in una stringa JSON e restituisce la stringa JSON aggiornata.

Parametri

Osservazioni

ALTER DATABASE <Database-Name-Here> SET COMPATIBILITY_LEVEL = 130

Formato risultati query come JSON con FOR JSON

Dati della tabella di input (tabella Persone)

domanda

SELECT Id, Name, Age
FROM People
FOR JSON PATH

Risultato

[
    {"Id":1,"Name":"John","Age":23},
    {"Id":2,"Name":"Jane","Age":31}
]

Analizzare il testo JSON

Le funzioni JSON_VALUE e JSON_QUERY analizzano il testo JSON e restituiscono valori scalari o oggetti / matrici sul percorso nel testo JSON.

DECLARE @json NVARCHAR(100) = '{"id": 1, "user":{"name":"John"}, "skills":["C#","SQL"]}'

SELECT
    JSON_VALUE(@json, '$.id') AS Id,
    JSON_VALUE(@json, '$.user.name') AS Name,
    JSON_QUERY(@json, '$.user') AS UserObject,
    JSON_QUERY(@json, '$.skills') AS Skills,
    JSON_VALUE(@json, '$.skills[0]') AS Skill0

Risultato

Unisciti alle entità JSON padre e figlio utilizzando CROSS APPLY OPENJSON

Unisci gli oggetti parent con le loro entità figlio, ad esempio vogliamo una tabella relazionale di ogni persona e dei suoi hobby

DECLARE @json nvarchar(1000) =
N'[
    {
        "id":1,
        "user":{"name":"John"},
        "hobbies":[
            {"name": "Reading"},
            {"name": "Surfing"}
        ]
    },
    {
        "id":2,
        "user":{"name":"Jane"},
        "hobbies":[
            {"name": "Programming"},
            {"name": "Running"}
        ]
    }
 ]'

domanda

SELECT 
    JSON_VALUE(person.value, '$.id') as Id,
    JSON_VALUE(person.value, '$.user.name') as PersonName,
    JSON_VALUE(hobbies.value, '$.name') as Hobby
FROM OPENJSON (@json) as person
    CROSS APPLY OPENJSON(person.value, '$.hobbies') as hobbies

In alternativa, questa query può essere scritta utilizzando la clausola WITH.

SELECT 
    Id, person.PersonName, Hobby
FROM OPENJSON (@json)
WITH(
    Id int '$.id',
    PersonName nvarchar(100) '$.user.name',
    Hobbies nvarchar(max) '$.hobbies' AS JSON
) as person
CROSS APPLY OPENJSON(Hobbies)
WITH(
    Hobby nvarchar(100) '$.name'
)

Risultato

Indicizza sulle proprietà JSON utilizzando colonne calcolate

Quando si memorizzano i documenti JSON in SQL Server, è necessario essere in grado di filtrare e ordinare in modo efficiente i risultati delle query sulle proprietà dei documenti JSON.

CREATE TABLE JsonTable
(
    id int identity primary key,
    jsonInfo nvarchar(max),
    CONSTRAINT [Content should be formatted as JSON]
    CHECK (ISJSON(jsonInfo)>0)
)

INSERT INTO JsonTable
VALUES(N'{"Name":"John","Age":23}'),
(N'{"Name":"Jane","Age":31}'),
(N'{"Name":"Bob","Age":37}'),
(N'{"Name":"Adam","Age":65}')
GO

Data la tabella sopra Se vogliamo trovare la riga con il nome = 'Adam', dovremmo eseguire la seguente query.

SELECT * 
FROM JsonTable Where 
JSON_VALUE(jsonInfo, '$.Name') = 'Adam'

Tuttavia questo richiederà al server SQL di eseguire una tabella completa che su un grande tavolo non è efficace.

Per accelerare, vorremmo aggiungere un indice, ma non possiamo direttamente consultare le proprietà nel documento JSON. La soluzione consiste nell'aggiungere una colonna calcolata sul percorso JSON $.Name , quindi aggiungere un indice nella colonna calcolata.

ALTER TABLE JsonTable
ADD vName as JSON_VALUE(jsonInfo, '$.Name')

CREATE INDEX idx_name
ON JsonTable(vName)

Ora quando eseguiamo la stessa query, invece di una scansione completa della tabella, il server SQL utilizza un indice per cercare nell'indice non cluster e trovare le righe che soddisfano le condizioni specificate.

Nota: affinché il server SQL utilizzi l'indice, è necessario creare la colonna calcolata con la stessa espressione che si intende utilizzare nelle query, in questo esempio JSON_VALUE(jsonInfo, '$.Name') , tuttavia è anche possibile utilizzare il nome della colonna calcolata vName

Formattare una riga tabella come un singolo oggetto JSON utilizzando FOR JSON

L' opzione WITHOUT_ARRAY_WRAPPER nella clausola FOR JSON rimuoverà le parentesi dell'array dall'output JSON. Ciò è utile se si restituisce una riga singola nella query.

Nota: questa opzione produrrà output JSON non valido se viene restituita più di una riga.

Dati della tabella di input (tabella Persone)

domanda

SELECT Id, Name, Age
FROM People
WHERE Id = 1
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

Risultato

{"Id":1,"Name":"John","Age":23}

Analizza il testo JSON usando la funzione OPENJSON

La funzione OPENJSON analizza il testo JSON e restituisce più uscite. I valori che devono essere restituiti vengono specificati utilizzando i percorsi definiti nella clausola WITH. Se per alcune colonne non è specificato un percorso, il nome della colonna viene utilizzato come percorso. Questa funzione trasmette i valori restituiti ai tipi SQL definiti nella clausola WITH. L'opzione AS JSON deve essere specificata nella definizione della colonna se alcuni oggetti / array devono essere restituiti.

DECLARE @json NVARCHAR(100) = '{"id": 1, "user":{"name":"John"}, "skills":["C#","SQL"]}'

SELECT * 
FROM OPENJSON (@json)
    WITH(Id int '$.id',
        Name nvarchar(100) '$.user.name',
        UserObject nvarchar(max) '$.user' AS JSON,
        Skills nvarchar(max) '$.skills' AS JSON,
        Skill0 nvarchar(20) '$.skills[0]')

Risultato