Is there a way to lookup a jsonb column by its values

I have a table, test, in postgres 12 with a jsonb column, data_col, that has many different keys and values. My requirement is to select * from that table where value matches a string.

for example, the table has data as below

id   some_value  data_col
---------------------------------------------------------------       
11   2018        {"a": "Old Farm"}
12   2019        {"b": "My house is old", "c": "See you tomorrow"}
13   2020        {"d": "The old house", "a": "Very Green", "e": "Olden days"}

As you can see, there are many different keys and so its not practical to lookup like the examples on the web suggests i.e col_name->>'Key'

I am looking to write a sql with a where clause to give me all rows that have the string "old" in it.

something like:

select * from test where data_col ILIKE '%old%'

should give me

11, 2018, Old Farm
12, 2019, My house is old
13, 2020, Olden days

Solution

One option uses jsonb_each():

select t.*, x.*
from test t
cross join lateral jsonb_each(t.data_col) x 
where x.value ilike '%old%'

Note that this multiplies the rows if an object contains "old" more than once. To avoid that, you can use exists instead:

select t.*
from test t
where exists (
    select 1
    from jsonb_each(t.data_col) x
    where x.val ilike '%old%'
)

Or if you want to aggregate all the matched values in one column:

select t.*, x.*
from test t
cross join lateral (
    select string_agg(x.val, ',') as vals
    from jsonb_each(t.data_col) x
    where x.val ilike '%old%'
) x
where x.vals is not null