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

vngu2lb8  于 2023-11-18  发布在  PostgreSQL
关注(0)|答案(1)|浏览(161)

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

  1. {
  2. "results": [
  3. {
  4. "createdByApp": "yes",
  5. "externalApps": [
  6. {
  7. "modifiedByUser": "User-1",
  8. "modifiedDate": "2023-09-12T08:43:10.779",
  9. "name": "test1"
  10. },
  11. {
  12. "modifiedByUser": "User-2",
  13. "modifiedDate": "2023-09-12T08:43:10.779",
  14. "name": "test2"
  15. },
  16. {
  17. "modifiedByUser": "User-3",
  18. "modifiedDate": "2023-09-12T08:43:10.779",
  19. "name": "test3"
  20. }
  21. ],
  22. "modifiedByApp": "7572b20b-a594-48dd-8ab3-8bc2718a23bd"
  23. }
  24. ]
  25. }

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

8fsztsew

8fsztsew1#

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

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

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

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


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

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


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

展开查看全部

相关问题