Memformat hasil kueri sebagai JSON dengan FOR JSON

Berlaku untuk: SQL Server 2016 (13.x) dan versi yang lebih baru Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics (hanya kumpulan SQL tanpa server)Titik akhir analitik SQL di Microsoft FabricWarehouse di Microsoft FabricSQL database di Microsoft Fabric

Format hasil kueri sebagai JSON, atau ekspor data dari SQL Server sebagai JSON, dengan menambahkan FOR JSON klausa ke SELECT pernyataan. FOR JSON Gunakan klausul untuk menyederhanakan aplikasi klien dengan mendelegasikan pemformatan output JSON dari aplikasi ke SQL Server.

Note

Azure Data Studio adalah editor kueri yang direkomendasikan untuk kueri JSON karena memformat hasil JSON secara otomatis, seperti yang ditunjukkan dalam artikel ini. SQL Server Management Studio menampilkan string yang tidak diformat.

Di Gudang Data Fabric, FOR JSON harus menjadi operator terakhir dalam kueri, sehingga tidak diizinkan di dalam subkueri.

Memformat hasil kueri

Saat Anda menggunakan FOR JSON klausul, Anda dapat menentukan struktur output JSON secara eksplisit, atau membiarkan struktur SELECT pernyataan menentukan output.

• Untuk mempertahankan kontrol penuh atas format output JSON, gunakan FOR JSON PATH. Anda dapat membuat objek pembungkus dan properti kompleks sarang.

• Untuk memformat output JSON secara otomatis berdasarkan struktur SELECT pernyataan, gunakan FOR JSON AUTO.

Berikut adalah contoh pernyataan SELECT dengan klausul FOR JSON dan outputnya.

UNTUK JALUR JSON

Mengontrol output dengan FOR JSON PATH

Dalam PATH mode, Anda dapat menggunakan sintaks titik - misalnya, Item.Price - untuk memformat output berlapis.

Berikut adalah contoh kueri yang menggunakan mode PATH dengan klausa FOR JSON. Contoh berikut juga menggunakan ROOT opsi untuk menentukan elemen akar bernama.

Info selengkapnya tentang FOR JSON PATH

Untuk informasi dan contoh yang lebih rinci, lihat Format Output JSON Berlapis dengan Mode PATH.

Untuk sintaks dan penggunaan, lihat SELECT - FOR Clause (Transact-SQL).

UNTUK JSON AUTO

Pernyataan SELECT mengontrol output dengan FOR JSON AUTO

Dalam AUTO mode, struktur SELECT pernyataan menentukan format output JSON.

Secara default, NULL nilai tidak disertakan dalam output. Anda dapat menggunakan INCLUDE_NULL_VALUES untuk mengubah perilaku ini.

Berikut adalah contoh kueri yang menggunakan mode AUTO dengan klausa FOR JSON.

SELECT name, surname
FROM emp
FOR JSON AUTO;

Dan inilah JSON yang dikembalikan.

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

Contoh dengan JOIN dan NULL

Contoh SELECT...FOR JSON AUTO berikut menyertakan tampilan seperti apa hasil JSON saat ada hubungan 1:banyak antara data dari tabel yang digabungkan.

Tidak adanya nilai null dari JSON yang dikembalikan juga diilustrasikan. Namun, Anda dapat mengambil alih perilaku default ini dengan menggunakan INCLUDE_NULL_VALUES kata kunci pada FOR klausa.

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

Dan selanjutnya adalah JSON yang dihasilkan oleh SELECT sebelumnya.

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"}]}
]

Informasi selengkapnya tentang FOR JSON AUTO

Untuk informasi dan contoh yang lebih rinci, lihat Memformat Output JSON Secara Otomatis dengan Mode OTOMATIS.

Untuk sintaks dan penggunaan, lihat SELECT - FOR Clause (Transact-SQL).

Mengontrol opsi output JSON lainnya

Kontrol output FOR JSON klausa, menggunakan opsi tambahan berikut.

• ROOT

  Untuk menambahkan elemen tingkat atas tunggal ke output JSON, tentukan ROOT opsi . Jika Anda tidak menentukan opsi ini, output JSON tidak memiliki elemen root. Untuk informasi selengkapnya, lihat Menambahkan Simpul Akar ke Output JSON dengan Opsi ROOT.

• INCLUDE_NULL_VALUES

  Untuk menyertakan nilai null dalam output JSON, tentukan INCLUDE_NULL_VALUES opsi . Jika Anda tidak menentukan opsi ini, output tidak menyertakan properti JSON untuk NULL nilai dalam hasil kueri. Untuk informasi selengkapnya, lihat Menyertakan Nilai Null di Opsi JSON - INCLUDE_NULL_VALUES.

• WITHOUT_ARRAY_WRAPPER

  Untuk menghapus tanda kurung siku yang mengelilingi output JSON dari klausul FOR JSON secara default, tentukan opsi WITHOUT_ARRAY_WRAPPER. Gunakan opsi ini untuk menghasilkan satu objek JSON sebagai output dari hasil satu baris. Jika Anda tidak menentukan opsi ini, output JSON diformat sebagai array - yaitu, output diapit dalam tanda kurung siku. Untuk informasi lebih lanjut, lihat Menghapus Kurung Siku dari JSON - WITHOUT_ARRAY_WRAPPER Option.

Keluaran dari klausul FOR JSON

Keluaran dari FOR JSON klausul memiliki karakteristik berikut:

1. Kumpulan hasil berisi satu kolom.

   • Kumpulan hasil kecil dapat berisi satu baris.
   • Kumpulan hasil besar membagi string JSON panjang di beberapa baris.

     • Secara default, SQL Server Management Studio (SSMS) menggabungkan hasil menjadi satu baris saat pengaturan output adalah Hasil ke Kisi. Bilah status SSMS menampilkan jumlah baris aktual.

     • Aplikasi klien lain mungkin memerlukan kode untuk merecombine hasil panjang menjadi satu string JSON yang valid dengan menggabungkan konten beberapa baris. Untuk contoh kode ini dalam aplikasi C#, lihat Menggunakan output FOR JSON di aplikasi klien C#.

       

2. Hasilnya diformat sebagai array objek JSON.

   • Jumlah elemen dalam array JSON sama dengan jumlah baris dalam hasil pernyataan SELECT (sebelum klausul FOR JSON diterapkan).

   • Setiap baris dalam hasil pernyataan SELECT (sebelum klausul FOR JSON diterapkan) menjadi objek JSON terpisah dalam array.

   • Setiap kolom dalam hasil pernyataan SELECT (sebelum klausul FOR JSON diterapkan) menjadi properti objek JSON.

3. Nama-nama kolom dan nilainya diekstrapolasi sesuai dengan sintaks JSON. Untuk informasi selengkapnya, lihat Cara JSON meloloskan karakter khusus dan karakter kontrol.

Example

Berikut adalah contoh yang menunjukkan bagaimana FOR JSON klausul memformat output JSON.

Hasil kueri

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

Output 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"
}]

Konten terkait

• Cara FOR JSON mengonversi jenis data SQL Server ke jenis data JSON
• Bagaimana FOR JSON meloloskan karakter khusus dan karakter kontrol
• Video: JSON sebagai jembatan antara NoSQL dan dunia relasional
• SELECT - Klausul FOR (Transact-SQL)
• Menggunakan output FOR JSON di SQL Database Engine dan di aplikasi klien