首页 文章

在PostgreSQL中搜索jsonb数组

提问于
浏览 次
4

我正在尝试在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);
regex postgresql jsonb

1 回答

  • 2

    你可以使用 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%');
    回复于

相关问题