Microsoft SQL Server
OPENJSON

Hämta nyckel: värdepar från JSON-text

OPENJSON-funktionen analyserar JSON-text och returnerar alla nycklar: värdepar på den första nivån i JSON:

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

Kolumntyp beskriver värdetyp, dvs. noll (0), sträng (1), nummer (2), booleska (3), matris (4) och objekt (5).

Förvandla JSON-matris till uppsättning rader

OPENJSON-funktionen analyserar samling av JSON-objekt och returnerar värden från JSON-text som uppsättning rader.

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
  )

I MED-klausulen specificeras returschema för OPENJSON-funktionen. Nycklar i JSON-objekt hämtas med kolumnnamn. Om någon nyckel i JSON inte anges i MED-klausulen (t.ex. pris i detta exempel) ignoreras den. Värdena konverteras automatiskt till specificerade typer.

Förvandla kapslade JSON-fält till uppsättning rader

OPENJSON-funktionen analyserar samling av JSON-objekt och returnerar värden från JSON-text som uppsättning rader. Om värdena i ingångsobjektet är kapslade kan ytterligare mappningsparametrar anges i varje kolumn i MED-klausulen:

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',
  )

I MED-klausulen specificeras returschema för OPENJSON-funktionen. Efter att typen anges väg till JSON-noderna där det returnerade värdet ska hittas. Nycklar i JSON-objekt hämtas av dessa banor. Värdena konverteras automatiskt till specificerade typer.

Extrahera inre JSON-underobjekt

OPENJSON kan extrahera fragment av JSON-objekt i JSON-texten. I kolumndefinitionen som hänvisar till JSON-subobjektet anger du alternativet nvarchar (max) och 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
  )

Infokolumnen mappas till "Info" -objektet. Resultaten blir:

Arbetar med kapslade JSON-undergrupper

JSON kan ha komplex struktur med inre matriser. I det här exemplet har vi en rad beställningar med kapslade underuppsättningar av 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}]}
]'

Vi kan analysera rotnivåegenskaper med hjälp av OPENJSON som kommer att returnera artiklar som AS JSON-fragment. Då kan vi tillämpa OPENJSON igen på artiklar array och öppna inre JSON tabell. Första nivån och det inre bordet kommer att "sammanfogas" som i JOIN mellan standardtabeller:

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

Resultat: