Modify field inside of Jsonb in Postgres

Question

I was going through the Postgres Jsonb documentation but was unable to find a solution for a small issue I'm having.

I've got a table : MY_TABLE

that has the following columns:

User, Name, Data and Purchased

One thing to note is that "Data" is a jsonb and has multiple fields. One of the fields inside of "Data" is "Attribute" but it is currently a string. How can I go about changing this to a list of strings?

I have tried using json_build_array but have not had any luck

So for example, I'd want my jsonb to look like :

   {
       "Id": 1,
       "Attributes": ["Test"]

   }

instead of

{
    "Id": 1,
    "Attributes": "Test"

}

I only care about the "Attributes" field inside of the Json, not any other fields.

Answer 1

You can do this with jsonb_set() and jsonb_build_array(), like so:

jsonb_set(js, '{Attributes}', jsonb_build_array(js->> 'Attributes'))

Demo on DB Fiddle:

with t as (select '{ "Dd":1, "Attributes":"Test"}'::jsonb js)
select 
    js, 
    jsonb_set(js, '{Attributes}', jsonb_build_array(js->> 'Attributes')) new_js
from t

js                              | new_js                           
:------------------------------ | :--------------------------------
{"Dd": 1, "Attributes": "Test"} | {"Dd": 1, "Attributes": ["Test"]}