Searching jsonb array in PostgreSQL

Question

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"}
]

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;

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%';

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%').

Update

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

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

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;

Outputs:

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

And putting in the constraints:

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%';

Outputs:

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.

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

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

Radek Postołowicz Radek Postołowicz · Accepted Answer · 2015-06-02T09:21:50

You can use 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%');

Searching jsonb array in PostgreSQL

Update

1 Answers