PostgreSQL Upsert item to an array of jsonb dicts based on a value inside & return TRUE/FALSE depending if it inserts or overwrites

Asked Sep 06 '19 at 22:53

Active Sep 07 '19 at 07:45

Viewed 91 times

This is a rather difficult one to explain, been stuck on this issue for 5 days now, but I'll do my best to explain!

Let's say i got a jsonb list stored in a column (found in this DBFiddle link): https://www.db-fiddle.com/f/susZ1iBDiBbZvEJRUKT54i/14

I'd like to insert value:

{"a": 1, "b": 16, "c" : ["rock", "fridge", "fry", "pan"]}

however, if there's an already existing "a" containing '1', I'd want it to be replaced with the new values.

One bonus thing would be to detect if the value already existed or not, where it would return TRUE/FALSE statement based on if it was inserted or overwritten!

edited Sep 07 '19 at 07:45

asked Sep 06 '19 at 22:53

Gofven

https://stackoverflow.com/questions/42944888 or https://stackoverflow.com/questions/34686141 – Sep 07 '19 at 14:24
it doesn't really solve the problem i'm having currently, cause the format it's going to be in is [ {}, {}, {} ] – Gofven Sep 08 '19 at 21:57

PostgreSQL Upsert item to an array of jsonb dicts based on a value inside & return TRUE/FALSE depending if it inserts or overwrites

0 Answers0