Postgres jsonb field to array

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.

I also want to ensure for some Attributes that have an empty string “Attributes”: “”, they get mapped to an empty list and not a list with an empty string ([] not [“”])

Answer

You can use jsonb_set(), and some conditional logic for the empty string:

jsonb_set(
    mycol,
    '{Attributes}',
    case when js ->> 'Attributes' <> '' 
        then jsonb_build_array(js ->> 'Attributes')
        else '[]'::jsonb
    end
)

Advertisement

Answer