如何在Postgres中的嵌套json中查询对象 [英] How to query objects in nested json in postgres

问题描述

让我们说我有一个名为users的表，该表具有名为attrs的jsonb列，其值如下:

Lets say I have table called users with jsonb column called attrs with values like this:

{
  "uuid5": {
    "label": "Email",
    "value": "[e-mail]"
  },
  "uuid6": {
    "label": "Last Name ",
    "value": "Yang"
  }
}

这里是单线:

"attrs": { "uuid5": { "label": "Email", "value": "[e-mail]" }, "uuid6": { "label": "Last Name ", "value": "Yang" }

如您所见，有uniq键uuid5，uuid6等.

As you can see there are uniq keys uuid5, uuid6 and so on.

如何获得标签=电子邮件"且值="[e-mail]"的用户?

How to get users with label = 'Email' and value = '[e-mail]'?

关于json函数的 postgres docs 中有一个函数名为 jsonb_each ，它返回一组JSON对象键/值对.但是我找不到基于此写查询的方法.

In postgres docs about json functions there is a function called jsonb_each which returns set of JSON object key/value pairs. But I could not find a way to write a query based on that.

推荐答案

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

You need jsonb_each to iterate over all entries in the attrs colum. It will return key/value pairs where the key is the uuid and the entry is your actual JSON structure you want to inspect. You can use that in conjunction with an EXISTS condition:

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' = '[e-mail]')

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

这篇关于如何在Postgres中的嵌套json中查询对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！