在jsonb值中搜索字符串- PostgreSQL

Question

为简单起见，table的一行如下所示:

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

我有一个像这样的jsonb对象{"KEY": "1x6t4y", "CODE": "IT137-521e9204-ABC-TESTE", "NAME": "A"}，我想在任何键的值中搜索查询。如果我的查询类似于'521e9204'，我希望它返回reference_keys在任何值中具有'521e9204'的行。基本上，在这种情况下，键不重要。

注意:列reference_keys和jsonb对象，总是一个一维数组。

我试过这样的查询:

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

问题是它重复行，对于json中的每个键，它弄乱了返回的项。

我也想过这样做:

SELECT DISTINCT jsonb_object_keys(reference_keys) from table;

，然后使用如下查询:

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

这似乎可以工作，但我真的不想依赖于这个解决方案。

Answer 1

您可以将JOIN重写为EXISTS条件，以避免重复:

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

如果你使用的是Postgres 12或更高版本，你也可以使用JSON路径查询:

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