Bagikan melalui

Memecahkan masalah umum dengan JSON di SQL Server

Berlaku untuk: SQL Server 2016 (13.x) dan versi yang lebih baru Azure SQL Database AzureSQL Managed InstanceAzure Synapse Analytics (hanya kumpulan SQL tanpa server)Database SQL di Microsoft Fabric

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

Untuk JSON dan keluaran JSON

UNTUK JSON PATH atau UNTUK JSON AUTO?

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

Answer. Gunakan FOR JSON PATH. Meskipun tidak ada perbedaan dalam output JSON, mode AUTO menerapkan beberapa logika tambahan yang memeriksa apakah kolom harus disarangkan. Pertimbangkan PATH sebagai opsi default.

Membuat struktur JSON berlapis

Question. Saya ingin menghasilkan JSON kompleks dengan beberapa array pada tingkat yang sama. UNTUK JSON PATH dapat membuat objek berlapis menggunakan jalur, dan FOR JSON AUTO membuat tingkat berlapis 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?

Answer. 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.

Menghindari JSON dengan karakter escape ganda dalam output FOR JSON

Question. Saya memiliki teks JSON yang disimpan dalam kolom tabel. Saya ingin menyertakannya 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.

Answer. JSON yang disimpan dalam kolom teks atau nilai literal 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 opsionalnya 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

Question. 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 hanya terjadi jika WITHOUT_ARRAY_WRAPPER ditentukan. Mengapa tidak diperlakukan sebagai objek JSON dan menyertakan unescaped dalam hasilnya?

Answer. Jika Anda menentukan opsi WITHOUT_ARRAY_WRAPPER di dalam FOR JSON, teks JSON yang dihasilkan mungkin tidak valid sebagai JSON. Oleh karena itu, 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

Question. 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 klausa WITH, hanya nilai skalar yang dikembalikan. Objek dan array dikembalikan sebagai nilai null. Bagaimana cara mengekstrak objek atau array sebagai objek JSON?

Answer. 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

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

Answer. JSON_VALUE dirancang untuk mengembalikan nilai skalar kecil. Umumnya fungsi mengembalikan NULL alih-alih kesalahan overflow. 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

Question. 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?

Answer. 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

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

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

Answer. Fungsi OPENJSON ini hanya tersedia di bawah tingkat kompatibilitas 130. Jika tingkat kompatibilitas database 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

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

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

Pelajari selengkapnya tentang JSON di SQL Database Engine

Untuk pengenalan visual dukungan JSON bawaan, lihat video berikut ini:

  • Last updated on