JSON

JSONB_SET

PostgreSQL

Mengganti atau menambah nilai pada path tertentu dalam JSON. Mengembalikan JSONB baru dengan nilai yang sudah diupdate.

Tipe hasil: jsonb

Syntax

SQL

JSONB_SET(target, path, new_value [, create_if_missing])

targetjsonbwajib

JSON data asli

pathtext[]wajib

Path ke nilai yang akan diubah

new_valuejsonbwajib

Nilai baru

create_if_missingbooleanopsional

Buat path jika tidak ada (default true)

SQL

1 SELECT JSONB_SET(
2   {"name": "John", "age": 30}::jsonb,
3   {age},
4   31::jsonb
5 );

Mengubah nilai age dari 30 ke 31.

Hasil

jsonb_set
{"name": "John", "age": 31}

SQL

1 SELECT JSONB_SET(
2   {"user": {"name": "John", "address": {"city": "Jakarta"}}}::jsonb,
3   {user,address,city},
4   "Surabaya"::jsonb
5 );

Mengubah nilai nested city.

Hasil

{"user": {"name": "John", "address": {"city": "Surabaya"}}}

SQL

1 UPDATE users
2 SET metadata = JSONB_SET(
3   COALESCE(metadata, {}::jsonb),
4   {last_login},
5   TO_JSONB(NOW())
6 )
7 WHERE id = 1;

Menambahkan key baru ke JSON column.

1	SELECT JSONB_SET(
2	{"user": {"name": "John", "address": {"city": "Jakarta"}}}::jsonb,
3	{user,address,city},
4	"Surabaya"::jsonb
5	);