I don't know why, but probably PHP persisted some of my data as object and some of them as array. My table looks something like:
seller_info_address Table:
ID (INT) | address (JSONB) |
------------+--------------------------------------------------|
1 | {"addressLines":{"0":"Technology Park",...},...} |
2 | {"addressLines":["Technology Park",...],...} |
Some addressLines are objects:
{
"addressLines": {
"0": "Technology Park",
"1": "Blanchard Road",
"3": "Dublin",
"4": "2"
},
"companyName": "...",
"emailAddress": [],
"...": "..."
}
Some addressLines are arrays:
{
"addressLines": [
"Technology Park",
"Blanchard Road",
"Dublin",
"2"
],
"companyName": "...",
"emailAddress": [],
"...": "..."
}
I would like to equalize the data with a SQL query, but I'm not sure how to do it. All addressLines
persisted as object should be updated to array form.
I am grateful for help, thanks!
You can convert the objects to an array using this:
select id, (select jsonb_agg(e.val order by e.key::int)
from jsonb_each(sia.address -> 'addressLines') as e(key,val))
from seller_info_address sia
where jsonb_typeof(address -> 'addressLines') = 'object'
The where condition makes sure we only do this for addresslines that are not an array.
The aggregation used can also be used inside an UPDATE statement:
update seller_info_address
set address = jsonb_set(address, '{addressLines}',
(select jsonb_agg(e.val order by e.key::int)
from jsonb_each(address -> 'addressLines') as e(key,val))
)
where jsonb_typeof(address -> 'addressLines') = 'object';