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

Answer 1

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:

• The SQL/JSON Path Language
• jsonpath Type