Search for string in jsonb values - PostgreSQL-CodePudding

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 'R1e9204%'

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->>'ROLE' like 'R1e9204%'

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

CodePudding user response：

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 'R1e9204%');

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")')