How to push to an array inside of a JSON object in a JSONB column in PostgreSQL

Question

I have the below data in a JSONB column in PostgreSQL.

{
    "myFirstDeck" : {
        "cards": [1, 2, 3]
    }
}

I would like to use one of the inbuilt JSONB PostgreSQL functions to push/append an integer to the cards array inside of the myFirstDeck key/object.

Any assistance would be much appreciated.

I have tried various variations using jsonb_set and concatenation but am struggling to get it working.

SELECT jsonb_set('{"myFirstDeck" : {"cards": [1, 2, 3]}}' :: jsonb, '{myFirstDeck,cards}' :: text[], to_jsonb('new_jsonb' :: text)). Result : {"myFirstDeck": {"cards": "new_jsonb"}}. see the manual — Edouard
– Edouard, Commented Jan 10, 2023 at 20:35
Two questions about card decks. Hmm. Same advice as I gave to that answer: use a join table. — Schwern
– Schwern, Commented Jan 10, 2023 at 20:38
Thanks for your response. This solution replaces the cards array with a new one, is there not an inbuilt way to append/push to the array without having to replace the original array with a new one? — Alex Mitchell
– Alex Mitchell, Commented Jan 10, 2023 at 20:43
"I have tried various variations using jsonb_set and concatenation" - please show us those attempts so that we can help you with them — Bergi
– Bergi, Commented Jan 10, 2023 at 20:49
I think a join table like @Schwern suggested is an easy solution to my query, thank you all. — Alex Mitchell
– Alex Mitchell, Commented Jan 10, 2023 at 20:52

user330315 · Accepted Answer · 2023-01-10 20:54:21Z

1

You can use jsonb_insert()

The following appends the value 4 at the end of the array

jsonb_insert(the_column, '{myFirstDeck,cards,-1}', to_jsonb(4), true)

But if you find yourself doing this a lot you should seriously consider using a properly normalized data model rather than abusing JSON.

answered Jan 10, 2023 at 20:54

user330315

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

1 Answer 1