Vojtech Vojtech - 2 months ago 6
SQL Question

Query with regexp_matches in select returns empty result set

I have following SQL:

SELECT code, name
ARRAY_TO_JSON(regexp_matches(code, '^(.*?)(24)(.*)$','i')) AS match_code,
ARRAY_TO_JSON(regexp_matches(name, '^(.*?)(24)(.*)$','i')) AS match_name
FROM manufacturer
WHERE (code ~* '^(.*?)(24)(.*)$' OR name ~* '^(.*?)(24)(.*)$')
ORDER BY name;


There's following record in the table:

code | name
-------------
24 | Item 24


The result of the query is:

code | name | match_code | match_name
-------------------------------------------------
24 | Item 24 | ["","24",""] | ["Item ","24",""]


Then I replaced string '24' by 'Item' in the query and I expect this result:

code | name | match_code | match_name
-------------------------------------------------
24 | Item 24 | [] | ["", "Item ","24"]


BUT the result is:

Empty result set


Function regexp_matches probably returns no row if it doesn't match.

How can I fix the query so that it returns rows even when regexp_matches doesn't match?

Thanks in advance.

Answer

regexp_matches returns a setof text[], i.e. a table, and it's sometimes confusing to use it as an output expression in SELECT. You can create a subquery so that you can move it to the FROM clause. Try this:

SELECT
    code,
    name,
    coalesce(array_to_json((SELECT * FROM regexp_matches(code, '^(.*?)(24)(.*)$','i'))),'[]') AS match_code,
    coalesce(array_to_json((SELECT * FROM regexp_matches(name, '^(.*?)(24)(.*)$','i'))),'[]') AS match_name
FROM manufacturer
WHERE (code  ~* '^(.*?)(24)(.*)$' OR name  ~* '^(.*?)(24)(.*)$')
ORDER BY name;

Notice that I am also using coalesce to convert NULL (which is what we get from the regexp_matches subquery if there are no matches) to an empty JSON array.