Contoh query update data JSON :
UPDATE NAMA_TABLE
SET NAMA_COLUMN=jsonb_set(NAMA_COLUMN, '{NAMA_KEY}', jsonb_set(NAMA_COLUMN->'NAMA_KEY', ?, VALUE_ANDA) , false)
RETURNING NAMA_COLUMN
Penjelasan
Syntax
jsonb_set(target jsonb, path text[], new_value jsonb[, create_missing boolean])
Keterangan
Fungsi jsonb_set adalah mengembalikan target dengan bagian yang ditunjuk kemudian digantikan oleh nilai baru, atau dengan nilai baru yang akan ditambahkan jika create_missing TRUE (default value adalah TRUE) dan value create_missing FALSE serta item yang ditunjuk tidak ada, maka tidak akan menambah nilai baru.
Operator
| Operator | Right Operand Type | Description | Example |
|---|---|---|---|
@> |
jsonb |
Does the left JSON value contain the right JSON path/value entries at the top level? | '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb |
<@ |
jsonb |
Are the left JSON path/value entries contained at the top level within the right JSON value? | '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb |
? |
text |
Does the string exist as a top-level key within the JSON value? | '{"a":1, "b":2}'::jsonb ? 'b' |
?| |
text[] |
Do any of these array strings exist as top-level keys? | '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c'] |
?& |
text[] |
Do all of these array strings exist as top-level keys? | '["a", "b"]'::jsonb ?& array['a', 'b'] |
|| |
jsonb |
Concatenate two jsonb values into a new jsonb value |
'["a", "b"]'::jsonb || '["c", "d"]'::jsonb |
- |
text |
Delete key/value pair or string element from left operand. Key/value pairs are matched based on their key value. | '{"a": "b"}'::jsonb - 'a' |
- |
text[] |
Delete multiple key/value pairs or string elements from left operand. Key/value pairs are matched based on their key value. | '{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[] |
- |
integer |
Delete the array element with specified index (Negative integers count from the end). Throws an error if top level container is not an array. | '["a", "b"]'::jsonb - 1 |
#- |
text[] |
Delete the field or element with specified path (for JSON arrays, negative integers count from the end) | '["a", {"b":1}]'::jsonb #- '{1,b}' |
