Append JSON element to array using postgres 9.4

Question

I am attempting to append a JSON element to an already existing array in my database. I know about jsonb_set however I can't upgrade to Postgres 9.4 as this is a groovy project and the latest postgresql version on Maven is 9.4.

I currently have a json structure like this:

"playersContainer": {
        "players": [
            {
                "id": "1",
                "name": "Nick Pocock",
                "teamName": "Shire Soldiers",
                "bio" : "Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nulla imperdiet lorem tellus, in bibendum sem dignissim sed. Etiam eu elit sit amet lacus accumsan blandit sed ut dolor. Mauris vel dui non nisi vestibulum commodo vel id magna. Donec egestas magna in tincidunt mollis. Fusce mauris arcu, rhoncus ut lacus sed, fermentum ultrices elit. In sollicitudin at ex dapibus vestibulum. Pellentesque congue, est id lobortis viverra, mauris lectus pharetra orci, ut suscipit nisl purus vehicula est. Aliquam suscipit non velit vel feugiat. Quisque nec dictum augue.",
                "ratings": [
                    1,
                    5,
                    6,
                    9
                ],
                "assists": 17,
                "manOfTheMatches": 20,
                "cleanSheets": 1,
                "data": [
                    3,
                    2,
                    3,
                    5,
                    6
                ],
                "totalGoals": 19

            }
}

I want to append new players to the player array and update my database, I tried doing an update but it wiped the rest of the data. Is there a way to target the players array and append the new json object to it?

Possible duplicate of [Appending (pushing) and removing from a JSON array in PostgreSQL 9.2, 9.3, and 9.4?](http://stackoverflow.com/questions/30707482/appending-pushing-and-removing-from-a-json-array-in-postgresql-9-2-9-3-and-9) — Evan Carroll, Apr 04 '17 at 18:39
Possible duplicate of [append a json object to an array within my postgres table](https://stackoverflow.com/questions/42076465/append-a-json-object-to-an-array-within-my-postgres-table) — Thomas Hunter II, Aug 11 '17 at 17:59

Roman Tkachuk · Answer 1 · 2017-02-06T20:15:57.610

0

Unfortunately 9.4 does not have json_set function. You can use something like this:

WITH old_players AS (
       SELECT $1 AS j
  ), new_player AS (
       SELECT $2 AS j
  ), all_players AS (
       SELECT json_array_elements(j->'playersContainer'->'players') AS p
         FROM old_players
        UNION ALL
       SELECT *
         FROM new_player
  )      SELECT ('{"playersContainer": {"players": ' || json_agg(p) || '}}')::json
    FROM all_players
;

You can use it as function:

CREATE OR REPLACE FUNCTION add_player(old_player json, new_player json) RETURNS json AS $$
WITH all_players AS (
       SELECT json_array_elements(($1)->'playersContainer'->'players') AS p
        UNION ALL
       SELECT $2
  ) 
  SELECT ('{"playersContainer": {"players": ' || json_agg(p) || '}}')::json
    FROM all_players
$$ LANGUAGE sql IMMUTABLE;

And after that you can just call it :

UPDATE site_content SET content = add_player(content, '{"id": "2",  "name": "Someone Else"}') where id = :id;

edited Feb 06 '17 at 20:15

answered Feb 06 '17 at 19:16

Roman Tkachuk

3,096
1
16
15

I am trying to use array_append but having no luck, I get any error saying it doesn't exist even though I am using 9.4 I try to get my players array using -> notation then append my new JSON, like this sql.executeUpdate(""" UPDATE site_content SET content = array_append(content->'playersContainer'->'players', '${json}'); where id = :id """, id: player.teamId) – N P Feb 06 '17 at 20:03
array_append is not json function (https://www.postgresql.org/docs/9.5/static/functions-json.html) – Roman Tkachuk Feb 06 '17 at 20:07
http://www.w3resource.com/PostgreSQL/postgresql_array_append-function.php – N P Feb 06 '17 at 20:08
9.4 also does not have json_agg. – Evan Carroll Apr 04 '17 at 17:54

Append JSON element to array using postgres 9.4

1 Answers1