CODEWITHSUNDEEP
sqlstringpostgresqlsql-likejsonb
TaB
TaB

Reputation: 129

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

Upvotes: 0

Views: 502

Answers (2)

user330315
user330315

Reputation:

As you are using Postgres 12, you can use a SQL/JSON path function:

select id, some_value, 
       jsonb_path_query_first(data_col, '$.* ? (@ like_regex "old" flag "i")') #>> '{}' 
from data

The #>> operator is only there to convert the scalar JSON value into text (as there is no direct cast from jsonb to text that would remove the double quotes)

If there are potentially more values with the substring, you can use jsonb_path_query_array() to all of them as an array (obviously you need to remove the #>> then)

Upvotes: 0

GMB
GMB

Reputation: 222412

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

Upvotes: 1

Related Questions