Sdílet prostřednictvím

Parsování a transformace dat JSON pomocí OPENJSON

  • Článek

platí pro: SQL Server 2016 (13.x) a novější Azure SQL Databaseazure SQL Managed Instanceazure Synapse Analytics

Funkce sady řádků OPENJSON převede text JSON na sadu řádků a sloupců. Po transformaci kolekce JSON na sadu řádků s OPENJSONmůžete na vrácených datech spustit libovolný dotaz SQL nebo ho vložit do tabulky SQL Serveru. Další informace o práci s daty JSON v databázovém stroji SQL Serveru najdete viz JSON data v SQL Serveru.

Funkce OPENJSON přebírá jeden objekt JSON nebo kolekci objektů JSON a transformuje je na jeden nebo více řádků. Ve výchozím nastavení vrátí funkce OPENJSON následující data:

  • Z objektu JSON vrátí funkce všechny páry klíč/hodnota, které najde na první úrovni.
  • Z pole JSON vrátí funkce všechny prvky pole s jejich indexy.

Můžete přidat volitelnou klauzuli WITH pro poskytnutí schématu, které explicitně definuje strukturu výstupu.

OPENJSON s výchozím výstupem

Pokud použijete funkci OPENJSON bez explicitního schématu výsledků – to znamená bez klauzule WITH za OPENJSON – vrátí funkce tabulku s následujícími třemi sloupci:

  1. name vlastnosti ve vstupním objektu (nebo index prvku ve vstupním poli).
  2. value vlastnosti nebo prvku pole.
  3. type (například řetězec, číslo, logická hodnota, pole nebo objekt).

OPENJSON vrátí každou vlastnost objektu JSON nebo každý prvek pole jako samostatný řádek.

Následující příklad používá OPENJSON s výchozím schématem – to znamená bez volitelné klauzule WITH – a vrátí jeden řádek pro každou vlastnost objektu JSON.

DECLARE @json NVARCHAR(MAX);

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

SELECT *
FROM OPENJSON(@json);

Tady je sada výsledků.

klíč hodnota typ
name John 1
surname Doe 1
age 45 2
skills [ "SQL" ,"C#" ,"MVC" ] 4

Další informace a příklady naleznete v tématu Použití OPENJSON s výchozím schématem.

Informace o syntaxi a použití naleznete v OPENJSON.

Výstup OPENJSON s explicitní strukturou

Když zadáte schéma výsledků pomocí klauzule WITH funkce OPENJSON, vrátí funkce tabulku pouze se sloupci, které definujete v klauzuli WITH. V volitelné klauzuli WITH zadáte sadu výstupních sloupců, jejich typů a cest ke zdrojovým vlastnostem JSON pro každou výstupní hodnotu. OPENJSON iteruje polem objektů JSON, přečte hodnotu zadané cesty pro každý sloupec a převede hodnotu na zadaný typ.

Následující příklad používá OPENJSON se schématem pro výstup, který explicitně zadáte v 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'
);

Tady jsou výsledky.

Číslo Datum Zákazník Množství
SO43659 2024-05-31T00:00:00 AW29825 1
SO43661 2024-06-01T00:00:00 AW73565 3

Tato funkce vrátí a naformátuje prvky pole JSON.

  • Pro každý prvek v poli JSON OPENJSON vygeneruje ve výstupní tabulce nový řádek. Dva prvky v poli JSON se ve vrácené tabulce převedou na dva řádky.

  • Pro každý sloupec zadaný pomocí syntaxe colName type json_pathOPENJSON převede hodnotu nalezenou v každém prvku pole na zadané cestě na zadaný typ. V tomto příkladu jsou hodnoty pro sloupec Date převzaty z každého prvku v cestě $.Order.Date a převedeny na hodnoty datetime.

Další informace a příklady najdete v tématu Použití OPENJSON s explicitním schématem (SQL Server).

Informace ohledně syntaxe a použití viz OPENJSON.

OPENJSON vyžaduje úroveň kompatibility 130.

Funkce OPENJSON je dostupná pouze při úrovni kompatibility 130 a vyšších. Pokud je úroveň kompatibility databáze nižší než 130, SQL Server nemůže najít a spustit funkci OPENJSON. Další integrované funkce JSON jsou k dispozici na všech úrovních kompatibility.

Úroveň kompatibility můžete zkontrolovat v zobrazení sys.databases nebo ve vlastnostech databáze a změnit úroveň kompatibility databáze pomocí následujícího příkazu:

ALTER DATABASE <DatabaseName> SET COMPATIBILITY_LEVEL = 130;

Související obsah