postgresql 按属性值选择数组元素Postgres Jsonb

vngu2lb8 于 2023-11-18 发布在 PostgreSQL

关注(0)|答案(1)|浏览(159)

我有一个对象存储在Postgres数据库的jsonb列中。
列中的数据如下所示：

{
    "results": [
        {
            "createdByApp": "yes",
            "externalApps": [
                {
                    "modifiedByUser": "User-1",
                    "modifiedDate": "2023-09-12T08:43:10.779",
                    "name": "test1"
                },
                {
                    "modifiedByUser": "User-2",
                    "modifiedDate": "2023-09-12T08:43:10.779",
                    "name": "test2"
                },
                {
                    "modifiedByUser": "User-3",
                    "modifiedDate": "2023-09-12T08:43:10.779",
                    "name": "test3"
                }
            ],
            "modifiedByApp": "7572b20b-a594-48dd-8ab3-8bc2718a23bd"
        }
    ]
}

字符串
我尝试做的是选择User-2修改的externalApp的name。
我可以通过使用以下命令将外部应用程序作为JSON对象拉取：
select (act."MessageBody"::json->'results'->0)->>'externalApps' as extApps个
这给了我externalApps内部的对象数组。
但是从那里我找不到一种方法来选择modifiedByUser = User-2的name
我不能保证User-2修改的应用程序总是数组中的第二个元素。如果modifiedByUser值匹配，我如何搜索数组并选择一个值？

postgresql

来源：https://stackoverflow.com/questions/77461806/select-array-element-by-property-value-postgres-jsonb

1条答案

按热度按时间

8fsztsew1#

在PostgreSQL 12及更高版本中，可以使用jsonb_path_query()函数：db<>fiddle demo

select jsonb_path_query( act."MessageBody"::jsonb
                        ,'$.results[*]
                           .externalApps[*]
                           ?(@.modifiedByUser==$var)
                           .name'
                        ,jsonb_build_object('var','User-2'))
from act;

字符串
| jsonb_path_query|
| --|
| “测试2”|
JSONPath语法提供了数组通配符[*]和过滤器表达式?()。您也可以动态传递要查找的user，作为外部变量。
对jsonb_array_elements()的支持可以追溯到9.4，因此您还可以：

select e['name'] as extApps
from act, jsonb_array_elements(act."MessageBody"::jsonb
                                ->'results'
                                ->0
                                ->'externalApps')_(e)
where e->>'modifiedByUser'='User-2';

型
| 埃克斯塔普斯|
| --|
| “测试2”|
如果你不确定externalApps是否是results的第0个元素，你需要两个：

select e2['name'] as extApps
from act, 
     jsonb_array_elements(act."MessageBody"::jsonb->'results')j1(e),
     jsonb_array_elements(e->'externalApps')j2(e2)
where e2->>'modifiedByUser'='User-2';

型
| 埃克斯塔普斯|
| --|
| “测试2”|
请注意，这些都是返回集合的函数，因此在后台有很多横向连接。

赞(0）回复(0）举报 2023-11-18

我来回答

postgresql 按属性值选择数组元素Postgres Jsonb

1条答案

相关问题

热门标签

最新问答