Search for string in jsonb values - PostgreSQL

Question

For simplicity, a row of table looks like this:

key: "z06khw1bwi886r18k1m7d66bi67yqlns",
reference_keys: {
    "KEY": "1x6t4y",
    "CODE": "IT137-521e9204-ABC-TESTE"
    "NAME": "A"
},

I have a jsonb object like this one {"KEY": "1x6t4y", "CODE": "IT137-521e9204-ABC-TESTE", "NAME": "A"} and I want to search for a query in the values of any key. If my query is something like '521e9204' I want it to return the row that reference_keys has '521e9204' in any value. Basicly the keys don't matter for this scenario.

Note: The column reference_keys and so the jsonb object, are always a 1 dimensional array.

I have tried a query like this:

SELECT * FROM table
LEFT JOIN jsonb_each_text(table.reference_keys) AS j(k, value) ON true
WHERE j.value LIKE '%521e9204%'

The problem is that it duplicates rows, for every key in the json and it messes up the returned items.

I have also thinked of doing something like this:

SELECT DISTINCT jsonb_object_keys(reference_keys) from table;

and then use a query like:

SELECT * FROM table
WHERE reference_keys->>'CODE' like '%521e9204%'

It seems like this would work but I really don't want to rely on this solution.

Can you please provide a complete and valid JSON value? Also which Postgres version are you using? — user330315
– user330315, Commented Nov 18, 2022 at 12:11

score 1 · Accepted Answer · 2022-11-18 12:37:08Z

1

You can rewrite your JOIN to an EXISTS condition to avoid the duplicates:

SELECT t.*
FROM the_table t
WHERE EXISTS (select * 
              from jsonb_each_text(t.reference_keys) AS j(k, value) 
              WHERE j.value LIKE '%521e9204%');

If you are using Postgres 12 or later, you can also use a JSON path query:

where jsonb_path_exists(reference_keys, 'strict $.** ? (@ like_regex "521e9204")')

edited Nov 18, 2022 at 12:37

answered Nov 18, 2022 at 12:16

user330315

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

1 Comment

Tomás Mendes Over a year ago

Sorry I forgot to mention what version I was using but this works perfectly. Thx!

Collectives™ on Stack Overflow

Search for string in jsonb values - PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related