PostgreSQL reverse LIKE with patterns in json array

Question

This question is related to PostgreSQL Reverse LIKE

I have a table a containing a json column a.matchers. a.matchers is an array of objects each of which has property pattern Given an inputString I would like to get all rows where at least one of the matchers[].pattern's would match inputString LIKE '%{pattern}%'

So basically (as pseudoquery):

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

Is this possible?

p.s.: PostgreSQL version being 9.6.3

edit: more extensive status quo description:

Table a:

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

JSON structure in column matchers:

[
    {
        pattern: string;
    }
]

Example use case:

I have a string "Some trees look nicer than others" and following rows in table a:

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

The query should return row with uuid "123..." as trees occurs in "Some trees look nicer than others"

Edit2:

final query which worked for me thanks to 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 || '%'

Please provide an example. Especially for json objects the exact structure is very important to know. — S-Man
Edited the question adding more details but in the meantime you provided an answer already ;-) — Hinnerk

S-Man S-Man · Accepted Answer · 2018-09-22T22:06:32

Table:

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"}]

Query:

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 expands the json array to one row per array element. The ->> operator gives the value of the pattern attribute contained by each array element as text.

B: Filtering the text values with LIKE operator

C: Because the table is expanded we have to reduce it again since we want only the original columns which we saved through the subquery in every row.

Result:

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

demo: db<>fiddle

If your column is of type json instead of jsonb you have to use json_array_elements of course.

Edit: After making the question more clearer it came out that the use case for the pattern is other way round. So the expected result can be achieved by changing the WHERE clause into:

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

PostgreSQL reverse LIKE with patterns in json array

1 Answers