3

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

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

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;
  • This is totally overkill even for 9.4. On 9.5 you can just use || – Evan Carroll Apr 4 '17 at 18:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.