8

For versions greater than 9.5 see this question

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.

klin
  • 112,967
  • 15
  • 204
  • 232
govindpatel
  • 2,539
  • 2
  • 19
  • 20
  • "*I dont wanted to create mutiple rows for this*" - why not? That seems to be the natural way of doing this, normalizing your data. Just because Postgres offers NoSQL/non-relational data types doesn't mean they have to be used for everything. I don't see anything in you example that would require de-normalizing this list of values into one JSON document. –  Jun 08 '15 at 11:26
  • How do you want to remove the value, by index or by value? – Evan Carroll Feb 14 '17 at 17:32

2 Answers2

5

Radek's idea can be used to define these handy functions:

create function jsonb_array_append(j jsonb, e text)
returns jsonb language sql immutable
as $$
    select array_to_json(array_append(array(select * from jsonb_array_elements_text(j)), e))::jsonb 
$$;

create function jsonb_array_remove(j jsonb, e text)
returns jsonb language sql immutable
as $$
    select array_to_json(array_remove(array(select * from jsonb_array_elements_text(j)), e))::jsonb 
$$;

create function jsonb_array_replace(j jsonb, e1 text, e2 text)
returns jsonb language sql immutable
as $$
    select array_to_json(array_replace(array(select * from jsonb_array_elements_text(j)), e1, e2))::jsonb 
$$;

The functions in action:

select jsonb_array_append('["alfa", "beta", "gamma"]', 'delta');
         jsonb_array_append
------------------------------------
 ["alfa", "beta", "gamma", "delta"]

select jsonb_array_remove('["alfa", "beta", "gamma"]', 'beta');
 jsonb_array_remove
-------------------
 ["alfa", "gamma"]

select jsonb_array_replace('["alfa", "beta", "gamma"]', 'alfa', 'delta');
     jsonb_array_replace
----------------------------
 ["delta", "beta", "gamma"]

If they prove useful for you, please appreciate Radek's answer. However, I have to add I fully agree with a_horse's comment.

klin
  • 112,967
  • 15
  • 204
  • 232
4

To add:

update jsontesting 
set jsondata = array_to_json(array(select * from jsonb_array_elements_text(jsondata)) || 'newString'::text)::jsonb 
where id = 7;

To remove:

update jsontesting 
set jsondata = array_to_json(array_remove(array(select * from jsonb_array_elements_text(jsondata)), 'toRemove'))::jsonb 
where id = 7;
Radek Postołowicz
  • 4,506
  • 2
  • 30
  • 47