I have created a table in PostgreSQL using this:
CREATE TEMP TABLE jsontesting
AS
SELECT id, jsondata::jsonb FROM ( VALUES
(1, '["abra","value","mango", "apple", "sample"]'),
(2, '["japan","china","india", "russia", "australia"]'),
(3, '["must", "match"]'),
(4, '["abra","value","true", "apple", "sample"]'),
(5, '["abra","false","mango", "apple", "sample"]'),
(6, '["string","value","mango", "apple", "sample"]'),
(7, '["must", "watch"]')
) AS t(id,jsondata);
Now what I wanted was to
add Something like append_to_json_array takes in the actual jsondata which is a json-array and the newString which I have to add to that jsondata array and this function should return the updated json-array.
UPDATE jsontesting
SET jsondata=append_to_json_array(jsondata, 'newString')
WHERE id = 7;
remove a value from the json data array, one function for removing the value.
I tried to search documentation of PostgreSQL but found nothing there.
To add the value use the JSON array append opperator (||
)
UPDATE jsontesting
SET jsondata = jsondata || '["newString"]'::jsonb
WHERE id = 7;
Removing the value looks like this
UPDATE jsontesting
SET jsondata = jsondata - "newString"
WHERE id = 7;
Concatenating to a nested field looks like this
UPDATE jsontesting
SET jsondata = jsonb_set(
jsondata::jsonb,
array['nestedfield'],
(jsondata->'nestedfield')::jsonb || '["newString"]'::jsonb)
WHERE id = 7;