2

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)

2
  • You'd have to unnest and aggregate the JSON again. I guess the jsonb_set method 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. Commented Jun 15, 2021 at 5:57
  • could you provide an example pls? Commented Jun 15, 2021 at 6:08

1 Answer 1

5
create type t_json_val as (path text[], val jsonb);

create or replace function jsonb_mset(a jsonb, variadic b t_json_val[])
    returns jsonb
    immutable
    language plpgsql
as $$
-- Set multiple jsonb values at once
declare
    bb t_json_val;
begin
    foreach bb in array b loop
        a := jsonb_set(a, bb.path, bb.val);
    end loop;
    return a;
end $$;
select jsonb_pretty(jsonb_mset('{
  "food": {
            "type": {
                     "id": 1,
                      "name": "good"
                    },
            "category": {
                          "id": 2,
                          "name": "Vegetables"
                       },
            "others": {
                       "descr": "It''s all good"
                     }
         }
}',
('{food, type, name}', '"not bad"'),
('{food, category, id}', '"1"')));

┌──────────────────────────────────────┐
│             jsonb_pretty             │
├──────────────────────────────────────┤
│ {                                   ↵│
│     "food": {                       ↵│
│         "type": {                   ↵│
│             "id": 1,                ↵│
│             "name": "not bad"       ↵│
│         },                          ↵│
│         "others": {                 ↵│
│             "descr": "It's all good"↵│
│         },                          ↵│
│         "category": {               ↵│
│             "id": "1",              ↵│
│             "name": "Vegetables"    ↵│
│         }                           ↵│
│     }                               ↵│
│ }                                    │
└──────────────────────────────────────┘

demo

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

1 Comment

ok... all I gotta say is: WOW!!!! that is exactly it.. I was about to write my own function because it looked like there is no "built-in" tool to use.. but you nailed it :) thx a lot.. saved a ton of my time!!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.