在PostgreSQL中搜索jsonb数组 [英] Searching jsonb array in PostgreSQL

问题描述

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

I'm trying to search a JSONB object in PostgreSQL 9.4. My question is similar to this thread.

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

However my data structure is slightly different which is causing me problems. My data structure is like:

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

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

and I want to search for matching objects in that array by msg (RegEx, LIKE, =, etc). To be more specific, I want all rows in the table where the JSONB field has an object with a "msg" that matches my request.

以下显示的结构类似于我的结构:

The following shows a structure similar to what I have:

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

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

This shows an attempt to implement the answer to the above link, but does not work (returns 0 rows):

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数组进行搜索吗?在上面的示例中，我想在表的任何行中找到其数据" JSONB字段包含一个对象，其中"msg"与某对象匹配(例如，LIKE'％est％').

Can anyone explain how to search through a JSONB array? In the above example I would like to find any row in the table whose "data" JSONB field contains an object where "msg" matches something (for example, LIKE '%est%').

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

This code creates a new type (needed for later):

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

然后，您可以使用它使用JSONB_POPULATE_RECORDSET撕裂该列:

Then you can use this to rip apart the column with 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

因此，问题的一部分仍然是如何将其作为子句放在另一个查询中.

So the part of the question still remaining is how to put this as a clause in another query.

因此，如果以上代码的输出= x，我将如何询问:

So, if the output of the above code = x, how would I ask:

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

推荐答案

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

要查询以下注释中提到的表:

To query table as mentioned in comment below:

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

这篇关于在PostgreSQL中搜索jsonb数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！