From: Nikita Glukhov Date: Tue, 6 Nov 2018 16:33:14 +0300 Subject: [PATCH 4/4] Jsonpath syntax extensions --- src/backend/utils/adt/jsonpath.c | 153 ++++++++++- src/backend/utils/adt/jsonpath_exec.c | 394 ++++++++++++++++++++++----- src/backend/utils/adt/jsonpath_gram.y | 55 +++- src/include/utils/jsonpath.h | 28 ++ src/test/regress/expected/json_jsonpath.out | 228 +++++++++++++++- src/test/regress/expected/jsonb_jsonpath.out | 262 +++++++++++++++++- src/test/regress/expected/jsonpath.out | 66 +++++ src/test/regress/sql/json_jsonpath.sql | 47 ++++ src/test/regress/sql/jsonb_jsonpath.sql | 58 +++- src/test/regress/sql/jsonpath.sql | 14 + 10 files changed, 1227 insertions(+), 78 deletions(-) diff --git a/src/backend/utils/adt/jsonpath.c b/src/backend/utils/adt/jsonpath.c index 11d457d..456db2e 100644 --- a/src/backend/utils/adt/jsonpath.c +++ b/src/backend/utils/adt/jsonpath.c @@ -136,12 +136,15 @@ flattenJsonPathParseItem(StringInfo buf, JsonPathParseItem *item, case jpiPlus: case jpiMinus: case jpiExists: + case jpiArray: { - int32 arg; + int32 arg = item->value.arg ? buf->len : 0; - arg = buf->len; appendBinaryStringInfo(buf, (char*)&arg /* fake value */, sizeof(arg)); + if (!item->value.arg) + break; + chld = flattenJsonPathParseItem(buf, item->value.arg, nestingLevel + argNestingLevel, insideArraySubscript); @@ -218,6 +221,61 @@ flattenJsonPathParseItem(StringInfo buf, JsonPathParseItem *item, case jpiDouble: case jpiKeyValue: break; + case jpiSequence: + { + int32 nelems = list_length(item->value.sequence.elems); + ListCell *lc; + int offset; + + appendBinaryStringInfo(buf, (char *) &nelems, sizeof(nelems)); + + offset = buf->len; + + appendStringInfoSpaces(buf, sizeof(int32) * nelems); + + foreach(lc, item->value.sequence.elems) + { + int32 elempos = + flattenJsonPathParseItem(buf, lfirst(lc), nestingLevel, + insideArraySubscript); + + *(int32 *) &buf->data[offset] = elempos - pos; + offset += sizeof(int32); + } + } + break; + case jpiObject: + { + int32 nfields = list_length(item->value.object.fields); + ListCell *lc; + int offset; + + 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(buf, field->value.args.left, + nestingLevel, + insideArraySubscript); + int32 valpos = + flattenJsonPathParseItem(buf, 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, "Unknown jsonpath item type: %d", item->type); } @@ -305,6 +363,8 @@ operationPriority(JsonPathItemType op) { switch (op) { + case jpiSequence: + return -1; case jpiOr: return 0; case jpiAnd: @@ -494,12 +554,12 @@ printJsonPathItem(StringInfo buf, JsonPathItem *v, bool inKey, bool printBracket 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, ']'); @@ -563,6 +623,54 @@ printJsonPathItem(StringInfo buf, JsonPathItem *v, bool inKey, bool printBracket 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; + case jpiArray: + appendStringInfoChar(buf, '['); + if (v->content.arg) + { + jspGetArg(v, &elem); + printJsonPathItem(buf, &elem, false, false); + } + 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, "Unknown jsonpath item type: %d", v->type); } @@ -585,7 +693,7 @@ jsonpath_out(PG_FUNCTION_ARGS) appendBinaryStringInfo(&buf, "strict ", 7); jspInit(&v, in); - printJsonPathItem(&buf, &v, false, true); + printJsonPathItem(&buf, &v, false, v.type != jpiSequence); PG_RETURN_CSTRING(buf.data); } @@ -688,6 +796,7 @@ jspInitByBuffer(JsonPathItem *v, char *base, int32 pos) case jpiPlus: case jpiMinus: case jpiFilter: + case jpiArray: read_int32(v->content.arg, base, pos); break; case jpiIndexArray: @@ -699,6 +808,16 @@ 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; + 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, "Unknown jsonpath item type: %d", v->type); } @@ -713,7 +832,8 @@ jspGetArg(JsonPathItem *v, JsonPathItem *a) v->type == jpiIsUnknown || v->type == jpiExists || v->type == jpiPlus || - v->type == jpiMinus + v->type == jpiMinus || + v->type == jpiArray ); jspInitByBuffer(a, v->base, v->content.arg); @@ -765,7 +885,10 @@ jspGetNext(JsonPathItem *v, JsonPathItem *a) v->type == jpiDouble || v->type == jpiDatetime || v->type == jpiKeyValue || - v->type == jpiStartsWith + v->type == jpiStartsWith || + v->type == jpiSequence || + v->type == jpiArray || + v->type == jpiObject ); if (a) @@ -869,3 +992,19 @@ 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]); +} + +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 e017ac1..853c899 100644 --- a/src/backend/utils/adt/jsonpath_exec.c +++ b/src/backend/utils/adt/jsonpath_exec.c @@ -83,6 +83,8 @@ static inline JsonPathExecResult recursiveExecuteNested(JsonPathExecContext *cxt static inline JsonPathExecResult recursiveExecuteUnwrap(JsonPathExecContext *cxt, JsonPathItem *jsp, JsonbValue *jb, JsonValueList *found); +static inline JsonbValue *wrapItem(JsonbValue *jbv); + static inline JsonbValue *wrapItemsInArray(const JsonValueList *items); @@ -1686,7 +1688,116 @@ recursiveExecuteNoUnwrap(JsonPathExecContext *cxt, JsonPathItem *jsp, break; case jpiIndexArray: - if (JsonbType(jb) == jbvArray || jspAutoWrap(cxt)) + if (JsonbType(jb) == jbvObject) + { + int innermostArraySize = cxt->innermostArraySize; + int i; + JsonbValue bin; + + if (jb->type != jbvBinary) + jb = JsonbWrapInBinary(jb, &bin); + + cxt->innermostArraySize = 1; + + for (i = 0; i < jsp->content.array.nelems; i++) + { + JsonPathItem from; + JsonPathItem to; + JsonbValue *key; + JsonbValue tmp; + JsonValueList keys = { 0 }; + bool range = jspGetArraySubscript(jsp, &from, &to, i); + + if (range) + { + int index_from; + int index_to; + + if (!jspAutoWrap(cxt)) + return jperMakeError(ERRCODE_INVALID_JSON_SUBSCRIPT); + + 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 = recursiveExecuteNext(cxt, jsp, NULL, jb, + found, true); + if (jperIsError(res)) + return res; + } + + if (res == jperOk && !found) + break; + + continue; + } + + res = recursiveExecute(cxt, &from, jb, &keys); + + if (jperIsError(res)) + return res; + + if (JsonValueListLength(&keys) != 1) + return jperMakeError(ERRCODE_INVALID_JSON_SUBSCRIPT); + + key = JsonValueListHead(&keys); + + if (JsonbType(key) == jbvScalar) + key = JsonbExtractScalar(key->val.binary.data, &tmp); + + res = jperNotFound; + + if (key->type == jbvNumeric && jspAutoWrap(cxt)) + { + int index = DatumGetInt32( + DirectFunctionCall1(numeric_int4, + DirectFunctionCall2(numeric_trunc, + NumericGetDatum(key->val.numeric), + Int32GetDatum(0)))); + + if (!index) + { + res = recursiveExecuteNext(cxt, jsp, NULL, jb, + found, true); + if (jperIsError(res)) + return res; + } + else if (!jspIgnoreStructuralErrors(cxt)) + return jperMakeError(ERRCODE_INVALID_JSON_SUBSCRIPT); + } + else if (key->type == jbvString) + { + key = findJsonbValueFromContainer(jb->val.binary.data, + JB_FOBJECT, key); + + if (key) + { + res = recursiveExecuteNext(cxt, jsp, NULL, key, + found, false); + if (jperIsError(res)) + return res; + } + else if (!jspIgnoreStructuralErrors(cxt)) + return jperMakeError(ERRCODE_JSON_MEMBER_NOT_FOUND); + } + else if (!jspIgnoreStructuralErrors(cxt)) + return jperMakeError(ERRCODE_INVALID_JSON_SUBSCRIPT); + + if (res == jperOk && !found) + break; + } + + cxt->innermostArraySize = innermostArraySize; + } + else if (JsonbType(jb) == jbvArray || jspAutoWrap(cxt)) { int innermostArraySize = cxt->innermostArraySize; int i; @@ -1771,9 +1882,13 @@ recursiveExecuteNoUnwrap(JsonPathExecContext *cxt, JsonPathItem *jsp, cxt->innermostArraySize = innermostArraySize; } - else if (!jspIgnoreStructuralErrors(cxt)) + else { - res = jperMakeError(ERRCODE_JSON_ARRAY_NOT_FOUND); + if (jspAutoWrap(cxt)) + res = recursiveExecuteNoUnwrap(cxt, jsp, wrapItem(jb), + found); + else if (!jspIgnoreStructuralErrors(cxt)) + res = jperMakeError(ERRCODE_JSON_ARRAY_NOT_FOUND); } break; @@ -2051,7 +2166,6 @@ recursiveExecuteNoUnwrap(JsonPathExecContext *cxt, JsonPathItem *jsp, { JsonbValue jbvbuf; Datum value; - text *datetime; Oid typid; int32 typmod = -1; int tz; @@ -2062,84 +2176,113 @@ recursiveExecuteNoUnwrap(JsonPathExecContext *cxt, JsonPathItem *jsp, res = jperMakeError(ERRCODE_INVALID_ARGUMENT_FOR_JSON_DATETIME_FUNCTION); - if (jb->type != jbvString) - break; + if (jb->type == jbvNumeric && !jsp->content.args.left) + { + /* Standard extension: unix epoch to timestamptz */ + MemoryContext mcxt = CurrentMemoryContext; - datetime = cstring_to_text_with_len(jb->val.string.val, - jb->val.string.len); + PG_TRY(); + { + Datum unix_epoch = + DirectFunctionCall1(numeric_float8, + NumericGetDatum(jb->val.numeric)); + + value = DirectFunctionCall1(float8_timestamptz, + unix_epoch); + typid = TIMESTAMPTZOID; + tz = 0; + res = jperOk; + } + PG_CATCH(); + { + if (ERRCODE_TO_CATEGORY(geterrcode()) != + ERRCODE_DATA_EXCEPTION) + PG_RE_THROW(); - if (jsp->content.args.left) + FlushErrorState(); + MemoryContextSwitchTo(mcxt); + } + PG_END_TRY(); + } + else if (jb->type == jbvString) { - char *template_str; - int template_len; - char *tzname = NULL; + text *datetime = + cstring_to_text_with_len(jb->val.string.val, + jb->val.string.len); - jspGetLeftArg(jsp, &elem); + if (jsp->content.args.left) + { + char *template_str; + int template_len; + char *tzname = NULL; - if (elem.type != jpiString) - elog(ERROR, "invalid jsonpath item type for .datetime() argument"); + jspGetLeftArg(jsp, &elem); - template_str = jspGetString(&elem, &template_len); + if (elem.type != jpiString) + elog(ERROR, "invalid jsonpath item type for .datetime() argument"); - if (jsp->content.args.right) - { - JsonValueList tzlist = { 0 }; - JsonPathExecResult tzres; - JsonbValue *tzjbv; + template_str = jspGetString(&elem, &template_len); - jspGetRightArg(jsp, &elem); - tzres = recursiveExecuteNoUnwrap(cxt, &elem, jb, - &tzlist); + if (jsp->content.args.right) + { + JsonValueList tzlist = { 0 }; + JsonPathExecResult tzres; + JsonbValue *tzjbv; - if (jperIsError(tzres)) - return tzres; + jspGetRightArg(jsp, &elem); + tzres = recursiveExecuteNoUnwrap(cxt, &elem, jb, + &tzlist); - if (JsonValueListLength(&tzlist) != 1) - break; + if (jperIsError(tzres)) + return tzres; - tzjbv = JsonValueListHead(&tzlist); + if (JsonValueListLength(&tzlist) != 1) + break; - if (tzjbv->type != jbvString) - break; + tzjbv = JsonValueListHead(&tzlist); - tzname = pnstrdup(tzjbv->val.string.val, - tzjbv->val.string.len); - } + if (tzjbv->type != jbvString) + break; - if (tryToParseDatetime(template_str, template_len, datetime, - tzname, false, - &value, &typid, &typmod, &tz)) - res = jperOk; + tzname = pnstrdup(tzjbv->val.string.val, + tzjbv->val.string.len); + } - if (tzname) - pfree(tzname); - } - else - { - const char *templates[] = { - "yyyy-mm-dd HH24:MI:SS TZH:TZM", - "yyyy-mm-dd HH24:MI:SS TZH", - "yyyy-mm-dd HH24:MI:SS", - "yyyy-mm-dd", - "HH24:MI:SS TZH:TZM", - "HH24:MI:SS TZH", - "HH24:MI:SS" - }; - int i; - - for (i = 0; i < sizeof(templates) / sizeof(*templates); i++) + if (tryToParseDatetime(template_str, template_len, + datetime, tzname, false, + &value, &typid, &typmod, &tz)) + res = jperOk; + + if (tzname) + pfree(tzname); + } + else { - if (tryToParseDatetime(templates[i], -1, datetime, - NULL, true, &value, &typid, - &typmod, &tz)) + const char *templates[] = { + "yyyy-mm-dd HH24:MI:SS TZH:TZM", + "yyyy-mm-dd HH24:MI:SS TZH", + "yyyy-mm-dd HH24:MI:SS", + "yyyy-mm-dd", + "HH24:MI:SS TZH:TZM", + "HH24:MI:SS TZH", + "HH24:MI:SS" + }; + int i; + + for (i = 0; i < sizeof(templates) / sizeof(*templates); i++) { - res = jperOk; - break; + if (tryToParseDatetime(templates[i], -1, datetime, + NULL, true, &value, &typid, + &typmod, &tz)) + { + res = jperOk; + break; + } } } - } - pfree(datetime); + pfree(datetime); + } if (jperIsError(res)) break; @@ -2269,6 +2412,133 @@ recursiveExecuteNoUnwrap(JsonPathExecContext *cxt, JsonPathItem *jsp, } } break; + 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 = recursiveExecute(cxt, &elem, jb, plist); + + if (jperIsError(res)) + break; + + if (!hasNext) + { + if (!found && res == jperOk) + break; + continue; + } + + memset(&it, 0, sizeof(it)); + + while ((v = JsonValueListNext(&list, &it))) + { + res = recursiveExecute(cxt, &next, v, found); + + if (jperIsError(res) || (!found && res == jperOk)) + { + i = jsp->content.sequence.nelems; + break; + } + } + } + + break; + } + case jpiArray: + { + JsonValueList list = { 0 }; + + if (jsp->content.arg) + { + jspGetArg(jsp, &elem); + res = recursiveExecute(cxt, &elem, jb, &list); + + if (jperIsError(res)) + break; + } + + res = recursiveExecuteNext(cxt, jsp, NULL, + wrapItemsInArray(&list), + found, false); + } + break; + case jpiObject: + { + JsonbParseState *ps = NULL; + JsonbValue *obj; + 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); + + recursiveExecute(cxt, &key, jb, &key_list); + + if (JsonValueListLength(&key_list) != 1) + { + res = jperMakeError(ERRCODE_SINGLETON_JSON_ITEM_REQUIRED); + break; + } + + jbv = JsonValueListHead(&key_list); + + if (JsonbType(jbv) == jbvScalar) + jbv = JsonbExtractScalar(jbv->val.binary.data, &jbvtmp); + + if (jbv->type != jbvString) + { + res = jperMakeError(ERRCODE_JSON_SCALAR_REQUIRED); /* XXX */ + break; + } + + pushJsonbValue(&ps, WJB_KEY, jbv); + + recursiveExecute(cxt, &val, jb, &val_list); + + if (JsonValueListLength(&val_list) != 1) + { + res = jperMakeError(ERRCODE_SINGLETON_JSON_ITEM_REQUIRED); + break; + } + + jbv = JsonValueListHead(&val_list); + + if (jbv->type == jbvObject || jbv->type == jbvArray) + jbv = JsonbWrapInBinary(jbv, &jbvtmp); + + pushJsonbValue(&ps, WJB_VALUE, jbv); + } + + if (jperIsError(res)) + break; + + obj = pushJsonbValue(&ps, WJB_END_OBJECT, NULL); + + res = recursiveExecuteNext(cxt, jsp, NULL, obj, 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 3856a06..be1d488 100644 --- a/src/backend/utils/adt/jsonpath_gram.y +++ b/src/backend/utils/adt/jsonpath_gram.y @@ -255,6 +255,26 @@ makeItemLikeRegex(JsonPathParseItem *expr, string *pattern, string *flags) return v; } +static JsonPathParseItem * +makeItemSequence(List *elems) +{ + JsonPathParseItem *v = makeItemType(jpiSequence); + + v->value.sequence.elems = elems; + + return v; +} + +static JsonPathParseItem * +makeItemObject(List *fields) +{ + JsonPathParseItem *v = makeItemType(jpiObject); + + v->value.object.fields = fields; + + return v; +} + %} /* BISON Declarations */ @@ -288,9 +308,9 @@ makeItemLikeRegex(JsonPathParseItem *expr, string *pattern, string *flags) %type scalar_value path_primary expr array_accessor any_path accessor_op key predicate delimited_predicate index_elem starts_with_initial datetime_template opt_datetime_template - expr_or_predicate + expr_or_predicate expr_or_seq expr_seq object_field -%type accessor_expr +%type accessor_expr expr_list object_field_list %type index_list @@ -314,7 +334,7 @@ makeItemLikeRegex(JsonPathParseItem *expr, string *pattern, string *flags) %% result: - mode expr_or_predicate { + mode expr_or_seq { *result = palloc(sizeof(JsonPathParseResult)); (*result)->expr = $2; (*result)->lax = $1; @@ -327,6 +347,20 @@ expr_or_predicate: | predicate { $$ = $1; } ; +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; } @@ -381,6 +415,21 @@ path_primary: | '$' { $$ = makeItemType(jpiRoot); } | '@' { $$ = makeItemType(jpiCurrent); } | LAST_P { $$ = makeItemType(jpiLast); } + | '(' 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: diff --git a/src/include/utils/jsonpath.h b/src/include/utils/jsonpath.h index b3cf4c2..3747985 100644 --- a/src/include/utils/jsonpath.h +++ b/src/include/utils/jsonpath.h @@ -84,6 +84,10 @@ typedef enum JsonPathItemType { jpiLast, /* LAST array subscript */ jpiStartsWith, /* STARTS WITH predicate */ 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 */ @@ -138,6 +142,19 @@ typedef struct JsonPathItem { } anybounds; struct { + int32 nelems; + int32 *elems; + } sequence; + + 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; @@ -164,6 +181,9 @@ 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 void jspGetObjectField(JsonPathItem *v, int i, + JsonPathItem *key, JsonPathItem *val); /* * Parsing @@ -209,6 +229,14 @@ struct JsonPathParseItem { uint32 flags; } like_regex; + struct { + List *elems; + } sequence; + + struct { + List *fields; + } object; + /* scalars */ Numeric numeric; bool boolean; diff --git a/src/test/regress/expected/json_jsonpath.out b/src/test/regress/expected/json_jsonpath.out index 1c71984..86e553d 100644 --- a/src/test/regress/expected/json_jsonpath.out +++ b/src/test/regress/expected/json_jsonpath.out @@ -123,7 +123,7 @@ select json '[1]' @? 'strict $[1.2]'; select json '[1]' @* 'strict $[1.2]'; ERROR: Invalid SQL/JSON subscript select json '{}' @* 'strict $[0.3]'; -ERROR: SQL/JSON array not found +ERROR: Invalid SQL/JSON subscript select json '{}' @? 'lax $[0.3]'; ?column? ---------- @@ -131,7 +131,7 @@ select json '{}' @? 'lax $[0.3]'; (1 row) select json '{}' @* 'strict $[1.2]'; -ERROR: SQL/JSON array not found +ERROR: Invalid SQL/JSON subscript select json '{}' @? 'lax $[1.2]'; ?column? ---------- @@ -139,7 +139,7 @@ select json '{}' @? 'lax $[1.2]'; (1 row) select json '{}' @* 'strict $[-2 to 3]'; -ERROR: SQL/JSON array not found +ERROR: Invalid SQL/JSON subscript select json '{}' @? 'lax $[-2 to 3]'; ?column? ---------- @@ -1228,6 +1228,25 @@ select json '{}' @* '$.datetime()'; ERROR: Invalid argument for SQL/JSON datetime function select json '""' @* '$.datetime()'; ERROR: Invalid argument for SQL/JSON datetime function +-- Standard extension: UNIX epoch to timestamptz +select json '0' @* '$.datetime()'; + ?column? +----------------------------- + "1970-01-01T00:00:00+00:00" +(1 row) + +select json '0' @* '$.datetime().type()'; + ?column? +---------------------------- + "timestamp with time zone" +(1 row) + +select json '1490216035.5' @* '$.datetime()'; + ?column? +------------------------------- + "2017-03-22T20:53:55.5+00:00" +(1 row) + select json '"10-03-2017"' @* '$.datetime("dd-mm-yyyy")'; ?column? -------------- @@ -1688,6 +1707,12 @@ SELECT json '[{"a": 1}, {"a": 2}]' @* '$[*] ? (@.a > 10)'; ---------- (0 rows) +SELECT json '[{"a": 1}, {"a": 2}]' @* '[$[*].a]'; + ?column? +---------- + [1, 2] +(1 row) + SELECT json '[{"a": 1}, {"a": 2}]' @# '$[*].a'; ?column? ---------- @@ -1706,6 +1731,12 @@ SELECT json '[{"a": 1}, {"a": 2}]' @# '$[*].a ? (@ > 10)'; (1 row) +SELECT json '[{"a": 1}, {"a": 2}]' @# '[$[*].a]'; + ?column? +---------- + [1, 2] +(1 row) + SELECT json '[{"a": 1}, {"a": 2}]' @? '$[*] ? (@.a > 1)'; ?column? ---------- @@ -1730,3 +1761,194 @@ SELECT json '[{"a": 1}, {"a": 2}]' @~ '$[*].a > 2'; f (1 row) +-- extension: path sequences +select json '[1,2,3,4,5]' @* '10, 20, $[*], 30'; + ?column? +---------- + 10 + 20 + 1 + 2 + 3 + 4 + 5 + 30 +(8 rows) + +select json '[1,2,3,4,5]' @* 'lax 10, 20, $[*].a, 30'; + ?column? +---------- + 10 + 20 + 30 +(3 rows) + +select json '[1,2,3,4,5]' @* 'strict 10, 20, $[*].a, 30'; +ERROR: SQL/JSON member not found +select json '[1,2,3,4,5]' @* '-(10, 20, $[1 to 3], 30)'; + ?column? +---------- + -10 + -20 + -2 + -3 + -4 + -30 +(6 rows) + +select json '[1,2,3,4,5]' @* 'lax (10, 20.5, $[1 to 3], "30").double()'; + ?column? +---------- + 10 + 20.5 + 2 + 3 + 4 + 30 +(6 rows) + +select json '[1,2,3,4,5]' @* '$[(0, $[*], 5) ? (@ == 3)]'; + ?column? +---------- + 4 +(1 row) + +select json '[1,2,3,4,5]' @* '$[(0, $[*], 3) ? (@ == 3)]'; +ERROR: Invalid SQL/JSON subscript +-- extension: array constructors +select json '[1, 2, 3]' @* '[]'; + ?column? +---------- + [] +(1 row) + +select json '[1, 2, 3]' @* '[1, 2, $[*], 4, 5]'; + ?column? +----------------------- + [1, 2, 1, 2, 3, 4, 5] +(1 row) + +select json '[1, 2, 3]' @* '[1, 2, $[*], 4, 5][*]'; + ?column? +---------- + 1 + 2 + 1 + 2 + 3 + 4 + 5 +(7 rows) + +select json '[1, 2, 3]' @* '[(1, (2, $[*])), (4, 5)]'; + ?column? +----------------------- + [1, 2, 1, 2, 3, 4, 5] +(1 row) + +select json '[1, 2, 3]' @* '[[1, 2], [$[*], 4], 5, [(1,2)?(@ > 5)]]'; + ?column? +------------------------------- + [[1, 2], [1, 2, 3, 4], 5, []] +(1 row) + +select json '[1, 2, 3]' @* 'strict [1, 2, $[*].a, 4, 5]'; +ERROR: SQL/JSON member not found +select json '[[1, 2], [3, 4, 5], [], [6, 7]]' @* '[$[*][*] ? (@ > 3)]'; + ?column? +-------------- + [4, 5, 6, 7] +(1 row) + +-- extension: object constructors +select json '[1, 2, 3]' @* '{}'; + ?column? +---------- + {} +(1 row) + +select json '[1, 2, 3]' @* '{a: 2 + 3, "b": [$[*], 4, 5]}'; + ?column? +-------------------------------- + {"a": 5, "b": [1, 2, 3, 4, 5]} +(1 row) + +select json '[1, 2, 3]' @* '{a: 2 + 3, "b": [$[*], 4, 5]}.*'; + ?column? +----------------- + 5 + [1, 2, 3, 4, 5] +(2 rows) + +select json '[1, 2, 3]' @* '{a: 2 + 3, "b": [$[*], 4, 5]}[*]'; + ?column? +-------------------------------- + {"a": 5, "b": [1, 2, 3, 4, 5]} +(1 row) + +select json '[1, 2, 3]' @* '{a: 2 + 3, "b": ($[*], 4, 5)}'; +ERROR: Singleton SQL/JSON item required +select json '[1, 2, 3]' @* '{a: 2 + 3, "b": {x: $, y: $[1] > 2, z: "foo"}}'; + ?column? +--------------------------------------------------------- + {"a": 5, "b": {"x": [1, 2, 3], "y": false, "z": "foo"}} +(1 row) + +-- extension: object subscripting +select json '{"a": 1}' @? '$["a"]'; + ?column? +---------- + t +(1 row) + +select json '{"a": 1}' @? '$["b"]'; + ?column? +---------- + f +(1 row) + +select json '{"a": 1}' @? 'strict $["b"]'; + ?column? +---------- + +(1 row) + +select json '{"a": 1}' @? '$["b", "a"]'; + ?column? +---------- + t +(1 row) + +select json '{"a": 1}' @* '$["a"]'; + ?column? +---------- + 1 +(1 row) + +select json '{"a": 1}' @* 'strict $["b"]'; +ERROR: SQL/JSON member not found +select json '{"a": 1}' @* 'lax $["b"]'; + ?column? +---------- +(0 rows) + +select json '{"a": 1, "b": 2}' @* 'lax $["b", "c", "b", "a", 0 to 3]'; + ?column? +------------------ + 2 + 2 + 1 + {"a": 1, "b": 2} +(4 rows) + +select json 'null' @* '{"a": 1}["a"]'; + ?column? +---------- + 1 +(1 row) + +select json 'null' @* '{"a": 1}["b"]'; + ?column? +---------- +(0 rows) + diff --git a/src/test/regress/expected/jsonb_jsonpath.out b/src/test/regress/expected/jsonb_jsonpath.out index 66dea4b..1d3215b 100644 --- a/src/test/regress/expected/jsonb_jsonpath.out +++ b/src/test/regress/expected/jsonb_jsonpath.out @@ -120,6 +120,32 @@ select jsonb '[1]' @? 'strict $[1.2]'; (1 row) +select jsonb '[1]' @* 'strict $[1.2]'; +ERROR: Invalid SQL/JSON subscript +select jsonb '{}' @* 'strict $[0.3]'; +ERROR: Invalid SQL/JSON subscript +select jsonb '{}' @? 'lax $[0.3]'; + ?column? +---------- + t +(1 row) + +select jsonb '{}' @* 'strict $[1.2]'; +ERROR: Invalid SQL/JSON subscript +select jsonb '{}' @? 'lax $[1.2]'; + ?column? +---------- + f +(1 row) + +select jsonb '{}' @* 'strict $[-2 to 3]'; +ERROR: Invalid SQL/JSON subscript +select jsonb '{}' @? 'lax $[-2 to 3]'; + ?column? +---------- + t +(1 row) + select jsonb '{"a": [1,2,3], "b": [3,4,5]}' @? '$ ? (@.a[*] > @.b[*])'; ?column? ---------- @@ -254,6 +280,12 @@ select jsonb '1' @* 'lax $[*]'; 1 (1 row) +select jsonb '{}' @* 'lax $[0]'; + ?column? +---------- + {} +(1 row) + select jsonb '[1]' @* 'lax $[0]'; ?column? ---------- @@ -287,6 +319,12 @@ select jsonb '[1]' @* '$[last]'; 1 (1 row) +select jsonb '{}' @* 'lax $[last]'; + ?column? +---------- + {} +(1 row) + select jsonb '[1,2,3]' @* '$[last]'; ?column? ---------- @@ -1179,8 +1217,6 @@ select jsonb 'null' @* '$.datetime()'; ERROR: Invalid argument for SQL/JSON datetime function select jsonb 'true' @* '$.datetime()'; ERROR: Invalid argument for SQL/JSON datetime function -select jsonb '1' @* '$.datetime()'; -ERROR: Invalid argument for SQL/JSON datetime function select jsonb '[]' @* '$.datetime()'; ?column? ---------- @@ -1192,6 +1228,25 @@ select jsonb '{}' @* '$.datetime()'; ERROR: Invalid argument for SQL/JSON datetime function select jsonb '""' @* '$.datetime()'; ERROR: Invalid argument for SQL/JSON datetime function +-- Standard extension: UNIX epoch to timestamptz +select jsonb '0' @* '$.datetime()'; + ?column? +----------------------------- + "1970-01-01T00:00:00+00:00" +(1 row) + +select jsonb '0' @* '$.datetime().type()'; + ?column? +---------------------------- + "timestamp with time zone" +(1 row) + +select jsonb '1490216035.5' @* '$.datetime()'; + ?column? +------------------------------- + "2017-03-22T20:53:55.5+00:00" +(1 row) + select jsonb '"10-03-2017"' @* '$.datetime("dd-mm-yyyy")'; ?column? -------------- @@ -1667,6 +1722,12 @@ SELECT jsonb '[{"a": 1}, {"a": 2}]' @* '$[*] ? (@.a > 10)'; ---------- (0 rows) +SELECT jsonb '[{"a": 1}, {"a": 2}]' @* '[$[*].a]'; + ?column? +---------- + [1, 2] +(1 row) + SELECT jsonb '[{"a": 1}, {"a": 2}]' @# '$[*].a'; ?column? ---------- @@ -1685,6 +1746,12 @@ SELECT jsonb '[{"a": 1}, {"a": 2}]' @# '$[*].a ? (@ > 10)'; (1 row) +SELECT jsonb '[{"a": 1}, {"a": 2}]' @# '[$[*].a]'; + ?column? +---------- + [1, 2] +(1 row) + SELECT jsonb '[{"a": 1}, {"a": 2}]' @? '$[*].a ? (@ > 1)'; ?column? ---------- @@ -1709,3 +1776,194 @@ SELECT jsonb '[{"a": 1}, {"a": 2}]' @~ '$[*].a > 2'; f (1 row) +-- extension: path sequences +select jsonb '[1,2,3,4,5]' @* '10, 20, $[*], 30'; + ?column? +---------- + 10 + 20 + 1 + 2 + 3 + 4 + 5 + 30 +(8 rows) + +select jsonb '[1,2,3,4,5]' @* 'lax 10, 20, $[*].a, 30'; + ?column? +---------- + 10 + 20 + 30 +(3 rows) + +select jsonb '[1,2,3,4,5]' @* 'strict 10, 20, $[*].a, 30'; +ERROR: SQL/JSON member not found +select jsonb '[1,2,3,4,5]' @* '-(10, 20, $[1 to 3], 30)'; + ?column? +---------- + -10 + -20 + -2 + -3 + -4 + -30 +(6 rows) + +select jsonb '[1,2,3,4,5]' @* 'lax (10, 20.5, $[1 to 3], "30").double()'; + ?column? +---------- + 10 + 20.5 + 2 + 3 + 4 + 30 +(6 rows) + +select jsonb '[1,2,3,4,5]' @* '$[(0, $[*], 5) ? (@ == 3)]'; + ?column? +---------- + 4 +(1 row) + +select jsonb '[1,2,3,4,5]' @* '$[(0, $[*], 3) ? (@ == 3)]'; +ERROR: Invalid SQL/JSON subscript +-- extension: array constructors +select jsonb '[1, 2, 3]' @* '[]'; + ?column? +---------- + [] +(1 row) + +select jsonb '[1, 2, 3]' @* '[1, 2, $[*], 4, 5]'; + ?column? +----------------------- + [1, 2, 1, 2, 3, 4, 5] +(1 row) + +select jsonb '[1, 2, 3]' @* '[1, 2, $[*], 4, 5][*]'; + ?column? +---------- + 1 + 2 + 1 + 2 + 3 + 4 + 5 +(7 rows) + +select jsonb '[1, 2, 3]' @* '[(1, (2, $[*])), (4, 5)]'; + ?column? +----------------------- + [1, 2, 1, 2, 3, 4, 5] +(1 row) + +select jsonb '[1, 2, 3]' @* '[[1, 2], [$[*], 4], 5, [(1,2)?(@ > 5)]]'; + ?column? +------------------------------- + [[1, 2], [1, 2, 3, 4], 5, []] +(1 row) + +select jsonb '[1, 2, 3]' @* 'strict [1, 2, $[*].a, 4, 5]'; +ERROR: SQL/JSON member not found +select jsonb '[[1, 2], [3, 4, 5], [], [6, 7]]' @* '[$[*][*] ? (@ > 3)]'; + ?column? +-------------- + [4, 5, 6, 7] +(1 row) + +-- extension: object constructors +select jsonb '[1, 2, 3]' @* '{}'; + ?column? +---------- + {} +(1 row) + +select jsonb '[1, 2, 3]' @* '{a: 2 + 3, "b": [$[*], 4, 5]}'; + ?column? +-------------------------------- + {"a": 5, "b": [1, 2, 3, 4, 5]} +(1 row) + +select jsonb '[1, 2, 3]' @* '{a: 2 + 3, "b": [$[*], 4, 5]}.*'; + ?column? +----------------- + 5 + [1, 2, 3, 4, 5] +(2 rows) + +select jsonb '[1, 2, 3]' @* '{a: 2 + 3, "b": [$[*], 4, 5]}[*]'; + ?column? +-------------------------------- + {"a": 5, "b": [1, 2, 3, 4, 5]} +(1 row) + +select jsonb '[1, 2, 3]' @* '{a: 2 + 3, "b": ($[*], 4, 5)}'; +ERROR: Singleton SQL/JSON item required +select jsonb '[1, 2, 3]' @* '{a: 2 + 3, "b": {x: $, y: $[1] > 2, z: "foo"}}'; + ?column? +--------------------------------------------------------- + {"a": 5, "b": {"x": [1, 2, 3], "y": false, "z": "foo"}} +(1 row) + +-- extension: object subscripting +select jsonb '{"a": 1}' @? '$["a"]'; + ?column? +---------- + t +(1 row) + +select jsonb '{"a": 1}' @? '$["b"]'; + ?column? +---------- + f +(1 row) + +select jsonb '{"a": 1}' @? 'strict $["b"]'; + ?column? +---------- + +(1 row) + +select jsonb '{"a": 1}' @? '$["b", "a"]'; + ?column? +---------- + t +(1 row) + +select jsonb '{"a": 1}' @* '$["a"]'; + ?column? +---------- + 1 +(1 row) + +select jsonb '{"a": 1}' @* 'strict $["b"]'; +ERROR: SQL/JSON member not found +select jsonb '{"a": 1}' @* 'lax $["b"]'; + ?column? +---------- +(0 rows) + +select jsonb '{"a": 1, "b": 2}' @* 'lax $["b", "c", "b", "a", 0 to 3]'; + ?column? +------------------ + 2 + 2 + 1 + {"a": 1, "b": 2} +(4 rows) + +select jsonb 'null' @* '{"a": 1}["a"]'; + ?column? +---------- + 1 +(1 row) + +select jsonb 'null' @* '{"a": 1}["b"]'; + ?column? +---------- +(0 rows) + diff --git a/src/test/regress/expected/jsonpath.out b/src/test/regress/expected/jsonpath.out index 193fc68..ea29105 100644 --- a/src/test/regress/expected/jsonpath.out +++ b/src/test/regress/expected/jsonpath.out @@ -510,6 +510,72 @@ select '((($ + 1)).a + ((2)).b ? ((((@ > 1)) || (exists(@.c)))))'::jsonpath; (($ + 1)."a" + 2."b"?(@ > 1 || exists (@."c"))) (1 row) +select '1, 2 + 3, $.a[*] + 5'::jsonpath; + jsonpath +------------------------ + 1, 2 + 3, $."a"[*] + 5 +(1 row) + +select '(1, 2, $.a)'::jsonpath; + jsonpath +------------- + 1, 2, $."a" +(1 row) + +select '(1, 2, $.a).a[*]'::jsonpath; + jsonpath +---------------------- + (1, 2, $."a")."a"[*] +(1 row) + +select '(1, 2, $.a) == 5'::jsonpath; + jsonpath +---------------------- + ((1, 2, $."a") == 5) +(1 row) + +select '$[(1, 2, $.a) to (3, 4)]'::jsonpath; + jsonpath +---------------------------- + $[(1, 2, $."a") to (3, 4)] +(1 row) + +select '$[(1, (2, $.a)), 3, (4, 5)]'::jsonpath; + jsonpath +----------------------------- + $[(1, (2, $."a")),3,(4, 5)] +(1 row) + +select '[]'::jsonpath; + jsonpath +---------- + [] +(1 row) + +select '[[1, 2], ([(3, 4, 5), 6], []), $.a[*]]'::jsonpath; + jsonpath +------------------------------------------ + [[1, 2], ([(3, 4, 5), 6], []), $."a"[*]] +(1 row) + +select '{}'::jsonpath; + jsonpath +---------- + {} +(1 row) + +select '{a: 1 + 2}'::jsonpath; + jsonpath +-------------- + {"a": 1 + 2} +(1 row) + +select '{a: 1 + 2, b : (1,2), c: [$[*],4,5], d: { "e e e": "f f f" }}'::jsonpath; + jsonpath +----------------------------------------------------------------------- + {"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/json_jsonpath.sql b/src/test/regress/sql/json_jsonpath.sql index 824f510..0901876 100644 --- a/src/test/regress/sql/json_jsonpath.sql +++ b/src/test/regress/sql/json_jsonpath.sql @@ -255,6 +255,11 @@ select json '[]' @* 'strict $.datetime()'; select json '{}' @* '$.datetime()'; select json '""' @* '$.datetime()'; +-- Standard extension: UNIX epoch to timestamptz +select json '0' @* '$.datetime()'; +select json '0' @* '$.datetime().type()'; +select json '1490216035.5' @* '$.datetime()'; + select json '"10-03-2017"' @* '$.datetime("dd-mm-yyyy")'; select json '"10-03-2017"' @* '$.datetime("dd-mm-yyyy").type()'; select json '"10-03-2017 12:34"' @* '$.datetime("dd-mm-yyyy")'; @@ -367,13 +372,55 @@ set time zone default; SELECT json '[{"a": 1}, {"a": 2}]' @* '$[*]'; SELECT json '[{"a": 1}, {"a": 2}]' @* '$[*] ? (@.a > 10)'; +SELECT json '[{"a": 1}, {"a": 2}]' @* '[$[*].a]'; SELECT json '[{"a": 1}, {"a": 2}]' @# '$[*].a'; SELECT json '[{"a": 1}, {"a": 2}]' @# '$[*].a ? (@ == 1)'; SELECT json '[{"a": 1}, {"a": 2}]' @# '$[*].a ? (@ > 10)'; +SELECT json '[{"a": 1}, {"a": 2}]' @# '[$[*].a]'; SELECT json '[{"a": 1}, {"a": 2}]' @? '$[*] ? (@.a > 1)'; SELECT json '[{"a": 1}, {"a": 2}]' @? '$[*].a ? (@ > 2)'; SELECT json '[{"a": 1}, {"a": 2}]' @~ '$[*].a > 1'; SELECT json '[{"a": 1}, {"a": 2}]' @~ '$[*].a > 2'; + +-- extension: path sequences +select json '[1,2,3,4,5]' @* '10, 20, $[*], 30'; +select json '[1,2,3,4,5]' @* 'lax 10, 20, $[*].a, 30'; +select json '[1,2,3,4,5]' @* 'strict 10, 20, $[*].a, 30'; +select json '[1,2,3,4,5]' @* '-(10, 20, $[1 to 3], 30)'; +select json '[1,2,3,4,5]' @* 'lax (10, 20.5, $[1 to 3], "30").double()'; +select json '[1,2,3,4,5]' @* '$[(0, $[*], 5) ? (@ == 3)]'; +select json '[1,2,3,4,5]' @* '$[(0, $[*], 3) ? (@ == 3)]'; + +-- extension: array constructors +select json '[1, 2, 3]' @* '[]'; +select json '[1, 2, 3]' @* '[1, 2, $[*], 4, 5]'; +select json '[1, 2, 3]' @* '[1, 2, $[*], 4, 5][*]'; +select json '[1, 2, 3]' @* '[(1, (2, $[*])), (4, 5)]'; +select json '[1, 2, 3]' @* '[[1, 2], [$[*], 4], 5, [(1,2)?(@ > 5)]]'; +select json '[1, 2, 3]' @* 'strict [1, 2, $[*].a, 4, 5]'; +select json '[[1, 2], [3, 4, 5], [], [6, 7]]' @* '[$[*][*] ? (@ > 3)]'; + +-- extension: object constructors +select json '[1, 2, 3]' @* '{}'; +select json '[1, 2, 3]' @* '{a: 2 + 3, "b": [$[*], 4, 5]}'; +select json '[1, 2, 3]' @* '{a: 2 + 3, "b": [$[*], 4, 5]}.*'; +select json '[1, 2, 3]' @* '{a: 2 + 3, "b": [$[*], 4, 5]}[*]'; +select json '[1, 2, 3]' @* '{a: 2 + 3, "b": ($[*], 4, 5)}'; +select json '[1, 2, 3]' @* '{a: 2 + 3, "b": {x: $, y: $[1] > 2, z: "foo"}}'; + +-- extension: object subscripting +select json '{"a": 1}' @? '$["a"]'; +select json '{"a": 1}' @? '$["b"]'; +select json '{"a": 1}' @? 'strict $["b"]'; +select json '{"a": 1}' @? '$["b", "a"]'; + +select json '{"a": 1}' @* '$["a"]'; +select json '{"a": 1}' @* 'strict $["b"]'; +select json '{"a": 1}' @* 'lax $["b"]'; +select json '{"a": 1, "b": 2}' @* 'lax $["b", "c", "b", "a", 0 to 3]'; + +select json 'null' @* '{"a": 1}["a"]'; +select json 'null' @* '{"a": 1}["b"]'; diff --git a/src/test/regress/sql/jsonb_jsonpath.sql b/src/test/regress/sql/jsonb_jsonpath.sql index 43f34ef..ad7a320 100644 --- a/src/test/regress/sql/jsonb_jsonpath.sql +++ b/src/test/regress/sql/jsonb_jsonpath.sql @@ -19,6 +19,14 @@ select jsonb '[1]' @? '$[0.5]'; select jsonb '[1]' @? '$[0.9]'; select jsonb '[1]' @? '$[1.2]'; select jsonb '[1]' @? 'strict $[1.2]'; +select jsonb '[1]' @* 'strict $[1.2]'; +select jsonb '{}' @* 'strict $[0.3]'; +select jsonb '{}' @? 'lax $[0.3]'; +select jsonb '{}' @* 'strict $[1.2]'; +select jsonb '{}' @? 'lax $[1.2]'; +select jsonb '{}' @* '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[*])'; @@ -42,12 +50,14 @@ select jsonb '[12, {"a": 13}, {"b": 14}]' @* 'lax $[0 to 10].a'; select jsonb '[12, {"a": 13}, {"b": 14}, "ccc", true]' @* '$[2.5 - 1 to $.size() - 2]'; select jsonb '1' @* 'lax $[0]'; select jsonb '1' @* 'lax $[*]'; +select jsonb '{}' @* 'lax $[0]'; select jsonb '[1]' @* 'lax $[0]'; select jsonb '[1]' @* 'lax $[*]'; select jsonb '[1,2,3]' @* 'lax $[*]'; select jsonb '[]' @* '$[last]'; select jsonb '[]' @* 'strict $[last]'; select jsonb '[1]' @* '$[last]'; +select jsonb '{}' @* 'lax $[last]'; select jsonb '[1,2,3]' @* '$[last]'; select jsonb '[1,2,3]' @* '$[last - 1]'; select jsonb '[1,2,3]' @* '$[last ? (@.type() == "number")]'; @@ -240,12 +250,16 @@ select jsonb '[null, 1, "abc", "abd", "aBdC", "abdacb", "babc"]' @* 'lax $[*] ? select jsonb 'null' @* '$.datetime()'; select jsonb 'true' @* '$.datetime()'; -select jsonb '1' @* '$.datetime()'; select jsonb '[]' @* '$.datetime()'; select jsonb '[]' @* 'strict $.datetime()'; select jsonb '{}' @* '$.datetime()'; select jsonb '""' @* '$.datetime()'; +-- Standard extension: UNIX epoch to timestamptz +select jsonb '0' @* '$.datetime()'; +select jsonb '0' @* '$.datetime().type()'; +select jsonb '1490216035.5' @* '$.datetime()'; + select jsonb '"10-03-2017"' @* '$.datetime("dd-mm-yyyy")'; select jsonb '"10-03-2017"' @* '$.datetime("dd-mm-yyyy").type()'; select jsonb '"10-03-2017 12:34"' @* '$.datetime("dd-mm-yyyy")'; @@ -373,13 +387,55 @@ set time zone default; SELECT jsonb '[{"a": 1}, {"a": 2}]' @* '$[*]'; SELECT jsonb '[{"a": 1}, {"a": 2}]' @* '$[*] ? (@.a > 10)'; +SELECT jsonb '[{"a": 1}, {"a": 2}]' @* '[$[*].a]'; SELECT jsonb '[{"a": 1}, {"a": 2}]' @# '$[*].a'; SELECT jsonb '[{"a": 1}, {"a": 2}]' @# '$[*].a ? (@ == 1)'; SELECT jsonb '[{"a": 1}, {"a": 2}]' @# '$[*].a ? (@ > 10)'; +SELECT jsonb '[{"a": 1}, {"a": 2}]' @# '[$[*].a]'; SELECT jsonb '[{"a": 1}, {"a": 2}]' @? '$[*].a ? (@ > 1)'; SELECT jsonb '[{"a": 1}, {"a": 2}]' @? '$[*] ? (@.a > 2)'; SELECT jsonb '[{"a": 1}, {"a": 2}]' @~ '$[*].a > 1'; SELECT jsonb '[{"a": 1}, {"a": 2}]' @~ '$[*].a > 2'; + +-- extension: path sequences +select jsonb '[1,2,3,4,5]' @* '10, 20, $[*], 30'; +select jsonb '[1,2,3,4,5]' @* 'lax 10, 20, $[*].a, 30'; +select jsonb '[1,2,3,4,5]' @* 'strict 10, 20, $[*].a, 30'; +select jsonb '[1,2,3,4,5]' @* '-(10, 20, $[1 to 3], 30)'; +select jsonb '[1,2,3,4,5]' @* 'lax (10, 20.5, $[1 to 3], "30").double()'; +select jsonb '[1,2,3,4,5]' @* '$[(0, $[*], 5) ? (@ == 3)]'; +select jsonb '[1,2,3,4,5]' @* '$[(0, $[*], 3) ? (@ == 3)]'; + +-- extension: array constructors +select jsonb '[1, 2, 3]' @* '[]'; +select jsonb '[1, 2, 3]' @* '[1, 2, $[*], 4, 5]'; +select jsonb '[1, 2, 3]' @* '[1, 2, $[*], 4, 5][*]'; +select jsonb '[1, 2, 3]' @* '[(1, (2, $[*])), (4, 5)]'; +select jsonb '[1, 2, 3]' @* '[[1, 2], [$[*], 4], 5, [(1,2)?(@ > 5)]]'; +select jsonb '[1, 2, 3]' @* 'strict [1, 2, $[*].a, 4, 5]'; +select jsonb '[[1, 2], [3, 4, 5], [], [6, 7]]' @* '[$[*][*] ? (@ > 3)]'; + +-- extension: object constructors +select jsonb '[1, 2, 3]' @* '{}'; +select jsonb '[1, 2, 3]' @* '{a: 2 + 3, "b": [$[*], 4, 5]}'; +select jsonb '[1, 2, 3]' @* '{a: 2 + 3, "b": [$[*], 4, 5]}.*'; +select jsonb '[1, 2, 3]' @* '{a: 2 + 3, "b": [$[*], 4, 5]}[*]'; +select jsonb '[1, 2, 3]' @* '{a: 2 + 3, "b": ($[*], 4, 5)}'; +select jsonb '[1, 2, 3]' @* '{a: 2 + 3, "b": {x: $, y: $[1] > 2, z: "foo"}}'; + +-- extension: object subscripting +select jsonb '{"a": 1}' @? '$["a"]'; +select jsonb '{"a": 1}' @? '$["b"]'; +select jsonb '{"a": 1}' @? 'strict $["b"]'; +select jsonb '{"a": 1}' @? '$["b", "a"]'; + +select jsonb '{"a": 1}' @* '$["a"]'; +select jsonb '{"a": 1}' @* 'strict $["b"]'; +select jsonb '{"a": 1}' @* 'lax $["b"]'; +select jsonb '{"a": 1, "b": 2}' @* 'lax $["b", "c", "b", "a", 0 to 3]'; + +select jsonb 'null' @* '{"a": 1}["a"]'; +select jsonb 'null' @* '{"a": 1}["b"]'; diff --git a/src/test/regress/sql/jsonpath.sql b/src/test/regress/sql/jsonpath.sql index 8a3ea42..653f928 100644 --- a/src/test/regress/sql/jsonpath.sql +++ b/src/test/regress/sql/jsonpath.sql @@ -96,6 +96,20 @@ select '($)'::jsonpath; select '(($))'::jsonpath; select '((($ + 1)).a + ((2)).b ? ((((@ > 1)) || (exists(@.c)))))'::jsonpath; +select '1, 2 + 3, $.a[*] + 5'::jsonpath; +select '(1, 2, $.a)'::jsonpath; +select '(1, 2, $.a).a[*]'::jsonpath; +select '(1, 2, $.a) == 5'::jsonpath; +select '$[(1, 2, $.a) to (3, 4)]'::jsonpath; +select '$[(1, (2, $.a)), 3, (4, 5)]'::jsonpath; + +select '[]'::jsonpath; +select '[[1, 2], ([(3, 4, 5), 6], []), $.a[*]]'::jsonpath; + +select '{}'::jsonpath; +select '{a: 1 + 2}'::jsonpath; +select '{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 --------------65FB62A2E0811333017E53AC--