Formatowanie wyników zapytania jako JSON z użyciem FOR JSON

Dotyczy: SQL Server 2016 (13.x) i nowsze wersje Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics (tylko bezserwerowa pula SQL)Punkt końcowy analizy SQL w Microsoft FabricMagazyn w Microsoft FabricSQL Database w Microsoft Fabric

Formatuj wyniki zapytania jako dane JSON lub eksportuj dane z programu SQL Server jako kod JSON, dodając klauzulę FOR JSON do instrukcji SELECT. Użyj klauzuli FOR JSON, aby uprościć aplikacje klienckie, delegując formatowanie danych wyjściowych JSON z aplikacji do programu SQL Server.

Note

Azure Data Studio jest zalecanym edytorem zapytań JSON, ponieważ automatycznie formatuje wyniki JSON, jak pokazano w tym artykule. Program SQL Server Management Studio wyświetla niesformatowany ciąg.

W Fabric Data Warehouse FOR JSON musi być ostatnim operatorem w zapytaniu i dlatego nie jest dozwolony wewnątrz podzapytań.

Formatowanie wyników zapytania

Jeśli używasz klauzuli FOR JSON, możesz jawnie określić strukturę danych wyjściowych JSON lub pozwolić strukturze instrukcji SELECT określić dane wyjściowe.

• Aby zachować pełną kontrolę nad formatem danych wyjściowych JSON, użyj FOR JSON PATH. Można tworzyć obiekty opakowujące i zagnieżdżać właściwości złożone.

• Aby automatycznie sformatować dane wyjściowe JSON na podstawie struktury instrukcji SELECT, użyj FOR JSON AUTO.

Oto przykład instrukcji SELECT z klauzulą FOR JSON i jej danymi wyjściowymi.

DLA ŚCIEŻKI JSON

Sterowanie danymi wyjściowymi za pomocą ścieżki JSON FOR

W trybie PATH można użyć składni kropki — na przykład Item.Price — do formatowania zagnieżdżonych danych wyjściowych.

Oto przykładowe zapytanie, które używa trybu PATH z klauzulą FOR JSON. W poniższym przykładzie użyto również opcji ROOT, aby określić nazwany element główny.

Więcej informacji o FOR JSON PATH

Aby uzyskać bardziej szczegółowe informacje i przykłady, zobacz Format zagnieżdżone dane wyjściowe JSON z trybem "PATH".

Aby uzyskać informacje o składni i użyciu, zobacz SELECT — FOR Clause (Transact-SQL).

DLA JSON AUTO

Instrukcja SELECT steruje danymi wyjściowymi za pomocą FOR JSON AUTO

W trybie AUTO struktura instrukcji SELECT określa format danych wyjściowych JSON.

Domyślnie wartości NULL nie są uwzględniane w danych wyjściowych. Aby zmienić to zachowanie, możesz użyć INCLUDE_NULL_VALUES.

Oto przykładowe zapytanie, które używa trybu AUTO z klauzulą FOR JSON.

SELECT name, surname
FROM emp
FOR JSON AUTO;

I oto zwrócony kod JSON.

[{
    "name": "John"
}, {
    "name": "Jane",
    "surname": "Doe"
}]

Przykład z funkcją JOIN i NULL

Poniższy przykład SELECT...FOR JSON AUTO zawiera wyświetlanie wyglądu wyników JSON, gdy istnieje relacja 1:wiele między danymi ze sprzężonych tabel.

Pokazano również brak wartości null ze zwróconego kodu JSON. Można jednak zastąpić to domyślne zachowanie, używając słowa kluczowego INCLUDE_NULL_VALUES w klauzuli FOR.

DROP TABLE IF EXISTS #tabStudent;
DROP TABLE IF EXISTS #tabClass;
GO

CREATE TABLE #tabClass (
    ClassGuid UNIQUEIDENTIFIER NOT NULL DEFAULT newid(),
    ClassName NVARCHAR(32) NOT NULL
);

CREATE TABLE #tabStudent (
    StudentGuid UNIQUEIDENTIFIER NOT NULL DEFAULT newid(),
    StudentName NVARCHAR(32) NOT NULL,
    ClassGuid UNIQUEIDENTIFIER NULL -- Foreign key.
);
GO

INSERT INTO #tabClass (ClassGuid, ClassName)
VALUES
    ('DE807673-ECFC-4850-930D-A86F921DE438', 'Algebra Math'),
    ('C55C6819-E744-4797-AC56-FF8A729A7F5C', 'Calculus Math'),
    ('98509D36-A2C8-4A65-A310-E744F5621C83', 'Art Painting');

INSERT INTO #tabStudent (StudentName, ClassGuid)
VALUES
    ('Alice Apple', 'DE807673-ECFC-4850-930D-A86F921DE438'),
    ('Alice Apple', 'C55C6819-E744-4797-AC56-FF8A729A7F5C'),
    ('Betty Boot', 'C55C6819-E744-4797-AC56-FF8A729A7F5C'),
    ('Betty Boot', '98509D36-A2C8-4A65-A310-E744F5621C83'),
    ('Carla Cap', null);
GO

SELECT c.ClassName,
    s.StudentName
FROM #tabClass AS c
RIGHT JOIN #tabStudent AS s ON s.ClassGuid = c.ClassGuid
ORDER BY c.ClassName,
    s.StudentName
FOR JSON AUTO
    -- To include NULL values in the output, uncomment the following line:
    --, INCLUDE_NULL_VALUES
    ;
GO

DROP TABLE IF EXISTS #tabStudent;
DROP TABLE IF EXISTS #tabClass;
GO

Następnie mamy kod JSON generowany przez poprzednie polecenie SELECT.

JSON_F52E2B61-18A1-11d1-B105-00805F49916B

[
   {"s":[{"StudentName":"Carla Cap"}]},
   {"ClassName":"Algebra Math","s":[{"StudentName":"Alice Apple"}]},
   {"ClassName":"Art Painting","s":[{"StudentName":"Betty Boot"}]},
   {"ClassName":"Calculus Math","s":[{"StudentName":"Alice Apple"},{"StudentName":"Betty Boot"}]}
]

Więcej informacji o funkcji FOR JSON AUTO

Aby uzyskać bardziej szczegółowe informacje i przykłady, zobacz Formatowanie danych wyjściowych JSON automatycznie przy użyciu trybu automatycznego.

Aby uzyskać informacje o składni i użyciu, zobacz SELECT — FOR Clause (Transact-SQL).

Kontrolowanie innych opcji danych wyjściowych JSON

Kontroluj dane wyjściowe klauzuli FOR JSON, korzystając z poniższych dodatkowych opcji.

• ROOT

  Aby dodać pojedynczy element najwyższego poziomu do danych wyjściowych JSON, określ opcję ROOT. Jeśli nie określisz tej opcji, dane wyjściowe JSON nie mają elementu głównego. Aby uzyskać więcej informacji, zobacz Dodawanie węzła głównego do danych wyjściowych JSON przy użyciu opcji ROOT.

• INCLUDE_NULL_VALUES

  Aby uwzględnić wartości null w danych wyjściowych JSON, określ opcję INCLUDE_NULL_VALUES. Jeśli nie określisz tej opcji, dane wyjściowe nie zawierają właściwości JSON dla NULL wartości w wynikach zapytania. Aby uzyskać więcej informacji, zobacz Include Null Values in JSON - INCLUDE_NULL_VALUES Option(Uwzględnij wartości null w formacie JSON — opcja INCLUDE_NULL_VALUES).

• WITHOUT_ARRAY_WRAPPER

  Aby usunąć nawiasy kwadratowe otaczające dane wyjściowe JSON klauzuli FOR JSON domyślnie, określ opcję WITHOUT_ARRAY_WRAPPER. Użyj tej opcji, aby wygenerować pojedynczy obiekt JSON jako dane wyjściowe z wyniku pojedynczego wiersza. Jeśli nie określisz tej opcji, dane wyjściowe JSON są formatowane jako tablica — czyli dane wyjściowe są ujęte w nawiasy kwadratowe. Aby uzyskać więcej informacji, zobacz Usuń nawiasy kwadratowe z formatu JSON — opcja WITHOUT_ARRAY_WRAPPER.

Dane wyjściowe klauzuli JSON FOR

Dane wyjściowe klauzuli FOR JSON mają następujące cechy:

1. Zestaw wyników zawiera jedną kolumnę.

   • Mały zestaw wyników może zawierać jeden wiersz.
   • Duży zestaw wyników dzieli długi ciąg JSON między wiele wierszy.

     • Domyślnie program SQL Server Management Studio (SSMS) łączy wyniki w jeden wiersz, gdy ustawienie danych wyjściowych ma wartość Wyniki do siatki. Pasek stanu programu SSMS wyświetla rzeczywistą liczbę wierszy.

     • Inne aplikacje klienckie mogą wymagać kodu, aby ponownie połączyć długie wyniki w jeden, prawidłowy ciąg JSON, łącząc zawartość wielu wierszy. Aby zapoznać się z przykładem tego kodu w aplikacji języka C#, zobacz Use FOR JSON output in a C# client app(Używanie danych wyjściowych FOR JSON w aplikacji klienckiej języka C#).

       

2. Wyniki są formatowane jako tablica obiektów JSON.

   • Liczba elementów w tablicy JSON jest równa liczbie wierszy w wynikach instrukcji SELECT (przed zastosowaniem klauzuli JSON FOR).

   • Każdy wiersz w wynikach instrukcji SELECT (przed zastosowaniem klauzuli JSON FOR) staje się oddzielnym obiektem JSON w tablicy.

   • Każda kolumna w wynikach instrukcji SELECT (przed zastosowaniem klauzuli JSON FOR) staje się właściwością obiektu JSON.

3. Zarówno nazwy kolumn, jak i ich wartości są odpowiednio kodowane zgodnie ze składnią JSON. Aby uzyskać więcej informacji, zobacz How FOR JSON escapes special characters and control characters (Jak w formacie JSON jest ucieczka znaków specjalnych i znaków kontrolnych).

Example

Oto przykład, który pokazuje, jak klauzula FOR JSON formatuje dane wyjściowe JSON.

Wyniki zapytania

A	B	C	D
10	11	12	X
20	21	22	Y
30	31	32	Z

Dane wyjściowe JSON

[{
    "A": 10,
    "B": 11,
    "C": 12,
    "D": "X"
}, {
    "A": 20,
    "B": 21,
    "C": 22,
    "D": "Y"
}, {
    "A": 30,
    "B": 31,
    "C": 32,
    "D": "Z"
}]

Treści powiązane

• Jak format JSON konwertuje typy danych programu SQL Server na typy danych JSON
• Jak JSON ucieka znaki specjalne i sterujące
• Wideo: JSON jako most między światami NoSQL i relacyjnymi
• SELECT — KLAUZULA FOR (Transact-SQL)
• Używanie danych wyjściowych FOR JSON w aparacie bazy danych SQL i w aplikacjach klienckich