From acff887d6d216da4b8bd4cf4add32d59750bded5 Mon Sep 17 00:00:00 2001 From: Nikita Glukhov 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 pg {a: 1, "b c": $.x} {"a": 1, "b c": "y"} + + Object subscription + Extract a field of JSON object using the specified expression as a key + {"a": 1, "b": "a"} + pg $[$.b] + 1 + 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