Postgres - 如何在 JSONB 字段上执行 LIKE 查询？

Question

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

注意，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 的行。

- f7n

---

我正在使用Postgres 9.6。 - f7n

Answer 1

这个代码：'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条件并不是必需的，因为这已经被第二个条件隐含了。第二个条件可以简化为：

Translated text:

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 Path 表达式：

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

- user330315

---

这是否意味着Postgres知道persons是一个数组，并且它会在数组的每个索引上执行查询，还是只在第一个索引上执行？ - f7n

是的，我做到了。顺便说一下，我正在使用Postgres 9.6。 - f7n

是否也可以按persons数组中的字段排序呢？比如按名称升序排列？ - f7n