sql - 从 postgres jsonb 列中提取字段

Question

我有一个带有 jsonb 列的 postgres 表，其值如下:

 id  |  messageStatus       |   payload
-----|----------------------|-------------
1    |    123               | {"commissionEvents":[{"id":1,"name1":"12","name2":15,"name4":"apple","name5":"fruit"},{"id":2,"name1":"22","name2":15,"name4":"sf","name5":"fdfjkd"}]}
2    |    124               | {"commissionEvents":[{"id":3,"name1":"32","name2":15,"name4":"sf","name5":"fdfjkd"},{"id":4,"name1":"42","name2":15,"name4":"apple","name5":"fruit"}]}
3    |    125               | {"commissionEvents":[{"id":5,"name1":"42","name2":15,"name4":"apple","name5":"fdfjkd"},{"id":6,"name1":"52","name2":15,"name4":"sf","name5":"fdfjkd"},{"id":7,"name1":"62","name2":15,"name4":"apple","name5":"fdfjkd"}]}

这里的 payload 列是一个 jsonb 数据类型，我想编写一个 postgres 查询以从 name4 = apple 的 commissionEvents 中获取 name1。

所以我的结果会是这样的:

由于我是这个 jsonb 的新手，任何人都可以建议我一些解决方案。

Answer 1

您需要取消所有数组元素的嵌套，然后您可以对其应用 WHERE 条件以过滤掉具有所需名称的元素。

select t.id, x.o ->> 'name1'
from the_table t
  cross join lateral jsonb_array_elements(t.payload -> 'commissionEvents') as x(o)
where x.o ->> 'name4' = 'apple'

在线示例:https://rextester.com/XWHG26387