Append a json object to a json array

Question

I have a column in DB of type json. The default value of it is [].

Im trying to append a json object to it, and every time it gets updated it will be appended with another json object.

data coming in

{"name":"foo", "timestamp": 123}

new value in DB should be:

[{"name":"foo", "timestamp": 123}]

another Update to row:

{"name":"john", "timestamp": 234}

updated value in row should now be:

[{"name":"foo", "timestamp": 123},{"name":"john", "timestamp": 234}]

I tried:

NEW."dryWeight" = OLD."dryWeight" || NEW."dryWeight";

maybe it is good if you use varchar store you data in string and take it from db change it whatever you want than update it again — Nurbek Boymurodov
– Nurbek Boymurodov, Commented Jun 27, 2020 at 8:08

score 1 · Accepted Answer · 2020-06-27 08:59:05Z

1

The || operator needs an array on the right if the value on the left side is an array as well

update the_table
   set the_column = the_column || '[{"name":"bar", "timestamp": 234}]'
where ...;

|| only works with the jsonb, not with a "plain" json

edited Jun 27, 2020 at 8:59

answered Jun 27, 2020 at 8:14

user330315

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

2 Comments

Harry Over a year ago

Thank you, this is how I am trying now: NEW."dryWeight" = OLD."dryWeight"::jsonb || NEW."dryWeight"::jsonb; new value i send is in array too, but then updated value is an array with only the latest object, not the object appended to array

user330315 Over a year ago

|| only works with jsonb, not json what is the actual data type you are using (it should be jsonb). You will need to show us your complete trigger function. (edit your question, don't put code in comments)

Collectives™ on Stack Overflow

Append a json object to a json array

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related