Memecahkan masalah umum dengan JSON di SQL Server

Berlaku untuk: SQL Server 2016 (13.x) dan yang lebih baru

Temukan jawaban di sini untuk beberapa pertanyaan umum tentang dukungan JSON bawaan di SQL Server.

Untuk output JSON dan JSON

UNTUK JSON PATH atau FOR JSON AUTO?

Pertanyaan. Saya ingin membuat hasil teks JSON dari kueri SQL sederhana pada satu tabel. UNTUK JSON PATH dan FOR JSON AUTO menghasilkan output yang sama. Manakah dari dua opsi ini yang harus saya gunakan?

Jawaban. Gunakan UNTUK JALUR JSON. Meskipun tidak ada perbedaan dalam output JSON, mode AUTO menerapkan beberapa logika tambahan yang memeriksa apakah kolom harus ditumpuk. Pertimbangkan PATH sebagai opsi default.

Membuat struktur JSON berlapis

Pertanyaan. Saya ingin menghasilkan JSON yang kompleks dengan beberapa array pada tingkat yang sama. UNTUK JSON PATH dapat membuat objek berlapis menggunakan jalur, dan FOR JSON AUTO membuat tingkat bersarang tambahan untuk setiap tabel. Tidak satu pun dari dua opsi ini memungkinkan saya menghasilkan output yang saya inginkan. Bagaimana cara membuat format JSON kustom yang tidak didukung secara langsung oleh opsi yang ada?

Jawaban. Anda dapat membuat struktur data apa pun dengan menambahkan kueri FOR JSON sebagai ekspresi kolom yang mengembalikan teks JSON. Anda juga dapat membuat JSON secara manual dengan menggunakan fungsi JSON_QUERY. Contoh berikut menunjukkan teknik ini.

SELECT col1, col2, col3,  
     (SELECT col11, col12, col13 FROM t11 WHERE t11.FK = t1.PK FOR JSON PATH) as t11,  
     (SELECT col21, col22, col23 FROM t21 WHERE t21.FK = t1.PK FOR JSON PATH) as t21,  
     (SELECT col31, col32, col33 FROM t31 WHERE t31.FK = t1.PK FOR JSON PATH) as t31,  
     JSON_QUERY('{"'+col4+'":"'+col5+'"}') as t41  
FROM t1  
FOR JSON PATH  

Setiap hasil kueri FOR JSON atau fungsi JSON_QUERY dalam ekspresi kolom diformat sebagai sub-objek JSON berlapis terpisah dan disertakan dalam hasil utama.

Mencegah JSON yang lolos ganda dalam output FOR JSON

Pertanyaan. Saya memiliki teks JSON yang disimpan dalam kolom tabel. Saya ingin memasukkannya ke dalam output FOR JSON. Tetapi FOR JSON lolos dari semua karakter di JSON, jadi saya mendapatkan string JSON alih-alih objek berlapis, seperti yang ditunjukkan dalam contoh berikut.

SELECT 'Text' AS myText, '{"day":23}' AS myJson  
FOR JSON PATH  

Kueri ini menghasilkan output berikut.

[{"myText":"Text", "myJson":"{\"day\":23}"}]  

Bagaimana cara mencegah perilaku ini? Saya ingin {"day":23} dikembalikan sebagai objek JSON dan bukan sebagai teks yang lolos.

Jawaban. JSON yang disimpan dalam kolom teks atau harfiah diperlakukan seperti teks apa pun. Artinya, dikelilingi dengan tanda kutip ganda dan lolos. Jika Anda ingin mengembalikan objek JSON yang tidak dilewati, teruskan kolom JSON sebagai argumen ke fungsi JSON_QUERY, seperti yang ditunjukkan dalam contoh berikut.

SELECT col1, col2, col3, JSON_QUERY(jsoncol1) AS jsoncol1  
FROM tab1  
FOR JSON PATH  

JSON_QUERY tanpa parameter kedua opsional hanya mengembalikan argumen pertama sebagai hasilnya. Karena JSON_QUERY selalu mengembalikan JSON yang valid, FOR JSON tahu bahwa hasil ini tidak harus diloloskan.

JSON yang dihasilkan dengan klausul WITHOUT_ARRAY_WRAPPER lolos dalam output FOR JSON

Pertanyaan. Saya mencoba memformat ekspresi kolom dengan menggunakan FOR JSON dan opsi WITHOUT_ARRAY_WRAPPER.

SELECT 'Text' as myText,  
   (SELECT 12 day, 8 mon FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) as myJson  
FOR JSON PATH   

Tampaknya teks yang dikembalikan oleh kueri FOR JSON lolos sebagai teks biasa. Ini terjadi hanya jika WITHOUT_ARRAY_WRAPPER ditentukan. Mengapa tidak diperlakukan sebagai objek JSON dan menyertakan unescaped dalam hasilnya?

Jawaban. Jika Anda menentukan WITHOUT_ARRAY_WRAPPER opsi di dalam FOR JSON, teks JSON yang dihasilkan belum tentu valid JSON. Oleh karena itu bagian luar FOR JSON mengasumsikan bahwa ini adalah teks biasa dan lolos dari string. Jika Anda yakin bahwa output JSON valid, bungkus dengan JSON_QUERY fungsi untuk mempromosikannya ke JSON yang diformat dengan benar, seperti yang ditunjukkan dalam contoh berikut.

SELECT 'Text' as myText,  
      JSON_QUERY((SELECT 12 day, 8 mon FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)) as myJson  
FOR JSON PATH    

Input OPENJSON dan JSON

Mengembalikan sub-objek JSON berlapis dari teks JSON dengan OPENJSON

Pertanyaan. Saya tidak dapat membuka array objek JSON kompleks yang berisi nilai skalar, objek, dan array menggunakan OPENJSON dengan skema eksplisit. Saat saya mereferensikan kunci dalam klausul WITH, hanya nilai skalar yang dikembalikan. Objek dan array dikembalikan sebagai nilai null. Bagaimana cara mengekstrak objek atau array sebagai objek JSON?

Jawaban. Jika Anda ingin mengembalikan objek atau array sebagai kolom, gunakan opsi AS JSON dalam definisi kolom, seperti yang diperlihatkan dalam contoh berikut.

SELECT scalar1, scalar2, obj1, obj2, arr1  
FROM OPENJSON(@json)  
    WITH ( scalar1 int,  
        scalar2 datetime2,  
        obj1 NVARCHAR(MAX) AS JSON,  
        obj2 NVARCHAR(MAX) AS JSON,  
        arr1 NVARCHAR(MAX) AS JSON)  

Mengembalikan nilai teks panjang dengan OPENJSON alih-alih JSON_VALUE

Pertanyaan. Saya memiliki kunci deskripsi dalam teks JSON yang berisi teks panjang. JSON_VALUE(@json, '$.description') mengembalikan NULL alih-alih nilai.

Jawaban. JSON_VALUE dirancang untuk mengembalikan nilai skalar kecil. Umumnya fungsi mengembalikan NULL alih-alih kesalahan luapan. Jika Anda ingin mengembalikan nilai yang lebih panjang, gunakan OPENJSON, yang mendukung nilai NVARCHAR(MAX), seperti yang ditunjukkan dalam contoh berikut.

SELECT myText FROM OPENJSON(@json) WITH (myText NVARCHAR(MAX) '$.description')  

Menangani kunci duplikat dengan OPENJSON alih-alih JSON_VALUE

Pertanyaan. Saya memiliki kunci duplikat dalam teks JSON. JSON_VALUE hanya mengembalikan kunci pertama yang ditemukan di jalur. Bagaimana cara mengembalikan semua kunci yang memiliki nama yang sama?

Jawaban. Fungsi skalar JSON bawaan hanya mengembalikan kemunculan pertama objek yang dirujuk. Jika Anda memerlukan lebih dari satu kunci, gunakan fungsi bernilai tabel OPENJSON, seperti yang ditunjukkan dalam contoh berikut.

SELECT value FROM OPENJSON(@json, '$.info.settings')  
WHERE [key] = 'color'  

OPENJSON memerlukan tingkat kompatibilitas 130

Pertanyaan. Saya mencoba menjalankan OPENJSON di SQL Server 2016 dan saya mendapatkan kesalahan berikut.

Msg 208, Level 16, State 1 'Invalid object name OPENJSON'

Jawaban. Fungsi OPENJSON hanya tersedia di bawah tingkat kompatibilitas 130. Jika tingkat kompatibilitas DB Anda lebih rendah dari 130, OPENJSON disembunyikan. Fungsi JSON lainnya tersedia di semua tingkat kompatibilitas.

Pertanyaan lain

Kunci referensi yang berisi karakter non-alfanumerik dalam teks JSON

Pertanyaan. Saya memiliki karakter non-alfanumerik dalam kunci dalam teks JSON saya. Bagaimana cara mereferensikan properti ini?

Jawaban. Anda harus mengelilinginya dengan tanda kutip di jalur JSON. Contohnya:JSON_VALUE(@json, '$."$info"."First Name".value')

Pelajari selengkapnya tentang JSON di SQL Server dan Azure SQL Database

Video Microsoft

Catatan

Beberapa tautan video di bagian ini mungkin tidak berfungsi saat ini. Microsoft memigrasikan konten sebelumnya di Channel 9 ke platform baru. Kami akan memperbarui tautan saat video dimigrasikan ke platform baru.

Untuk pengenalan visual dukungan JSON bawaan di SQL Server dan Azure SQL Database, lihat video berikut ini: