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 · Accepted Answer · 2015-06-03 11:48:15Z

5

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

edited Jun 3, 2015 at 11:48

answered Jun 2, 2015 at 9:21

Radek Postołowicz

4,8742 gold badges32 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

browner87 Over a year ago

Thank you very much for this! It is indeed very useful and indeed works. However I'm having trouble adapting this for my purpose (as in my original question). If I need to, say, SELECT * FROM atable WHERE <columnx contains an object where msg LIKE '%est%'>, how would I use the above code?

Radek Postołowicz Over a year ago

@browner87 I've updated answer. Please comment if further explanation needed.

ArchNoob Over a year ago

@RadekPostołowicz Oooh Thank You! I had a problem which I coudn't address and this question & answer made it so simple! Thanks browner too.

Collectives™ on Stack Overflow

Searching jsonb array in PostgreSQL

Update

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Update

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related