JSON_MODIFY (Transact-SQL)

  • Artikel

Berlaku untuk: SQL Server 2016 (13.x) dan yang lebih baru Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics

Updates nilai properti dalam string JSON dan mengembalikan string JSON yang diperbarui.

Konvensi sintaks Transact-SQL

Sintaks

JSON_MODIFY ( expression , path , newValue )

Argumen

ekspresi
Ekspresi. Biasanya nama variabel atau kolom yang berisi teks JSON.

JSON_MODIFY mengembalikan kesalahan jika ekspresi tidak berisi JSON yang valid.

jalur
Ekspresi jalur JSON yang menentukan properti yang akan diperbarui.

path memiliki sintaks berikut:

[append] [ lax | strict ] $.<json path>

  • Menambahkan
    Pengubah opsional yang menentukan bahwa nilai baru harus ditambahkan ke array yang direferensikan oleh <jalur> json.

  • Lax
    Menentukan bahwa properti yang dirujuk oleh <jalur> json tidak harus ada. Jika properti tidak ada, JSON_MODIFY mencoba menyisipkan nilai baru pada jalur yang ditentukan. Penyisipan mungkin gagal jika properti tidak dapat disisipkan pada jalur. Jika Anda tidak menentukan lax atau ketat, lax adalah mode default.

  • Ketat
    Menentukan bahwa properti yang direferensikan oleh <jalur> json harus berada dalam ekspresi JSON. Jika properti tidak ada, JSON_MODIFY mengembalikan kesalahan.

  • <jalur json>
    Menentukan jalur untuk memperbarui properti. Untuk informasi selengkapnya, lihat Ekspresi Jalur JSON (SQL Server).

Di SQL Server 2017 (14.x) dan di Azure SQL Database, Anda dapat memberikan variabel sebagai nilai jalur.

JSON_MODIFY mengembalikan kesalahan jika format jalur tidak valid.

newValue
Nilai baru untuk properti yang ditentukan oleh jalur.
Nilai baru harus berupa [n]varchar atau teks.

Dalam mode lax, JSON_MODIFY menghapus kunci yang ditentukan jika nilai baru adalah NULL.

JSON_MODIFY keluar dari semua karakter khusus dalam nilai baru jika jenis nilainya adalah NVARCHAR atau VARCHAR. Nilai teks tidak lolos jika JSON diformat dengan benar yang dihasilkan oleh FOR JSON, JSON_QUERY, atau JSON_MODIFY.

Tampilkan Nilai

Mengembalikan nilai ekspresi yang diperbarui sebagai teks JSON yang diformat dengan benar.

Keterangan

Fungsi JSON_MODIFY memungkinkan Anda memperbarui nilai properti yang ada, menyisipkan pasangan kunci:nilai baru, atau menghapus kunci berdasarkan kombinasi mode dan nilai yang disediakan.

Tabel berikut membandingkan perilaku JSON_MODIFY dalam mode lax dan dalam mode ketat. Untuk informasi selengkapnya tentang spesifikasi mode jalur opsional (lax atau ketat), lihat Ekspresi Jalur JSON (SQL Server).

Nilai baru Jalur ada Mode lax Mode ketat
Bukan NULL Ya Perbarui nilai yang ada. Perbarui nilai yang ada.
Bukan NULL Tidak Cobalah untuk membuat pasangan kunci:nilai baru pada jalur yang ditentukan.

Ini mungkin gagal. Misalnya, jika Anda menentukan jalur $.user.setting.theme, JSON_MODIFY tidak menyisipkan kunci theme jika $.user objek atau $.user.settings tidak ada, atau jika pengaturan adalah array atau nilai skalar.		 Kesalahan - INVALID_PROPERTY
NULL Ya Hapus properti yang ada. Atur nilai yang ada ke null.
NULL Tidak Tidak ada tindakan. Argumen pertama dikembalikan sebagai hasilnya. Kesalahan - INVALID_PROPERTY

Dalam mode lax, JSON_MODIFY mencoba membuat pasangan kunci:nilai baru, tetapi dalam beberapa kasus mungkin gagal.

Contoh

Contoh - Operasi dasar

Contoh berikut menunjukkan operasi dasar yang dapat dilakukan dengan teks JSON.

Kueri


DECLARE @info NVARCHAR(100)='{"name":"John","skills":["C#","SQL"]}'

PRINT @info

-- Update name  

SET @info=JSON_MODIFY(@info,'$.name','Mike')

PRINT @info

-- Insert surname  

SET @info=JSON_MODIFY(@info,'$.surname','Smith')

PRINT @info

-- Set name NULL 

SET @info=JSON_MODIFY(@info,'strict $.name',NULL)

PRINT @info

-- Delete name  

SET @info=JSON_MODIFY(@info,'$.name',NULL)

PRINT @info

-- Add skill  

SET @info=JSON_MODIFY(@info,'append $.skills','Azure')

PRINT @info

Hasil

{
    "name": "John",
    "skills": ["C#", "SQL"]
} {
    "name": "Mike",
    "skills": ["C#", "SQL"]
} {
    "name": "Mike",
    "skills": ["C#", "SQL"],
    "surname": "Smith"
} {
    "skills": ["C#", "SQL"],
    "surname": "Smith"
} {
    "skills": ["C#", "SQL", "Azure"],
    "surname": "Smith"
}

Contoh - Beberapa pembaruan

Dengan JSON_MODIFY Anda hanya dapat memperbarui satu properti. Jika Anda harus melakukan beberapa pembaruan, Anda dapat menggunakan beberapa panggilan JSON_MODIFY.

Kueri

DECLARE @info NVARCHAR(100)='{"name":"John","skills":["C#","SQL"]}'

PRINT @info

-- Multiple updates  

SET @info=JSON_MODIFY(JSON_MODIFY(JSON_MODIFY(@info,'$.name','Mike'),'$.surname','Smith'),'append $.skills','Azure')

PRINT @info

Hasil

{
    "name": "John",
    "skills": ["C#", "SQL"]
} {
    "name": "Mike",
    "skills": ["C#", "SQL", "Azure"],
    "surname": "Smith"
}

Contoh - Mengganti nama kunci

Contoh berikut menunjukkan cara mengganti nama properti dalam teks JSON dengan fungsi JSON_MODIFY. Pertama, Anda dapat mengambil nilai properti yang ada dan menyisipkannya sebagai pasangan kunci:nilai baru. Kemudian Anda dapat menghapus kunci lama dengan mengatur nilai properti lama ke NULL.

Kueri

DECLARE @product NVARCHAR(100)='{"price":49.99}'

PRINT @product

-- Rename property  

SET @product=
 JSON_MODIFY(
  JSON_MODIFY(@product,'$.Price',CAST(JSON_VALUE(@product,'$.price') AS NUMERIC(4,2))),
  '$.price',
  NULL
 )

PRINT @product

Hasil

{
    "price": 49.99
} {
    "Price": 49.99
}

Jika Anda tidak mentransmisikan nilai baru ke jenis numerik, JSON_MODIFY memperlakukannya sebagai teks dan mengelilinginya dengan tanda kutip ganda.

Contoh - Menaikkan nilai

Contoh berikut menunjukkan cara menaikkan nilai properti dalam teks JSON dengan fungsi JSON_MODIFY. Pertama, Anda dapat mengambil nilai properti yang ada dan menyisipkannya sebagai pasangan kunci:nilai baru. Kemudian Anda dapat menghapus kunci lama dengan mengatur nilai properti lama ke NULL.

Kueri

DECLARE @stats NVARCHAR(100)='{"click_count": 173}'

PRINT @stats

-- Increment value  

SET @stats=JSON_MODIFY(@stats,'$.click_count',
 CAST(JSON_VALUE(@stats,'$.click_count') AS INT)+1)

PRINT @stats

Hasil

{
    "click_count": 173
} {
    "click_count": 174
}

Contoh - Memodifikasi objek JSON

JSON_MODIFY memperlakukan argumen newValue sebagai teks biasa meskipun berisi teks JSON yang diformat dengan benar. Akibatnya, output JSON dari fungsi dikelilingi dengan tanda kutip ganda dan semua karakter khusus lolos, seperti yang ditunjukkan dalam contoh berikut.

Kueri

DECLARE @info NVARCHAR(100)='{"name":"John","skills":["C#","SQL"]}'

PRINT @info

-- Update skills array

SET @info=JSON_MODIFY(@info,'$.skills','["C#","T-SQL","Azure"]')

PRINT @info

Hasil

{
    "name": "John",
    "skills": ["C#", "SQL"]
} {
    "name": "John",
    "skills": "[\"C#\",\"T-SQL\",\"Azure\"]"
}

Untuk menghindari pelepasan otomatis, berikan newValue dengan menggunakan fungsi JSON_QUERY. JSON_MODIFY tahu bahwa nilai yang dikembalikan oleh JSON_QUERY diformat dengan benar JSON, sehingga tidak lolos dari nilai.

Kueri

DECLARE @info NVARCHAR(100)='{"name":"John","skills":["C#","SQL"]}'

PRINT @info

-- Update skills array  

SET @info=JSON_MODIFY(@info,'$.skills',JSON_QUERY('["C#","T-SQL","Azure"]'))

PRINT @info

Hasil

{
    "name": "John",
    "skills": ["C#", "SQL"]
} {
    "name": "John",
    "skills": ["C#", "T-SQL", "Azure"]
}

Contoh - Memperbarui kolom JSON

Contoh berikut memperbarui nilai properti dalam kolom tabel yang berisi JSON.

UPDATE Employee
SET jsonCol=JSON_MODIFY(jsonCol,'$.info.address.town','London')
WHERE EmployeeID=17

Lihat juga