使用PostgreSQL 12在jsonb列的数组字段中搜索部分值

Question

我有一个表，其中包含一个名为data的jsonb字段。此字段可以包含任意数据。其中一个可能是CCD_ 3。喜欢

{
...
"emails": ["[e-mail]", "[e-mail]"],
...
}

因此，email字段是Array。通常，如果它是一个字符串，我可以很容易地在这个数据上使用ILIKE或~*，然而，作为一个数组让我三思而后行。因为它仍然有效。

我运行的查询：

select * from test_tbl where "data" ->> 'emails' ILIKE '%spenc%';
-- OR --
select * from test_tbl where "data" ->> 'emails' ~* 'spenc';

另请参阅：https://rextester.com/FFQ83366

在数组元素上使用ilike和/或~*运算符是否正确和安全？或者有更好的方法来做这项工作吗？

请注意，我需要部分搜索，因为客户端只能提供部分搜索查询。所以，搜索确切的电子邮件地址对我来说不是一个选择(可以，但不应该局限于(

我正在使用PostgreSQL12.2

Answer 1

您需要运行数组：

select t.*
from test_tbl t
where exists (select *
from jsonb_array_elements_text(t.data -> 'emails') as t(email)
where t.email like '%spence%');

或者，您可以使用JSON路径表达式

select *
from test_tbl t
where data @? '$.emails[*] ? (@ like_regex ".*spenc.*")'

在线示例