sql - Postgres jsonb列从字符串与通配符匹配的数组中选择

Question

我希望将 postgres jsonb 列与数组一起使用并能够对它们进行过滤，所以我有一个带有列的模式:

CREATE TABLE testtable
(
id  varchar(32) NOT NULL,   
refs    jsonb           NULL,
)

并且该列包含Json格式的数据:

{ "refs": ["one-1-0", "two-3-2", "two-3-4" ] }

我希望能够返回包含以(例如)“two-3-”开头的数组元素的所有行

我已经尝试了几种方法，但无法让它按照我的意愿工作(我所拥有的最接近的方法是将数组部分作为文本获取并将其作为字符串进行搜索 - 但这很讨厌)

我还想给这个列添加一个合适的索引来支持这个查询。

任何建议都将非常出色，我们将不胜感激!谢谢

Answer 1

step-by-step demo:db<>fiddle

SELECT DISTINCT                                   -- 3
    id, refs
FROM
    testtable,
    jsonb_array_elements_text(refs -> 'refs')     -- 1
WHERE
    value LIKE 'two_3%'                           -- 2

1. 将数组扩展为每个数组元素一行
2. 使用LIKE 比较器和% 通配符按数组元素(值)过滤所有记录
3. 如果出现多次，则每次出现一条记录。所以如果你只想要原始数据集，你可以使用 DISTINCT
删除重复项