PostgreSQL 反向 LIKE 与 json 数组中的模式

Question

这个问题与PostgreSQL反向LIKE有关

我有一个表a包含一个 json 列a.matchers.a.matchers是一个对象的数组，每个对象都有属性pattern给定一个inputString我想获得至少一个matchers[].pattern与inputString LIKE '%{pattern}%'匹配的所有行

所以基本上(作为伪查询(：

SELECT * 
FROM a 
WHERE 
'inputStringABC' LIKE ANY('%' || json_array_elements(a.matchers)->>'pattern' || '%') 

这可能吗？

p.s.：PostgreSQL版本是9.6.3

编辑：更广泛的现状描述：

表 a：

Column            |            Type             |                   Modifiers                   | Storage  | Stats target | Description
------------------------------+-----------------------------+-----------------------------------------------+----------+--------------+-------------
uuid                         | uuid                        | not null default uuid_generate_v4()           | plain    |              |
matchers                     | json                        |                                               | extended |              |

列匹配器中的 JSON 结构：

[
{
pattern: string;
}
]

示例用例：

我有一个字符串"有些树看起来比其他树更好"，表a中的以下行：

• uuid: "123...", matchers: [{ pattern: "trees" }]
• uuid: "987...", matchers: [{ pattern: "bees" }, { pattern: "plants" }]

查询应返回带有 uuid"123..."的行，因为trees出现在"Some trees look nicer than others"

编辑2：

最后一个查询，这要归功于 S-MAN 对我有用

SELECT DISTINCT                                                  
uuid,
pattern
FROM (
SELECT 
*, 
jsonb_array_elements(a.matchers)->>'pattern' as pattern  
FROM a
) s
WHERE 
'Some trees look nicer than others' LIKE '%' || pattern || '%'   

Answer 1

表：

id   matchers
1    [{"abc": 1, "pattern": "ABC"}, {"pattern": "ABAB"}, {"cde": "FOO", "pattern": "AABB"}]
2    [{"pattern": "AB"}, {"cde": "BAR", "pattern": "CDE"}]
3    [{"abc": 2, "pattern": "CD"}, {"pattern": "FG"}] 

查询：

SELECT DISTINCT                                                  -- C
id,
matchers
FROM (
SELECT 
*, 
jsonb_array_elements(a.matchers)->>'pattern' as pattern  -- A
FROM a
) s
WHERE 
pattern LIKE '%AB%'                                          -- B

答：jsonb_array_elements将 json 数组扩展到每个数组元素一行。->>运算符以文本形式提供每个数组元素所包含的模式属性的值。

B：使用LIKE运算符过滤文本值

C：因为表被扩展了，我们必须再次减少它，因为我们只想要通过子查询保存在每一行中的原始列。

结果：

id   matchers
1    [{"abc": 1, "pattern": "ABC"}, {"pattern": "ABAB"}, {"cde": "FOO", "pattern": "AABB"}]
2    [{"pattern": "AB"}, {"cde": "BAR", "pattern": "CDE"}]

演示：数据库<>小提琴

---

如果您的列类型是json而不是jsonb您当然必须使用json_array_elements。

---

编辑：在使问题更清晰之后，发现该模式的用例是另一种方式。因此，通过将WHERE子句更改为：

'long input string including pattern' LIKE '%' || pattern || '%'

延伸阅读