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;

genichm · Accepted Answer

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;

Rajan Brahma · Answer

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;

Evan Carroll · Answer

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
));

Pushing or appending to JSON array in PostgreSQL 9.4

3 Answers

Add your own answers!

Ask a Question