Looks like I didn't make myself undestood: I do not produce the regular expression dynamically (on a side note, I would have to be careful to escape the right stuff, regardless of SQL or jsonpath). It is the WHERE clause of the query that I build dynamically. That's why I would like the pattern to be submitted as a variable, (which I anyway employ with a "q" flag).
Question: is there another way to express the contains predicate in jsonpath?
You can use a format function to build it dynamically. Unfortunately it is a bit of a pain since you need to do escaping; which is a pain for regex. SQL scope doesn't have this problem so moving your logic outside of a json is should seriously be considered before trying to construct dynamic jsonpath expressions.
I get the impression we are conforming to a standard here so even proposing a patch to change this behavior would require some convincing to deviate from the standard on this point. Though I could see adding a new format escape and related quote_jsonpathliteral function to be something we'd be more open to in order to make dynamic json path expressions more easily doable.
