Sign up ×
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them, it only takes a minute:

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);
share|improve this question

1 Answer 1

up vote 1 down vote accepted

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%');
share|improve this answer
    
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? – browner87 Jun 3 at 11:15
    
@browner87 I've updated answer. Please comment if further explanation needed. – Radek Postołowicz Jun 3 at 11:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.