Pushing or appending to JSON array in PostgreSQL 9.4

Question

I have table where one of the fields is JSON array. I need to append received JSON array into that field without overriding existing values.

Something like that:

CREATE OR REPLACE FUNCTION add_array(
 array_received json[])
 RETURNS void AS
$BODY$
 update table set _array_field = _array_field | array_received ...;
$BODY$
 LANGUAGE plpgsql VOLATILE;

Answer 1

In pre-9.5 you can use json_array_elements, and array_to_json(array_agg()), like this.

SELECT array_to_json(array_agg(x))
FROM (
 SELECT jsonb_array_elements('[1,2,3]'::jsonb)
 UNION ALL SELECT '4'::jsonb
) AS t(x);

You can also write this using the ARRAY constructor like this..

SELECT array_to_json(ARRAY(
 SELECT jsonb_array_elements('[1,2,3]'::jsonb)
 UNION ALL SELECT '4'::jsonb
));

Answer 2

Faced a similar problem, so I tried the following which worked for me. my schema was something like this, temptable(id serial, events jsonb) and the jsonb array was like this,

[{"ts": "t1", "event": "e1", "value": {"search": ["s1"]}}, {"ts": "t2", "event": "e2", "value": {"search": ["s2"]}}]

my objective was to add a new item ('{"event":"e2","value":{"search":["s2"]},"ts":"t2"}') to the array,

the following query worked for me

--insert an array element
UPDATE temptable
SET events = jsonb_set(
 events::jsonb,
 concat('{',jsonb_array_length(events),'}')::text[],
 '{"event":"f2","value":{"search":["f2"]},"ts":"f2"}'::jsonb) 
WHERE id = 6;

Answer 3

I did it with plv8 language. For windows users get packages from

PosgreSQL 9.3

http://www.postgresonline.com/journal/archives/305-PostgreSQL-9.3-extension-treats-for-windows-users-plV8.html

PostgreSQL 9.4

http://www.postgresonline.com/journal/archives/341-PLV8-binaries-for-PostgreSQL-9.4-windows-both-32-bit-and-64-bit.html

PostgreSQL 9.5

http://www.postgresonline.com/journal/archives/360-PLV8-binaries-for-PostgreSQL-9.5-windows-both-32-bit-and-64-bit.html

PostgreSQL 9.6 Beta1

http://www.postgresonline.com/journal/archives/367-PLV8-binaries-for-PostgreSQL-9.6beta1-windows-both-32-bit-and-64-bit.html

Then run this command

CREATE EXTENSION plv8

This is the function

CREATE OR REPLACE FUNCTION jsonarray_append(row_id bigint, append json[]) RETURNS void AS $$
 var json_array = [];
 var json_result = plv8.execute('select json_array_field from sometable where _id=1ドル',[row_id]);
 if(json_result[0].json_array_field != null){
 for(var i=0; i < append.length; i++){
 json_result[0].json_array_field .push(append[i]);
 }
 json_array = json_result[0].json_array_field;
 }
 else{
 json_array = append;
 }
 plv8.execute('update sometable set json_array_field = 1ドル where _id=2ドル', [json_array, row_id]);
$$ LANGUAGE plv8 IMMUTABLE STRICT;

• Evan Carroll: This is totally overkill even for 9.4. On 9.5 you can just use ||