AnswerBun.com

Pushing or appending to JSON array in PostgreSQL 9.4

Database Administrators Asked by genichm on December 7, 2020

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;

3 Answers

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;

Correct answer by genichm on December 7, 2020

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;

Answered by Rajan Brahma on December 7, 2020

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

Answered by Evan Carroll on December 7, 2020

Add your own answers!

Related Questions

Issues with MySQL 8.0.17

1  Asked on July 31, 2020 by vishal

       

SSRS Standalone install license

1  Asked on July 22, 2020 by preben-huybrechts

     

Ask a Question

Get help from others!

© 2022 AnswerBun.com. All rights reserved. Sites we Love: PCI Database, MenuIva, UKBizDB, Menu Kuliner, Sharing RPP, SolveDir