时间戳

首页 > 解决方案 > Postgres - 如何对 JSONB 字段执行 LIKE 查询？

postgresql - Postgres - 如何对 JSONB 字段执行 LIKE 查询？

问题描述

我有一个名为 的 jsonb 字段passengers，其结构如下：

请注意，persons 是一个数组

{
    "adults": {
        "count": 2,
        "persons": [
            {
                "age": 45,
                "name": "Prof. Kyleigh Walsh II",
                "birth_date": "01-01-1975"
            },
            {
                "age": 42,
                "name": "Milford Wiza",
                "birth_date": "02-02-1978"
            }
        ]
    }
}

如何对此 JSONB 的名称字段执行查询？例如，选择与名称字段匹配的所有行Prof？

这是我的初步尝试：

SELECT passengers from opportunities
WHERE 'passengers->adults' != NULL
AND 'passengers->adults->persons->name' LIKE '%Prof';

这将返回 0 行，但如您所见，我有一行名称Prof. Kyleigh Walsh II

标签： postgresqljsonbpostgresql-9.6

解决方案

这：'passengers->adults->persons->name' LIKE '%Prof';检查字符串是否以 .'passengers->adults->persons->name'结尾Prof。

JSON 运算符的每个键都需要是一个单独的元素，并且列名不能用单引号引起来。所以'passengers->adults->persons->name'需要passengers -> 'adults' -> 'persons' -> 'name'

->运算符返回一个值，jsonb你想要一个text值，所以最后一个运算符应该是->>

也!= null不起作用，您需要使用is not null.

SELECT passengers 
from opportunities
WHERE passengers -> 'adults' is not NULL
 AND  passengers -> 'adults' -> 'persons' ->> 'name' LIKE 'Prof%';

该is not null条件并不是真正必要的，因为第二个条件暗示了这一点。第二个条件可以简化为：

SELECT passengers 
from opportunities
WHERE passengers #>> '{adults,persons,name}' LIKE 'Prof%';

但是作为persons一个数组，上述方法不起作用，您需要使用不同的方法。

使用 Postgres 9.6，您将需要一个子查询来取消嵌套数组元素（并因此迭代每个元素）。

SELECT passengers 
from opportunities
WHERE exists (select * 
              from jsonb_array_elements(passengers -> 'adults' -> 'persons') as p(person)
              where p.person ->> 'name' LIKE 'Prof%');

要将开头的字符串与 LIKE 匹配，通配符必须位于结尾。'%Prof'会匹配'Some Prof'但不匹配'Prof. Kyleigh Walsh II'

使用 Postgres 12，您可以使用SQL/JSON 路径表达式：

SELECT passengers 
from opportunities
WHERE passengers @? '$.adults.persons[*] ? (@.name like_regex "Prof.*")'

推荐阅读