Postgres: How to alter jsonb value type for each element in an array?

Question

I have a jsonb column named items in Postgres 10.12 like this:

{
    "items": [
        {
            "itemQty": 2,
            "itemName": "snake"
        },
        {
            "itemQty": 1,
            "itemName": "x kodiyum"
        }
    ]
}

Now I want to convert itemQty type to string for every array element so that the new values are like this:

{
    "items": [
        {
            "itemQty": "2",
            "itemName": "snake"
        },
        {
            "itemQty": "1",
            "itemName": "x kodiyum"
        }
    ]
}

How do I do this? I have gone through the documentation for Postgres jsonb and couldn't figure out.

On the server-side, I am using Spring boot and Hibernate with com.vladmihalcea.hibernate.type.json (Hibernate Types 52) if it helps.

Thanks

GMB · Accepted Answer · 2020-06-01 15:57:20Z

1

You could unnest the array, modify the elements, and then rebuild it. Assuming that the primary key of your table is id, that would be:

select jsonb_build_object(
    'items', jsonb_agg(
        jsonb_build_object(
            'itemQty',  (x.obj ->> 'itemQty')::text,
            'itemName', x.obj ->> 'Name'
        )
    ) 
    )new_items
from mytable t
cross join lateral jsonb_array_elements(t.items -> 'items') as x(obj)
group by id

Note that the explicit cast to ::text is not really needed here, as ->> extract text values anyway: I kept it because it makes the intent clearer.

If you want an update statement:

update mytable t
set items = (
    select jsonb_build_object(
        'items', jsonb_agg(
            jsonb_build_object(
                'itemQty',  (x.obj ->> 'itemQty')::text,
                'itemName', x.obj ->> 'Name'
            )
        ) 
    )
    from jsonb_array_elements(t.items -> 'items') as x(obj)
)

Demo on DB Fiddle

answered Jun 1, 2020 at 15:57

GMB

224k25 gold badges103 silver badges151 bronze badges

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

1 Comment

Tushar Garg Over a year ago

I have 30+ key-value pairs per element in the array. Do I need to include all of them in jsonb_build_object function?

Collectives™ on Stack Overflow

Postgres: How to alter jsonb value type for each element in an array?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related