Hi, hackers!
Attached patches implement several useful jsonpath syntax extensions.
I already published them two years ago in the original SQL/JSON thread,
but then after creation of separate threads for SQL/JSON functions and
JSON_TABLE I forgot about them.
A brief description of the patches:
1. Introduced new jsonpath modifier 'pg' which is used for enabling
PostgreSQL-specific extensions. This feature was already proposed in the
discussion of jsonpath's like_regex implementation.
2. Added support for raw jbvObject and jbvArray JsonbValues inside jsonpath
engine. Now, jsonpath can operate with JSON arrays and objects only in
jbvBinary form. But with introduction of array and object constructors in
patches #4 and #5 raw in-memory jsonb containers can appear in jsonpath engine.
In some places we can iterate through jbvArrays, in others we need to encode
jbvArrays and jbvObjects into jbvBinay.
3. SQL/JSON sequence construction syntax. A simple comma-separated list can be
used to concatenate single values or sequences into a single resulting sequence.
SELECT jsonb_path_query('[1, 2, 3]', 'pg $[*], 4, 2 + 3');
jsonb_path_query
------------------
1
2
3
4
5
SELECT jsonb_path_query('{ "a": [1, 2, 3], "b": [4, 5] }',
'pg ($.a[*], $.b[*]) ? (@ % 2 == 1)');
jsonb_path_query
------------------
1
3
5
Patches #4-#6 implement ECMAScript-like syntax constructors and accessors:
4. Array construction syntax.
This can also be considered as enclosing a sequence constructor into brackets.
SELECT jsonb_path_query('[1, 2, 3]', 'pg [$[*], 4, 2 + 3]');
jsonb_path_query
------------------
[1, 2, 3, 4, 5]
Having this feature, jsonb_path_query_array() becomes somewhat redundant.
5. Object construction syntax. It is useful for constructing derived objects
from the interesting parts of the original object. (But this is not sufficient
to "project" each object in array, item method like '.map()' is needed here.)
SELECT jsonb_path_query('{"b": 2}', 'pg { a : 1, b : $.b, "x y" : $.b + 3 }');
jsonb_path_query
-------------------------------
{ "a" : 1, "b": 3, "x y": 5 }
Fields with empty values are simply skipped regardless of lax/strict mode:
SELECT jsonb_path_query('{"a": 1}', 'pg { b : $.b, a : $.a ? (@ > 1) }');
jsonb_path_query
------------------
{}
6. Object subscription syntax. This gives us ability to specify what key to
extract on runtime. The syntax is the same as ordinary array subscription
syntax.
-- non-existent $.x is simply skipped in lax mode
SELECT jsonb_path_query('{"a": "b", "b": "c"}', 'pg $[$.a, "x", "a"]');
jsonb_path_query
------------------
"c"
"b"
SELECT jsonb_path_query('{"a": "b", "b": "c"}', 'pg $[$fld]', '{"fld": "b"}');
jsonb_path_query
------------------
"c"
--
Nikita Glukhov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
>From 258579bf45484fd150d952cf78f32f37fabff77a Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <n.glu...@postgrespro.ru>
Date: Thu, 27 Jun 2019 19:58:35 +0300
Subject: [PATCH v1 1/8] Add jsonpath 'pg' modifier for enabling extensions
---
doc/src/sgml/func.sgml | 29 ++++++++++++++++++
src/backend/utils/adt/jsonpath.c | 54 ++++++++++++++++++++++++++--------
src/backend/utils/adt/jsonpath_exec.c | 4 +++
src/backend/utils/adt/jsonpath_gram.y | 17 +++++++----
src/backend/utils/adt/jsonpath_scan.l | 1 +
src/include/utils/jsonpath.h | 9 ++++--
src/test/regress/expected/jsonpath.out | 18 ++++++++++++
src/test/regress/sql/jsonpath.sql | 3 ++
src/tools/pgindent/typedefs.list | 1 +
9 files changed, 116 insertions(+), 20 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 28035f1..d344b95 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -12601,6 +12601,35 @@ table2-mapping
</sect3>
+ <sect3 id="pg-extensions">
+ <title>Extensions</title>
+ <para>
+ <productname>PostgreSQL</productname> has some extensions to the SQL/JSON
+ Path standard. These syntax extensions that can enabled by the specifying
+ additional <literal>pg</literal> modifier before the
+ <literal>strict</literal>/<literal>lax</literal> flags.
+ <xref linkend="functions-jsonpath-extensions"/> shows these
+ extensions with examples.
+ </para>
+
+ <table id="functions-jsonpath-extensions">
+ <title><type>jsonpath</type> Syntax Extensions</title>
+ <tgroup cols="5">
+ <thead>
+ <row>
+ <entry>Name</entry>
+ <entry>Description</entry>
+ <entry>Example JSON</entry>
+ <entry>Example JSON path</entry>
+ <entry>Result</entry>
+ </row>
+ </thead>
+ <tbody>
+ </tbody>
+ </tgroup>
+ </table>
+ </sect3>
+
<sect3 id="jsonpath-regular-expressions">
<title>Regular Expressions</title>
diff --git a/src/backend/utils/adt/jsonpath.c b/src/backend/utils/adt/jsonpath.c
index 3c0dc38..17c09a7 100644
--- a/src/backend/utils/adt/jsonpath.c
+++ b/src/backend/utils/adt/jsonpath.c
@@ -72,11 +72,20 @@
#include "utils/jsonpath.h"
+/* Context for jsonpath encoding. */
+typedef struct JsonPathEncodingContext
+{
+ StringInfo buf; /* output buffer */
+ bool ext; /* PG extensions are enabled? */
+} JsonPathEncodingContext;
+
static Datum jsonPathFromCstring(char *in, int len);
static char *jsonPathToCstring(StringInfo out, JsonPath *in,
int estimated_len);
-static int flattenJsonPathParseItem(StringInfo buf, JsonPathParseItem *item,
- int nestingLevel, bool insideArraySubscript);
+static int flattenJsonPathParseItem(JsonPathEncodingContext *cxt,
+ JsonPathParseItem *item,
+ int nestingLevel,
+ bool insideArraySubscript);
static void alignStringInfoInt(StringInfo buf);
static int32 reserveSpaceForItemPointer(StringInfo buf);
static void printJsonPathItem(StringInfo buf, JsonPathItem *v, bool inKey,
@@ -167,6 +176,7 @@ jsonpath_send(PG_FUNCTION_ARGS)
static Datum
jsonPathFromCstring(char *in, int len)
{
+ JsonPathEncodingContext cxt;
JsonPathParseResult *jsonpath = parsejsonpath(in, len);
JsonPath *res;
StringInfoData buf;
@@ -182,13 +192,18 @@ jsonPathFromCstring(char *in, int len)
errmsg("invalid input syntax for type %s: \"%s\"", "jsonpath",
in)));
- flattenJsonPathParseItem(&buf, jsonpath->expr, 0, false);
+ cxt.buf = &buf;
+ cxt.ext = jsonpath->ext;
+
+ flattenJsonPathParseItem(&cxt, jsonpath->expr, 0, false);
res = (JsonPath *) buf.data;
SET_VARSIZE(res, buf.len);
res->header = JSONPATH_VERSION;
if (jsonpath->lax)
res->header |= JSONPATH_LAX;
+ if (jsonpath->ext)
+ res->header |= JSONPATH_EXT;
PG_RETURN_JSONPATH_P(res);
}
@@ -212,6 +227,8 @@ jsonPathToCstring(StringInfo out, JsonPath *in, int estimated_len)
}
enlargeStringInfo(out, estimated_len);
+ if (in->header & JSONPATH_EXT)
+ appendBinaryStringInfo(out, "pg ", 3);
if (!(in->header & JSONPATH_LAX))
appendBinaryStringInfo(out, "strict ", 7);
@@ -221,14 +238,27 @@ jsonPathToCstring(StringInfo out, JsonPath *in, int estimated_len)
return out->data;
}
+static void
+checkJsonPathExtensionsEnabled(JsonPathEncodingContext *cxt,
+ JsonPathItemType type)
+{
+ if (!cxt->ext)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
+ errmsg("%s contains extended operators that were not enabled", "jsonpath"),
+ errhint("use \"%s\" modifier at the start of %s string to enable extensions",
+ "pg", "jsonpath")));
+}
+
/*
* Recursive function converting given jsonpath parse item and all its
* children into a binary representation.
*/
static int
-flattenJsonPathParseItem(StringInfo buf, JsonPathParseItem *item,
+flattenJsonPathParseItem(JsonPathEncodingContext *cxt, JsonPathParseItem *item,
int nestingLevel, bool insideArraySubscript)
{
+ StringInfo buf = cxt->buf;
/* position from beginning of jsonpath data */
int32 pos = buf->len - JSONPATH_HDRSZ;
int32 chld;
@@ -296,13 +326,13 @@ flattenJsonPathParseItem(StringInfo buf, JsonPathParseItem *item,
int32 right = reserveSpaceForItemPointer(buf);
chld = !item->value.args.left ? pos :
- flattenJsonPathParseItem(buf, item->value.args.left,
+ flattenJsonPathParseItem(cxt, item->value.args.left,
nestingLevel + argNestingLevel,
insideArraySubscript);
*(int32 *) (buf->data + left) = chld - pos;
chld = !item->value.args.right ? pos :
- flattenJsonPathParseItem(buf, item->value.args.right,
+ flattenJsonPathParseItem(cxt, item->value.args.right,
nestingLevel + argNestingLevel,
insideArraySubscript);
*(int32 *) (buf->data + right) = chld - pos;
@@ -323,7 +353,7 @@ flattenJsonPathParseItem(StringInfo buf, JsonPathParseItem *item,
item->value.like_regex.patternlen);
appendStringInfoChar(buf, '\0');
- chld = flattenJsonPathParseItem(buf, item->value.like_regex.expr,
+ chld = flattenJsonPathParseItem(cxt, item->value.like_regex.expr,
nestingLevel,
insideArraySubscript);
*(int32 *) (buf->data + offs) = chld - pos;
@@ -342,7 +372,7 @@ flattenJsonPathParseItem(StringInfo buf, JsonPathParseItem *item,
int32 arg = reserveSpaceForItemPointer(buf);
chld = !item->value.arg ? pos :
- flattenJsonPathParseItem(buf, item->value.arg,
+ flattenJsonPathParseItem(cxt, item->value.arg,
nestingLevel + argNestingLevel,
insideArraySubscript);
*(int32 *) (buf->data + arg) = chld - pos;
@@ -384,12 +414,12 @@ flattenJsonPathParseItem(StringInfo buf, JsonPathParseItem *item,
int32 *ppos;
int32 topos;
int32 frompos =
- flattenJsonPathParseItem(buf,
+ flattenJsonPathParseItem(cxt,
item->value.array.elems[i].from,
nestingLevel, true) - pos;
if (item->value.array.elems[i].to)
- topos = flattenJsonPathParseItem(buf,
+ topos = flattenJsonPathParseItem(cxt,
item->value.array.elems[i].to,
nestingLevel, true) - pos;
else
@@ -424,7 +454,7 @@ flattenJsonPathParseItem(StringInfo buf, JsonPathParseItem *item,
if (item->next)
{
- chld = flattenJsonPathParseItem(buf, item->next, nestingLevel,
+ chld = flattenJsonPathParseItem(cxt, item->next, nestingLevel,
insideArraySubscript) - pos;
*(int32 *) (buf->data + next) = chld;
}
@@ -832,7 +862,7 @@ operationPriority(JsonPathItemType op)
void
jspInit(JsonPathItem *v, JsonPath *js)
{
- Assert((js->header & ~JSONPATH_LAX) == JSONPATH_VERSION);
+ Assert((js->header & JSONPATH_VERSION_MASK) == JSONPATH_VERSION);
jspInitByBuffer(v, js->data, 0);
}
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index b6fdd47..9f13f4b 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -101,6 +101,8 @@ typedef struct JsonPathExecContext
int innermostArraySize; /* for LAST array index evaluation */
bool laxMode; /* true for "lax" mode, false for "strict"
* mode */
+ bool useExtensions; /* use PostgreSQL-specific extensions?
+ * (enabled by 'pg' modifier in jsonpath) */
bool ignoreStructuralErrors; /* with "true" structural errors such
* as absence of required json item or
* unexpected json item type are
@@ -157,6 +159,7 @@ typedef struct JsonValueListIterator
#define jspAutoWrap(cxt) ((cxt)->laxMode)
#define jspIgnoreStructuralErrors(cxt) ((cxt)->ignoreStructuralErrors)
#define jspThrowErrors(cxt) ((cxt)->throwErrors)
+#define jspUseExtensions(cxt) ((cxt)->useExtensions)
/* Convenience macro: return or throw error depending on context */
#define RETURN_ERROR(throw_error) \
@@ -559,6 +562,7 @@ executeJsonPath(JsonPath *path, Jsonb *vars, Jsonb *json, bool throwErrors,
cxt.vars = vars;
cxt.laxMode = (path->header & JSONPATH_LAX) != 0;
+ cxt.useExtensions = (path->header & JSONPATH_EXT) != 0;
cxt.ignoreStructuralErrors = cxt.laxMode;
cxt.root = &jbv;
cxt.current = &jbv;
diff --git a/src/backend/utils/adt/jsonpath_gram.y b/src/backend/utils/adt/jsonpath_gram.y
index f87db8c..426dbb1 100644
--- a/src/backend/utils/adt/jsonpath_gram.y
+++ b/src/backend/utils/adt/jsonpath_gram.y
@@ -88,7 +88,7 @@ static JsonPathParseItem *makeItemLikeRegex(JsonPathParseItem *expr,
int integer;
}
-%token <str> TO_P NULL_P TRUE_P FALSE_P IS_P UNKNOWN_P EXISTS_P
+%token <str> TO_P NULL_P TRUE_P FALSE_P IS_P UNKNOWN_P EXISTS_P PG_P
%token <str> IDENT_P STRING_P NUMERIC_P INT_P VARIABLE_P
%token <str> OR_P AND_P NOT_P
%token <str> LESS_P LESSEQUAL_P EQUAL_P NOTEQUAL_P GREATEREQUAL_P GREATER_P
@@ -109,7 +109,7 @@ static JsonPathParseItem *makeItemLikeRegex(JsonPathParseItem *expr,
%type <optype> comp_op method
-%type <boolean> mode
+%type <boolean> mode pg_opt
%type <str> key_name
@@ -127,10 +127,11 @@ static JsonPathParseItem *makeItemLikeRegex(JsonPathParseItem *expr,
%%
result:
- mode expr_or_predicate {
+ pg_opt mode expr_or_predicate {
*result = palloc(sizeof(JsonPathParseResult));
- (*result)->expr = $2;
- (*result)->lax = $1;
+ (*result)->expr = $3;
+ (*result)->lax = $2;
+ (*result)->ext = $1;
}
| /* EMPTY */ { *result = NULL; }
;
@@ -140,6 +141,11 @@ expr_or_predicate:
| predicate { $$ = $1; }
;
+pg_opt:
+ PG_P { $$ = true; }
+ | /* EMPTY */ { $$ = false; }
+ ;
+
mode:
STRICT_P { $$ = false; }
| LAX_P { $$ = true; }
@@ -292,6 +298,7 @@ key_name:
| WITH_P
| LIKE_REGEX_P
| FLAG_P
+ | PG_P
;
method:
diff --git a/src/backend/utils/adt/jsonpath_scan.l b/src/backend/utils/adt/jsonpath_scan.l
index 70681b7..c8bce27 100644
--- a/src/backend/utils/adt/jsonpath_scan.l
+++ b/src/backend/utils/adt/jsonpath_scan.l
@@ -305,6 +305,7 @@ typedef struct JsonPathKeyword
*/
static const JsonPathKeyword keywords[] = {
{ 2, false, IS_P, "is"},
+ { 2, false, PG_P, "pg"},
{ 2, false, TO_P, "to"},
{ 3, false, ABS_P, "abs"},
{ 3, false, LAX_P, "lax"},
diff --git a/src/include/utils/jsonpath.h b/src/include/utils/jsonpath.h
index 4ef0880..d56175f 100644
--- a/src/include/utils/jsonpath.h
+++ b/src/include/utils/jsonpath.h
@@ -25,8 +25,10 @@ typedef struct
char data[FLEXIBLE_ARRAY_MEMBER];
} JsonPath;
-#define JSONPATH_VERSION (0x01)
-#define JSONPATH_LAX (0x80000000)
+#define JSONPATH_VERSION 0x01
+#define JSONPATH_LAX 0x80000000 /* lax/strict mode */
+#define JSONPATH_EXT 0x40000000 /* PG extensions */
+#define JSONPATH_VERSION_MASK (~(JSONPATH_LAX | JSONPATH_EXT))
#define JSONPATH_HDRSZ (offsetof(JsonPath, data))
#define DatumGetJsonPathP(d) ((JsonPath *) DatumGetPointer(PG_DETOAST_DATUM(d)))
@@ -241,7 +243,8 @@ struct JsonPathParseItem
typedef struct JsonPathParseResult
{
JsonPathParseItem *expr;
- bool lax;
+ bool lax; /* lax/strict mode */
+ bool ext; /* PostgreSQL extensions */
} JsonPathParseResult;
extern JsonPathParseResult *parsejsonpath(const char *str, int len);
diff --git a/src/test/regress/expected/jsonpath.out b/src/test/regress/expected/jsonpath.out
index e399fa9..52b36a8 100644
--- a/src/test/regress/expected/jsonpath.out
+++ b/src/test/regress/expected/jsonpath.out
@@ -21,6 +21,24 @@ select 'lax $'::jsonpath;
$
(1 row)
+select 'pg $'::jsonpath;
+ jsonpath
+----------
+ pg $
+(1 row)
+
+select 'pg strict $'::jsonpath;
+ jsonpath
+-------------
+ pg strict $
+(1 row)
+
+select 'pg lax $'::jsonpath;
+ jsonpath
+----------
+ pg $
+(1 row)
+
select '$.a'::jsonpath;
jsonpath
----------
diff --git a/src/test/regress/sql/jsonpath.sql b/src/test/regress/sql/jsonpath.sql
index 17ab775..315e42f 100644
--- a/src/test/regress/sql/jsonpath.sql
+++ b/src/test/regress/sql/jsonpath.sql
@@ -4,6 +4,9 @@ select ''::jsonpath;
select '$'::jsonpath;
select 'strict $'::jsonpath;
select 'lax $'::jsonpath;
+select 'pg $'::jsonpath;
+select 'pg strict $'::jsonpath;
+select 'pg lax $'::jsonpath;
select '$.a'::jsonpath;
select '$.a.v'::jsonpath;
select '$.a.*'::jsonpath;
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index e216de9..d4148e1 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1130,6 +1130,7 @@ JsonLikeRegexContext
JsonParseContext
JsonPath
JsonPathBool
+JsonPathEncodingContext
JsonPathExecContext
JsonPathExecResult
JsonPathGinAddPathItemFunc
--
2.7.4
>From 6adeecd232441cfd4f0c52ed394bb193f430b72f Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <n.glu...@postgrespro.ru>
Date: Fri, 25 Jan 2019 22:50:25 +0300
Subject: [PATCH v1 2/8] Add raw jbvArray and jbvObject support to jsonpath
---
src/backend/utils/adt/jsonpath_exec.c | 96 +++++++++++++++++++++++++++++++----
1 file changed, 85 insertions(+), 11 deletions(-)
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index 9f13f4b..1b05307 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -254,6 +254,7 @@ static JsonbValue *JsonbInitBinary(JsonbValue *jbv, Jsonb *jb);
static int JsonbType(JsonbValue *jb);
static JsonbValue *getScalar(JsonbValue *scalar, enum jbvType type);
static JsonbValue *wrapItemsInArray(const JsonValueList *items);
+static JsonbValue *wrapJsonObjectOrArray(JsonbValue *jbv, JsonbValue *buf);
static int compareDatetime(Datum val1, Oid typid1, Datum val2, Oid typid2,
bool useTz, bool *have_error);
@@ -646,7 +647,14 @@ executeItemOptUnwrapTarget(JsonPathExecContext *cxt, JsonPathItem *jsp,
}
case jpiKey:
- if (JsonbType(jb) == jbvObject)
+ if (jb->type == jbvObject)
+ {
+ JsonbValue obj;
+
+ jb = wrapJsonObjectOrArray(jb, &obj);
+ return executeItemOptUnwrapTarget(cxt, jsp, jb, found, unwrap);
+ }
+ else if (jb->type == jbvBinary && JsonbType(jb) == jbvObject)
{
JsonbValue *v;
JsonbValue key;
@@ -725,6 +733,7 @@ executeItemOptUnwrapTarget(JsonPathExecContext *cxt, JsonPathItem *jsp,
int innermostArraySize = cxt->innermostArraySize;
int i;
int size = JsonbArraySize(jb);
+ bool binary = jb->type == jbvBinary;
bool singleton = size < 0;
bool hasNext = jspGetNext(jsp, &elem);
@@ -784,7 +793,7 @@ executeItemOptUnwrapTarget(JsonPathExecContext *cxt, JsonPathItem *jsp,
v = jb;
copy = true;
}
- else
+ else if (binary)
{
v = getIthJsonbValueFromContainer(jb->val.binary.data,
(uint32) index);
@@ -794,6 +803,11 @@ executeItemOptUnwrapTarget(JsonPathExecContext *cxt, JsonPathItem *jsp,
copy = false;
}
+ else
+ {
+ v = &jb->val.array.elems[index];
+ copy = true;
+ }
if (!hasNext && !found)
return jperOk;
@@ -858,10 +872,10 @@ executeItemOptUnwrapTarget(JsonPathExecContext *cxt, JsonPathItem *jsp,
case jpiAnyKey:
if (JsonbType(jb) == jbvObject)
{
+ JsonbValue bin;
bool hasNext = jspGetNext(jsp, &elem);
- if (jb->type != jbvBinary)
- elog(ERROR, "invalid jsonb object type: %d", jb->type);
+ jb = wrapJsonObjectOrArray(jb, &bin);
return executeAnyItem
(cxt, hasNext ? &elem : NULL,
@@ -926,8 +940,11 @@ executeItemOptUnwrapTarget(JsonPathExecContext *cxt, JsonPathItem *jsp,
case jpiAny:
{
+ JsonbValue bin;
bool hasNext = jspGetNext(jsp, &elem);
+ jb = wrapJsonObjectOrArray(jb, &bin);
+
/* first try without any intermediate steps */
if (jsp->content.anybounds.first == 0)
{
@@ -1127,10 +1144,34 @@ executeItemUnwrapTargetArray(JsonPathExecContext *cxt, JsonPathItem *jsp,
JsonbValue *jb, JsonValueList *found,
bool unwrapElements)
{
- if (jb->type != jbvBinary)
+ if (jb->type == jbvArray)
{
- Assert(jb->type != jbvArray);
- elog(ERROR, "invalid jsonb array value type: %d", jb->type);
+ JsonPathExecResult res = jperNotFound;
+ JsonbValue *elem = jb->val.array.elems;
+ JsonbValue *last = elem + jb->val.array.nElems;
+
+ for (; elem < last; elem++)
+ {
+ if (jsp)
+ {
+ res = executeItemOptUnwrapTarget(cxt, jsp, elem, found,
+ unwrapElements);
+
+ if (jperIsError(res))
+ break;
+ if (res == jperOk && !found)
+ break;
+ }
+ else
+ {
+ if (found)
+ JsonValueListAppend(found, copyJsonbValue(elem));
+ else
+ return jperOk;
+ }
+ }
+
+ return res;
}
return executeAnyItem
@@ -1191,8 +1232,6 @@ executeItemOptUnwrapResult(JsonPathExecContext *cxt, JsonPathItem *jsp,
JsonValueListInitIterator(&seq, &it);
while ((item = JsonValueListNext(&seq, &it)))
{
- Assert(item->type != jbvArray);
-
if (JsonbType(item) == jbvArray)
executeItemUnwrapTargetArray(cxt, NULL, item, found, false);
else
@@ -1931,6 +1970,7 @@ executeKeyValueMethod(JsonPathExecContext *cxt, JsonPathItem *jsp,
JsonPathExecResult res = jperNotFound;
JsonPathItem next;
JsonbContainer *jbc;
+ JsonbValue bin;
JsonbValue key;
JsonbValue val;
JsonbValue idval;
@@ -1942,12 +1982,13 @@ executeKeyValueMethod(JsonPathExecContext *cxt, JsonPathItem *jsp,
int64 id;
bool hasNext;
- if (JsonbType(jb) != jbvObject || jb->type != jbvBinary)
+ if (JsonbType(jb) != jbvObject)
RETURN_ERROR(ereport(ERROR,
(errcode(ERRCODE_SQL_JSON_OBJECT_NOT_FOUND),
errmsg("jsonpath item method .%s() can only be applied to an object",
jspOperationName(jsp->type)))));
+ jb = wrapJsonObjectOrArray(jb, &bin);
jbc = jb->val.binary.data;
if (!JsonContainerSize(jbc))
@@ -2144,7 +2185,8 @@ getJsonPathVariable(JsonPathExecContext *cxt, JsonPathItem *variable,
static int
JsonbArraySize(JsonbValue *jb)
{
- Assert(jb->type != jbvArray);
+ if (jb->type == jbvArray)
+ return jb->val.array.nElems;
if (jb->type == jbvBinary)
{
@@ -2521,6 +2563,33 @@ JsonbInitBinary(JsonbValue *jbv, Jsonb *jb)
}
/*
+ * Transform a JsonbValue into a binary JsonbValue by encoding it to a
+ * binary jsonb container.
+ */
+static JsonbValue *
+JsonbWrapInBinary(JsonbValue *jbv, JsonbValue *out)
+{
+ Jsonb *jb;
+
+ if (!out)
+ out = palloc(sizeof(*out));
+
+ jb = JsonbValueToJsonb(jbv);
+ JsonbInitBinary(out, jb);
+
+ return out;
+}
+
+static JsonbValue *
+wrapJsonObjectOrArray(JsonbValue *jbv, JsonbValue *buf)
+{
+ if (jbv->type != jbvObject && jbv->type != jbvArray)
+ return jbv;
+
+ return JsonbWrapInBinary(jbv, buf);
+}
+
+/*
* Returns jbv* type of JsonbValue. Note, it never returns jbvBinary as is.
*/
static int
@@ -2569,7 +2638,12 @@ wrapItemsInArray(const JsonValueList *items)
JsonValueListInitIterator(items, &it);
while ((jbv = JsonValueListNext(items, &it)))
+ {
+ JsonbValue bin;
+
+ jbv = wrapJsonObjectOrArray(jbv, &bin);
pushJsonbValue(&ps, WJB_ELEM, jbv);
+ }
return pushJsonbValue(&ps, WJB_END_ARRAY, NULL);
}
--
2.7.4
>From c5b2005706d378c60c8108fc498d59f088e7ca72 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <n.glu...@postgrespro.ru>
Date: Wed, 5 Apr 2017 22:53:12 +0300
Subject: [PATCH v1 3/8] Add jsonpath sequence constructors
---
doc/src/sgml/func.sgml | 7 +++
src/backend/utils/adt/jsonpath.c | 68 ++++++++++++++++++++++++++--
src/backend/utils/adt/jsonpath_exec.c | 46 +++++++++++++++++++
src/backend/utils/adt/jsonpath_gram.y | 32 +++++++++++--
src/include/utils/jsonpath.h | 12 +++++
src/test/regress/expected/jsonb_jsonpath.out | 54 ++++++++++++++++++++++
src/test/regress/expected/jsonpath.out | 36 +++++++++++++++
src/test/regress/sql/jsonb_jsonpath.sql | 9 ++++
src/test/regress/sql/jsonpath.sql | 7 +++
9 files changed, 264 insertions(+), 7 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index d344b95..9defd1a 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -12625,6 +12625,13 @@ table2-mapping
</row>
</thead>
<tbody>
+ <row>
+ <entry>Sequence constructor</entry>
+ <entry>Construct a JSON sequence from a list of comma-separated expressions</entry>
+ <entry><literal>[1, 2, 3]</literal></entry>
+ <entry><literal>pg $[*], 4, 5</literal></entry>
+ <entry><literal>1, 2, 3, 4, 5</literal></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/backend/utils/adt/jsonpath.c b/src/backend/utils/adt/jsonpath.c
index 17c09a7..0fb4257 100644
--- a/src/backend/utils/adt/jsonpath.c
+++ b/src/backend/utils/adt/jsonpath.c
@@ -233,7 +233,7 @@ jsonPathToCstring(StringInfo out, JsonPath *in, int estimated_len)
appendBinaryStringInfo(out, "strict ", 7);
jspInit(&v, in);
- printJsonPathItem(out, &v, false, true);
+ printJsonPathItem(out, &v, false, v.type != jpiSequence);
return out->data;
}
@@ -448,6 +448,31 @@ flattenJsonPathParseItem(JsonPathEncodingContext *cxt, JsonPathParseItem *item,
case jpiDouble:
case jpiKeyValue:
break;
+ case jpiSequence:
+ {
+ int32 nelems = list_length(item->value.sequence.elems);
+ ListCell *lc;
+ int offset;
+
+ checkJsonPathExtensionsEnabled(cxt, item->type);
+
+ appendBinaryStringInfo(buf, (char *) &nelems, sizeof(nelems));
+
+ offset = buf->len;
+
+ appendStringInfoSpaces(buf, sizeof(int32) * nelems);
+
+ foreach(lc, item->value.sequence.elems)
+ {
+ int32 elempos =
+ flattenJsonPathParseItem(cxt, lfirst(lc), nestingLevel,
+ insideArraySubscript);
+
+ *(int32 *) &buf->data[offset] = elempos - pos;
+ offset += sizeof(int32);
+ }
+ }
+ break;
default:
elog(ERROR, "unrecognized jsonpath item type: %d", item->type);
}
@@ -670,12 +695,12 @@ printJsonPathItem(StringInfo buf, JsonPathItem *v, bool inKey,
if (i)
appendStringInfoChar(buf, ',');
- printJsonPathItem(buf, &from, false, false);
+ printJsonPathItem(buf, &from, false, from.type == jpiSequence);
if (range)
{
appendBinaryStringInfo(buf, " to ", 4);
- printJsonPathItem(buf, &to, false, false);
+ printJsonPathItem(buf, &to, false, to.type == jpiSequence);
}
}
appendStringInfoChar(buf, ']');
@@ -736,6 +761,25 @@ printJsonPathItem(StringInfo buf, JsonPathItem *v, bool inKey,
case jpiKeyValue:
appendBinaryStringInfo(buf, ".keyvalue()", 11);
break;
+ case jpiSequence:
+ if (printBracketes || jspHasNext(v))
+ appendStringInfoChar(buf, '(');
+
+ for (i = 0; i < v->content.sequence.nelems; i++)
+ {
+ JsonPathItem elem;
+
+ if (i)
+ appendBinaryStringInfo(buf, ", ", 2);
+
+ jspGetSequenceElement(v, i, &elem);
+
+ printJsonPathItem(buf, &elem, false, elem.type == jpiSequence);
+ }
+
+ if (printBracketes || jspHasNext(v))
+ appendStringInfoChar(buf, ')');
+ break;
default:
elog(ERROR, "unrecognized jsonpath item type: %d", v->type);
}
@@ -808,6 +852,8 @@ operationPriority(JsonPathItemType op)
{
switch (op)
{
+ case jpiSequence:
+ return -1;
case jpiOr:
return 0;
case jpiAnd:
@@ -944,6 +990,11 @@ jspInitByBuffer(JsonPathItem *v, char *base, int32 pos)
read_int32(v->content.anybounds.first, base, pos);
read_int32(v->content.anybounds.last, base, pos);
break;
+ case jpiSequence:
+ read_int32(v->content.sequence.nelems, base, pos);
+ read_int32_n(v->content.sequence.elems, base, pos,
+ v->content.sequence.nelems);
+ break;
default:
elog(ERROR, "unrecognized jsonpath item type: %d", v->type);
}
@@ -1008,7 +1059,8 @@ jspGetNext(JsonPathItem *v, JsonPathItem *a)
v->type == jpiDouble ||
v->type == jpiDatetime ||
v->type == jpiKeyValue ||
- v->type == jpiStartsWith);
+ v->type == jpiStartsWith ||
+ v->type == jpiSequence);
if (a)
jspInitByBuffer(a, v->base, v->nextPos);
@@ -1103,3 +1155,11 @@ jspGetArraySubscript(JsonPathItem *v, JsonPathItem *from, JsonPathItem *to,
return true;
}
+
+void
+jspGetSequenceElement(JsonPathItem *v, int i, JsonPathItem *elem)
+{
+ Assert(v->type == jpiSequence);
+
+ jspInitByBuffer(elem, v->base, v->content.sequence.elems[i]);
+}
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index 1b05307..1e31d3c 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -1129,6 +1129,52 @@ executeItemOptUnwrapTarget(JsonPathExecContext *cxt, JsonPathItem *jsp,
return executeKeyValueMethod(cxt, jsp, jb, found);
+ case jpiSequence:
+ {
+ JsonPathItem next;
+ bool hasNext = jspGetNext(jsp, &next);
+ JsonValueList list;
+ JsonValueList *plist = hasNext ? &list : found;
+ JsonValueListIterator it;
+ int i;
+
+ for (i = 0; i < jsp->content.sequence.nelems; i++)
+ {
+ JsonbValue *v;
+
+ if (hasNext)
+ memset(&list, 0, sizeof(list));
+
+ jspGetSequenceElement(jsp, i, &elem);
+ res = executeItem(cxt, &elem, jb, plist);
+
+ if (jperIsError(res))
+ break;
+
+ if (!hasNext)
+ {
+ if (!found && res == jperOk)
+ break;
+ continue;
+ }
+
+ JsonValueListInitIterator(&list, &it);
+
+ while ((v = JsonValueListNext(&list, &it)))
+ {
+ res = executeItem(cxt, &next, v, found);
+
+ if (jperIsError(res) || (!found && res == jperOk))
+ {
+ i = jsp->content.sequence.nelems;
+ break;
+ }
+ }
+ }
+
+ break;
+ }
+
default:
elog(ERROR, "unrecognized jsonpath item type: %d", jsp->type);
}
diff --git a/src/backend/utils/adt/jsonpath_gram.y b/src/backend/utils/adt/jsonpath_gram.y
index 426dbb1..1de6f5a 100644
--- a/src/backend/utils/adt/jsonpath_gram.y
+++ b/src/backend/utils/adt/jsonpath_gram.y
@@ -56,6 +56,7 @@ static JsonPathParseItem *makeAny(int first, int last);
static JsonPathParseItem *makeItemLikeRegex(JsonPathParseItem *expr,
JsonPathString *pattern,
JsonPathString *flags);
+static JsonPathParseItem *makeItemSequence(List *elems);
/*
* Bison doesn't allocate anything that needs to live across parser calls,
@@ -101,9 +102,9 @@ static JsonPathParseItem *makeItemLikeRegex(JsonPathParseItem *expr,
%type <value> scalar_value path_primary expr array_accessor
any_path accessor_op key predicate delimited_predicate
index_elem starts_with_initial expr_or_predicate
- datetime_template opt_datetime_template
+ datetime_template opt_datetime_template expr_seq expr_or_seq
-%type <elems> accessor_expr
+%type <elems> accessor_expr expr_list
%type <indexs> index_list
@@ -127,7 +128,7 @@ static JsonPathParseItem *makeItemLikeRegex(JsonPathParseItem *expr,
%%
result:
- pg_opt mode expr_or_predicate {
+ pg_opt mode expr_or_seq {
*result = palloc(sizeof(JsonPathParseResult));
(*result)->expr = $3;
(*result)->lax = $2;
@@ -146,6 +147,20 @@ pg_opt:
| /* EMPTY */ { $$ = false; }
;
+expr_or_seq:
+ expr_or_predicate { $$ = $1; }
+ | expr_seq { $$ = $1; }
+ ;
+
+expr_seq:
+ expr_list { $$ = makeItemSequence($1); }
+ ;
+
+expr_list:
+ expr_or_predicate ',' expr_or_predicate { $$ = list_make2($1, $3); }
+ | expr_list ',' expr_or_predicate { $$ = lappend($1, $3); }
+ ;
+
mode:
STRICT_P { $$ = false; }
| LAX_P { $$ = true; }
@@ -201,6 +216,7 @@ path_primary:
| '$' { $$ = makeItemType(jpiRoot); }
| '@' { $$ = makeItemType(jpiCurrent); }
| LAST_P { $$ = makeItemType(jpiLast); }
+ | '(' expr_seq ')' { $$ = $2; }
;
accessor_expr:
@@ -550,6 +566,16 @@ makeItemLikeRegex(JsonPathParseItem *expr, JsonPathString *pattern,
return v;
}
+static JsonPathParseItem *
+makeItemSequence(List *elems)
+{
+ JsonPathParseItem *v = makeItemType(jpiSequence);
+
+ v->value.sequence.elems = elems;
+
+ return v;
+}
+
/*
* Convert from XQuery regex flags to those recognized by our regex library.
*/
diff --git a/src/include/utils/jsonpath.h b/src/include/utils/jsonpath.h
index d56175f..4cbdce3 100644
--- a/src/include/utils/jsonpath.h
+++ b/src/include/utils/jsonpath.h
@@ -87,6 +87,7 @@ typedef enum JsonPathItemType
jpiLast, /* LAST array subscript */
jpiStartsWith, /* STARTS WITH predicate */
jpiLikeRegex, /* LIKE_REGEX predicate */
+ jpiSequence, /* sequence constructor: 'expr, ...' */
} JsonPathItemType;
/* XQuery regex mode flags for LIKE_REGEX predicate */
@@ -149,6 +150,12 @@ typedef struct JsonPathItem
struct
{
+ int32 nelems;
+ int32 *elems;
+ } sequence;
+
+ struct
+ {
char *data; /* for bool, numeric and string/key */
int32 datalen; /* filled only for string/key */
} value;
@@ -176,6 +183,7 @@ extern bool jspGetBool(JsonPathItem *v);
extern char *jspGetString(JsonPathItem *v, int32 *len);
extern bool jspGetArraySubscript(JsonPathItem *v, JsonPathItem *from,
JsonPathItem *to, int i);
+extern void jspGetSequenceElement(JsonPathItem *v, int i, JsonPathItem *elem);
extern const char *jspOperationName(JsonPathItemType type);
@@ -229,6 +237,10 @@ struct JsonPathParseItem
uint32 flags;
} like_regex;
+ struct {
+ List *elems;
+ } sequence;
+
/* scalars */
Numeric numeric;
bool boolean;
diff --git a/src/test/regress/expected/jsonb_jsonpath.out b/src/test/regress/expected/jsonb_jsonpath.out
index ef8db2d..c5252ad 100644
--- a/src/test/regress/expected/jsonb_jsonpath.out
+++ b/src/test/regress/expected/jsonb_jsonpath.out
@@ -2524,3 +2524,57 @@ ORDER BY s1.num, s2.num;
{"s": "B"} | {"s": "B"} | false | true | true | true | false
(144 rows)
+-- extension: path sequences
+select jsonb_path_query('[1,2,3,4,5]', 'pg 10, 20, $[*], 30');
+ jsonb_path_query
+------------------
+ 10
+ 20
+ 1
+ 2
+ 3
+ 4
+ 5
+ 30
+(8 rows)
+
+select jsonb_path_query('[1,2,3,4,5]', 'pg lax 10, 20, $[*].a, 30');
+ jsonb_path_query
+------------------
+ 10
+ 20
+ 30
+(3 rows)
+
+select jsonb_path_query('[1,2,3,4,5]', 'pg strict 10, 20, $[*].a, 30');
+ERROR: jsonpath member accessor can only be applied to an object
+select jsonb_path_query('[1,2,3,4,5]', 'pg -(10, 20, $[1 to 3], 30)');
+ jsonb_path_query
+------------------
+ -10
+ -20
+ -2
+ -3
+ -4
+ -30
+(6 rows)
+
+select jsonb_path_query('[1,2,3,4,5]', 'pg lax (10, 20.5, $[1 to 3], "30").double()');
+ jsonb_path_query
+------------------
+ 10
+ 20.5
+ 2
+ 3
+ 4
+ 30
+(6 rows)
+
+select jsonb_path_query('[1,2,3,4,5]', 'pg $[(0, $[*], 5) ? (@ == 3)]');
+ jsonb_path_query
+------------------
+ 4
+(1 row)
+
+select jsonb_path_query('[1,2,3,4,5]', 'pg $[(0, $[*], 3) ? (@ == 3)]');
+ERROR: jsonpath array subscript is not a single numeric value
diff --git a/src/test/regress/expected/jsonpath.out b/src/test/regress/expected/jsonpath.out
index 52b36a8..f6a132c 100644
--- a/src/test/regress/expected/jsonpath.out
+++ b/src/test/regress/expected/jsonpath.out
@@ -568,6 +568,42 @@ select '((($ + 1)).a + ((2)).b ? ((((@ > 1)) || (exists(@.c)))))'::jsonpath;
(($ + 1)."a" + 2."b"?(@ > 1 || exists (@."c")))
(1 row)
+select 'pg 1, 2 + 3, $.a[*] + 5'::jsonpath;
+ jsonpath
+---------------------------
+ pg 1, 2 + 3, $."a"[*] + 5
+(1 row)
+
+select 'pg (1, 2, $.a)'::jsonpath;
+ jsonpath
+----------------
+ pg 1, 2, $."a"
+(1 row)
+
+select 'pg (1, 2, $.a).a[*]'::jsonpath;
+ jsonpath
+-------------------------
+ pg (1, 2, $."a")."a"[*]
+(1 row)
+
+select 'pg (1, 2, $.a) == 5'::jsonpath;
+ jsonpath
+-------------------------
+ pg ((1, 2, $."a") == 5)
+(1 row)
+
+select 'pg $[(1, 2, $.a) to (3, 4)]'::jsonpath;
+ jsonpath
+-------------------------------
+ pg $[(1, 2, $."a") to (3, 4)]
+(1 row)
+
+select 'pg $[(1, (2, $.a)), 3, (4, 5)]'::jsonpath;
+ jsonpath
+--------------------------------
+ pg $[(1, (2, $."a")),3,(4, 5)]
+(1 row)
+
select '$ ? (@.a < 1)'::jsonpath;
jsonpath
---------------
diff --git a/src/test/regress/sql/jsonb_jsonpath.sql b/src/test/regress/sql/jsonb_jsonpath.sql
index 591be00..0619ade 100644
--- a/src/test/regress/sql/jsonb_jsonpath.sql
+++ b/src/test/regress/sql/jsonb_jsonpath.sql
@@ -578,3 +578,12 @@ SELECT
jsonb_path_query_first(s1.j, '$.s > $s', vars => s2.j) gt
FROM str s1, str s2
ORDER BY s1.num, s2.num;
+
+-- extension: path sequences
+select jsonb_path_query('[1,2,3,4,5]', 'pg 10, 20, $[*], 30');
+select jsonb_path_query('[1,2,3,4,5]', 'pg lax 10, 20, $[*].a, 30');
+select jsonb_path_query('[1,2,3,4,5]', 'pg strict 10, 20, $[*].a, 30');
+select jsonb_path_query('[1,2,3,4,5]', 'pg -(10, 20, $[1 to 3], 30)');
+select jsonb_path_query('[1,2,3,4,5]', 'pg lax (10, 20.5, $[1 to 3], "30").double()');
+select jsonb_path_query('[1,2,3,4,5]', 'pg $[(0, $[*], 5) ? (@ == 3)]');
+select jsonb_path_query('[1,2,3,4,5]', 'pg $[(0, $[*], 3) ? (@ == 3)]');
diff --git a/src/test/regress/sql/jsonpath.sql b/src/test/regress/sql/jsonpath.sql
index 315e42f..2a53168 100644
--- a/src/test/regress/sql/jsonpath.sql
+++ b/src/test/regress/sql/jsonpath.sql
@@ -106,6 +106,13 @@ select '($)'::jsonpath;
select '(($))'::jsonpath;
select '((($ + 1)).a + ((2)).b ? ((((@ > 1)) || (exists(@.c)))))'::jsonpath;
+select 'pg 1, 2 + 3, $.a[*] + 5'::jsonpath;
+select 'pg (1, 2, $.a)'::jsonpath;
+select 'pg (1, 2, $.a).a[*]'::jsonpath;
+select 'pg (1, 2, $.a) == 5'::jsonpath;
+select 'pg $[(1, 2, $.a) to (3, 4)]'::jsonpath;
+select 'pg $[(1, (2, $.a)), 3, (4, 5)]'::jsonpath;
+
select '$ ? (@.a < 1)'::jsonpath;
select '$ ? (@.a < -1)'::jsonpath;
select '$ ? (@.a < +1)'::jsonpath;
--
2.7.4
>From b8a785c71d4fca8aa5660b6be91b5637c00f8f6f Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <n.glu...@postgrespro.ru>
Date: Thu, 6 Apr 2017 22:36:05 +0300
Subject: [PATCH v1 4/8] Add jsonpath array constructors
---
doc/src/sgml/func.sgml | 7 ++++
src/backend/utils/adt/jsonpath.c | 30 ++++++++++++---
src/backend/utils/adt/jsonpath_exec.c | 19 ++++++++++
src/backend/utils/adt/jsonpath_gram.y | 2 +
src/include/utils/jsonpath.h | 1 +
src/test/regress/expected/jsonb_jsonpath.out | 57 ++++++++++++++++++++++++++++
src/test/regress/expected/jsonpath.out | 12 ++++++
src/test/regress/sql/jsonb_jsonpath.sql | 11 ++++++
src/test/regress/sql/jsonpath.sql | 3 ++
9 files changed, 136 insertions(+), 6 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 9defd1a..7347e71 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -12632,6 +12632,13 @@ table2-mapping
<entry><literal>pg $[*], 4, 5</literal></entry>
<entry><literal>1, 2, 3, 4, 5</literal></entry>
</row>
+ <row>
+ <entry>Array constructor</entry>
+ <entry>Construct a JSON array by enumeration of its elements enclosed in brackets</entry>
+ <entry><literal>[1, 2, 3]</literal></entry>
+ <entry><literal>pg [$[*], 4, 5]</literal></entry>
+ <entry><literal>[1, 2, 3, 4, 5]</literal></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/backend/utils/adt/jsonpath.c b/src/backend/utils/adt/jsonpath.c
index 0fb4257..16031ba 100644
--- a/src/backend/utils/adt/jsonpath.c
+++ b/src/backend/utils/adt/jsonpath.c
@@ -368,13 +368,19 @@ flattenJsonPathParseItem(JsonPathEncodingContext *cxt, JsonPathParseItem *item,
case jpiMinus:
case jpiExists:
case jpiDatetime:
+ case jpiArray:
{
int32 arg = reserveSpaceForItemPointer(buf);
- chld = !item->value.arg ? pos :
- flattenJsonPathParseItem(cxt, item->value.arg,
- nestingLevel + argNestingLevel,
- insideArraySubscript);
+ if (item->type == jpiArray)
+ checkJsonPathExtensionsEnabled(cxt, item->type);
+
+ if (!item->value.arg)
+ break;
+
+ chld = flattenJsonPathParseItem(cxt, item->value.arg,
+ nestingLevel + argNestingLevel,
+ insideArraySubscript);
*(int32 *) (buf->data + arg) = chld - pos;
}
break;
@@ -780,6 +786,15 @@ printJsonPathItem(StringInfo buf, JsonPathItem *v, bool inKey,
if (printBracketes || jspHasNext(v))
appendStringInfoChar(buf, ')');
break;
+ case jpiArray:
+ appendStringInfoChar(buf, '[');
+ if (v->content.arg)
+ {
+ jspGetArg(v, &elem);
+ printJsonPathItem(buf, &elem, false, false);
+ }
+ appendStringInfoChar(buf, ']');
+ break;
default:
elog(ERROR, "unrecognized jsonpath item type: %d", v->type);
}
@@ -979,6 +994,7 @@ jspInitByBuffer(JsonPathItem *v, char *base, int32 pos)
case jpiMinus:
case jpiFilter:
case jpiDatetime:
+ case jpiArray:
read_int32(v->content.arg, base, pos);
break;
case jpiIndexArray:
@@ -1009,7 +1025,8 @@ jspGetArg(JsonPathItem *v, JsonPathItem *a)
v->type == jpiExists ||
v->type == jpiPlus ||
v->type == jpiMinus ||
- v->type == jpiDatetime);
+ v->type == jpiDatetime ||
+ v->type == jpiArray);
jspInitByBuffer(a, v->base, v->content.arg);
}
@@ -1060,7 +1077,8 @@ jspGetNext(JsonPathItem *v, JsonPathItem *a)
v->type == jpiDatetime ||
v->type == jpiKeyValue ||
v->type == jpiStartsWith ||
- v->type == jpiSequence);
+ v->type == jpiSequence ||
+ v->type == jpiArray);
if (a)
jspInitByBuffer(a, v->base, v->nextPos);
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index 1e31d3c..1dee30a 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -1175,6 +1175,25 @@ executeItemOptUnwrapTarget(JsonPathExecContext *cxt, JsonPathItem *jsp,
break;
}
+ case jpiArray:
+ {
+ JsonValueList list = {0};
+ JsonbValue *arr;
+
+ if (jsp->content.arg)
+ {
+ jspGetArg(jsp, &elem);
+ res = executeItem(cxt, &elem, jb, &list);
+
+ if (jperIsError(res))
+ break;
+ }
+
+ arr = wrapItemsInArray(&list);
+ res = executeNextItem(cxt, jsp, NULL, arr, found, false);
+ break;
+ }
+
default:
elog(ERROR, "unrecognized jsonpath item type: %d", jsp->type);
}
diff --git a/src/backend/utils/adt/jsonpath_gram.y b/src/backend/utils/adt/jsonpath_gram.y
index 1de6f5a..3507c1f 100644
--- a/src/backend/utils/adt/jsonpath_gram.y
+++ b/src/backend/utils/adt/jsonpath_gram.y
@@ -217,6 +217,8 @@ path_primary:
| '@' { $$ = makeItemType(jpiCurrent); }
| LAST_P { $$ = makeItemType(jpiLast); }
| '(' expr_seq ')' { $$ = $2; }
+ | '[' ']' { $$ = makeItemUnary(jpiArray, NULL); }
+ | '[' expr_or_seq ']' { $$ = makeItemUnary(jpiArray, $2); }
;
accessor_expr:
diff --git a/src/include/utils/jsonpath.h b/src/include/utils/jsonpath.h
index 4cbdce3..1b2b964 100644
--- a/src/include/utils/jsonpath.h
+++ b/src/include/utils/jsonpath.h
@@ -88,6 +88,7 @@ typedef enum JsonPathItemType
jpiStartsWith, /* STARTS WITH predicate */
jpiLikeRegex, /* LIKE_REGEX predicate */
jpiSequence, /* sequence constructor: 'expr, ...' */
+ jpiArray, /* array constructor: '[expr, ...]' */
} JsonPathItemType;
/* XQuery regex mode flags for LIKE_REGEX predicate */
diff --git a/src/test/regress/expected/jsonb_jsonpath.out b/src/test/regress/expected/jsonb_jsonpath.out
index c5252ad..cd56876 100644
--- a/src/test/regress/expected/jsonb_jsonpath.out
+++ b/src/test/regress/expected/jsonb_jsonpath.out
@@ -2199,6 +2199,12 @@ SELECT jsonb_path_query('[{"a": 1}, {"a": 2}]', '$[*] ? (@.a > 10)');
------------------
(0 rows)
+SELECT jsonb_path_query('[{"a": 1}, {"a": 2}]', 'pg [$[*].a]');
+ jsonb_path_query
+------------------
+ [1, 2]
+(1 row)
+
SELECT jsonb_path_query_array('[{"a": 1}, {"a": 2}, {}]', 'strict $[*].a');
ERROR: JSON object does not contain key "a"
SELECT jsonb_path_query_array('[{"a": 1}, {"a": 2}]', '$[*].a');
@@ -2231,6 +2237,12 @@ SELECT jsonb_path_query_array('[{"a": 1}, {"a": 2}, {"a": 3}, {"a": 5}]', '$[*].
[]
(1 row)
+SELECT jsonb_path_query_array('[{"a": 1}, {"a": 2}]', 'pg [$[*].a]');
+ jsonb_path_query_array
+------------------------
+ [[1, 2]]
+(1 row)
+
SELECT jsonb_path_query_first('[{"a": 1}, {"a": 2}, {}]', 'strict $[*].a');
ERROR: JSON object does not contain key "a"
SELECT jsonb_path_query_first('[{"a": 1}, {"a": 2}, {}]', 'strict $[*].a', silent => true);
@@ -2578,3 +2590,48 @@ select jsonb_path_query('[1,2,3,4,5]', 'pg $[(0, $[*], 5) ? (@ == 3)]');
select jsonb_path_query('[1,2,3,4,5]', 'pg $[(0, $[*], 3) ? (@ == 3)]');
ERROR: jsonpath array subscript is not a single numeric value
+-- extension: array constructors
+select jsonb_path_query('[1, 2, 3]', 'pg []');
+ jsonb_path_query
+------------------
+ []
+(1 row)
+
+select jsonb_path_query('[1, 2, 3]', 'pg [1, 2, $[*], 4, 5]');
+ jsonb_path_query
+-----------------------
+ [1, 2, 1, 2, 3, 4, 5]
+(1 row)
+
+select jsonb_path_query('[1, 2, 3]', 'pg [1, 2, $[*], 4, 5][*]');
+ jsonb_path_query
+------------------
+ 1
+ 2
+ 1
+ 2
+ 3
+ 4
+ 5
+(7 rows)
+
+select jsonb_path_query('[1, 2, 3]', 'pg [(1, (2, $[*])), (4, 5)]');
+ jsonb_path_query
+-----------------------
+ [1, 2, 1, 2, 3, 4, 5]
+(1 row)
+
+select jsonb_path_query('[1, 2, 3]', 'pg [[1, 2], [$[*], 4], 5, [(1,2)?(@ > 5)]]');
+ jsonb_path_query
+-------------------------------
+ [[1, 2], [1, 2, 3, 4], 5, []]
+(1 row)
+
+select jsonb_path_query('[1, 2, 3]', 'pg strict [1, 2, $[*].a, 4, 5]');
+ERROR: jsonpath member accessor can only be applied to an object
+select jsonb_path_query('[[1, 2], [3, 4, 5], [], [6, 7]]', 'pg [$[*][*] ? (@ > 3)]');
+ jsonb_path_query
+------------------
+ [4, 5, 6, 7]
+(1 row)
+
diff --git a/src/test/regress/expected/jsonpath.out b/src/test/regress/expected/jsonpath.out
index f6a132c..e212deb 100644
--- a/src/test/regress/expected/jsonpath.out
+++ b/src/test/regress/expected/jsonpath.out
@@ -604,6 +604,18 @@ select 'pg $[(1, (2, $.a)), 3, (4, 5)]'::jsonpath;
pg $[(1, (2, $."a")),3,(4, 5)]
(1 row)
+select 'pg []'::jsonpath;
+ jsonpath
+----------
+ pg []
+(1 row)
+
+select 'pg [[1, 2], ([(3, 4, 5), 6], []), $.a[*]]'::jsonpath;
+ jsonpath
+---------------------------------------------
+ pg [[1, 2], ([(3, 4, 5), 6], []), $."a"[*]]
+(1 row)
+
select '$ ? (@.a < 1)'::jsonpath;
jsonpath
---------------
diff --git a/src/test/regress/sql/jsonb_jsonpath.sql b/src/test/regress/sql/jsonb_jsonpath.sql
index 0619ade..be5c7c8 100644
--- a/src/test/regress/sql/jsonb_jsonpath.sql
+++ b/src/test/regress/sql/jsonb_jsonpath.sql
@@ -525,6 +525,7 @@ set time zone default;
SELECT jsonb_path_query('[{"a": 1}, {"a": 2}]', '$[*]');
SELECT jsonb_path_query('[{"a": 1}, {"a": 2}]', '$[*] ? (@.a > 10)');
+SELECT jsonb_path_query('[{"a": 1}, {"a": 2}]', 'pg [$[*].a]');
SELECT jsonb_path_query_array('[{"a": 1}, {"a": 2}, {}]', 'strict $[*].a');
SELECT jsonb_path_query_array('[{"a": 1}, {"a": 2}]', '$[*].a');
@@ -532,6 +533,7 @@ SELECT jsonb_path_query_array('[{"a": 1}, {"a": 2}]', '$[*].a ? (@ == 1)');
SELECT jsonb_path_query_array('[{"a": 1}, {"a": 2}]', '$[*].a ? (@ > 10)');
SELECT jsonb_path_query_array('[{"a": 1}, {"a": 2}, {"a": 3}, {"a": 5}]', '$[*].a ? (@ > $min && @ < $max)', vars => '{"min": 1, "max": 4}');
SELECT jsonb_path_query_array('[{"a": 1}, {"a": 2}, {"a": 3}, {"a": 5}]', '$[*].a ? (@ > $min && @ < $max)', vars => '{"min": 3, "max": 4}');
+SELECT jsonb_path_query_array('[{"a": 1}, {"a": 2}]', 'pg [$[*].a]');
SELECT jsonb_path_query_first('[{"a": 1}, {"a": 2}, {}]', 'strict $[*].a');
SELECT jsonb_path_query_first('[{"a": 1}, {"a": 2}, {}]', 'strict $[*].a', silent => true);
@@ -587,3 +589,12 @@ select jsonb_path_query('[1,2,3,4,5]', 'pg -(10, 20, $[1 to 3], 30)');
select jsonb_path_query('[1,2,3,4,5]', 'pg lax (10, 20.5, $[1 to 3], "30").double()');
select jsonb_path_query('[1,2,3,4,5]', 'pg $[(0, $[*], 5) ? (@ == 3)]');
select jsonb_path_query('[1,2,3,4,5]', 'pg $[(0, $[*], 3) ? (@ == 3)]');
+
+-- extension: array constructors
+select jsonb_path_query('[1, 2, 3]', 'pg []');
+select jsonb_path_query('[1, 2, 3]', 'pg [1, 2, $[*], 4, 5]');
+select jsonb_path_query('[1, 2, 3]', 'pg [1, 2, $[*], 4, 5][*]');
+select jsonb_path_query('[1, 2, 3]', 'pg [(1, (2, $[*])), (4, 5)]');
+select jsonb_path_query('[1, 2, 3]', 'pg [[1, 2], [$[*], 4], 5, [(1,2)?(@ > 5)]]');
+select jsonb_path_query('[1, 2, 3]', 'pg strict [1, 2, $[*].a, 4, 5]');
+select jsonb_path_query('[[1, 2], [3, 4, 5], [], [6, 7]]', 'pg [$[*][*] ? (@ > 3)]');
diff --git a/src/test/regress/sql/jsonpath.sql b/src/test/regress/sql/jsonpath.sql
index 2a53168..23ae376 100644
--- a/src/test/regress/sql/jsonpath.sql
+++ b/src/test/regress/sql/jsonpath.sql
@@ -113,6 +113,9 @@ select 'pg (1, 2, $.a) == 5'::jsonpath;
select 'pg $[(1, 2, $.a) to (3, 4)]'::jsonpath;
select 'pg $[(1, (2, $.a)), 3, (4, 5)]'::jsonpath;
+select 'pg []'::jsonpath;
+select 'pg [[1, 2], ([(3, 4, 5), 6], []), $.a[*]]'::jsonpath;
+
select '$ ? (@.a < 1)'::jsonpath;
select '$ ? (@.a < -1)'::jsonpath;
select '$ ? (@.a < +1)'::jsonpath;
--
2.7.4
>From 912ad135bbc2ae9b041635d588b724336fc69549 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <n.glu...@postgrespro.ru>
Date: Thu, 6 Apr 2017 23:34:14 +0300
Subject: [PATCH v1 5/8] Add jsonpath object constructors
---
doc/src/sgml/func.sgml | 7 +++
src/backend/utils/adt/jsonpath.c | 70 +++++++++++++++++++++++++++-
src/backend/utils/adt/jsonpath_exec.c | 56 ++++++++++++++++++++++
src/backend/utils/adt/jsonpath_gram.y | 26 ++++++++++-
src/include/utils/jsonpath.h | 18 +++++++
src/test/regress/expected/jsonb_jsonpath.out | 44 +++++++++++++++++
src/test/regress/expected/jsonpath.out | 18 +++++++
src/test/regress/sql/jsonb_jsonpath.sql | 10 ++++
src/test/regress/sql/jsonpath.sql | 4 ++
9 files changed, 251 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 7347e71..2fd1bae 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -12639,6 +12639,13 @@ table2-mapping
<entry><literal>pg [$[*], 4, 5]</literal></entry>
<entry><literal>[1, 2, 3, 4, 5]</literal></entry>
</row>
+ <row>
+ <entry>Object constructor</entry>
+ <entry>Construct a JSON object by enumeration of its fields enclosed in braces</entry>
+ <entry><literal>{"x": "y"}</literal></entry>
+ <entry><literal>pg {a: 1, "b c": $.x}</literal></entry>
+ <entry><literal>{"a": 1, "b c": "y"}</literal></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/backend/utils/adt/jsonpath.c b/src/backend/utils/adt/jsonpath.c
index 16031ba..3415c71 100644
--- a/src/backend/utils/adt/jsonpath.c
+++ b/src/backend/utils/adt/jsonpath.c
@@ -479,6 +479,40 @@ flattenJsonPathParseItem(JsonPathEncodingContext *cxt, JsonPathParseItem *item,
}
}
break;
+ case jpiObject:
+ {
+ int32 nfields = list_length(item->value.object.fields);
+ ListCell *lc;
+ int offset;
+
+ checkJsonPathExtensionsEnabled(cxt, item->type);
+
+ appendBinaryStringInfo(buf, (char *) &nfields, sizeof(nfields));
+
+ offset = buf->len;
+
+ appendStringInfoSpaces(buf, sizeof(int32) * 2 * nfields);
+
+ foreach(lc, item->value.object.fields)
+ {
+ JsonPathParseItem *field = lfirst(lc);
+ int32 keypos =
+ flattenJsonPathParseItem(cxt, field->value.args.left,
+ nestingLevel,
+ insideArraySubscript);
+ int32 valpos =
+ flattenJsonPathParseItem(cxt, field->value.args.right,
+ nestingLevel,
+ insideArraySubscript);
+ int32 *ppos = (int32 *) &buf->data[offset];
+
+ ppos[0] = keypos - pos;
+ ppos[1] = valpos - pos;
+
+ offset += 2 * sizeof(int32);
+ }
+ }
+ break;
default:
elog(ERROR, "unrecognized jsonpath item type: %d", item->type);
}
@@ -795,6 +829,26 @@ printJsonPathItem(StringInfo buf, JsonPathItem *v, bool inKey,
}
appendStringInfoChar(buf, ']');
break;
+ case jpiObject:
+ appendStringInfoChar(buf, '{');
+
+ for (i = 0; i < v->content.object.nfields; i++)
+ {
+ JsonPathItem key;
+ JsonPathItem val;
+
+ jspGetObjectField(v, i, &key, &val);
+
+ if (i)
+ appendBinaryStringInfo(buf, ", ", 2);
+
+ printJsonPathItem(buf, &key, false, false);
+ appendBinaryStringInfo(buf, ": ", 2);
+ printJsonPathItem(buf, &val, false, val.type == jpiSequence);
+ }
+
+ appendStringInfoChar(buf, '}');
+ break;
default:
elog(ERROR, "unrecognized jsonpath item type: %d", v->type);
}
@@ -1011,6 +1065,11 @@ jspInitByBuffer(JsonPathItem *v, char *base, int32 pos)
read_int32_n(v->content.sequence.elems, base, pos,
v->content.sequence.nelems);
break;
+ case jpiObject:
+ read_int32(v->content.object.nfields, base, pos);
+ read_int32_n(v->content.object.fields, base, pos,
+ v->content.object.nfields * 2);
+ break;
default:
elog(ERROR, "unrecognized jsonpath item type: %d", v->type);
}
@@ -1078,7 +1137,8 @@ jspGetNext(JsonPathItem *v, JsonPathItem *a)
v->type == jpiKeyValue ||
v->type == jpiStartsWith ||
v->type == jpiSequence ||
- v->type == jpiArray);
+ v->type == jpiArray ||
+ v->type == jpiObject);
if (a)
jspInitByBuffer(a, v->base, v->nextPos);
@@ -1181,3 +1241,11 @@ jspGetSequenceElement(JsonPathItem *v, int i, JsonPathItem *elem)
jspInitByBuffer(elem, v->base, v->content.sequence.elems[i]);
}
+
+void
+jspGetObjectField(JsonPathItem *v, int i, JsonPathItem *key, JsonPathItem *val)
+{
+ Assert(v->type == jpiObject);
+ jspInitByBuffer(key, v->base, v->content.object.fields[i].key);
+ jspInitByBuffer(val, v->base, v->content.object.fields[i].val);
+}
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index 1dee30a..8afe56b 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -1194,6 +1194,62 @@ executeItemOptUnwrapTarget(JsonPathExecContext *cxt, JsonPathItem *jsp,
break;
}
+ case jpiObject:
+ {
+ JsonbParseState *ps = NULL;
+ int i;
+
+ pushJsonbValue(&ps, WJB_BEGIN_OBJECT, NULL);
+
+ for (i = 0; i < jsp->content.object.nfields; i++)
+ {
+ JsonbValue *jbv;
+ JsonbValue jbvtmp;
+ JsonPathItem key;
+ JsonPathItem val;
+ JsonValueList key_list = {0};
+ JsonValueList val_list = {0};
+
+ jspGetObjectField(jsp, i, &key, &val);
+
+ res = executeItem(cxt, &key, jb, &key_list);
+ if (jperIsError(res))
+ return res;
+
+ if (JsonValueListLength(&key_list) != 1 ||
+ !(jbv = getScalar(JsonValueListHead(&key_list), jbvString)))
+ RETURN_ERROR(ereport(ERROR,
+ (errcode(ERRCODE_SQL_JSON_SCALAR_REQUIRED),
+ errmsg("key in jsonpath object constructor is not a single string"))));
+
+ res = executeItem(cxt, &val, jb, &val_list);
+ if (jperIsError(res))
+ return res;
+
+ if (jspIgnoreStructuralErrors(cxt) &&
+ JsonValueListIsEmpty(&val_list))
+ continue; /* skip empty fields in lax mode */
+
+ if (JsonValueListLength(&val_list) != 1)
+ RETURN_ERROR(ereport(ERROR,
+ (errcode(ERRCODE_SINGLETON_SQL_JSON_ITEM_REQUIRED),
+ errmsg("value in jsonpath object constructor is not single"),
+ errhint("Use jsonpath array syntax to wrap multi-item sequences into arrays"))));
+
+ pushJsonbValue(&ps, WJB_KEY, jbv);
+
+ jbv = JsonValueListHead(&val_list);
+ jbv = wrapJsonObjectOrArray(jbv, &jbvtmp);
+
+ pushJsonbValue(&ps, WJB_VALUE, jbv);
+ }
+
+ jb = pushJsonbValue(&ps, WJB_END_OBJECT, NULL);
+
+ res = executeNextItem(cxt, jsp, NULL, jb, found, false);
+ break;
+ }
+
default:
elog(ERROR, "unrecognized jsonpath item type: %d", jsp->type);
}
diff --git a/src/backend/utils/adt/jsonpath_gram.y b/src/backend/utils/adt/jsonpath_gram.y
index 3507c1f..93bbd55 100644
--- a/src/backend/utils/adt/jsonpath_gram.y
+++ b/src/backend/utils/adt/jsonpath_gram.y
@@ -57,6 +57,7 @@ static JsonPathParseItem *makeItemLikeRegex(JsonPathParseItem *expr,
JsonPathString *pattern,
JsonPathString *flags);
static JsonPathParseItem *makeItemSequence(List *elems);
+static JsonPathParseItem *makeItemObject(List *fields);
/*
* Bison doesn't allocate anything that needs to live across parser calls,
@@ -103,8 +104,9 @@ static JsonPathParseItem *makeItemSequence(List *elems);
any_path accessor_op key predicate delimited_predicate
index_elem starts_with_initial expr_or_predicate
datetime_template opt_datetime_template expr_seq expr_or_seq
+ object_field
-%type <elems> accessor_expr expr_list
+%type <elems> accessor_expr expr_list object_field_list
%type <indexs> index_list
@@ -219,6 +221,18 @@ path_primary:
| '(' expr_seq ')' { $$ = $2; }
| '[' ']' { $$ = makeItemUnary(jpiArray, NULL); }
| '[' expr_or_seq ']' { $$ = makeItemUnary(jpiArray, $2); }
+ | '{' object_field_list '}' { $$ = makeItemObject($2); }
+ ;
+
+object_field_list:
+ /* EMPTY */ { $$ = NIL; }
+ | object_field { $$ = list_make1($1); }
+ | object_field_list ',' object_field { $$ = lappend($1, $3); }
+ ;
+
+object_field:
+ key_name ':' expr_or_predicate
+ { $$ = makeItemBinary(jpiObjectField, makeItemString(&$1), $3); }
;
accessor_expr:
@@ -578,6 +592,16 @@ makeItemSequence(List *elems)
return v;
}
+static JsonPathParseItem *
+makeItemObject(List *fields)
+{
+ JsonPathParseItem *v = makeItemType(jpiObject);
+
+ v->value.object.fields = fields;
+
+ return v;
+}
+
/*
* Convert from XQuery regex flags to those recognized by our regex library.
*/
diff --git a/src/include/utils/jsonpath.h b/src/include/utils/jsonpath.h
index 1b2b964..9aefe70 100644
--- a/src/include/utils/jsonpath.h
+++ b/src/include/utils/jsonpath.h
@@ -89,6 +89,8 @@ typedef enum JsonPathItemType
jpiLikeRegex, /* LIKE_REGEX predicate */
jpiSequence, /* sequence constructor: 'expr, ...' */
jpiArray, /* array constructor: '[expr, ...]' */
+ jpiObject, /* object constructor: '{ key : value, ... }' */
+ jpiObjectField, /* element of object constructor: 'key : value' */
} JsonPathItemType;
/* XQuery regex mode flags for LIKE_REGEX predicate */
@@ -157,6 +159,16 @@ typedef struct JsonPathItem
struct
{
+ int32 nfields;
+ struct
+ {
+ int32 key;
+ int32 val;
+ } *fields;
+ } object;
+
+ struct
+ {
char *data; /* for bool, numeric and string/key */
int32 datalen; /* filled only for string/key */
} value;
@@ -185,6 +197,8 @@ extern char *jspGetString(JsonPathItem *v, int32 *len);
extern bool jspGetArraySubscript(JsonPathItem *v, JsonPathItem *from,
JsonPathItem *to, int i);
extern void jspGetSequenceElement(JsonPathItem *v, int i, JsonPathItem *elem);
+extern void jspGetObjectField(JsonPathItem *v, int i,
+ JsonPathItem *key, JsonPathItem *val);
extern const char *jspOperationName(JsonPathItemType type);
@@ -242,6 +256,10 @@ struct JsonPathParseItem
List *elems;
} sequence;
+ struct {
+ List *fields;
+ } object;
+
/* scalars */
Numeric numeric;
bool boolean;
diff --git a/src/test/regress/expected/jsonb_jsonpath.out b/src/test/regress/expected/jsonb_jsonpath.out
index cd56876..8a12757 100644
--- a/src/test/regress/expected/jsonb_jsonpath.out
+++ b/src/test/regress/expected/jsonb_jsonpath.out
@@ -2635,3 +2635,47 @@ select jsonb_path_query('[[1, 2], [3, 4, 5], [], [6, 7]]', 'pg [$[*][*] ? (@ > 3
[4, 5, 6, 7]
(1 row)
+-- extension: object constructors
+select jsonb_path_query('[1, 2, 3]', 'pg {}');
+ jsonb_path_query
+------------------
+ {}
+(1 row)
+
+select jsonb_path_query('[1, 2, 3]', 'pg {a: 2 + 3, "b": [$[*], 4, 5]}');
+ jsonb_path_query
+--------------------------------
+ {"a": 5, "b": [1, 2, 3, 4, 5]}
+(1 row)
+
+select jsonb_path_query('[1, 2, 3]', 'pg {a: 2 + 3, "b": [$[*], 4, 5]}.*');
+ jsonb_path_query
+------------------
+ 5
+ [1, 2, 3, 4, 5]
+(2 rows)
+
+select jsonb_path_query('[1, 2, 3]', 'pg {a: 2 + 3, "b": [$[*], 4, 5]}[*]');
+ jsonb_path_query
+--------------------------------
+ {"a": 5, "b": [1, 2, 3, 4, 5]}
+(1 row)
+
+select jsonb_path_query('[1, 2, 3]', 'pg {a: 2 + 3, "b": ($[*], 4, 5)}');
+ERROR: value in jsonpath object constructor is not single
+HINT: Use jsonpath array syntax to wrap multi-item sequences into arrays
+select jsonb_path_query('[1, 2, 3]', 'pg {a: 2 + 3, "b": {x: $, y: $[1] > 2, z: "foo"}}');
+ jsonb_path_query
+---------------------------------------------------------
+ {"a": 5, "b": {"x": [1, 2, 3], "y": false, "z": "foo"}}
+(1 row)
+
+select jsonb_path_query('[1, 2, 3]', 'pg {a: 2 + 3, "b": $[*] ? (@ > 3), c: "foo"}');
+ jsonb_path_query
+----------------------
+ {"a": 5, "c": "foo"}
+(1 row)
+
+select jsonb_path_query('[1, 2, 3]', 'pg strict {a: 2 + 3, "b": $[*] ? (@ > 3), c: "foo"}');
+ERROR: value in jsonpath object constructor is not single
+HINT: Use jsonpath array syntax to wrap multi-item sequences into arrays
diff --git a/src/test/regress/expected/jsonpath.out b/src/test/regress/expected/jsonpath.out
index e212deb..a669327 100644
--- a/src/test/regress/expected/jsonpath.out
+++ b/src/test/regress/expected/jsonpath.out
@@ -616,6 +616,24 @@ select 'pg [[1, 2], ([(3, 4, 5), 6], []), $.a[*]]'::jsonpath;
pg [[1, 2], ([(3, 4, 5), 6], []), $."a"[*]]
(1 row)
+select 'pg {}'::jsonpath;
+ jsonpath
+----------
+ pg {}
+(1 row)
+
+select 'pg {a: 1 + 2}'::jsonpath;
+ jsonpath
+-----------------
+ pg {"a": 1 + 2}
+(1 row)
+
+select 'pg {a: 1 + 2, b : (1,2), c: [$[*],4,5], d: { "e e e": "f f f" }}'::jsonpath;
+ jsonpath
+--------------------------------------------------------------------------
+ pg {"a": 1 + 2, "b": (1, 2), "c": [$[*], 4, 5], "d": {"e e e": "f f f"}}
+(1 row)
+
select '$ ? (@.a < 1)'::jsonpath;
jsonpath
---------------
diff --git a/src/test/regress/sql/jsonb_jsonpath.sql b/src/test/regress/sql/jsonb_jsonpath.sql
index be5c7c8..f4de5d0 100644
--- a/src/test/regress/sql/jsonb_jsonpath.sql
+++ b/src/test/regress/sql/jsonb_jsonpath.sql
@@ -598,3 +598,13 @@ select jsonb_path_query('[1, 2, 3]', 'pg [(1, (2, $[*])), (4, 5)]');
select jsonb_path_query('[1, 2, 3]', 'pg [[1, 2], [$[*], 4], 5, [(1,2)?(@ > 5)]]');
select jsonb_path_query('[1, 2, 3]', 'pg strict [1, 2, $[*].a, 4, 5]');
select jsonb_path_query('[[1, 2], [3, 4, 5], [], [6, 7]]', 'pg [$[*][*] ? (@ > 3)]');
+
+-- extension: object constructors
+select jsonb_path_query('[1, 2, 3]', 'pg {}');
+select jsonb_path_query('[1, 2, 3]', 'pg {a: 2 + 3, "b": [$[*], 4, 5]}');
+select jsonb_path_query('[1, 2, 3]', 'pg {a: 2 + 3, "b": [$[*], 4, 5]}.*');
+select jsonb_path_query('[1, 2, 3]', 'pg {a: 2 + 3, "b": [$[*], 4, 5]}[*]');
+select jsonb_path_query('[1, 2, 3]', 'pg {a: 2 + 3, "b": ($[*], 4, 5)}');
+select jsonb_path_query('[1, 2, 3]', 'pg {a: 2 + 3, "b": {x: $, y: $[1] > 2, z: "foo"}}');
+select jsonb_path_query('[1, 2, 3]', 'pg {a: 2 + 3, "b": $[*] ? (@ > 3), c: "foo"}');
+select jsonb_path_query('[1, 2, 3]', 'pg strict {a: 2 + 3, "b": $[*] ? (@ > 3), c: "foo"}');
diff --git a/src/test/regress/sql/jsonpath.sql b/src/test/regress/sql/jsonpath.sql
index 23ae376..555470b 100644
--- a/src/test/regress/sql/jsonpath.sql
+++ b/src/test/regress/sql/jsonpath.sql
@@ -116,6 +116,10 @@ select 'pg $[(1, (2, $.a)), 3, (4, 5)]'::jsonpath;
select 'pg []'::jsonpath;
select 'pg [[1, 2], ([(3, 4, 5), 6], []), $.a[*]]'::jsonpath;
+select 'pg {}'::jsonpath;
+select 'pg {a: 1 + 2}'::jsonpath;
+select 'pg {a: 1 + 2, b : (1,2), c: [$[*],4,5], d: { "e e e": "f f f" }}'::jsonpath;
+
select '$ ? (@.a < 1)'::jsonpath;
select '$ ? (@.a < -1)'::jsonpath;
select '$ ? (@.a < +1)'::jsonpath;
--
2.7.4
>From acff887d6d216da4b8bd4cf4add32d59750bded5 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <n.glu...@postgrespro.ru>
Date: Fri, 12 May 2017 00:07:24 +0300
Subject: [PATCH v1 6/8] Add jsonpath object subscripting
---
doc/src/sgml/func.sgml | 7 ++
src/backend/utils/adt/jsonpath_exec.c | 175 +++++++++++++++++++++++++--
src/test/regress/expected/jsonb_jsonpath.out | 96 +++++++++++++++
src/test/regress/sql/jsonb_jsonpath.sql | 24 ++++
4 files changed, 289 insertions(+), 13 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 2fd1bae..317afaa 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -12646,6 +12646,13 @@ table2-mapping
<entry><literal>pg {a: 1, "b c": $.x}</literal></entry>
<entry><literal>{"a": 1, "b c": "y"}</literal></entry>
</row>
+ <row>
+ <entry>Object subscription</entry>
+ <entry>Extract a field of JSON object using the specified expression as a key</entry>
+ <entry><literal>{"a": 1, "b": "a"}</literal></entry>
+ <entry><literal>pg $[$.b]</literal></entry>
+ <entry><literal>1</literal></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index 8afe56b..21d6ca8 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -728,7 +728,142 @@ executeItemOptUnwrapTarget(JsonPathExecContext *cxt, JsonPathItem *jsp,
break;
case jpiIndexArray:
- if (JsonbType(jb) == jbvArray || jspAutoWrap(cxt))
+ if (JsonbType(jb) == jbvObject &&
+ (jspAutoWrap(cxt) || /* numeric indexes */
+ jspUseExtensions(cxt))) /* string indexes */
+ {
+ int innermostArraySize = cxt->innermostArraySize;
+ int i;
+ JsonbValue bin;
+
+ jb = wrapJsonObjectOrArray(jb, &bin);
+ Assert(jb->type == jbvBinary);
+
+ cxt->innermostArraySize = 1;
+
+ for (i = 0; i < jsp->content.array.nelems; i++)
+ {
+ JsonPathItem from;
+ JsonPathItem to;
+ JsonbValue *key;
+ JsonValueList keys = { 0 };
+ bool range = jspGetArraySubscript(jsp, &from, &to, i);
+
+ if (range)
+ {
+ int index_from;
+ int index_to;
+
+ if (!jspAutoWrap(cxt))
+ RETURN_ERROR(ereport(ERROR,
+ (errcode(ERRCODE_SQL_JSON_ARRAY_NOT_FOUND),
+ errmsg("jsonpath array accessor can only be applied to an array"))));
+
+ res = getArrayIndex(cxt, &from, jb, &index_from);
+ if (jperIsError(res))
+ return res;
+
+ res = getArrayIndex(cxt, &to, jb, &index_to);
+ if (jperIsError(res))
+ return res;
+
+ res = jperNotFound;
+
+ if (index_from <= 0 && index_to >= 0)
+ {
+ res = executeNextItem(cxt, jsp, NULL, jb, found,
+ true);
+ if (jperIsError(res))
+ return res;
+ }
+
+ if (res == jperOk && !found)
+ break;
+
+ continue;
+ }
+
+ res = executeItem(cxt, &from, jb, &keys);
+
+ if (jperIsError(res))
+ return res;
+
+ if (JsonValueListLength(&keys) != 1)
+ RETURN_ERROR(ereport(ERROR,
+ (errcode(ERRCODE_INVALID_SQL_JSON_SUBSCRIPT),
+ errmsg("object subscript is not a single value"))));
+
+ key = JsonValueListHead(&keys);
+
+ Assert(key->type != jbvBinary ||
+ !JsonContainerIsScalar(key->val.binary.data));
+
+ res = jperNotFound;
+
+ if (key->type == jbvNumeric && jspAutoWrap(cxt))
+ {
+ int index;
+
+ res = getArrayIndex(cxt, NULL, key, &index);
+
+ if (jperIsError(res))
+ return res;
+
+ if (!index) /* only [0] can be extracted */
+ {
+ res = executeNextItem(cxt, jsp, NULL, jb, found,
+ true);
+ if (jperIsError(res))
+ return res;
+ }
+ else if (!jspIgnoreStructuralErrors(cxt))
+ RETURN_ERROR(ereport(ERROR,
+ (errcode(ERRCODE_INVALID_SQL_JSON_SUBSCRIPT),
+ errmsg("jsonpath array subscript is out of bounds"))));
+ else
+ res = jperNotFound;
+ }
+ else if (key->type == jbvString && jspUseExtensions(cxt))
+ {
+ JsonbValue valbuf;
+ JsonbValue *val;
+
+ val = getKeyJsonValueFromContainer(jb->val.binary.data,
+ key->val.string.val,
+ key->val.string.len,
+ &valbuf);
+
+ if (val)
+ {
+ res = executeNextItem(cxt, jsp, NULL, val, found,
+ true);
+ if (jperIsError(res))
+ return res;
+ }
+ else if (!jspIgnoreStructuralErrors(cxt))
+ RETURN_ERROR(ereport(ERROR,
+ (errcode(ERRCODE_SQL_JSON_MEMBER_NOT_FOUND),
+ errmsg("JSON object does not contain the specified key"))));
+ }
+ else if (!jspIgnoreStructuralErrors(cxt))
+ {
+ if (jspUseExtensions(cxt))
+ RETURN_ERROR(ereport(ERROR,
+ (errcode(ERRCODE_INVALID_SQL_JSON_SUBSCRIPT),
+ errmsg("jsonpath object subscript is not a string or number"))));
+ else
+ RETURN_ERROR(ereport(ERROR,
+ (errcode(ERRCODE_INVALID_SQL_JSON_SUBSCRIPT),
+ errmsg("jsonpath object subscript is not a number"))));
+ }
+
+ if (res == jperOk && !found)
+ break;
+ }
+
+ cxt->innermostArraySize = innermostArraySize;
+ }
+ else if (JsonbType(jb) == jbvArray || jspAutoWrap(cxt))
{
int innermostArraySize = cxt->innermostArraySize;
int i;
@@ -833,9 +968,14 @@ executeItemOptUnwrapTarget(JsonPathExecContext *cxt, JsonPathItem *jsp,
}
else if (!jspIgnoreStructuralErrors(cxt))
{
- RETURN_ERROR(ereport(ERROR,
- (errcode(ERRCODE_SQL_JSON_ARRAY_NOT_FOUND),
- errmsg("jsonpath array accessor can only be applied to an array"))));
+ if (jspUseExtensions(cxt))
+ RETURN_ERROR(ereport(ERROR,
+ (errcode(ERRCODE_SQL_JSON_ARRAY_NOT_FOUND),
+ errmsg("jsonpath array accessor can only be applied to an array or object"))));
+ else
+ RETURN_ERROR(ereport(ERROR,
+ (errcode(ERRCODE_SQL_JSON_ARRAY_NOT_FOUND),
+ errmsg("jsonpath array accessor can only be applied to an array"))));
}
break;
@@ -2543,19 +2683,28 @@ getArrayIndex(JsonPathExecContext *cxt, JsonPathItem *jsp, JsonbValue *jb,
int32 *index)
{
JsonbValue *jbv;
- JsonValueList found = {0};
- JsonPathExecResult res = executeItem(cxt, jsp, jb, &found);
Datum numeric_index;
bool have_error = false;
- if (jperIsError(res))
- return res;
+ if (jsp)
+ {
+ JsonValueList found = {0};
+ JsonPathExecResult res = executeItem(cxt, jsp, jb, &found);
- if (JsonValueListLength(&found) != 1 ||
- !(jbv = getScalar(JsonValueListHead(&found), jbvNumeric)))
- RETURN_ERROR(ereport(ERROR,
- (errcode(ERRCODE_INVALID_SQL_JSON_SUBSCRIPT),
- errmsg("jsonpath array subscript is not a single numeric value"))));
+ if (jperIsError(res))
+ return res;
+
+ if (JsonValueListLength(&found) != 1 ||
+ !(jbv = getScalar(JsonValueListHead(&found), jbvNumeric)))
+ RETURN_ERROR(ereport(ERROR,
+ (errcode(ERRCODE_INVALID_SQL_JSON_SUBSCRIPT),
+ errmsg("jsonpath array subscript is not a single numeric value"))));
+ }
+ else
+ {
+ jbv = jb; /* use the specified numeric value */
+ Assert(jbv->type == jbvNumeric);
+ }
numeric_index = DirectFunctionCall2(numeric_trunc,
NumericGetDatum(jbv->val.numeric),
diff --git a/src/test/regress/expected/jsonb_jsonpath.out b/src/test/regress/expected/jsonb_jsonpath.out
index 8a12757..69f0a61 100644
--- a/src/test/regress/expected/jsonb_jsonpath.out
+++ b/src/test/regress/expected/jsonb_jsonpath.out
@@ -177,6 +177,32 @@ select jsonb '[1]' @? 'strict $[1.2]';
(1 row)
+select jsonb_path_query('[1]', 'strict $[1.2]');
+ERROR: jsonpath array subscript is out of bounds
+select jsonb_path_query('{}', 'strict $[0.3]');
+ERROR: jsonpath array accessor can only be applied to an array
+select jsonb '{}' @? 'lax $[0.3]';
+ ?column?
+----------
+ t
+(1 row)
+
+select jsonb_path_query('{}', 'strict $[1.2]');
+ERROR: jsonpath array accessor can only be applied to an array
+select jsonb '{}' @? 'lax $[1.2]';
+ ?column?
+----------
+ f
+(1 row)
+
+select jsonb_path_query('{}', 'strict $[-2 to 3]');
+ERROR: jsonpath array accessor can only be applied to an array
+select jsonb '{}' @? 'lax $[-2 to 3]';
+ ?column?
+----------
+ t
+(1 row)
+
select jsonb '{"a": [1,2,3], "b": [3,4,5]}' @? '$ ? (@.a[*] > @.b[*])';
?column?
----------
@@ -404,6 +430,12 @@ select jsonb_path_query('1', 'lax $[*]');
1
(1 row)
+select jsonb_path_query('{}', 'lax $[0]');
+ jsonb_path_query
+------------------
+ {}
+(1 row)
+
select jsonb_path_query('[1]', 'lax $[0]');
jsonb_path_query
------------------
@@ -454,6 +486,12 @@ select jsonb_path_query('[1]', '$[last]');
1
(1 row)
+select jsonb_path_query('{}', '$[last]');
+ jsonb_path_query
+------------------
+ {}
+(1 row)
+
select jsonb_path_query('[1,2,3]', '$[last]');
jsonb_path_query
------------------
@@ -2679,3 +2717,61 @@ select jsonb_path_query('[1, 2, 3]', 'pg {a: 2 + 3, "b": $[*] ? (@ > 3), c: "foo
select jsonb_path_query('[1, 2, 3]', 'pg strict {a: 2 + 3, "b": $[*] ? (@ > 3), c: "foo"}');
ERROR: value in jsonpath object constructor is not single
HINT: Use jsonpath array syntax to wrap multi-item sequences into arrays
+-- extension: object subscripting
+select jsonb '{"a": 1}' @? 'pg $["a"]';
+ ?column?
+----------
+ t
+(1 row)
+
+select jsonb '{"a": 1}' @? 'pg $["b"]';
+ ?column?
+----------
+ f
+(1 row)
+
+select jsonb '{"a": 1}' @? 'pg strict $["b"]';
+ ?column?
+----------
+
+(1 row)
+
+select jsonb '{"a": 1}' @? 'pg $["b", "a"]';
+ ?column?
+----------
+ t
+(1 row)
+
+select jsonb_path_query('{"a": 1}', 'pg $["a"]');
+ jsonb_path_query
+------------------
+ 1
+(1 row)
+
+select jsonb_path_query('{"a": 1}', 'pg strict $["b"]');
+ERROR: JSON object does not contain the specified key
+select jsonb_path_query('{"a": 1}', 'pg lax $["b"]');
+ jsonb_path_query
+------------------
+(0 rows)
+
+select jsonb_path_query('{"a": 1, "b": 2}', 'pg lax $["b", "c", "b", "a", 0 to 3]');
+ jsonb_path_query
+------------------
+ 2
+ 2
+ 1
+ {"a": 1, "b": 2}
+(4 rows)
+
+select jsonb_path_query('null', 'pg {"a": 1}["a"]');
+ jsonb_path_query
+------------------
+ 1
+(1 row)
+
+select jsonb_path_query('null', 'pg {"a": 1}["b"]');
+ jsonb_path_query
+------------------
+(0 rows)
+
diff --git a/src/test/regress/sql/jsonb_jsonpath.sql b/src/test/regress/sql/jsonb_jsonpath.sql
index f4de5d0..999096d 100644
--- a/src/test/regress/sql/jsonb_jsonpath.sql
+++ b/src/test/regress/sql/jsonb_jsonpath.sql
@@ -30,6 +30,14 @@ select jsonb '[1]' @? '$[0.5]';
select jsonb '[1]' @? '$[0.9]';
select jsonb '[1]' @? '$[1.2]';
select jsonb '[1]' @? 'strict $[1.2]';
+select jsonb_path_query('[1]', 'strict $[1.2]');
+select jsonb_path_query('{}', 'strict $[0.3]');
+select jsonb '{}' @? 'lax $[0.3]';
+select jsonb_path_query('{}', 'strict $[1.2]');
+select jsonb '{}' @? 'lax $[1.2]';
+select jsonb_path_query('{}', 'strict $[-2 to 3]');
+select jsonb '{}' @? 'lax $[-2 to 3]';
+
select jsonb '{"a": [1,2,3], "b": [3,4,5]}' @? '$ ? (@.a[*] > @.b[*])';
select jsonb '{"a": [1,2,3], "b": [3,4,5]}' @? '$ ? (@.a[*] >= @.b[*])';
select jsonb '{"a": [1,2,3], "b": [3,4,"5"]}' @? '$ ? (@.a[*] >= @.b[*])';
@@ -80,6 +88,7 @@ select jsonb_path_query('[12, {"a": 13}, {"b": 14}]', 'lax $[0 to 10 / 0].a');
select jsonb_path_query('[12, {"a": 13}, {"b": 14}, "ccc", true]', '$[2.5 - 1 to $.size() - 2]');
select jsonb_path_query('1', 'lax $[0]');
select jsonb_path_query('1', 'lax $[*]');
+select jsonb_path_query('{}', 'lax $[0]');
select jsonb_path_query('[1]', 'lax $[0]');
select jsonb_path_query('[1]', 'lax $[*]');
select jsonb_path_query('[1,2,3]', 'lax $[*]');
@@ -90,6 +99,7 @@ select jsonb_path_query('[]', '$[last ? (exists(last))]');
select jsonb_path_query('[]', 'strict $[last]');
select jsonb_path_query('[]', 'strict $[last]', silent => true);
select jsonb_path_query('[1]', '$[last]');
+select jsonb_path_query('{}', '$[last]');
select jsonb_path_query('[1,2,3]', '$[last]');
select jsonb_path_query('[1,2,3]', '$[last - 1]');
select jsonb_path_query('[1,2,3]', '$[last ? (@.type() == "number")]');
@@ -608,3 +618,17 @@ select jsonb_path_query('[1, 2, 3]', 'pg {a: 2 + 3, "b": ($[*], 4, 5)}');
select jsonb_path_query('[1, 2, 3]', 'pg {a: 2 + 3, "b": {x: $, y: $[1] > 2, z: "foo"}}');
select jsonb_path_query('[1, 2, 3]', 'pg {a: 2 + 3, "b": $[*] ? (@ > 3), c: "foo"}');
select jsonb_path_query('[1, 2, 3]', 'pg strict {a: 2 + 3, "b": $[*] ? (@ > 3), c: "foo"}');
+
+-- extension: object subscripting
+select jsonb '{"a": 1}' @? 'pg $["a"]';
+select jsonb '{"a": 1}' @? 'pg $["b"]';
+select jsonb '{"a": 1}' @? 'pg strict $["b"]';
+select jsonb '{"a": 1}' @? 'pg $["b", "a"]';
+
+select jsonb_path_query('{"a": 1}', 'pg $["a"]');
+select jsonb_path_query('{"a": 1}', 'pg strict $["b"]');
+select jsonb_path_query('{"a": 1}', 'pg lax $["b"]');
+select jsonb_path_query('{"a": 1, "b": 2}', 'pg lax $["b", "c", "b", "a", 0 to 3]');
+
+select jsonb_path_query('null', 'pg {"a": 1}["a"]');
+select jsonb_path_query('null', 'pg {"a": 1}["b"]');
--
2.7.4
