UPDATE json array using aggregrate function

Question

Working with Postgres 9.3, Python 2.7, psycopg2.
I have a table called SomeTable with a json field some_json_array and row_id key.

some_json_array looks something like this:

"[{'key': 'value_one'}, {'key': 'value_two'}, etc]"

I also have a function in which I'm trying to add some elements to the json array of SomeTable corresponding to the given row_id.

My code is as follows:

CREATE OR REPLACE FUNCTION add_elements (insertion_id smallint, new_elements_json json)
RETURNS void AS $$
BEGIN
    UPDATE SomeTable
    SET some_json_array = (SELECT array_to_json(array_agg(some_json_array) || array_agg(new_elements_json)))
    WHERE row_id = insertion_id;
END;
$$ LANGUAGE plpgsql;

I get the following error:

Cannot use aggregate function in UPDATE

which I believe is complaining about array_agg().

How would I modify this function to make it work? I can stick the WHERE clause into the SELECT statement if necessary.

Community · Accepted Answer · 2017-04-13 12:42:40Z

2

Aggregate functions cannot be used like plain functions, only in an aggregate context.

Since Postgres cannot manipulate JSON arrays directly (it's just a valid json value to Postgres, not an array) you have to ...

either cast the json value to text, concatenate the new element, generating valid JSON syntax and transform back,
or transform to a Postgres array of json: json[], append the new element and transform this Postgres array back to a json value.
array_to_json() is smart enough to not encode the json elements of the Postgres array another time.

The first approach is much more error prone. You would have to build valid JSON by hand. Here is an implementation of the second variant:

CREATE OR REPLACE FUNCTION add_elements (_id int2, _elem json)
  RETURNS void AS
$func$
BEGIN
    UPDATE SomeTable s
    SET    some_json_array = array_to_json(ARRAY(
              SELECT * FROM json_array_elements(s.some_json_array)
              UNION  ALL SELECT _elem
              ))
    WHERE  s.row_id = _id;
END
$func$ LANGUAGE plpgsql;

The function wrapper is mostly irrelevant to the problem. Could just be a plain SQL statement.

How to turn json array into postgres array?

edited Apr 13, 2017 at 12:42

CommunityBot

11 silver badge

answered Feb 20, 2015 at 5:17

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Teboto Over a year ago

So it turns out that the resulting json from the add_elements() function doesn't actually become a true json object because I can no longer access the values using ->> without getting this error Cannot extract field from a non-object. Was this an expected side effect?

Teboto Over a year ago

I got it! So the current code returns a an array within an array, so all I did was replace UNION ALL SELECT _elem with UNION ALL SELECT json_array_elements(_elem) and got the original json format!

Erwin Brandstetter Over a year ago

My function expects a single JSON element, not a JSON array. Hence the parameter name _elem - guess I missed the intention behind your parameter name new_elements_json. Your fix is the way to go.

Collectives™ on Stack Overflow

UPDATE json array using aggregrate function

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related