在PostgreSQL中搜索jsonb数组

Question

在PostgreSQL中搜索jsonb数组

6

我正在尝试在PostgreSQL 9.4中搜索JSONB对象。我的问题类似于这个线程。

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

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

我希望能够在该数组中通过 msg（正则表达式、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数组吗？在上面的例子中，我想要查找包含“msg”匹配某些内容（例如LIKE'%est%'）的对象的“data” JSONB字段的任何行。

更新

这段代码创建了一个新类型（稍后需要）：

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

然后您可以使用这个函数JSONB_POPULATE_RECORDSET来解析带有列的JSONB数据:

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);

- browner87

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Radek Postołowicz · Accepted Answer

您可以使用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%');