CODEWITHSUNDEEP
postgresqlpostgresql-json
Harry
Harry

Reputation: 13329

Append a json object to a json array

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";

Upvotes: 0

Views: 838

Answers (1)

user330315
user330315

Reputation:

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

Upvotes: 1

Related Questions