agora inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feedFrom: Nikita Glukhov <n.gluhov@postgrespro.ru>
Subject: [PATCH v1 6/8] Add jsonpath object subscripting
Date: Fri, 12 May 2017 00:07:24 +0300
---
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
--------------574DCE16CAD28E71E2083DCD--
view thread (2+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: n.gluhov@postgrespro.ru
Subject: Re: [PATCH v1 6/8] Add jsonpath object subscripting
In-Reply-To: <no-message-id-712002@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox