I have been facing a problem recently regarding JSONB data type in my Postgresql DB.
I have a rather complex structure of my column (let's say the table is called RATING and the column name FOOD_VALUE - making it up) which goes, for example, as follows:
{
"food": {
"type": {
"id": 1,
"name": "good"
},
"category": {
"id": 2,
"name": "Vegetables"
},
"others": {
"descr": "It's all good"
}
}
}
This is an example, the actual one might have up to hundreds of values (nested of course).
I want to update several values (in this example two -> food.type.name + food.category.id) without modifying the structure itself.
This is the desired output:
{
"food": {
"type": {
"id": 1,
"name": "not bad"
},
"category": {
"id": 1,
"name": "Vegetables"
},
"others": {
"descr": "It's all good"
}
}
}
With JSONB_SET I can only manipulate one record within the same column -> RATING.FOOD_VALUE ... I know I can nest them as well, but considering I need to update 40+ values, this might become a hell to read (and therefore maintain)
So this:
UPDATE rating
SET food_value = jsonb_set(
jsonb_set(food_value,'{food, type, name}', '"not bad"')::jsonb
,'{food, category, id}','"1"'::jsonb)
WHERE ....
is definitely a no-go ..
Another way of achieving this is to use JSON_BUILD_OBJECT:
UPDATE rating
SET food_value = food_value
|| jsonb_build_object('food', jsonb_build_object('type', jsonb_build_object('name', 'not bad')))
|| jsonb_build_object('food', jsonb_build_object('category', jsonb_build_object('id', 1)))
... and so on
WHERE ....
This looks a bit more promising when it comes to maintenance, readability, etc.. but the problem I faced here is that it changes the structure of the JSON files format :( so the query above will result in
{
"food": {
"type": {
"name": "not bad"
},
"category": {
"id": 1
}
}
}
Any idea how to get out of this predicament? I need to find a way how to update dozens of fields inside a complex JSON structure without modification of the structure itself (just values)
jsonb_setmethod might be the most readable one. JSON is always messier in SQL than a normalized schema; that's kind of in the nature of things.