Insert new item in JSONB column based on value of other field - postgres

user_3790279 0
Viewed 2482

I have the following jsonb structure with many entries in it

[
   {
      "name":"test",
      "features":[
         {
            "name":"feature1",
            "granted":false
         },
         {
            "name":"feature2",
            "granted":true
         }
      ]
   }...
]

I'd like to add a new entry in the features array when the parent name element has value "test" and feature1 granted is "false".

The idea is to write a flyway script to migrate my data. I've been battling with jsonb_insert but I can't figure out the path portion of it since I can have potentially many elements in there and I can't just add a given subscript.

End result should be:

[
   {
      "name":"test",
      "features":[
         {
            "name":"feature1",
            "granted":false
         },
         {
            "name":"feature2",
            "granted":true
         },
         {
            "name":"newFeature",
            "granted":false
         }
      ]
   }
]

EDIT1

So far I've attempted:

UPDATE my_table SET modules =
    jsonb_insert(my_column, '{features, [0]}', '{"name": "newFeature", "granted": false}')
WHERE my_column ->> 'name' = 'test' AND my_column @> '{"features": [{"name":"feature1", "granted": false}]}';

The statement executes but no updates are actually done.

EDIT2

I modified the query just to test the PATH out to

UPDATE my_table SET modules =
    jsonb_insert(my_column, '{0, features, 0}', '{"name": "newFeature", "granted": false}')
WHERE my_column ->> 'name' = 'test' AND my_column @> '{"features": [{"name":"feature1", "granted": false}]}';

However this only always updates the first entry in the array, and the object I need to update is not guaranteed to always be in this position

jsonb flyway postgresql
1 Answers

This should be enough information to complete the query:

Let's create the mock data

create table a (id serial primary key , b jsonb);

insert into a (b)
values ('[
  {
    "name": "test",
    "features": [
      {
        "name": "feature1",
        "granted": false
      },
      {
        "name": "feature2",
        "granted": true
      }
    ]
  },
  {
    "name": "another-name",
    "features": [
      {
        "name": "feature1",
        "granted": false
      },
      {
        "name": "feature2",
        "granted": true
      }
    ]
  }
]');

Now explode the array using jsonb_array_elements with ordinality to get the index and the property

select first_level.id, position, feature_position, feature
from (select a.id, arr.*
      from a,
           jsonb_array_elements(a.b) with ordinality arr (elem, position)
      where elem ->> 'name' = 'test') first_level,
     jsonb_array_elements(first_level.elem -> 'features') with ordinality features (feature, feature_position);

The result of this query is:

1,1,1,"{""name"": ""feature1"", ""granted"": false}"
1,1,2,"{""name"": ""feature2"", ""granted"": true}"

There you have the necessary info that you need to fetch the sub elements that you need, as well as all the indexes that you needed for your query.

Now, to the final edit, you already had the query that you wanted:

UPDATE my_table SET modules =
    jsonb_insert(my_column, '{0, features, 0}', '{"name": "newFeature", "granted": false}')
WHERE my_column ->> 'name' = 'test' AND my_column @> '{"features": [{"name":"feature1", "granted": false}]}';

In the where you'll use the id, because those are the rows that you are interested in, and in the indexes you got them from the query. So:

UPDATE my_table SET modules =
    jsonb_insert(my_column, '{' || exploded_info.position::string || ', features, ' || exploded_info.feature_position || '}', '{"name": "newFeature", "granted": false}') from (/* previous query */) as exploded_info
WHERE exploded_info.id = my_table.id and exploded_info.feature -> 'granted' = false;

As you can see this easily get's very nasty.

I'd recommend either using a more sql approach, that is, having features in a table instead of inside a json, a fk linking that to your table... If you really need to use the json, for example, because the domain is really complex and defined at the application level and very flexible. Then I would recommend doing the updates inside app code

user_15361330
Related