How do I search for a specific string in a JSON Postgres data type column?

Question

I have a column named params in a table named reports which contains JSON.

I need to find which rows contain the text 'authVar' anywhere in the JSON array. I don't know the path or level in which the text could appear.

I want to just search through the JSON with a standard like operator.

Something like:

SELECT * FROM reports
WHERE params LIKE '%authVar%'

I have searched and googled and read the Postgres docs. I don't understand the JSON data type very well, and figure I am missing something easy.

The JSON looks something like this.

[  
   {  
      "tileId":18811,
      "Params":{  
         "data":[  
            {  
               "name":"Week Ending",
               "color":"#27B5E1",
               "report":"report1",
               "locations":{  
                  "c1":0,
                  "c2":0,
                  "r1":"authVar",
                  "r2":66
               }
            }
         ]
      }
   }
]

klin · Accepted Answer · 2020-10-25 14:31:27Z

168

In Postgres 11 or earlier it is possible to recursively walk through an unknown json structure, but it would be rather complex and costly. I would propose the brute force method which should work well:

select *
from reports
where params::text like '%authVar%';
-- or 
-- where params::text like '%"authVar"%';
-- if you are looking for the exact value

The query is very fast but may return unexpected extra rows in cases when the searched string is a part of one of the keys.

In Postgres 12+ the recursive searching in JSONB is pretty comfortable with the new feature of jsonpath.

Find a string value containing authVar:

select *
from reports
where jsonb_path_exists(params, '$.** ? (@.type() == "string" && @ like_regex "authVar")')

The jsonpath:

$.**                     find any value at any level (recursive processing)
?                        where
@.type() == "string"     value is string
&&                       and
@ like_regex "authVar"   value contains 'authVar'

Or find the exact value:

select *
from reports
where jsonb_path_exists(params, '$.** ? (@ == "authVar")')

Read in the documentation:

edited Oct 25, 2020 at 14:31

answered Aug 23, 2017 at 21:42

klin

123k15 gold badges240 silver badges262 bronze badges

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

10 Comments

Cale Sweeney Over a year ago

What would be the advantage of recursively walking through?

Cale Sweeney Over a year ago

This tutorial explains the "params::text" in detail: postgresqltutorial.com/postgresql-cast

klin Over a year ago

@CaleSweeney - This simple solution will fail when we're searching for an attribute which is the same as one of keys.

ersu Over a year ago

Is there a way to use "jsonb_path_exists" with multiple params and case insensitive?

klin Over a year ago

@ersu - yes, read 9.15.2.2. Regular Expressions in the docs.

|

Collectives™ on Stack Overflow

How do I search for a specific string in a JSON Postgres data type column?

1 Answer 1

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related