根据其他字段的值在JSONB列中插入新项目-Postgres [英] Insert new item in JSONB column based on value of other field - postgres

问题描述

我具有以下jsonb结构，其中包含许多条目

I have the following jsonb structure with many entries in it

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

当父级name元素的值为"test"时，我想在features数组中添加一个新条目.并且feature1 granted为假".

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".

这个想法是编写一个flyway脚本来迁移我的数据. 我一直在与jsonb_insert作斗争，但由于其中可能有很多元素而且不能仅仅添加给定的下标，所以我无法弄清楚它的路径部分.

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.

最终结果应为:

[
   {
      "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

我修改了查询，只是为了测试PATH到

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

推荐答案

此信息应该足以完成查询:

This should be enough information to complete the query:

让我们创建模拟数据

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
      }
    ]
  }
]');

现在使用具有顺序性的jsonb_array_elements爆炸数组以获取索引和属性

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);

此查询的结果是:

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}]}';

在您将使用id的位置，因为这些是您感兴趣的行，并且在索引中是从查询中获取它们的.所以:

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.

我建议使用更多的sql方法，即在表中而不是在json中具有功能，然后将其链接到您的表中. 例如，如果您确实需要使用json，则因为域确实很复杂，并且在应用程序级别定义且非常灵活.然后，我建议在应用程序代码中进行更新

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

这篇关于根据其他字段的值在JSONB列中插入新项目-Postgres的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！