Microsoft SQL Server
OPENJSON

Uzyskaj klucz: pary wartości z tekstu JSON

Funkcja OPENJSON analizuje tekst JSON i zwraca wszystkie pary klucz: wartość na pierwszym poziomie JSON:

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

Typ kolumny opisuje typ wartości, tj. Null (0), string (1), number (2), boolean (3), array (4) i object (5).

Przekształć tablicę JSON w zestaw wierszy

Funkcja OPENJSON analizuje kolekcję obiektów JSON i zwraca wartości z tekstu JSON jako zestaw wierszy.

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
  )

W klauzuli WITH podano schemat powrotu funkcji OPENJSON. Klucze w obiektach JSON są pobierane według nazw kolumn. Jeśli jakiś klucz w JSON nie jest określony w klauzuli WITH (np. Price w tym przykładzie), zostanie zignorowany. Wartości są automatycznie konwertowane na określone typy.

Przekształć zagnieżdżone pola JSON w zestaw wierszy

Funkcja OPENJSON analizuje kolekcję obiektów JSON i zwraca wartości z tekstu JSON jako zestaw wierszy. Jeśli wartości w obiekcie wejściowym są zagnieżdżone, dodatkowy parametr odwzorowania można określić w każdej kolumnie w klauzuli 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',
  )

W klauzuli WITH podano schemat powrotu funkcji OPENJSON. Po określeniu typu ścieżka do węzłów JSON, w których należy znaleźć zwracaną wartość. Klucze w obiektach JSON są pobierane przez te ścieżki. Wartości są automatycznie konwertowane na określone typy.

Wyodrębnianie wewnętrznych podobiektów JSON

OPENJSON może wyodrębniać fragmenty obiektów JSON w tekście JSON. W definicji kolumny, która odwołuje się do podobiektu JSON, ustaw typ nvarchar (max) i opcję 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
  )

Kolumna informacyjna zostanie odwzorowana na obiekt „Info”. Wyniki będą:

Praca z zagnieżdżonymi pod-tablicami JSON

JSON może mieć złożoną strukturę z wewnętrznymi tablicami. W tym przykładzie mamy tablicę zamówień z zagnieżdżoną pod-tablicą 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}]}
]'

Możemy analizować właściwości poziomu głównego za pomocą OPENJSON, który zwróci fragment tablicy elementów JAK JSON. Następnie możemy ponownie zastosować OPENJSON do tablicy Przedmioty i otworzyć wewnętrzną tabelę JSON. Stół pierwszego poziomu i stół wewnętrzny zostaną „połączone” jak w JOIN między standardowymi stołami:

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

Wyniki: