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

问题描述

我有一个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' || '%')

这可能吗？

ps: PostgreSQL 版本为 9.6.3

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

表一：

           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 || '%'

标签： sqljsonpostgresql

解决方案

表：

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

A：jsonb_array_elements将 json 数组扩展为每个数组元素一行。运算符将->>每个数组元素包含的模式属性的值作为文本给出。

LIKEB：使用运算符过滤文本值

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

结果：

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

演示：db<>fiddle

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

编辑：在使问题更清楚之后，结果表明该模式的用例是相反的。所以将WHERE子句改为：

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

进一步阅读

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

问题描述

解决方案

推荐阅读