时间戳

首页 > 解决方案 > 查询列表中的 JSON 对象 (Postgres 11)

sql - 查询列表中的 JSON 对象 (Postgres 11)

问题描述

我有一个存储 jsonb 的“contact_info”列，如下所示：

[ {"phoneNumber":"123-4567", "emailAddress":"email@address.com"} ]

我正在尝试检索电话号码等于“123-4567”的所有行。

如何在单行查询中做到这一点？我可以返回整个列，SELECT contact_info FROM db_name.db_table;但没有成功拉动单个对象并查询里面的电话号码。

我一直在看 [this question][1] 但它与我的情况略有不同（json 对象列表与包含列表的 json 对象），我不知道如何将解决方案应用于我的版本问题。

标签： sqlarraysjsonpostgresqlpostgresql-11

解决方案

如果您的所有数组都只有一个元素，那么这很简单：

select t.*, t.contact_info -> 0 ->> 'emailAddress' as email_address
from db_table t
where t.contact_info -> 0 ->> 'phoneNumber' = '123-4567'

否则，可以使用jsonb_array_elements()横向连接来取消嵌套数组，然后过滤：

select t.*, x.obj ->> 'emailAddress' as email_address
from db_table t
cross join lateral jsonb_array_elements(t.contact_info) x(obj)
where x.obj ->> 'phoneNumber' = '123-4567'

请注意，如果给定数组中的多个对象与电话号码匹配，则会返回多行。如果要避免这种情况，可以使用子查询：

select t.*, x.obj ->> 'emailAddress' as email_address
from db_table t
where exists (
    select 1
    from jsonb_array_elements(t.contact_info) x(obj)
    where x.obj ->> 'phoneNumber' = '123-4567'
)

推荐阅读