regex - 在 PostgreSQL 中搜索 jsonb 数组

Question

我正在尝试在 PostgreSQL 9.4 中搜索 JSONB 对象。我的问题类似于 this thread .

但是我的数据结构略有不同，这导致了我的问题。我的数据结构是这样的:

[
    {"id":1, "msg":"testing"}
    {"id":2, "msg":"tested"}
    {"id":3, "msg":"nothing"}
]

我想通过 msg(RegEx、LIKE、= 等)在该数组中搜索匹配的对象。更具体地说，我希望表中所有行的 JSONB 字段都有一个对象，该对象的“msg”与我的请求相匹配。

下面显示了一个类似于我的结构:

SELECT * FROM 
    (SELECT 
        '[{"id":1,"msg":"testing"},{"id":2,"msg":"tested"},{"id":3,"msg":"nothing"}]'::jsonb as data) 
    as jsonbexample;

这显示了对上述链接的答案的尝试，但不起作用(返回 0 行):

SELECT * FROM 
    (SELECT 
        '[{"id":1,"msg":"testing"},{"id":2,"msg":"tested"},{"id":3,"msg":"nothing"}]'::jsonb as data) 
    as jsonbexample 
WHERE 
    (data #>> '{msg}') LIKE '%est%';

谁能解释一下如何搜索 JSONB 数组？在上面的示例中，我想在表中找到任何行，其“data”JSONB 字段包含一个对象，其中“msg”匹配某些内容(例如，LIKE '%est%')。

---

更新

此代码创建一个新类型(稍后需要):

CREATE TYPE AlertLine AS (id INTEGER, msg TEXT);

然后您可以使用它来拆分带有 JSONB_POPULATE_RECORDSET 的列:

SELECT * FROM 
    JSONB_POPULATE_RECORDSET(
        null::AlertLine, 
        (SELECT '[{"id":1,"msg":"testing"},
                  {"id":2,"msg":"tested"},
                  {"id":3,"msg":"nothing"}]'::jsonb 
         as data
        )
    ) as jsonbex;

输出:

 id |   msg   
----+---------
  1 | testing
  2 | tested
  3 | nothing

并加入约束:

SELECT * FROM 
    JSONB_POPULATE_RECORDSET(
        null::AlertLine, 
        (SELECT '[{"id":1,"msg":"testing"},
                  {"id":2,"msg":"tested"},
                  {"id":3,"msg":"nothing"}]'::jsonb 
         as data)
        ) as jsonbex 
WHERE 
    msg LIKE '%est%';

输出:

id |   msg   
---+---------
 1 | testing
 2 | tested

所以剩下的部分问题是如何将它作为子句放在另一个查询中。

那么，如果上面代码的输出=x，我会怎么问:

SELECT * FROM mytable WHERE x > (0 rows);

Answer 1

你可以使用exists:

SELECT * FROM 
    (SELECT 
        '[{"id":1,"msg":"testing"},{"id":2,"msg":"tested"},{"id":3,"msg":"nothing"}]'::jsonb as data) 
    as jsonbexample 
WHERE 
    EXISTS (SELECT 1 FROM jsonb_array_elements(data) as j(data) WHERE (data#>> '{msg}') LIKE '%est%');

查询下面评论中提到的表:

SELECT * FROM atable 
WHERE EXISTS (SELECT 1 FROM jsonb_array_elements(columnx) as j(data) WHERE (data#>> '{msg}') LIKE '%est%');