Postgres how to turn a field into a json array

Question

I saved a json like this in a column of my db:

{"a":137,"b":"28","c":"1","d":"5","e":19,"f":true}

is it possible with a query to transform "e" into an array without removing the value?

{"a":137,"b":"28","c":"1","d":"5","e":[19],"f":true}

user330315 · Accepted Answer · 2020-07-08 14:51:44Z

1

update the_table
  set the_column = the_column||jsonb_build_object('e', array_to_json(array[the_column -> 'e']))
where ...

array[the_column -> 'e'] creates a "native" array out of the single element referenced by the key 'e'. This array is converted to JSON and a new JSON value is created using jsonb_build_object() which is then concatenated to the existing value. This will overwrite the existing key "e".

The above assumes that the column is defined as jsonb (which it should be). If it's only json you need to cast it to make the replacement work with ||

answered Jul 8, 2020 at 14:51

user330315

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

Collectives™ on Stack Overflow

Postgres how to turn a field into a json array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related