json - 如何在postgres中查询嵌套json中的对象

Question

假设我有一个名为 users 的表，其中包含名为 attrs 的 jsonb 列，其值如下:

{
  "uuid5": {
    "label": "Email",
    "value": "example@test.com.tw"
  },
  "uuid6": {
    "label": "Last Name ",
    "value": "Yang"
  }
}

这是一个单行:

"attrs": { "uuid5": { "label": "Email", "value": "example@test.com.tw" }, "uuid6": { "label": "Last Name ", "value": "Yang" }

如您所见，有 uniq 键 uuid5、uuid6 等等。

如何获取label = 'Email' value = 'example@test.com.tw'的用户？

在postgres docs关于 json 函数，有一个函数叫做 jsonb_each它返回一组 JSON 对象键/值对。但我找不到基于此编写查询的方法。

Answer 1

您需要 jsonb_each 来遍历 attrs 列中的所有条目。它将返回键/值对，其中键是 uuid，条目是您要检查的实际 JSON 结构。您可以将其与 EXISTS 条件结合使用:

select u.*
from users u
where exists (select *
              from jsonb_each(u.attrs) as t(uid,entry)
              where t.entry ->> 'label' = 'Email'
                and t.entry ->> 'value' = 'example@test.com.tw')

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