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.

Please provide sample data and expected results.

GMB
– GMB

2020-03-04 15:27:37 +00:00
Commented Mar 4, 2020 at 15:27 — GMB
– GMB, Commented Mar 4, 2020 at 15:27
@GMB just updated the question, thanks for responding

blazerix
– blazerix

2020-03-04 15:32:52 +00:00
Commented Mar 4, 2020 at 15:32 — blazerix
– blazerix, Commented Mar 4, 2020 at 15:32

GMB · Accepted Answer · 2020-03-04 15:40:44Z

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"]}

answered Mar 4, 2020 at 15:40

GMB

224k25 gold badges103 silver badges151 bronze badges

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

5 Comments

blazerix Over a year ago

Thank you very much - do you know how I could use my table in the query you provided? I've replaced '{ "Id:1, "Attributes : "Test"}' with MY_TABLE.data but getting syntax issues.

GMB Over a year ago

@blazerix: select jsonb_set(data, ...) from mytable

blazerix Over a year ago

I have the following:

with t as (select jsonb_set(data) from public.mytable as js)     select          js,          jsonb_set(js, '{Attributes}', jsonb_build_array(js->> 'Attributes')) new_js     from t

but it says function jsonb_set does not exist

GMB Over a year ago

@blazerix: no. Just: select data, jsonb_set(data, '{Attributes}', jsonb_build_array(data ->> 'Attributes')) new_data from mytable.

blazerix Over a year ago

Thank you very much, this is what I was looking for. I will read more into jsonb_set and jsonb_build_array so I have a more clear understanding.

Collectives™ on Stack Overflow

Modify field inside of Jsonb in Postgres

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related