Udostępnij za pośrednictwem

Analizowanie i przekształcanie danych JSON przy użyciu formatu OPENJSON

  • Artykuł

Dotyczy: SQL Server 2016 (13.x) i nowsze wersje Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics (tylko bezserwerowa pula SQL)punktu końcowego analizy SQL w usłudze Microsoft FabricWarehouse w usłudze Microsoft Fabric

Funkcja zestawu wierszy OPENJSON konwertuje tekst JSON na zestaw wierszy i kolumn. Po przekształceniu kolekcji JSON w zestaw wierszy przy użyciu OPENJSONmożna uruchomić dowolne zapytanie SQL dla zwróconych danych lub wstawić je do tabeli programu SQL Server. Aby uzyskać więcej informacji na temat pracy z danymi JSON w silniku bazy danych SQL Server, zobacz dane JSON w programie SQL Server.

Funkcja OPENJSON przyjmuje pojedynczy obiekt JSON lub kolekcję obiektów JSON i przekształca je w co najmniej jeden wiersz. Domyślnie funkcja OPENJSON zwraca następujące dane:

  • Z obiektu JSON funkcja zwraca wszystkie pary klucz/wartość znalezione na pierwszym poziomie.
  • Z tablicy JSON funkcja zwraca wszystkie elementy tablicy z ich indeksami.

Możesz dodać opcjonalną klauzulę WITH, aby podać schemat, który jawnie definiuje strukturę danych wyjściowych.

Plik OPENJSON z domyślnymi danymi wyjściowymi

Jeśli używasz funkcji OPENJSON bez podawania jawnego schematu dla wyników — czyli bez klauzuli WITH po OPENJSON — funkcja zwraca tabelę z następującymi trzema kolumnami:

  1. Identyfikator name we właściwości obiektu wejściowego (lub indeks elementu w tablicy wejściowej).
  2. value właściwości lub elementu tablicy.
  3. type (na przykład ciąg, liczba, wartość logiczna, tablica lub obiekt).

OPENJSON zwraca każdą właściwość obiektu JSON lub każdy element tablicy jako oddzielny wiersz.

W poniższym przykładzie użyto OPENJSON ze schematem domyślnym — czyli bez opcjonalnej klauzuli WITH — i zwraca jeden wiersz dla każdej właściwości obiektu JSON.

DECLARE @json NVARCHAR(MAX);

SET @json='{ "name": "John", "surname": "Doe", "age": 45, "skills": [ "SQL", "C#", "MVC" ]}';

SELECT *
FROM OPENJSON(@json);

Oto zestaw wyników.

klucz wartość typ
name John 1
surname Doe 1
age 45 2
skills [ "SQL" ,"C#" ,"MVC" ] 4

Aby uzyskać więcej informacji i przykładów, zobacz Użyj OPENJSON z domyślnym schematem.

Aby uzyskać informacje o składni i użyciu, zobacz OPENJSON.

Wynik OPENJSON ze zdefiniowaną strukturą

Po określeniu schematu wyników przy użyciu klauzuli WITH funkcji OPENJSON funkcja zwraca tabelę zawierającą tylko kolumny zdefiniowane w klauzuli WITH. W opcjonalnej klauzuli WITH należy określić zestaw kolumn wyjściowych, ich typów i ścieżki właściwości źródłowych JSON dla każdej wartości wyjściowej. OPENJSON iteruje tablicę obiektów JSON, odczytuje wartość w określonej ścieżce dla każdej kolumny i konwertuje wartość na określony typ.

W poniższym przykładzie użyto OPENJSON ze schematem dla danych wyjściowych, które jawnie określono w klauzuli WITH.

DECLARE @json NVARCHAR(MAX);

SET @json = N'[
    {
        "Order": {
            "Number": "SO43659",
            "Date": "2024-05-31T00:00:00"
        },
        "AccountNumber": "AW29825",
        "Item": {
            "Price": 2024.9940,
            "Quantity": 1
        }
    },
    {
        "Order": {
            "Number": "SO43661",
            "Date": "2024-06-01T00:00:00"
        },
        "AccountNumber": "AW73565",
        "Item": {
            "Price": 2024.9940,
            "Quantity": 3
        }
    }
]';

SELECT *
FROM OPENJSON(@json) WITH (
    Number VARCHAR(200) '$.Order.Number',
    DATE DATETIME '$.Order.Date',
    Customer VARCHAR(200) '$.AccountNumber',
    Quantity INT '$.Item.Quantity'
);

Oto zestaw wyników.

Liczba Data Klient Ilość
SO43659 2024-05-31T00:00:00 AW29825 1
SO43661 2024-06-01T00:00:00 AW73565 3

Ta funkcja zwraca i formatuje elementy tablicy JSON.

  • Dla każdego elementu w tablicy JSON OPENJSON generuje nowy wiersz w tabeli wyjściowej. Dwa elementy w tablicy JSON są konwertowane na dwa wiersze w zwracanej tabeli.

  • Dla każdej kolumny określonej przy użyciu składni colName type json_pathOPENJSON konwertuje wartość znajdującą się w każdym elemenie tablicy na określonej ścieżce do określonego typu. W tym przykładzie wartości kolumny Date są pobierane z każdego elementu na ścieżce $.Order.Date i konwertowane na wartości daty/godziny.

Aby uzyskać więcej informacji i przykładów, zobacz Użyj OPENJSON z jawnym schematem (SQL Server).

Aby uzyskać informacje o składni i użyciu, zobacz OPENJSON.

OPENJSON wymaga poziomu zgodności 130

Funkcja OPENJSON jest dostępna tylko na poziomie zgodności 130 i nowszym. Jeśli poziom zgodności bazy danych jest niższy niż 130, program SQL Server nie może odnaleźć i uruchomić funkcji OPENJSON. Inne wbudowane funkcje JSON są dostępne na wszystkich poziomach zgodności.

Poziom zgodności można sprawdzić w widoku sys.databases lub we właściwościach bazy danych oraz zmienić poziom zgodności bazy danych przy użyciu następującego polecenia:

ALTER DATABASE <DatabaseName> SET COMPATIBILITY_LEVEL = 130;

Powiązana zawartość