Let's say I have a column with this jsonb data :
{
"indicators": [
{
"year": 2019,
"indicatorsByYear": [
{
"value": 3120,
"code": "Nb_h"
},
{
"value": 18,
"code": "S_ppa"
},
{
"value": 95,
"code": "T_occ"
}
]
},
{
"year": 2020,
"indicatorsByYear": [
{
"value": 300,
"code": "Nb_h"
},
{
"value": 18,
"code": "S_ppa"
},
{
"value": 55,
"code": "T_occ"
}
]
}
],
"dataProvidedByUser": false
}
The idea is to migrate this column to a simplified object like this :
{
"indicatorsByYear": {
"2019": [
{
"value": 3120,
"code": "Nb_h"
},
{
"value": 18,
"code": "S_ppa"
},
{
"value": 95,
"code": "T_occ"
}
],
"2020": [
{
"value": 300,
"code": "Nb_h"
},
{
"value": 18,
"code": "S_ppa"
},
{
"value": 55,
"code": "T_occ"
}
]
},
"dataProvidedByUser": false
}
How can I transform the indicators array to map object with year as key and indicatorsByYear as value.
For info, the maximum number of years that I can have is 11 years (from 2010 to 2020), some columns have all the years others only some.
My attempts with something like that without success
update site
SET data = data
|| jsonb_build_object('indicatorsByYear',
jsonb_build_object(
data -> 'indicators' ->> 'year',
data -> 'indicators' ->> 'indicatorsByYear'
))
Any help would be very much appreciated! Thanks in advance.
data -> 'indicators' is an array, whose elements you need to consider individually and then aggregate back together into an object. You can use jsonb_array_elements and jsonb_object_agg respectively for this.
Also, you'll want to remove the old indicators key from the data column.
UPDATE site
SET data = jsonb_set(
data - 'indicators',
'{indicatorsByYear}',
(
SELECT jsonb_object_agg(el ->> 'year', el -> 'indicatorsByYear')
FROM jsonb_array_elements(data -> 'indicators') el
)
);
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments