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

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

Leave a Comment

Hata!: SQLSTATE[HY000] [1045] Access denied for user 'divattrend_liink'@'localhost' (using password: YES)