Hi!
On Fri, Jun 14, 2019 at 10:16 AM Kyotaro Horiguchi
<horikyota....@gmail.com> wrote:
> At Thu, 13 Jun 2019 14:59:51 +0100, Thom Brown <t...@linux.com> wrote
> in <CAA-aLv4VVX=b9rk5hkfpxjczqaitdqo04tew9i0wiqvhdkc...@mail.gmail.com>
> > Hi,
> >
> > I've been reading through the documentation regarding jsonpath and
> > jsonb_path_query etc., and I have found it lacking explanation for
> > some functionality, and I've also had some confusion when using the
> > feature.
> >
> > ? operator
> > ==========
> > The first mention of '?' is in section 9.15, where it says:
> >
> > "Suppose you would like to retrieve all heart rate values higher than
> > 130. You can achieve this using the following expression:
> > '$.track.segments[*].HR ? (@ > 130)'"
> >
> > So what is the ? operator doing here? Sure, there's the regular ?
>
> It is described just above as:
>
> | Each filter expression must be enclosed in parentheses and
> | preceded by a question mark.
+1
> > operator, which is given as an example further down the page:
> >
> > '{"a":1, "b":2}'::jsonb ? 'b'
> >
> > But this doesn't appear to have the same purpose.
>
> The section is mentioning path expressions and the '?' is a jsonb
> operator. It's somewhat confusing but not so much comparing with
> around..
+1
> > like_regex
> > ==========
> > Then there's like_regex, which shows an example that uses the keyword
> > "flag", but that is the only instance of that keyword being mentioned,
> > and the flags available to this expression aren't anywhere to be seen.
>
> It is described as POSIX regular expressions. So '9.7.3 POSIX
> Regular Expressions' is that. But linking it would
> helpful. (attached 0001)
Actually, standard requires supporting the same regex flags as
XQuery/XPath does [1]. Perhaps, we found that we miss support for 'q'
flag, while it's trivial. Attached patch fixes that. Documentation
should contain description of flags. That will be posted as separate
patch.
> > is unknown
> > ==========
> > "is unknown" suggests a boolean output, but the example shows an
> > output of "infinity". While I understand what it does, this appears
> > inconsistent with all other "is..." functions (e.g. is_valid(lsn),
> > pg_is_other_temp_schema(oid), pg_opclass_is_visible(opclass_oid),
> > pg_is_in_backup() etc.).
>
> It's the right behavior. Among them, only "infinity" gives
> "unknown' for the test (@ > 0). -1 gives false, 2 and 3 true.
+1
We follow here SQL standard for jsonpath language. There is no direct
analogy with our SQL-level functions.
>
> > $varname
> > ==========
> > The jsonpath variable, $varname, has an incomplete description: "A
> > named variable. Its value must be set in the PASSING clause of an
> > SQL/JSON query function. for details."
>
> Yeah, it is apparently chopped amid. In the sgml source, the
> missing part is "<!-- TBD: See <xref
> linkend="sqljson-input-clause"/> -->", and the PASSING clause is
> not implemented yet. On the other hand a similar stuff is
> currently implemented as vas parameter in some jsonb
> functions. Linking it to there might be helpful (Attached 0002).
>
> > Binary operation error
> > ==========
> > I get an error when I run this query:
> >
> > postgres=# SELECT jsonb_path_query('[2]', '2 + $[1]');
> > psql: ERROR: right operand of jsonpath operator + is not a single numeric
> > value
> >
> > While I know it's correct to get an error in this scenario as there is
> > no element beyond 0, the message I get is confusing. I'd expect this
> > if it encountered another array in that position, but not for
> > exceeding the upper bound of the array.
>
> Something like attached makes it clerer? (Attached 0003)
Thank you. Will review these two and commit.
> | ERROR: right operand of jsonpath operator + is not a single numeric value
> | DETAIL: It was an array with 0 elements.
>
> > Cryptic error
> > ==========
> > postgres=# SELECT jsonb_path_query('[1, "2",
> > {},[{"a":2}],2.3,null,"2019-06-05T13:25:43.511Z"]','$[**].type()');
> > psql: ERROR: syntax error, unexpected ANY_P at or near "**" of jsonpath
> > input
> > LINE 1: ...},[{"a":2}],2.3,null,"2019-06-05T13:25:43.511Z"]','$[**].typ...
> > ^
> > Again, I expect an error, but the message produced doesn't help me.
> > I'll remove the ANY_P if I can find it.
>
> Yeah, I had a similar error:
>
> =# select jsonb_path_query('[-1,2,7, "infinity"]', '$[*] ? (($hoge) is
> unknown)', '{"hoge": (@ > 0)}');
> ERROR: syntax error, unexpected IS_P at or near " " of jsonpath input
>
> When the errors are issued, the caller side is commented as:
>
> jsonpath_scan.l:481
> > jsonpath_yyerror(NULL, "bogus input"); /* shouldn't happen */
>
> The error message is reasonable if it were really shouldn't
> happen, but it quite easily happen. I don't have an idea of how
> to fix it for the present..
I'm also not sure. Need further thinking about it.
> > Can't use nested arrays with jsonpath
> > ==========
> >
> > I encounter an error in this scenario:
> >
> > postgres=# select jsonb_path_query('[1, 2, 1, [1,2], 3]','$[*] ? (@ ==
> > [1,2])');
> > psql: ERROR: syntax error, unexpected '[' at or near "[" of jsonpath input
> > LINE 1: select jsonb_path_query('[1, 2, 1, [1,2], 3]','$[*] ? (@ == ...
> >
> > So these filter operators only work with scalars?
>
> Perhaps true. It seems that SQL/JSON is saying so. Array is not
> comparable with anything. (See 6.13.5 Comparison predicates in
> [1])
That's true. But we may we extended version of jsonpath having more
features than standard defined. We can pick proposal [2] to evade
possible incompatibility with future standard updates.
Links.
1. https://www.w3.org/TR/xpath-functions/#func-matches
2. https://www.postgresql.org/message-id/5CF28EA0.80902%40anastigmatix.net
------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
From 16375595a67f1816cb753a3644198ba9b27cc823 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <n.gluhov@postgrespro.ru>
Date: Fri, 14 Jun 2019 15:24:54 +0300
Subject: [PATCH] Implement like_regex flag 'q' in jsonpath
---
src/backend/utils/adt/jsonpath.c | 2 ++
src/backend/utils/adt/jsonpath_exec.c | 6 +++++
src/backend/utils/adt/jsonpath_gram.y | 8 +++++++
src/include/utils/jsonpath.h | 1 +
src/test/regress/expected/jsonb_jsonpath.out | 36 ++++++++++++++++++++++++++++
src/test/regress/expected/jsonpath.out | 18 ++++++++++++++
src/test/regress/sql/jsonb_jsonpath.sql | 6 +++++
src/test/regress/sql/jsonpath.sql | 3 +++
8 files changed, 80 insertions(+)
diff --git a/src/backend/utils/adt/jsonpath.c b/src/backend/utils/adt/jsonpath.c
index d5da155..87ae60e 100644
--- a/src/backend/utils/adt/jsonpath.c
+++ b/src/backend/utils/adt/jsonpath.c
@@ -563,6 +563,8 @@ printJsonPathItem(StringInfo buf, JsonPathItem *v, bool inKey,
appendStringInfoChar(buf, 'm');
if (v->content.like_regex.flags & JSP_REGEX_WSPACE)
appendStringInfoChar(buf, 'x');
+ if (v->content.like_regex.flags & JSP_REGEX_QUOTE)
+ appendStringInfoChar(buf, 'q');
appendStringInfoChar(buf, '"');
}
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index 873d64b..bef9112 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -1664,6 +1664,12 @@ executeLikeRegex(JsonPathItem *jsp, JsonbValue *str, JsonbValue *rarg,
cxt->cflags &= ~REG_NEWLINE;
if (flags & JSP_REGEX_WSPACE)
cxt->cflags |= REG_EXPANDED;
+ if ((flags & JSP_REGEX_QUOTE) &&
+ !(flags & (JSP_REGEX_MLINE | JSP_REGEX_SLINE | JSP_REGEX_WSPACE)))
+ {
+ cxt->cflags &= ~REG_ADVANCED;
+ cxt->cflags |= REG_QUOTE;
+ }
}
if (RE_compile_and_execute(cxt->regex, str->val.string.val,
diff --git a/src/backend/utils/adt/jsonpath_gram.y b/src/backend/utils/adt/jsonpath_gram.y
index 22c2089..a0a930c 100644
--- a/src/backend/utils/adt/jsonpath_gram.y
+++ b/src/backend/utils/adt/jsonpath_gram.y
@@ -510,6 +510,14 @@ makeItemLikeRegex(JsonPathParseItem *expr, JsonPathString *pattern,
v->value.like_regex.flags |= JSP_REGEX_WSPACE;
cflags |= REG_EXPANDED;
break;
+ case 'q':
+ v->value.like_regex.flags |= JSP_REGEX_QUOTE;
+ if (!(v->value.like_regex.flags & (JSP_REGEX_MLINE | JSP_REGEX_SLINE | JSP_REGEX_WSPACE)))
+ {
+ cflags &= ~REG_ADVANCED;
+ cflags |= REG_QUOTE;
+ }
+ break;
default:
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
diff --git a/src/include/utils/jsonpath.h b/src/include/utils/jsonpath.h
index 3e9d60c..40ad5fd 100644
--- a/src/include/utils/jsonpath.h
+++ b/src/include/utils/jsonpath.h
@@ -91,6 +91,7 @@ typedef enum JsonPathItemType
#define JSP_REGEX_SLINE 0x02 /* s flag, single-line mode */
#define JSP_REGEX_MLINE 0x04 /* m flag, multi-line mode */
#define JSP_REGEX_WSPACE 0x08 /* x flag, expanded syntax */
+#define JSP_REGEX_QUOTE 0x10 /* q flag, no special characters */
/*
* Support functions to parse/construct binary value.
diff --git a/src/test/regress/expected/jsonb_jsonpath.out b/src/test/regress/expected/jsonb_jsonpath.out
index b486fb6..31a871a 100644
--- a/src/test/regress/expected/jsonb_jsonpath.out
+++ b/src/test/regress/expected/jsonb_jsonpath.out
@@ -1622,6 +1622,42 @@ select jsonb_path_query('[null, 1, "abc", "abd", "aBdC", "abdacb", "adc\nabc", "
"abdacb"
(2 rows)
+select jsonb_path_query('[null, 1, "a\b", "a\\b", "^a\\b$"]', 'lax $[*] ? (@ like_regex "a\\b" flag "q")');
+ jsonb_path_query
+------------------
+ "a\\b"
+ "^a\\b$"
+(2 rows)
+
+select jsonb_path_query('[null, 1, "a\b", "a\\b", "^a\\b$"]', 'lax $[*] ? (@ like_regex "a\\b" flag "")');
+ jsonb_path_query
+------------------
+ "a\b"
+(1 row)
+
+select jsonb_path_query('[null, 1, "a\b", "a\\b", "^a\\b$"]', 'lax $[*] ? (@ like_regex "^a\\b$" flag "q")');
+ jsonb_path_query
+------------------
+ "^a\\b$"
+(1 row)
+
+select jsonb_path_query('[null, 1, "a\b", "a\\b", "^a\\b$"]', 'lax $[*] ? (@ like_regex "^a\\B$" flag "q")');
+ jsonb_path_query
+------------------
+(0 rows)
+
+select jsonb_path_query('[null, 1, "a\b", "a\\b", "^a\\b$"]', 'lax $[*] ? (@ like_regex "^a\\B$" flag "iq")');
+ jsonb_path_query
+------------------
+ "^a\\b$"
+(1 row)
+
+select jsonb_path_query('[null, 1, "a\b", "a\\b", "^a\\b$"]', 'lax $[*] ? (@ like_regex "^a\\b$" flag "")');
+ jsonb_path_query
+------------------
+ "a\b"
+(1 row)
+
-- jsonpath operators
SELECT jsonb_path_query('[{"a": 1}, {"a": 2}]', '$[*]');
jsonb_path_query
diff --git a/src/test/regress/expected/jsonpath.out b/src/test/regress/expected/jsonpath.out
index 0f9cd17..ecdd453 100644
--- a/src/test/regress/expected/jsonpath.out
+++ b/src/test/regress/expected/jsonpath.out
@@ -453,6 +453,24 @@ select '$ ? (@ like_regex "pattern" flag "xsms")'::jsonpath;
$?(@ like_regex "pattern" flag "sx")
(1 row)
+select '$ ? (@ like_regex "pattern" flag "q")'::jsonpath;
+ jsonpath
+-------------------------------------
+ $?(@ like_regex "pattern" flag "q")
+(1 row)
+
+select '$ ? (@ like_regex "pattern" flag "iq")'::jsonpath;
+ jsonpath
+--------------------------------------
+ $?(@ like_regex "pattern" flag "iq")
+(1 row)
+
+select '$ ? (@ like_regex "pattern" flag "smixq")'::jsonpath;
+ jsonpath
+----------------------------------------
+ $?(@ like_regex "pattern" flag "imxq")
+(1 row)
+
select '$ ? (@ like_regex "pattern" flag "a")'::jsonpath;
ERROR: invalid input syntax for type jsonpath
LINE 1: select '$ ? (@ like_regex "pattern" flag "a")'::jsonpath;
diff --git a/src/test/regress/sql/jsonb_jsonpath.sql b/src/test/regress/sql/jsonb_jsonpath.sql
index 464ff94..733fbd4 100644
--- a/src/test/regress/sql/jsonb_jsonpath.sql
+++ b/src/test/regress/sql/jsonb_jsonpath.sql
@@ -339,6 +339,12 @@ select jsonb_path_query('[null, 1, "abc", "abd", "aBdC", "abdacb", "adc\nabc", "
select jsonb_path_query('[null, 1, "abc", "abd", "aBdC", "abdacb", "adc\nabc", "babc"]', 'lax $[*] ? (@ like_regex "^a b.* c " flag "ix")');
select jsonb_path_query('[null, 1, "abc", "abd", "aBdC", "abdacb", "adc\nabc", "babc"]', 'lax $[*] ? (@ like_regex "^ab.*c" flag "m")');
select jsonb_path_query('[null, 1, "abc", "abd", "aBdC", "abdacb", "adc\nabc", "babc"]', 'lax $[*] ? (@ like_regex "^ab.*c" flag "s")');
+select jsonb_path_query('[null, 1, "a\b", "a\\b", "^a\\b$"]', 'lax $[*] ? (@ like_regex "a\\b" flag "q")');
+select jsonb_path_query('[null, 1, "a\b", "a\\b", "^a\\b$"]', 'lax $[*] ? (@ like_regex "a\\b" flag "")');
+select jsonb_path_query('[null, 1, "a\b", "a\\b", "^a\\b$"]', 'lax $[*] ? (@ like_regex "^a\\b$" flag "q")');
+select jsonb_path_query('[null, 1, "a\b", "a\\b", "^a\\b$"]', 'lax $[*] ? (@ like_regex "^a\\B$" flag "q")');
+select jsonb_path_query('[null, 1, "a\b", "a\\b", "^a\\b$"]', 'lax $[*] ? (@ like_regex "^a\\B$" flag "iq")');
+select jsonb_path_query('[null, 1, "a\b", "a\\b", "^a\\b$"]', 'lax $[*] ? (@ like_regex "^a\\b$" flag "")');
-- jsonpath operators
diff --git a/src/test/regress/sql/jsonpath.sql b/src/test/regress/sql/jsonpath.sql
index 9171ddb..29ea77a 100644
--- a/src/test/regress/sql/jsonpath.sql
+++ b/src/test/regress/sql/jsonpath.sql
@@ -83,6 +83,9 @@ select '$ ? (@ like_regex "pattern" flag "i")'::jsonpath;
select '$ ? (@ like_regex "pattern" flag "is")'::jsonpath;
select '$ ? (@ like_regex "pattern" flag "isim")'::jsonpath;
select '$ ? (@ like_regex "pattern" flag "xsms")'::jsonpath;
+select '$ ? (@ like_regex "pattern" flag "q")'::jsonpath;
+select '$ ? (@ like_regex "pattern" flag "iq")'::jsonpath;
+select '$ ? (@ like_regex "pattern" flag "smixq")'::jsonpath;
select '$ ? (@ like_regex "pattern" flag "a")'::jsonpath;
select '$ < 1'::jsonpath;
--
2.7.4
