如何执行jsonb键的LIKE查询? [英] How can I perform a LIKE query for a jsonb key?
问题描述
我具有以下jsonb结构:
I have the following jsonb structure:
{'this': '1', 'this_that': '0', 'this_and_that': '5'}
如何选择包含LIKE运算符的行?
How do I select rows that contain a LIKE operator?
SELECT * FROM myjson WHERE j ? 'this_%'
返回0行...希望与'this_that'和'this_and_that'相匹配. (请注意:"_"后面的字符可能会有很大差异,因此我无法进行完全匹配.)
Returns 0 rows...was hoping it would match 'this_that' and 'this_and_that'. (Note: the characters following '_' will potentially vary greatly and therefore I am not able to do an exact match).
推荐答案
您的示例不起作用,因为在jsonb
和text
类型之间没有隐式强制转换.您可以强制执行强制转换:
Your example should not work because there are not implicit cast between jsonb
and text
types. You can enforce casting:
SELECT '{"this": 1, "this_that": 0, "this_and_that": 5}'::jsonb::text
like '%"this%';
这不是干净的解决方案.更好的方法是解压缩json,并使用横向联接对未解压缩的数据进行过滤
It is not clean solution. Some better is unpacking json, and filtering over unpacked data with lateral join
postgres=# SELECT key FROM myjson, lateral jsonb_each_text(j)
WHERE key LIKE 'this\_%';
┌───────────────┐
│ key │
╞═══════════════╡
│ this_that │
│ this_and_that │
└───────────────┘
(2 rows)
这篇关于如何执行jsonb键的LIKE查询?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!