select中使用regexp_matches的查询返回空结果集

Question

我有以下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;

表中有以下记录：

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

查询的结果是：

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

然后，我在查询中将字符串'24‘替换为'Item’，我期望得到以下结果：

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

但结果是：

Empty result set

如果不匹配，函数regexp_matches可能不返回任何行。

如何修复查询，使其即使在regexp_matches不匹配的情况下也能返回行？

提前谢谢。

Answer 1

regexp_matches返回一个setof text[]，也就是一个表，在SELECT中使用它作为输出表达式有时会让人感到困惑。您可以创建子查询，以便将其移动到FROM子句。试试这个：

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;

注意，我还使用coalesce将NULL (如果没有匹配，我们从JSON子查询中得到的内容)转换为一个空的regexp_matches数组。