我想使用正则表达式筛选器查询JSONBarray.在docs之后，我应该能够做到:
WHERE jsonb_path_exists(
data,
'$.value[*] ? (@ like_regex $foo flag "i")',
jsonb_build_object('foo', 'demo')
)
但我收到了一个错误:
ERROR: syntax error at or near "$foo" of jsonpath input
我想这是因为$是一个有效的正则表达式字符？
以下是一些演示数据:
INSERT INTO table_data (fields) VALUES
('[{"value": "Sales"}]'),
('[{"value": "CEO Staff"}]'),
('[{"value": "Procurement"'),
('[{"value": "CEO Staff"}]');
我希望查询所有值包含'ceo'的值.
我可以在当前的postgres 16.0中重现这个问题.参数替换适用于其他jsonpath operators，如==，但不适用于like_regex.然而，手册似乎没有提到这一限制.
我不认为这与regexp表达式中$的特殊含义有关(它会用双引号引起来).错误在更早的时候就开始了.看起来那里就是不支持参数替换.
值得注意的是，手册中提到了value ==101辆，但实际上是string like_regex103辆(突出强调了我的).但这一领先优势持平，因为也有string starts with103的领先优势，变量替代在那里如预期般起作用.
有一个workaround:动态构建jsonpath表达式并使用关联的Postgres operator @?:
SELECT *
FROM tbl
WHERE data @? format('$[*].value ? (@ like_regex %s flag "i")', '"CEO"')::jsonpath;
Using (optional) format() for convenient string concatenation. The string literal '"CEO"' can be replaced by an expression - the target use case I presume?
Then cast to jsonpath.
The plot twist: this is superior anyway. It can use an index - as opposed to using the function jsonb_path_exists()! (And the operator @? does not allow for parameter substitution to begin with.)
An index like (among others):
CREATE INDEX ON tbl USING gin (data jsonb_path_ops);
密切相关(也请看那边的小提琴！)
索引的使用在内部绑定到运算符，而不是函数.有些函数可以由查询规划器转换，但在本例中不能.相关: