Microsoft SQL Server
OPENJSON

Get key: waardeparen uit JSON-tekst

OPENJSON-functie parseert JSON-tekst en retourneert alle sleutel: waardeparen op het eerste niveau van JSON:

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

Kolomtype beschrijft het type waarde, dwz null (0), string (1), nummer (2), boolean (3), array (4) en object (5).

Transformeer JSON-array in rijen

De functie OPENJSON ontleedt de verzameling JSON-objecten en retourneert waarden uit JSON-tekst als een reeks rijen.

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
  )

In de WITH-clausule wordt het retourschema van de OPENJSON-functie opgegeven. Sleutels in de JSON-objecten worden opgehaald met kolomnamen. Als een sleutel in JSON niet is opgegeven in de clausule WITH (bijvoorbeeld Price in dit voorbeeld), wordt deze genegeerd. Waarden worden automatisch omgezet in opgegeven typen.

Transformeer geneste JSON-velden in rijen

De functie OPENJSON ontleedt de verzameling JSON-objecten en retourneert waarden uit JSON-tekst als een reeks rijen. Als de waarden in het invoerobject zijn genest, kan een aanvullende toewijzingsparameter worden opgegeven in elke kolom in de clausule 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',
  )

In de WITH-clausule wordt het retourschema van de OPENJSON-functie opgegeven. Nadat het type is opgegeven, pad naar de JSON-knooppunten waar de geretourneerde waarde moet worden gevonden. Sleutels in de JSON-objecten worden door deze paden opgehaald. Waarden worden automatisch omgezet in opgegeven typen.

Binnenste JSON-subobjecten extraheren

OPENJSON kan fragmenten van JSON-objecten in de JSON-tekst extraheren. Stel in de kolomdefinitie die verwijst naar het JSON-subobject het type nvarchar (max) en de AS JSON-optie in:

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
  )

Kolom Info wordt toegewezen aan "Info" -object. Resultaten zullen zijn:

Werken met geneste JSON-subarrays

JSON kan een complexe structuur hebben met innerlijke arrays. In dit voorbeeld hebben we een reeks orders met een geneste subarray van 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}]}
]'

We kunnen eigenschappen op rootniveau parseren met OPENJSON die Items array AS JSON-fragment retourneren. Vervolgens kunnen we OPENJSON opnieuw toepassen op Items-array en de binnenste JSON-tabel openen. De tabel op het eerste niveau en de binnenste tabel worden "samengevoegd" zoals in de JOIN tussen standaardtabellen:

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

resultaten: