diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index 9aa9b28..0011769 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -149,6 +149,12 @@ + jsonpath + + binary JSON path + + + line infinite line on a plane diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 2f59af2..2c231e9 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -11133,6 +11133,7 @@ table2-mapping Operator Right Operand Type + Return type Description Example Example Result @@ -11142,6 +11143,7 @@ table2-mapping -> int + json or jsonb Get JSON array element (indexed from zero, negative integers count from the end) '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2 @@ -11150,6 +11152,7 @@ table2-mapping -> text + json or jsonb Get JSON object field by key '{"a": {"b":"foo"}}'::json->'a' {"b":"foo"} @@ -11157,6 +11160,7 @@ table2-mapping ->> int + text Get JSON array element as text '[1,2,3]'::json->>2 3 @@ -11164,6 +11168,7 @@ table2-mapping ->> text + text Get JSON object field as text '{"a":1,"b":2}'::json->>'b' 2 @@ -11171,6 +11176,7 @@ table2-mapping #> text[] + json or jsonb Get JSON object at specified path '{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}' {"c": "foo"} @@ -11178,10 +11184,47 @@ table2-mapping #>> text[] + text Get JSON object at specified path as text '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}' 3 + + @* + jsonpath + setof json or setof jsonb + Get all JSON items returned by JSON path for a specified JSON value + '{"a":[1,2,3,4,5]}'::json @* '$.a[*] ? (@ > 2)' + +3 +4 +5 + + + + @# + jsonpath + json or jsonb + Get all JSON items returned by JSON path for a specified JSON value (if there is more than one item, they will be wrapped into an array) + '{"a":[1,2,3,4,5]}'::json @# '$.a[*] ? (@ > 2)' + [3, 4, 5] + + + @? + jsonpath + boolean + Does JSON path return any item for a specified JSON value? + '{"a":[1,2,3,4,5]}'::json @? '$.a[*] ? (@ > 2)' + true + + + @~ + jsonpath + boolean + Get JSON path predicate result for a specified JSON value + '{"a":[1,2,3,4,5]}'::json @~ '$.a[*] > 2' + true + diff --git a/doc/src/sgml/json.sgml b/doc/src/sgml/json.sgml index 731b469..f179bc4 100644 --- a/doc/src/sgml/json.sgml +++ b/doc/src/sgml/json.sgml @@ -569,4 +569,16 @@ SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qu compared using the default database collation. + + + <type>jsonpath</type> + + json + path + + + + TODO + + diff --git a/src/backend/Makefile b/src/backend/Makefile index 4a28267..23419cd 100644 --- a/src/backend/Makefile +++ b/src/backend/Makefile @@ -139,6 +139,9 @@ storage/lmgr/lwlocknames.h: storage/lmgr/generate-lwlocknames.pl storage/lmgr/lw utils/errcodes.h: utils/generate-errcodes.pl utils/errcodes.txt $(MAKE) -C utils errcodes.h +utils/adt/jsonpath_gram.h: utils/adt/jsonpath_gram.y + $(MAKE) -C utils/adt jsonpath_gram.h + # see explanation in parser/Makefile utils/fmgrprotos.h: utils/fmgroids.h ; @@ -169,7 +172,7 @@ submake-schemapg: .PHONY: generated-headers -generated-headers: $(top_builddir)/src/include/parser/gram.h $(top_builddir)/src/include/catalog/schemapg.h $(top_builddir)/src/include/storage/lwlocknames.h $(top_builddir)/src/include/utils/errcodes.h $(top_builddir)/src/include/utils/fmgroids.h $(top_builddir)/src/include/utils/fmgrprotos.h $(top_builddir)/src/include/utils/probes.h +generated-headers: $(top_builddir)/src/include/parser/gram.h $(top_builddir)/src/include/catalog/schemapg.h $(top_builddir)/src/include/storage/lwlocknames.h $(top_builddir)/src/include/utils/errcodes.h $(top_builddir)/src/include/utils/fmgroids.h $(top_builddir)/src/include/utils/fmgrprotos.h $(top_builddir)/src/include/utils/probes.h $(top_builddir)/src/include/utils/jsonpath_gram.h $(top_builddir)/src/include/parser/gram.h: parser/gram.h prereqdir=`cd '$(dir $<)' >/dev/null && pwd` && \ @@ -186,6 +189,11 @@ $(top_builddir)/src/include/storage/lwlocknames.h: storage/lmgr/lwlocknames.h cd '$(dir $@)' && rm -f $(notdir $@) && \ $(LN_S) "$$prereqdir/$(notdir $<)" . +$(top_builddir)/src/include/utils/jsonpath_gram.h: utils/adt/jsonpath_gram.h + prereqdir=`cd '$(dir $<)' >/dev/null && pwd` && \ + cd '$(dir $@)' && rm -f $(notdir $@) && \ + $(LN_S) "$$prereqdir/$(notdir $<)" . + $(top_builddir)/src/include/utils/errcodes.h: utils/errcodes.h prereqdir=`cd '$(dir $<)' >/dev/null && pwd` && \ cd '$(dir $@)' && rm -f $(notdir $@) && \ @@ -220,6 +228,7 @@ distprep: $(MAKE) -C replication repl_gram.c repl_scanner.c syncrep_gram.c syncrep_scanner.c $(MAKE) -C storage/lmgr lwlocknames.h $(MAKE) -C utils fmgrtab.c fmgroids.h fmgrprotos.h errcodes.h + $(MAKE) -C utils/adt jsonpath_gram.c jsonpath_gram.h jsonpath_scan.c $(MAKE) -C utils/misc guc-file.c $(MAKE) -C utils/sort qsort_tuple.c @@ -308,6 +317,7 @@ endif clean: rm -f $(LOCALOBJS) postgres$(X) $(POSTGRES_IMP) \ $(top_builddir)/src/include/parser/gram.h \ + $(top_builddir)/src/include/utils/jsonpath_gram.h \ $(top_builddir)/src/include/catalog/schemapg.h \ $(top_builddir)/src/include/storage/lwlocknames.h \ $(top_builddir)/src/include/utils/fmgroids.h \ @@ -344,6 +354,7 @@ maintainer-clean: distclean utils/fmgrtab.c \ utils/errcodes.h \ utils/misc/guc-file.c \ + utils/adt/jsonpath_gram.h \ utils/sort/qsort_tuple.c diff --git a/src/backend/lib/stringinfo.c b/src/backend/lib/stringinfo.c index 798a823..da0c098 100644 --- a/src/backend/lib/stringinfo.c +++ b/src/backend/lib/stringinfo.c @@ -306,3 +306,24 @@ enlargeStringInfo(StringInfo str, int needed) str->maxlen = newlen; } + +/* + * alignStringInfoInt - aling StringInfo to int by adding + * zero padding bytes + */ +void +alignStringInfoInt(StringInfo buf) +{ + switch(INTALIGN(buf->len) - buf->len) + { + case 3: + appendStringInfoCharMacro(buf, 0); + case 2: + appendStringInfoCharMacro(buf, 0); + case 1: + appendStringInfoCharMacro(buf, 0); + default: + break; + } +} + diff --git a/src/backend/utils/adt/Makefile b/src/backend/utils/adt/Makefile index 4b35dbb..41b00fd 100644 --- a/src/backend/utils/adt/Makefile +++ b/src/backend/utils/adt/Makefile @@ -17,7 +17,8 @@ OBJS = acl.o amutils.o arrayfuncs.o array_expanded.o array_selfuncs.o \ float.o format_type.o formatting.o genfile.o \ geo_ops.o geo_selfuncs.o geo_spgist.o inet_cidr_ntop.o inet_net_pton.o \ int.o int8.o json.o jsonb.o jsonb_gin.o jsonb_op.o jsonb_util.o \ - jsonfuncs.o like.o lockfuncs.o mac.o mac8.o misc.o nabstime.o name.o \ + jsonfuncs.o jsonpath_gram.o jsonpath_scan.o jsonpath.o jsonpath_exec.o \ + like.o lockfuncs.o mac.o mac8.o misc.o nabstime.o name.o \ network.o network_gist.o network_selfuncs.o network_spgist.o \ numeric.o numutils.o oid.o oracle_compat.o \ orderedsetaggs.o pg_locale.o pg_lsn.o pg_upgrade_support.o \ @@ -32,6 +33,26 @@ OBJS = acl.o amutils.o arrayfuncs.o array_expanded.o array_selfuncs.o \ txid.o uuid.o varbit.o varchar.o varlena.o version.o \ windowfuncs.o xid.o xml.o +# Latest flex causes warnings in this file. +ifeq ($(GCC),yes) +scan.o: CFLAGS += -Wno-error +endif + +jsonpath_gram.c: BISONFLAGS += -d + +jsonpath_scan.c: FLEXFLAGS = -CF -p -p + +jsonpath_gram.h: jsonpath_gram.c ; + +# Force these dependencies to be known even without dependency info built: +jsonpath_gram.o jsonpath_scan.o jsonpath_parser.o: jsonpath_gram.h + +# jsonpath_gram.c, jsonpath_gram.h, and jsonpath_scan.c are in the distribution +# tarball, so they are not cleaned here. +clean distclean maintainer-clean: + rm -f lex.backup + + like.o: like.c like_match.c varlena.o: varlena.c levenshtein.c diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c index 37e97f2..1890064 100644 --- a/src/backend/utils/adt/formatting.c +++ b/src/backend/utils/adt/formatting.c @@ -3755,8 +3755,8 @@ to_date(PG_FUNCTION_ARGS) * presence of date/time/zone components in the format string. */ Datum -to_datetime(text *date_txt, const char *fmt, int fmt_len, bool strict, - Oid *typid, int32 *typmod) +to_datetime(text *date_txt, const char *fmt, int fmt_len, char *tzname, + bool strict, Oid *typid, int32 *typmod, int *tz) { struct pg_tm tm; fsec_t fsec; @@ -3765,6 +3765,7 @@ to_datetime(text *date_txt, const char *fmt, int fmt_len, bool strict, do_to_timestamp(date_txt, fmt, fmt_len, strict, &tm, &fsec, &flags); *typmod = -1; /* TODO implement FF1, ..., FF9 */ + *tz = 0; if (flags & DCH_DATED) { @@ -3773,20 +3774,27 @@ to_datetime(text *date_txt, const char *fmt, int fmt_len, bool strict, if (flags & DCH_ZONED) { TimestampTz result; - int tz; if (tm.tm_zone) + tzname = (char *) tm.tm_zone; + + if (tzname) { - int dterr = DecodeTimezone((char *) tm.tm_zone, &tz); + int dterr = DecodeTimezone(tzname, tz); if (dterr) - DateTimeParseError(dterr, text_to_cstring(date_txt), - "timestamptz"); + DateTimeParseError(dterr, tzname, "timestamptz"); } else - tz = DetermineTimeZoneOffset(&tm, session_timezone); + { + ereport(ERROR, + (errcode(ERRCODE_INVALID_DATETIME_FORMAT), + errmsg("missing time-zone in timestamptz input string"))); - if (tm2timestamp(&tm, fsec, &tz, &result) != 0) + *tz = DetermineTimeZoneOffset(&tm, session_timezone); + } + + if (tm2timestamp(&tm, fsec, tz, &result) != 0) ereport(ERROR, (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE), errmsg("timestamptz out of range"))); @@ -3850,20 +3858,27 @@ to_datetime(text *date_txt, const char *fmt, int fmt_len, bool strict, if (flags & DCH_ZONED) { TimeTzADT *result = palloc(sizeof(TimeTzADT)); - int tz; if (tm.tm_zone) + tzname = (char *) tm.tm_zone; + + if (tzname) { - int dterr = DecodeTimezone((char *) tm.tm_zone, &tz); + int dterr = DecodeTimezone(tzname, tz); if (dterr) - DateTimeParseError(dterr, text_to_cstring(date_txt), - "timetz"); + DateTimeParseError(dterr, tzname, "timetz"); } else - tz = DetermineTimeZoneOffset(&tm, session_timezone); + { + ereport(ERROR, + (errcode(ERRCODE_INVALID_DATETIME_FORMAT), + errmsg("missing time-zone in timestamptz input string"))); + + *tz = DetermineTimeZoneOffset(&tm, session_timezone); + } - if (tm2timetz(&tm, fsec, tz, result) != 0) + if (tm2timetz(&tm, fsec, *tz, result) != 0) ereport(ERROR, (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE), errmsg("timetz out of range"))); diff --git a/src/backend/utils/adt/json.c b/src/backend/utils/adt/json.c index 6f0fe94..79eeac7 100644 --- a/src/backend/utils/adt/json.c +++ b/src/backend/utils/adt/json.c @@ -1506,7 +1506,7 @@ datum_to_json(Datum val, bool is_null, StringInfo result, { char buf[MAXDATELEN + 1]; - JsonEncodeDateTime(buf, val, DATEOID); + JsonEncodeDateTime(buf, val, DATEOID, NULL); appendStringInfo(result, "\"%s\"", buf); } break; @@ -1514,7 +1514,7 @@ datum_to_json(Datum val, bool is_null, StringInfo result, { char buf[MAXDATELEN + 1]; - JsonEncodeDateTime(buf, val, TIMESTAMPOID); + JsonEncodeDateTime(buf, val, TIMESTAMPOID, NULL); appendStringInfo(result, "\"%s\"", buf); } break; @@ -1522,7 +1522,7 @@ datum_to_json(Datum val, bool is_null, StringInfo result, { char buf[MAXDATELEN + 1]; - JsonEncodeDateTime(buf, val, TIMESTAMPTZOID); + JsonEncodeDateTime(buf, val, TIMESTAMPTZOID, NULL); appendStringInfo(result, "\"%s\"", buf); } break; @@ -1553,7 +1553,7 @@ datum_to_json(Datum val, bool is_null, StringInfo result, * optionally preallocated buffer 'buf'. */ char * -JsonEncodeDateTime(char *buf, Datum value, Oid typid) +JsonEncodeDateTime(char *buf, Datum value, Oid typid, int *tzp) { if (!buf) buf = palloc(MAXDATELEN + 1); @@ -1630,11 +1630,30 @@ JsonEncodeDateTime(char *buf, Datum value, Oid typid) const char *tzn = NULL; timestamp = DatumGetTimestampTz(value); + + /* + * If time-zone is specified, we apply a time-zone shift, + * convert timestamptz to pg_tm as if it was without time-zone, + * and then use specified time-zone for encoding timestamp + * into a string. + */ + if (tzp) + { + tz = *tzp; + timestamp -= (TimestampTz) tz * USECS_PER_SEC; + } + /* Same as timestamptz_out(), but forcing DateStyle */ if (TIMESTAMP_NOT_FINITE(timestamp)) EncodeSpecialTimestamp(timestamp, buf); - else if (timestamp2tm(timestamp, &tz, &tm, &fsec, &tzn, NULL) == 0) + else if (timestamp2tm(timestamp, tzp ? NULL : &tz, &tm, &fsec, + tzp ? NULL : &tzn, NULL) == 0) + { + if (tzp) + tm.tm_isdst = 1; /* set time-zone presence flag */ + EncodeDateTime(&tm, fsec, true, tz, tzn, USE_XSD_DATES, buf); + } else ereport(ERROR, (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE), diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c index 0f70180..0f20162 100644 --- a/src/backend/utils/adt/jsonb.c +++ b/src/backend/utils/adt/jsonb.c @@ -787,17 +787,17 @@ datum_to_jsonb(Datum val, bool is_null, JsonbInState *result, break; case JSONBTYPE_DATE: jb.type = jbvString; - jb.val.string.val = JsonEncodeDateTime(NULL, val, DATEOID); + jb.val.string.val = JsonEncodeDateTime(NULL, val, DATEOID, NULL); jb.val.string.len = strlen(jb.val.string.val); break; case JSONBTYPE_TIMESTAMP: jb.type = jbvString; - jb.val.string.val = JsonEncodeDateTime(NULL, val, TIMESTAMPOID); + jb.val.string.val = JsonEncodeDateTime(NULL, val, TIMESTAMPOID, NULL); jb.val.string.len = strlen(jb.val.string.val); break; case JSONBTYPE_TIMESTAMPTZ: jb.type = jbvString; - jb.val.string.val = JsonEncodeDateTime(NULL, val, TIMESTAMPTZOID); + jb.val.string.val = JsonEncodeDateTime(NULL, val, TIMESTAMPTZOID, NULL); jb.val.string.len = strlen(jb.val.string.val); break; case JSONBTYPE_JSONCAST: @@ -1845,3 +1845,27 @@ jsonb_object_agg_finalfn(PG_FUNCTION_ARGS) PG_RETURN_POINTER(out); } + +/* + * Extract scalar value from raw-scalar pseudo-array jsonb. + */ +JsonbValue * +JsonbExtractScalar(JsonbContainer *jbc, JsonbValue *res) +{ + JsonbIterator *it = JsonbIteratorInit(jbc); + JsonbIteratorToken tok PG_USED_FOR_ASSERTS_ONLY; + JsonbValue tmp; + + tok = JsonbIteratorNext(&it, &tmp, true); + Assert(tok == WJB_BEGIN_ARRAY); + Assert(tmp.val.array.nElems == 1 && tmp.val.array.rawScalar); + + tok = JsonbIteratorNext(&it, res, true); + Assert (tok == WJB_ELEM); + Assert(IsAJsonbScalar(res)); + + tok = JsonbIteratorNext(&it, &tmp, true); + Assert (tok == WJB_END_ARRAY); + + return res; +} diff --git a/src/backend/utils/adt/jsonb_util.c b/src/backend/utils/adt/jsonb_util.c index 2524584..5332793 100644 --- a/src/backend/utils/adt/jsonb_util.c +++ b/src/backend/utils/adt/jsonb_util.c @@ -15,8 +15,11 @@ #include "access/hash.h" #include "catalog/pg_collation.h" +#include "catalog/pg_type.h" #include "miscadmin.h" #include "utils/builtins.h" +#include "utils/datetime.h" +#include "utils/jsonapi.h" #include "utils/jsonb.h" #include "utils/memutils.h" #include "utils/varlena.h" @@ -241,6 +244,7 @@ compareJsonbContainers(JsonbContainer *a, JsonbContainer *b) res = (va.val.object.nPairs > vb.val.object.nPairs) ? 1 : -1; break; case jbvBinary: + case jbvDatetime: elog(ERROR, "unexpected jbvBinary value"); } } @@ -1741,11 +1745,28 @@ convertJsonbScalar(StringInfo buffer, JEntry *jentry, JsonbValue *scalarVal) JENTRY_ISBOOL_TRUE : JENTRY_ISBOOL_FALSE; break; + case jbvDatetime: + { + char buf[MAXDATELEN + 1]; + size_t len; + + JsonEncodeDateTime(buf, + scalarVal->val.datetime.value, + scalarVal->val.datetime.typid, + &scalarVal->val.datetime.tz); + len = strlen(buf); + appendToBuffer(buffer, buf, len); + + *jentry = JENTRY_ISSTRING | len; + } + break; + default: elog(ERROR, "invalid jsonb scalar type"); } } + /* * Compare two jbvString JsonbValue values, a and b. * diff --git a/src/backend/utils/adt/jsonpath.c b/src/backend/utils/adt/jsonpath.c new file mode 100644 index 0000000..ffcdf25 --- /dev/null +++ b/src/backend/utils/adt/jsonpath.c @@ -0,0 +1,869 @@ +/*------------------------------------------------------------------------- + * + * jsonpath.c + * + * Copyright (c) 2017, PostgreSQL Global Development Group + * + * IDENTIFICATION + * src/backend/utils/adt/jsonpath.c + * + *------------------------------------------------------------------------- + */ + +#include "postgres.h" +#include "funcapi.h" +#include "miscadmin.h" +#include "lib/stringinfo.h" +#include "utils/builtins.h" +#include "utils/json.h" +#include "utils/jsonpath.h" + +/*****************************INPUT/OUTPUT************************************/ + +/* + * Convert AST to flat jsonpath type representation + */ +static int +flattenJsonPathParseItem(StringInfo buf, JsonPathParseItem *item, + bool allowCurrent, bool insideArraySubscript) +{ + /* position from begining of jsonpath data */ + int32 pos = buf->len - JSONPATH_HDRSZ; + int32 chld, next; + + check_stack_depth(); + + appendStringInfoChar(buf, (char)(item->type)); + alignStringInfoInt(buf); + + next = (item->next) ? buf->len : 0; + + /* + * actual value will be recorded later, after next and + * children processing + */ + appendBinaryStringInfo(buf, (char*)&next /* fake value */, sizeof(next)); + + switch(item->type) + { + case jpiString: + case jpiVariable: + case jpiKey: + appendBinaryStringInfo(buf, (char*)&item->value.string.len, + sizeof(item->value.string.len)); + appendBinaryStringInfo(buf, item->value.string.val, item->value.string.len); + appendStringInfoChar(buf, '\0'); + break; + case jpiNumeric: + appendBinaryStringInfo(buf, (char*)item->value.numeric, + VARSIZE(item->value.numeric)); + break; + case jpiBool: + appendBinaryStringInfo(buf, (char*)&item->value.boolean, + sizeof(item->value.boolean)); + break; + case jpiAnd: + case jpiOr: + case jpiEqual: + case jpiNotEqual: + case jpiLess: + case jpiGreater: + case jpiLessOrEqual: + case jpiGreaterOrEqual: + case jpiAdd: + case jpiSub: + case jpiMul: + case jpiDiv: + case jpiMod: + case jpiStartsWith: + case jpiDatetime: + { + int32 left, right; + + left = buf->len; + + /* + * first, reserve place for left/right arg's positions, then + * record both args and sets actual position in reserved places + */ + appendBinaryStringInfo(buf, (char*)&left /* fake value */, sizeof(left)); + right = buf->len; + appendBinaryStringInfo(buf, (char*)&right /* fake value */, sizeof(right)); + + chld = !item->value.args.left ? 0 : + flattenJsonPathParseItem(buf, item->value.args.left, + allowCurrent, + insideArraySubscript); + *(int32*)(buf->data + left) = chld; + + chld = !item->value.args.right ? 0 : + flattenJsonPathParseItem(buf, item->value.args.right, + allowCurrent, + insideArraySubscript); + *(int32*)(buf->data + right) = chld; + } + break; + case jpiLikeRegex: + { + int32 offs; + + appendBinaryStringInfo(buf, + (char *) &item->value.like_regex.flags, + sizeof(item->value.like_regex.flags)); + offs = buf->len; + appendBinaryStringInfo(buf, (char *) &offs /* fake value */, sizeof(offs)); + + appendBinaryStringInfo(buf, + (char *) &item->value.like_regex.patternlen, + sizeof(item->value.like_regex.patternlen)); + appendBinaryStringInfo(buf, item->value.like_regex.pattern, + item->value.like_regex.patternlen); + appendStringInfoChar(buf, '\0'); + + chld = flattenJsonPathParseItem(buf, item->value.like_regex.expr, + allowCurrent, + insideArraySubscript); + *(int32 *)(buf->data + offs) = chld; + } + break; + case jpiFilter: + case jpiIsUnknown: + case jpiNot: + case jpiPlus: + case jpiMinus: + case jpiExists: + { + int32 arg; + + arg = buf->len; + appendBinaryStringInfo(buf, (char*)&arg /* fake value */, sizeof(arg)); + + chld = flattenJsonPathParseItem(buf, item->value.arg, + item->type == jpiFilter || + allowCurrent, + insideArraySubscript); + *(int32*)(buf->data + arg) = chld; + } + break; + case jpiNull: + break; + case jpiRoot: + break; + case jpiAnyArray: + case jpiAnyKey: + break; + case jpiCurrent: + if (!allowCurrent) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("@ is not allowed in root expressions"))); + break; + case jpiLast: + if (!insideArraySubscript) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("LAST is allowed only in array subscripts"))); + break; + case jpiIndexArray: + { + int32 nelems = item->value.array.nelems; + int offset; + int i; + + appendBinaryStringInfo(buf, (char *) &nelems, sizeof(nelems)); + + offset = buf->len; + + appendStringInfoSpaces(buf, sizeof(int32) * 2 * nelems); + + for (i = 0; i < nelems; i++) + { + int32 *ppos; + int32 topos; + int32 frompos = + flattenJsonPathParseItem(buf, + item->value.array.elems[i].from, + true, true); + + if (item->value.array.elems[i].to) + topos = flattenJsonPathParseItem(buf, + item->value.array.elems[i].to, + true, true); + else + topos = 0; + + ppos = (int32 *) &buf->data[offset + i * 2 * sizeof(int32)]; + + ppos[0] = frompos; + ppos[1] = topos; + } + } + break; + case jpiAny: + appendBinaryStringInfo(buf, + (char*)&item->value.anybounds.first, + sizeof(item->value.anybounds.first)); + appendBinaryStringInfo(buf, + (char*)&item->value.anybounds.last, + sizeof(item->value.anybounds.last)); + break; + case jpiType: + case jpiSize: + case jpiAbs: + case jpiFloor: + case jpiCeiling: + case jpiDouble: + case jpiKeyValue: + break; + default: + elog(ERROR, "Unknown jsonpath item type: %d", item->type); + } + + if (item->next) + *(int32*)(buf->data + next) = + flattenJsonPathParseItem(buf, item->next, allowCurrent, + insideArraySubscript); + + return pos; +} + +Datum +jsonpath_in(PG_FUNCTION_ARGS) +{ + char *in = PG_GETARG_CSTRING(0); + int32 len = strlen(in); + JsonPathParseResult *jsonpath = parsejsonpath(in, len); + JsonPath *res; + StringInfoData buf; + + initStringInfo(&buf); + enlargeStringInfo(&buf, 4 * len /* estimation */); + + appendStringInfoSpaces(&buf, JSONPATH_HDRSZ); + + if (!jsonpath) + ereport(ERROR, + (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION), + errmsg("invalid input syntax for jsonpath: \"%s\"", in))); + + flattenJsonPathParseItem(&buf, jsonpath->expr, false, false); + + res = (JsonPath*)buf.data; + SET_VARSIZE(res, buf.len); + res->header = JSONPATH_VERSION; + if (jsonpath->lax) + res->header |= JSONPATH_LAX; + + PG_RETURN_JSONPATH_P(res); +} + +static void +printOperation(StringInfo buf, JsonPathItemType type) +{ + switch(type) + { + case jpiAnd: + appendBinaryStringInfo(buf, " && ", 4); break; + case jpiOr: + appendBinaryStringInfo(buf, " || ", 4); break; + case jpiEqual: + appendBinaryStringInfo(buf, " == ", 4); break; + case jpiNotEqual: + appendBinaryStringInfo(buf, " != ", 4); break; + case jpiLess: + appendBinaryStringInfo(buf, " < ", 3); break; + case jpiGreater: + appendBinaryStringInfo(buf, " > ", 3); break; + case jpiLessOrEqual: + appendBinaryStringInfo(buf, " <= ", 4); break; + case jpiGreaterOrEqual: + appendBinaryStringInfo(buf, " >= ", 4); break; + case jpiAdd: + appendBinaryStringInfo(buf, " + ", 3); break; + case jpiSub: + appendBinaryStringInfo(buf, " - ", 3); break; + case jpiMul: + appendBinaryStringInfo(buf, " * ", 3); break; + case jpiDiv: + appendBinaryStringInfo(buf, " / ", 3); break; + case jpiMod: + appendBinaryStringInfo(buf, " % ", 3); break; + case jpiStartsWith: + appendBinaryStringInfo(buf, " starts with ", 13); break; + default: + elog(ERROR, "Unknown jsonpath item type: %d", type); + } +} + +static int +operationPriority(JsonPathItemType op) +{ + switch (op) + { + case jpiOr: + return 0; + case jpiAnd: + return 1; + case jpiEqual: + case jpiNotEqual: + case jpiLess: + case jpiGreater: + case jpiLessOrEqual: + case jpiGreaterOrEqual: + case jpiStartsWith: + return 2; + case jpiAdd: + case jpiSub: + return 3; + case jpiMul: + case jpiDiv: + case jpiMod: + return 4; + case jpiPlus: + case jpiMinus: + return 5; + default: + return 6; + } +} + +static void +printJsonPathItem(StringInfo buf, JsonPathItem *v, bool inKey, bool printBracketes) +{ + JsonPathItem elem; + int i; + + check_stack_depth(); + + switch(v->type) + { + case jpiNull: + appendStringInfoString(buf, "null"); + break; + case jpiKey: + if (inKey) + appendStringInfoChar(buf, '.'); + escape_json(buf, jspGetString(v, NULL)); + break; + case jpiString: + escape_json(buf, jspGetString(v, NULL)); + break; + case jpiVariable: + appendStringInfoChar(buf, '$'); + escape_json(buf, jspGetString(v, NULL)); + break; + case jpiNumeric: + appendStringInfoString(buf, + DatumGetCString(DirectFunctionCall1(numeric_out, + PointerGetDatum(jspGetNumeric(v))))); + break; + case jpiBool: + if (jspGetBool(v)) + appendBinaryStringInfo(buf, "true", 4); + else + appendBinaryStringInfo(buf, "false", 5); + break; + case jpiAnd: + case jpiOr: + case jpiEqual: + case jpiNotEqual: + case jpiLess: + case jpiGreater: + case jpiLessOrEqual: + case jpiGreaterOrEqual: + case jpiAdd: + case jpiSub: + case jpiMul: + case jpiDiv: + case jpiMod: + case jpiStartsWith: + if (printBracketes) + appendStringInfoChar(buf, '('); + jspGetLeftArg(v, &elem); + printJsonPathItem(buf, &elem, false, + operationPriority(elem.type) <= + operationPriority(v->type)); + printOperation(buf, v->type); + jspGetRightArg(v, &elem); + printJsonPathItem(buf, &elem, false, + operationPriority(elem.type) <= + operationPriority(v->type)); + if (printBracketes) + appendStringInfoChar(buf, ')'); + break; + case jpiLikeRegex: + if (printBracketes) + appendStringInfoChar(buf, '('); + + jspInitByBuffer(&elem, v->base, v->content.like_regex.expr); + printJsonPathItem(buf, &elem, false, + operationPriority(elem.type) <= + operationPriority(v->type)); + + appendBinaryStringInfo(buf, " like_regex ", 12); + + escape_json(buf, v->content.like_regex.pattern); + + if (v->content.like_regex.flags) + { + appendBinaryStringInfo(buf, " flag \"", 7); + + if (v->content.like_regex.flags & JSP_REGEX_ICASE) + appendStringInfoChar(buf, 'i'); + if (v->content.like_regex.flags & JSP_REGEX_SLINE) + appendStringInfoChar(buf, 's'); + if (v->content.like_regex.flags & JSP_REGEX_MLINE) + appendStringInfoChar(buf, 'm'); + if (v->content.like_regex.flags & JSP_REGEX_WSPACE) + appendStringInfoChar(buf, 'x'); + + appendStringInfoChar(buf, '"'); + } + + if (printBracketes) + appendStringInfoChar(buf, ')'); + break; + case jpiPlus: + case jpiMinus: + if (printBracketes) + appendStringInfoChar(buf, '('); + appendStringInfoChar(buf, v->type == jpiPlus ? '+' : '-'); + jspGetArg(v, &elem); + printJsonPathItem(buf, &elem, false, + operationPriority(elem.type) <= + operationPriority(v->type)); + if (printBracketes) + appendStringInfoChar(buf, ')'); + break; + case jpiFilter: + appendBinaryStringInfo(buf, "?(", 2); + jspGetArg(v, &elem); + printJsonPathItem(buf, &elem, false, false); + appendStringInfoChar(buf, ')'); + break; + case jpiNot: + appendBinaryStringInfo(buf, "!(", 2); + jspGetArg(v, &elem); + printJsonPathItem(buf, &elem, false, false); + appendStringInfoChar(buf, ')'); + break; + case jpiIsUnknown: + appendStringInfoChar(buf, '('); + jspGetArg(v, &elem); + printJsonPathItem(buf, &elem, false, false); + appendBinaryStringInfo(buf, ") is unknown", 12); + break; + case jpiExists: + appendBinaryStringInfo(buf,"exists (", 8); + jspGetArg(v, &elem); + printJsonPathItem(buf, &elem, false, false); + appendStringInfoChar(buf, ')'); + break; + case jpiCurrent: + Assert(!inKey); + appendStringInfoChar(buf, '@'); + break; + case jpiRoot: + Assert(!inKey); + appendStringInfoChar(buf, '$'); + break; + case jpiLast: + appendBinaryStringInfo(buf, "last", 4); + break; + case jpiAnyArray: + appendBinaryStringInfo(buf, "[*]", 3); + break; + case jpiAnyKey: + if (inKey) + appendStringInfoChar(buf, '.'); + appendStringInfoChar(buf, '*'); + break; + case jpiIndexArray: + appendStringInfoChar(buf, '['); + for (i = 0; i < v->content.array.nelems; i++) + { + JsonPathItem from; + JsonPathItem to; + bool range = jspGetArraySubscript(v, &from, &to, i); + + if (i) + appendStringInfoChar(buf, ','); + + printJsonPathItem(buf, &from, false, false); + + if (range) + { + appendBinaryStringInfo(buf, " to ", 4); + printJsonPathItem(buf, &to, false, false); + } + } + appendStringInfoChar(buf, ']'); + break; + case jpiAny: + if (inKey) + appendStringInfoChar(buf, '.'); + + if (v->content.anybounds.first == 0 && + v->content.anybounds.last == PG_UINT32_MAX) + appendBinaryStringInfo(buf, "**", 2); + else if (v->content.anybounds.first == 0) + appendStringInfo(buf, "**{,%u}", v->content.anybounds.last); + else if (v->content.anybounds.last == PG_UINT32_MAX) + appendStringInfo(buf, "**{%u,}", v->content.anybounds.first); + else if (v->content.anybounds.first == v->content.anybounds.last) + appendStringInfo(buf, "**{%u}", v->content.anybounds.first); + else + appendStringInfo(buf, "**{%u,%u}", v->content.anybounds.first, + v->content.anybounds.last); + break; + case jpiType: + appendBinaryStringInfo(buf, ".type()", 7); + break; + case jpiSize: + appendBinaryStringInfo(buf, ".size()", 7); + break; + case jpiAbs: + appendBinaryStringInfo(buf, ".abs()", 6); + break; + case jpiFloor: + appendBinaryStringInfo(buf, ".floor()", 8); + break; + case jpiCeiling: + appendBinaryStringInfo(buf, ".ceiling()", 10); + break; + case jpiDouble: + appendBinaryStringInfo(buf, ".double()", 9); + break; + case jpiDatetime: + appendBinaryStringInfo(buf, ".datetime(", 10); + if (v->content.args.left) + { + jspGetLeftArg(v, &elem); + printJsonPathItem(buf, &elem, false, false); + + if (v->content.args.right) + { + appendBinaryStringInfo(buf, ", ", 2); + jspGetRightArg(v, &elem); + printJsonPathItem(buf, &elem, false, false); + } + } + appendStringInfoChar(buf, ')'); + break; + case jpiKeyValue: + appendBinaryStringInfo(buf, ".keyvalue()", 11); + break; + default: + elog(ERROR, "Unknown jsonpath item type: %d", v->type); + } + + if (jspGetNext(v, &elem)) + printJsonPathItem(buf, &elem, true, true); +} + +Datum +jsonpath_out(PG_FUNCTION_ARGS) +{ + JsonPath *in = PG_GETARG_JSONPATH_P(0); + StringInfoData buf; + JsonPathItem v; + + initStringInfo(&buf); + enlargeStringInfo(&buf, VARSIZE(in) /* estimation */); + + if (!(in->header & JSONPATH_LAX)) + appendBinaryStringInfo(&buf, "strict ", 7); + + jspInit(&v, in); + printJsonPathItem(&buf, &v, false, true); + + PG_RETURN_CSTRING(buf.data); +} + +/********************Support functions for JsonPath****************************/ + +/* + * Support macroses to read stored values + */ + +#define read_byte(v, b, p) do { \ + (v) = *(uint8*)((b) + (p)); \ + (p) += 1; \ +} while(0) \ + +#define read_int32(v, b, p) do { \ + (v) = *(uint32*)((b) + (p)); \ + (p) += sizeof(int32); \ +} while(0) \ + +#define read_int32_n(v, b, p, n) do { \ + (v) = (void *)((b) + (p)); \ + (p) += sizeof(int32) * (n); \ +} while(0) \ + +/* + * Read root node and fill root node representation + */ +void +jspInit(JsonPathItem *v, JsonPath *js) +{ + Assert((js->header & ~JSONPATH_LAX) == JSONPATH_VERSION); + jspInitByBuffer(v, js->data, 0); +} + +/* + * Read node from buffer and fill its representation + */ +void +jspInitByBuffer(JsonPathItem *v, char *base, int32 pos) +{ + v->base = base; + + read_byte(v->type, base, pos); + + switch(INTALIGN(pos) - pos) + { + case 3: pos++; + case 2: pos++; + case 1: pos++; + default: break; + } + + read_int32(v->nextPos, base, pos); + + switch(v->type) + { + case jpiNull: + case jpiRoot: + case jpiCurrent: + case jpiAnyArray: + case jpiAnyKey: + case jpiType: + case jpiSize: + case jpiAbs: + case jpiFloor: + case jpiCeiling: + case jpiDouble: + case jpiKeyValue: + case jpiLast: + break; + case jpiKey: + case jpiString: + case jpiVariable: + read_int32(v->content.value.datalen, base, pos); + /* follow next */ + case jpiNumeric: + case jpiBool: + v->content.value.data = base + pos; + break; + case jpiAnd: + case jpiOr: + case jpiAdd: + case jpiSub: + case jpiMul: + case jpiDiv: + case jpiMod: + case jpiEqual: + case jpiNotEqual: + case jpiLess: + case jpiGreater: + case jpiLessOrEqual: + case jpiGreaterOrEqual: + case jpiStartsWith: + case jpiDatetime: + read_int32(v->content.args.left, base, pos); + read_int32(v->content.args.right, base, pos); + break; + case jpiLikeRegex: + read_int32(v->content.like_regex.flags, base, pos); + read_int32(v->content.like_regex.expr, base, pos); + read_int32(v->content.like_regex.patternlen, base, pos); + v->content.like_regex.pattern = base + pos; + break; + case jpiNot: + case jpiExists: + case jpiIsUnknown: + case jpiPlus: + case jpiMinus: + case jpiFilter: + read_int32(v->content.arg, base, pos); + break; + case jpiIndexArray: + read_int32(v->content.array.nelems, base, pos); + read_int32_n(v->content.array.elems, base, pos, + v->content.array.nelems * 2); + break; + case jpiAny: + read_int32(v->content.anybounds.first, base, pos); + read_int32(v->content.anybounds.last, base, pos); + break; + default: + elog(ERROR, "Unknown jsonpath item type: %d", v->type); + } +} + +void +jspGetArg(JsonPathItem *v, JsonPathItem *a) +{ + Assert( + v->type == jpiFilter || + v->type == jpiNot || + v->type == jpiIsUnknown || + v->type == jpiExists || + v->type == jpiPlus || + v->type == jpiMinus + ); + + jspInitByBuffer(a, v->base, v->content.arg); +} + +bool +jspGetNext(JsonPathItem *v, JsonPathItem *a) +{ + if (jspHasNext(v)) + { + Assert( + v->type == jpiString || + v->type == jpiNumeric || + v->type == jpiBool || + v->type == jpiNull || + v->type == jpiKey || + v->type == jpiAny || + v->type == jpiAnyArray || + v->type == jpiAnyKey || + v->type == jpiIndexArray || + v->type == jpiFilter || + v->type == jpiCurrent || + v->type == jpiExists || + v->type == jpiRoot || + v->type == jpiVariable || + v->type == jpiLast || + v->type == jpiAdd || + v->type == jpiSub || + v->type == jpiMul || + v->type == jpiDiv || + v->type == jpiMod || + v->type == jpiPlus || + v->type == jpiMinus || + v->type == jpiEqual || + v->type == jpiNotEqual || + v->type == jpiGreater || + v->type == jpiGreaterOrEqual || + v->type == jpiLess || + v->type == jpiLessOrEqual || + v->type == jpiAnd || + v->type == jpiOr || + v->type == jpiNot || + v->type == jpiIsUnknown || + v->type == jpiType || + v->type == jpiSize || + v->type == jpiAbs || + v->type == jpiFloor || + v->type == jpiCeiling || + v->type == jpiDouble || + v->type == jpiDatetime || + v->type == jpiKeyValue || + v->type == jpiStartsWith + ); + + if (a) + jspInitByBuffer(a, v->base, v->nextPos); + return true; + } + + return false; +} + +void +jspGetLeftArg(JsonPathItem *v, JsonPathItem *a) +{ + Assert( + v->type == jpiAnd || + v->type == jpiOr || + v->type == jpiEqual || + v->type == jpiNotEqual || + v->type == jpiLess || + v->type == jpiGreater || + v->type == jpiLessOrEqual || + v->type == jpiGreaterOrEqual || + v->type == jpiAdd || + v->type == jpiSub || + v->type == jpiMul || + v->type == jpiDiv || + v->type == jpiMod || + v->type == jpiDatetime || + v->type == jpiStartsWith + ); + + jspInitByBuffer(a, v->base, v->content.args.left); +} + +void +jspGetRightArg(JsonPathItem *v, JsonPathItem *a) +{ + Assert( + v->type == jpiAnd || + v->type == jpiOr || + v->type == jpiEqual || + v->type == jpiNotEqual || + v->type == jpiLess || + v->type == jpiGreater || + v->type == jpiLessOrEqual || + v->type == jpiGreaterOrEqual || + v->type == jpiAdd || + v->type == jpiSub || + v->type == jpiMul || + v->type == jpiDiv || + v->type == jpiMod || + v->type == jpiDatetime || + v->type == jpiStartsWith + ); + + jspInitByBuffer(a, v->base, v->content.args.right); +} + +bool +jspGetBool(JsonPathItem *v) +{ + Assert(v->type == jpiBool); + + return (bool)*v->content.value.data; +} + +Numeric +jspGetNumeric(JsonPathItem *v) +{ + Assert(v->type == jpiNumeric); + + return (Numeric)v->content.value.data; +} + +char* +jspGetString(JsonPathItem *v, int32 *len) +{ + Assert( + v->type == jpiKey || + v->type == jpiString || + v->type == jpiVariable + ); + + if (len) + *len = v->content.value.datalen; + return v->content.value.data; +} + +bool +jspGetArraySubscript(JsonPathItem *v, JsonPathItem *from, JsonPathItem *to, + int i) +{ + Assert(v->type == jpiIndexArray); + + jspInitByBuffer(from, v->base, v->content.array.elems[i].from); + + if (!v->content.array.elems[i].to) + return false; + + jspInitByBuffer(to, v->base, v->content.array.elems[i].to); + + return true; +} diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c new file mode 100644 index 0000000..233bb42 --- /dev/null +++ b/src/backend/utils/adt/jsonpath_exec.c @@ -0,0 +1,2693 @@ +/*------------------------------------------------------------------------- + * + * jsonpath_exec.c + * + * Copyright (c) 2017, PostgreSQL Global Development Group + * + * IDENTIFICATION + * src/backend/utils/adt/jsonpath_exec.c + * + *------------------------------------------------------------------------- + */ + +#include "postgres.h" +#include "funcapi.h" +#include "miscadmin.h" +#include "catalog/pg_collation.h" +#include "catalog/pg_type.h" +#include "lib/stringinfo.h" +#include "regex/regex.h" +#include "utils/builtins.h" +#include "utils/formatting.h" +#include "utils/json.h" +#include "utils/jsonpath.h" +#include "utils/varlena.h" + +typedef struct JsonPathExecContext +{ + List *vars; + bool lax; + JsonbValue *root; /* for $ evaluation */ + int innermostArraySize; /* for LAST array index evaluation */ +} JsonPathExecContext; + +typedef struct JsonValueListIterator +{ + ListCell *lcell; +} JsonValueListIterator; + +#define JsonValueListIteratorEnd ((ListCell *) -1) + +static inline JsonPathExecResult recursiveExecute(JsonPathExecContext *cxt, + JsonPathItem *jsp, JsonbValue *jb, + JsonValueList *found); + +static inline JsonPathExecResult recursiveExecuteBool(JsonPathExecContext *cxt, + JsonPathItem *jsp, JsonbValue *jb); + +static inline JsonPathExecResult recursiveExecuteUnwrap(JsonPathExecContext *cxt, + JsonPathItem *jsp, JsonbValue *jb, JsonValueList *found); + +static inline JsonbValue *wrapItemsInArray(const JsonValueList *items); + + +static inline void +JsonValueListAppend(JsonValueList *jvl, JsonbValue *jbv) +{ + if (jvl->singleton) + { + jvl->list = list_make2(jvl->singleton, jbv); + jvl->singleton = NULL; + } + else if (!jvl->list) + jvl->singleton = jbv; + else + jvl->list = lappend(jvl->list, jbv); +} + +static inline void +JsonValueListConcat(JsonValueList *jvl1, JsonValueList jvl2) +{ + if (jvl1->singleton) + { + if (jvl2.singleton) + jvl1->list = list_make2(jvl1->singleton, jvl2.singleton); + else + jvl1->list = lcons(jvl1->singleton, jvl2.list); + + jvl1->singleton = NULL; + } + else if (jvl2.singleton) + { + if (jvl1->list) + jvl1->list = lappend(jvl1->list, jvl2.singleton); + else + jvl1->singleton = jvl2.singleton; + } + else if (jvl1->list) + jvl1->list = list_concat(jvl1->list, jvl2.list); + else + jvl1->list = jvl2.list; +} + +static inline int +JsonValueListLength(const JsonValueList *jvl) +{ + return jvl->singleton ? 1 : list_length(jvl->list); +} + +static inline bool +JsonValueListIsEmpty(JsonValueList *jvl) +{ + return !jvl->singleton && list_length(jvl->list) <= 0; +} + +static inline JsonbValue * +JsonValueListHead(JsonValueList *jvl) +{ + return jvl->singleton ? jvl->singleton : linitial(jvl->list); +} + +static inline void +JsonValueListClear(JsonValueList *jvl) +{ + jvl->singleton = NULL; + jvl->list = NIL; +} + +static inline List * +JsonValueListGetList(JsonValueList *jvl) +{ + if (jvl->singleton) + return list_make1(jvl->singleton); + + return jvl->list; +} + +/* + * Get the next item from the sequence advancing iterator. + */ +static inline JsonbValue * +JsonValueListNext(const JsonValueList *jvl, JsonValueListIterator *it) +{ + if (it->lcell == JsonValueListIteratorEnd) + return NULL; + + if (it->lcell) + it->lcell = lnext(it->lcell); + else + { + if (jvl->singleton) + { + it->lcell = JsonValueListIteratorEnd; + return jvl->singleton; + } + + it->lcell = list_head(jvl->list); + } + + if (!it->lcell) + { + it->lcell = JsonValueListIteratorEnd; + return NULL; + } + + return lfirst(it->lcell); +} + +/* + * Initialize a binary JsonbValue with the given jsonb container. + */ +static inline JsonbValue * +JsonbInitBinary(JsonbValue *jbv, Jsonb *jb) +{ + jbv->type = jbvBinary; + jbv->val.binary.data = &jb->root; + jbv->val.binary.len = VARSIZE_ANY_EXHDR(jb); + + return jbv; +} + +/* + * Transform a JsonbValue into a binary JsonbValue by encoding it to a + * binary jsonb container. + */ +static inline JsonbValue * +JsonbWrapInBinary(JsonbValue *jbv, JsonbValue *out) +{ + Jsonb *jb = JsonbValueToJsonb(jbv); + + if (!out) + out = palloc(sizeof(*out)); + + return JsonbInitBinary(out, jb); +} + +/********************Execute functions for JsonPath***************************/ + +/* + * Find value of jsonpath variable in a list of passing params + */ +static void +computeJsonPathVariable(JsonPathItem *variable, List *vars, JsonbValue *value) +{ + ListCell *cell; + JsonPathVariable *var = NULL; + bool isNull; + Datum computedValue; + char *varName; + int varNameLength; + + Assert(variable->type == jpiVariable); + varName = jspGetString(variable, &varNameLength); + + foreach(cell, vars) + { + var = (JsonPathVariable*)lfirst(cell); + + if (varNameLength == VARSIZE_ANY_EXHDR(var->varName) && + !strncmp(varName, VARDATA_ANY(var->varName), varNameLength)) + break; + + var = NULL; + } + + if (var == NULL) + ereport(ERROR, + (errcode(ERRCODE_NO_DATA_FOUND), + errmsg("could not find '%s' passed variable", + pnstrdup(varName, varNameLength)))); + + computedValue = var->cb(var->cb_arg, &isNull); + + if (isNull) + { + value->type = jbvNull; + return; + } + + switch(var->typid) + { + case BOOLOID: + value->type = jbvBool; + value->val.boolean = DatumGetBool(computedValue); + break; + case NUMERICOID: + value->type = jbvNumeric; + value->val.numeric = DatumGetNumeric(computedValue); + break; + break; + case INT2OID: + value->type = jbvNumeric; + value->val.numeric = DatumGetNumeric(DirectFunctionCall1( + int2_numeric, computedValue)); + break; + case INT4OID: + value->type = jbvNumeric; + value->val.numeric = DatumGetNumeric(DirectFunctionCall1( + int4_numeric, computedValue)); + break; + case INT8OID: + value->type = jbvNumeric; + value->val.numeric = DatumGetNumeric(DirectFunctionCall1( + int8_numeric, computedValue)); + break; + case FLOAT4OID: + value->type = jbvNumeric; + value->val.numeric = DatumGetNumeric(DirectFunctionCall1( + float4_numeric, computedValue)); + break; + case FLOAT8OID: + value->type = jbvNumeric; + value->val.numeric = DatumGetNumeric(DirectFunctionCall1( + float4_numeric, computedValue)); + break; + case TEXTOID: + case VARCHAROID: + value->type = jbvString; + value->val.string.val = VARDATA_ANY(computedValue); + value->val.string.len = VARSIZE_ANY_EXHDR(computedValue); + break; + case DATEOID: + case TIMEOID: + case TIMETZOID: + case TIMESTAMPOID: + case TIMESTAMPTZOID: + value->type = jbvDatetime; + value->val.datetime.typid = var->typid; + value->val.datetime.typmod = var->typmod; + value->val.datetime.tz = 0; + value->val.datetime.value = computedValue; + break; + case JSONBOID: + { + Jsonb *jb = DatumGetJsonbP(computedValue); + + if (JB_ROOT_IS_SCALAR(jb)) + JsonbExtractScalar(&jb->root, value); + else + JsonbInitBinary(value, jb); + } + break; + case (Oid) -1: /* raw JsonbValue */ + *value = *(JsonbValue *) DatumGetPointer(computedValue); + break; + default: + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("only bool, numeric and text types could be casted to supported jsonpath types"))); + } +} + +/* + * Convert jsonpath's scalar or variable node to actual jsonb value + */ +static void +computeJsonPathItem(JsonPathExecContext *cxt, JsonPathItem *item, JsonbValue *value) +{ + switch(item->type) + { + case jpiNull: + value->type = jbvNull; + break; + case jpiBool: + value->type = jbvBool; + value->val.boolean = jspGetBool(item); + break; + case jpiNumeric: + value->type = jbvNumeric; + value->val.numeric = jspGetNumeric(item); + break; + case jpiString: + value->type = jbvString; + value->val.string.val = jspGetString(item, &value->val.string.len); + break; + case jpiVariable: + computeJsonPathVariable(item, cxt->vars, value); + break; + default: + elog(ERROR, "Wrong type"); + } +} + + +/* + * Returns jbv* type of of JsonbValue. Note, it never returns + * jbvBinary as is - jbvBinary is used as mark of store naked + * scalar value. To improve readability it defines jbvScalar + * as alias to jbvBinary + */ +#define jbvScalar jbvBinary +static inline int +JsonbType(JsonbValue *jb) +{ + int type = jb->type; + + if (jb->type == jbvBinary) + { + JsonbContainer *jbc = jb->val.binary.data; + + if (JsonContainerIsScalar(jbc)) + type = jbvScalar; + else if (JsonContainerIsObject(jbc)) + type = jbvObject; + else if (JsonContainerIsArray(jbc)) + type = jbvArray; + else + elog(ERROR, "Unknown container type: 0x%08x", jbc->header); + } + + return type; +} + +/* + * Get the type name of a SQL/JSON item. + */ +static const char * +JsonbTypeName(JsonbValue *jb) +{ + JsonbValue jbvbuf; + + if (jb->type == jbvBinary) + { + JsonbContainer *jbc = jb->val.binary.data; + + if (JsonContainerIsScalar(jbc)) + jb = JsonbExtractScalar(jbc, &jbvbuf); + else if (JsonContainerIsArray(jbc)) + return "array"; + else if (JsonContainerIsObject(jbc)) + return "object"; + else + elog(ERROR, "Unknown container type: 0x%08x", jbc->header); + } + + switch (jb->type) + { + case jbvObject: + return "object"; + case jbvArray: + return "array"; + case jbvNumeric: + return "number"; + case jbvString: + return "string"; + case jbvBool: + return "boolean"; + case jbvNull: + return "null"; + case jbvDatetime: + switch (jb->val.datetime.typid) + { + case DATEOID: + return "date"; + case TIMEOID: + return "time without time zone"; + case TIMETZOID: + return "time with time zone"; + case TIMESTAMPOID: + return "timestamp without time zone"; + case TIMESTAMPTZOID: + return "timestamp with time zone"; + default: + elog(ERROR, "unknown jsonb value datetime type oid %d", + jb->val.datetime.typid); + } + return "unknown"; + default: + elog(ERROR, "Unknown jsonb value type: %d", jb->type); + return "unknown"; + } +} + +/* + * Returns the size of an array item, or -1 if item is not an array. + */ +static int +JsonbArraySize(JsonbValue *jb) +{ + if (jb->type == jbvArray) + return jb->val.array.nElems; + + if (jb->type == jbvBinary) + { + JsonbContainer *jbc = jb->val.binary.data; + + if (JsonContainerIsArray(jbc) && !JsonContainerIsScalar(jbc)) + return JsonContainerSize(jbc); + } + + return -1; +} + +/* + * Compare two numerics. + */ +static int +compareNumeric(Numeric a, Numeric b) +{ + return DatumGetInt32( + DirectFunctionCall2( + numeric_cmp, + PointerGetDatum(a), + PointerGetDatum(b) + ) + ); +} + +/* + * Cross-type comparison of two datetime SQL/JSON items. If items are + * uncomparable, 'error' flag is set. + */ +static int +compareDatetime(Datum val1, Oid typid1, Datum val2, Oid typid2, bool *error) +{ + PGFunction cmpfunc = NULL; + + switch (typid1) + { + case DATEOID: + switch (typid2) + { + case DATEOID: + cmpfunc = date_cmp; + break; + case TIMESTAMPOID: + cmpfunc = date_cmp_timestamp; + break; + case TIMESTAMPTZOID: + cmpfunc = date_cmp_timestamptz; + break; + case TIMEOID: + case TIMETZOID: + *error = true; + return 0; + } + break; + + case TIMEOID: + switch (typid2) + { + case TIMEOID: + cmpfunc = time_cmp; + break; + case TIMETZOID: + val1 = DirectFunctionCall1(time_timetz, val1); + cmpfunc = timetz_cmp; + break; + case DATEOID: + case TIMESTAMPOID: + case TIMESTAMPTZOID: + *error = true; + return 0; + } + break; + + case TIMETZOID: + switch (typid2) + { + case TIMEOID: + val2 = DirectFunctionCall1(time_timetz, val2); + cmpfunc = timetz_cmp; + break; + case TIMETZOID: + cmpfunc = timetz_cmp; + break; + case DATEOID: + case TIMESTAMPOID: + case TIMESTAMPTZOID: + *error = true; + return 0; + } + break; + + case TIMESTAMPOID: + switch (typid2) + { + case DATEOID: + cmpfunc = timestamp_cmp_date; + break; + case TIMESTAMPOID: + cmpfunc = timestamp_cmp; + break; + case TIMESTAMPTZOID: + cmpfunc = timestamp_cmp_timestamptz; + break; + case TIMEOID: + case TIMETZOID: + *error = true; + return 0; + } + break; + + case TIMESTAMPTZOID: + switch (typid2) + { + case DATEOID: + cmpfunc = timestamptz_cmp_date; + break; + case TIMESTAMPOID: + cmpfunc = timestamptz_cmp_timestamp; + break; + case TIMESTAMPTZOID: + cmpfunc = timestamp_cmp; + break; + case TIMEOID: + case TIMETZOID: + *error = true; + return 0; + } + break; + + default: + elog(ERROR, "unknown SQL/JSON datetime type oid: %d", typid1); + } + + if (!cmpfunc) + elog(ERROR, "unknown SQL/JSON datetime type oid: %d", typid2); + + *error = false; + + return DatumGetInt32(DirectFunctionCall2(cmpfunc, val1, val2)); +} + +/* + * Check equality of two SLQ/JSON items of the same type. + */ +static inline JsonPathExecResult +checkEquality(JsonbValue *jb1, JsonbValue *jb2, bool not) +{ + bool eq = false; + + if (jb1->type != jb2->type) + { + if (jb1->type == jbvNull || jb2->type == jbvNull) + return not ? jperOk : jperNotFound; + + return jperError; + } + + switch (jb1->type) + { + case jbvNull: + eq = true; + break; + case jbvString: + eq = (jb1->val.string.len == jb2->val.string.len && + memcmp(jb2->val.string.val, jb1->val.string.val, + jb1->val.string.len) == 0); + break; + case jbvBool: + eq = (jb2->val.boolean == jb1->val.boolean); + break; + case jbvNumeric: + eq = (compareNumeric(jb1->val.numeric, jb2->val.numeric) == 0); + break; + case jbvDatetime: + { + bool error; + + eq = compareDatetime(jb1->val.datetime.value, + jb1->val.datetime.typid, + jb2->val.datetime.value, + jb2->val.datetime.typid, + &error) == 0; + + if (error) + return jperError; + + break; + } + + case jbvBinary: + case jbvObject: + case jbvArray: + return jperError; + + default: + elog(ERROR, "Unknown jsonb value type %d", jb1->type); + } + + return (not ^ eq) ? jperOk : jperNotFound; +} + +/* + * Compare two SLQ/JSON items using comparison operation 'op'. + */ +static JsonPathExecResult +makeCompare(int32 op, JsonbValue *jb1, JsonbValue *jb2) +{ + int cmp; + bool res; + + if (jb1->type != jb2->type) + { + if (jb1->type != jbvNull && jb2->type != jbvNull) + /* non-null items of different types are not order-comparable */ + return jperError; + + if (jb1->type != jbvNull || jb2->type != jbvNull) + /* comparison of nulls to non-nulls returns always false */ + return jperNotFound; + + /* both values are JSON nulls */ + } + + switch (jb1->type) + { + case jbvNull: + cmp = 0; + break; + case jbvNumeric: + cmp = compareNumeric(jb1->val.numeric, jb2->val.numeric); + break; + case jbvString: + cmp = varstr_cmp(jb1->val.string.val, jb1->val.string.len, + jb2->val.string.val, jb2->val.string.len, + DEFAULT_COLLATION_OID); + break; + case jbvDatetime: + { + bool error; + + cmp = compareDatetime(jb1->val.datetime.value, + jb1->val.datetime.typid, + jb2->val.datetime.value, + jb2->val.datetime.typid, + &error); + + if (error) + return jperError; + } + break; + default: + return jperError; + } + + switch (op) + { + case jpiEqual: + res = (cmp == 0); + break; + case jpiNotEqual: + res = (cmp != 0); + break; + case jpiLess: + res = (cmp < 0); + break; + case jpiGreater: + res = (cmp > 0); + break; + case jpiLessOrEqual: + res = (cmp <= 0); + break; + case jpiGreaterOrEqual: + res = (cmp >= 0); + break; + default: + elog(ERROR, "Unknown operation"); + return jperError; + } + + return res ? jperOk : jperNotFound; +} + +static JsonbValue * +copyJsonbValue(JsonbValue *src) +{ + JsonbValue *dst = palloc(sizeof(*dst)); + + *dst = *src; + + return dst; +} + +/* + * Execute next jsonpath item if it does exist. + */ +static inline JsonPathExecResult +recursiveExecuteNext(JsonPathExecContext *cxt, + JsonPathItem *cur, JsonPathItem *next, + JsonbValue *v, JsonValueList *found, bool copy) +{ + JsonPathItem elem; + bool hasNext; + + if (!cur) + hasNext = next != NULL; + else if (next) + hasNext = jspHasNext(cur); + else + { + next = &elem; + hasNext = jspGetNext(cur, next); + } + + if (hasNext) + return recursiveExecute(cxt, next, v, found); + + if (found) + JsonValueListAppend(found, copy ? copyJsonbValue(v) : v); + + return jperOk; +} + +/* + * Execute jsonpath expression and automatically unwrap each array item from + * the resulting sequence in lax mode. + */ +static inline JsonPathExecResult +recursiveExecuteAndUnwrap(JsonPathExecContext *cxt, JsonPathItem *jsp, + JsonbValue *jb, JsonValueList *found) +{ + if (cxt->lax) + { + JsonValueList seq = { 0 }; + JsonValueListIterator it = { 0 }; + JsonPathExecResult res = recursiveExecute(cxt, jsp, jb, &seq); + JsonbValue *item; + + if (jperIsError(res)) + return res; + + while ((item = JsonValueListNext(&seq, &it))) + { + if (item->type == jbvArray) + { + JsonbValue *elem = item->val.array.elems; + JsonbValue *last = elem + item->val.array.nElems; + + for (; elem < last; elem++) + JsonValueListAppend(found, copyJsonbValue(elem)); + } + else if (item->type == jbvBinary && + JsonContainerIsArray(item->val.binary.data)) + { + JsonbValue elem; + JsonbIterator *it = JsonbIteratorInit(item->val.binary.data); + JsonbIteratorToken tok; + + while ((tok = JsonbIteratorNext(&it, &elem, true)) != WJB_DONE) + { + if (tok == WJB_ELEM) + JsonValueListAppend(found, copyJsonbValue(&elem)); + } + } + else + JsonValueListAppend(found, item); + } + + return jperOk; + } + + return recursiveExecute(cxt, jsp, jb, found); +} + +/* + * Execute comparison expression. True is returned only if found any pair of + * items from the left and right operand's sequences which is satifistfying + * condition. In strict mode all pairs should be comparable, otherwise an error + * is returned. + */ +static JsonPathExecResult +executeExpr(JsonPathExecContext *cxt, JsonPathItem *jsp, JsonbValue *jb) +{ + JsonPathExecResult res; + JsonPathItem elem; + JsonValueList lseq = { 0 }; + JsonValueList rseq = { 0 }; + JsonValueListIterator lseqit = { 0 }; + JsonbValue *lval; + bool error = false; + bool found = false; + + jspGetLeftArg(jsp, &elem); + res = recursiveExecuteAndUnwrap(cxt, &elem, jb, &lseq); + if (jperIsError(res)) + return jperError; + + jspGetRightArg(jsp, &elem); + res = recursiveExecuteAndUnwrap(cxt, &elem, jb, &rseq); + if (jperIsError(res)) + return jperError; + + while ((lval = JsonValueListNext(&lseq, &lseqit))) + { + JsonValueListIterator rseqit = { 0 }; + JsonbValue *rval; + + while ((rval = JsonValueListNext(&rseq, &rseqit))) + { + switch (jsp->type) + { + case jpiEqual: + res = checkEquality(lval, rval, false); + break; + case jpiNotEqual: + res = checkEquality(lval, rval, true); + break; + case jpiLess: + case jpiGreater: + case jpiLessOrEqual: + case jpiGreaterOrEqual: + res = makeCompare(jsp->type, lval, rval); + break; + default: + elog(ERROR, "Unknown operation"); + } + + if (res == jperOk) + { + if (cxt->lax) + return jperOk; + + found = true; + } + else if (res == jperError) + { + if (!cxt->lax) + return jperError; + + error = true; + } + } + } + + if (found) /* possible only in strict mode */ + return jperOk; + + if (error) /* possible only in lax mode */ + return jperError; + + return jperNotFound; +} + +/* + * Execute binary arithemitc expression on singleton numeric operands. + * Array operands are automatically unwrapped in lax mode. + */ +static JsonPathExecResult +executeBinaryArithmExpr(JsonPathExecContext *cxt, JsonPathItem *jsp, + JsonbValue *jb, JsonValueList *found) +{ + MemoryContext mcxt = CurrentMemoryContext; + JsonPathExecResult jper; + JsonPathItem elem; + JsonValueList lseq = { 0 }; + JsonValueList rseq = { 0 }; + JsonbValue *lval; + JsonbValue *rval; + JsonbValue lvalbuf; + JsonbValue rvalbuf; + PGFunction func; + Datum ldatum; + Datum rdatum; + Datum res; + bool hasNext; + + jspGetLeftArg(jsp, &elem); + + /* XXX by standard unwrapped only operands of multiplicative expressions */ + jper = recursiveExecuteAndUnwrap(cxt, &elem, jb, &lseq); + + if (jper == jperOk) + { + jspGetRightArg(jsp, &elem); + jper = recursiveExecuteAndUnwrap(cxt, &elem, jb, &rseq); /* XXX */ + } + + if (jper != jperOk || + JsonValueListLength(&lseq) != 1 || + JsonValueListLength(&rseq) != 1) + return jperMakeError(ERRCODE_SINGLETON_JSON_ITEM_REQUIRED); + + lval = JsonValueListHead(&lseq); + + if (JsonbType(lval) == jbvScalar) + lval = JsonbExtractScalar(lval->val.binary.data, &lvalbuf); + + if (lval->type != jbvNumeric) + return jperMakeError(ERRCODE_SINGLETON_JSON_ITEM_REQUIRED); + + rval = JsonValueListHead(&rseq); + + if (JsonbType(rval) == jbvScalar) + rval = JsonbExtractScalar(rval->val.binary.data, &rvalbuf); + + if (rval->type != jbvNumeric) + return jperMakeError(ERRCODE_SINGLETON_JSON_ITEM_REQUIRED); + + hasNext = jspGetNext(jsp, &elem); + + if (!found && !hasNext) + return jperOk; + + ldatum = NumericGetDatum(lval->val.numeric); + rdatum = NumericGetDatum(rval->val.numeric); + + switch (jsp->type) + { + case jpiAdd: + func = numeric_add; + break; + case jpiSub: + func = numeric_sub; + break; + case jpiMul: + func = numeric_mul; + break; + case jpiDiv: + func = numeric_div; + break; + case jpiMod: + func = numeric_mod; + break; + default: + elog(ERROR, "unknown jsonpath arithmetic operation %d", jsp->type); + func = NULL; + break; + } + + PG_TRY(); + { + res = DirectFunctionCall2(func, ldatum, rdatum); + } + PG_CATCH(); + { + int errcode = geterrcode(); + + if (ERRCODE_TO_CATEGORY(errcode) != ERRCODE_DATA_EXCEPTION) + PG_RE_THROW(); + + FlushErrorState(); + MemoryContextSwitchTo(mcxt); + + return jperMakeError(errcode); + } + PG_END_TRY(); + + lval = palloc(sizeof(*lval)); + lval->type = jbvNumeric; + lval->val.numeric = DatumGetNumeric(res); + + return recursiveExecuteNext(cxt, jsp, &elem, lval, found, false); +} + +/* + * Execute unary arithemitc expression for each numeric item in its operand's + * sequence. Array operand is automatically unwrapped in lax mode. + */ +static JsonPathExecResult +executeUnaryArithmExpr(JsonPathExecContext *cxt, JsonPathItem *jsp, + JsonbValue *jb, JsonValueList *found) +{ + JsonPathExecResult jper; + JsonPathExecResult jper2; + JsonPathItem elem; + JsonValueList seq = { 0 }; + JsonValueListIterator it = { 0 }; + JsonbValue *val; + bool hasNext; + + jspGetArg(jsp, &elem); + jper = recursiveExecuteAndUnwrap(cxt, &elem, jb, &seq); + + if (jperIsError(jper)) + return jperMakeError(ERRCODE_JSON_NUMBER_NOT_FOUND); + + jper = jperNotFound; + + hasNext = jspGetNext(jsp, &elem); + + while ((val = JsonValueListNext(&seq, &it))) + { + if (JsonbType(val) == jbvScalar) + JsonbExtractScalar(val->val.binary.data, val); + + if (val->type == jbvNumeric) + { + if (!found && !hasNext) + return jperOk; + } + else if (!found && !hasNext) + continue; /* skip non-numerics processing */ + + if (val->type != jbvNumeric) + return jperMakeError(ERRCODE_JSON_NUMBER_NOT_FOUND); + + switch (jsp->type) + { + case jpiPlus: + break; + case jpiMinus: + val->val.numeric = + DatumGetNumeric(DirectFunctionCall1( + numeric_uminus, NumericGetDatum(val->val.numeric))); + break; + default: + elog(ERROR, "unknown jsonpath arithmetic operation %d", jsp->type); + } + + jper2 = recursiveExecuteNext(cxt, jsp, &elem, val, found, false); + + if (jperIsError(jper2)) + return jper2; + + if (jper2 == jperOk) + { + if (!found) + return jperOk; + jper = jperOk; + } + } + + return jper; +} + +/* + * implements jpiAny node (** operator) + */ +static JsonPathExecResult +recursiveAny(JsonPathExecContext *cxt, JsonPathItem *jsp, JsonbValue *jb, + JsonValueList *found, uint32 level, uint32 first, uint32 last) +{ + JsonPathExecResult res = jperNotFound; + JsonbIterator *it; + int32 r; + JsonbValue v; + + check_stack_depth(); + + if (level > last) + return res; + + it = JsonbIteratorInit(jb->val.binary.data); + + /* + * Recursivly iterate over jsonb objects/arrays + */ + while((r = JsonbIteratorNext(&it, &v, true)) != WJB_DONE) + { + if (r == WJB_KEY) + { + r = JsonbIteratorNext(&it, &v, true); + Assert(r == WJB_VALUE); + } + + if (r == WJB_VALUE || r == WJB_ELEM) + { + + if (level >= first) + { + /* check expression */ + res = recursiveExecuteNext(cxt, NULL, jsp, &v, found, true); + + if (jperIsError(res)) + break; + + if (res == jperOk && !found) + break; + } + + if (level < last && v.type == jbvBinary) + { + res = recursiveAny(cxt, jsp, &v, found, level + 1, first, last); + + if (jperIsError(res)) + break; + + if (res == jperOk && found == NULL) + break; + } + } + } + + return res; +} + +/* + * Execute array subscript expression and convert resulting numeric item to the + * integer type with truncation. + */ +static JsonPathExecResult +getArrayIndex(JsonPathExecContext *cxt, JsonPathItem *jsp, JsonbValue *jb, + int32 *index) +{ + JsonbValue *jbv; + JsonValueList found = { 0 }; + JsonbValue tmp; + JsonPathExecResult res = recursiveExecute(cxt, jsp, jb, &found); + + if (jperIsError(res)) + return res; + + if (JsonValueListLength(&found) != 1) + return jperMakeError(ERRCODE_INVALID_JSON_SUBSCRIPT); + + jbv = JsonValueListHead(&found); + + if (JsonbType(jbv) == jbvScalar) + jbv = JsonbExtractScalar(jbv->val.binary.data, &tmp); + + if (jbv->type != jbvNumeric) + return jperMakeError(ERRCODE_INVALID_JSON_SUBSCRIPT); + + *index = DatumGetInt32(DirectFunctionCall1(numeric_int4, + DirectFunctionCall2(numeric_trunc, + NumericGetDatum(jbv->val.numeric), + Int32GetDatum(0)))); + + return jperOk; +} + +static JsonPathExecResult +executeStartsWithPredicate(JsonPathExecContext *cxt, JsonPathItem *jsp, + JsonbValue *jb) +{ + JsonPathExecResult res; + JsonPathItem elem; + JsonValueList lseq = { 0 }; + JsonValueList rseq = { 0 }; + JsonValueListIterator lit = { 0 }; + JsonbValue *whole; + JsonbValue *initial; + JsonbValue initialbuf; + bool error = false; + bool found = false; + + jspGetRightArg(jsp, &elem); + res = recursiveExecute(cxt, &elem, jb, &rseq); + if (jperIsError(res)) + return jperError; + + if (JsonValueListLength(&rseq) != 1) + return jperError; + + initial = JsonValueListHead(&rseq); + + if (JsonbType(initial) == jbvScalar) + initial = JsonbExtractScalar(initial->val.binary.data, &initialbuf); + + if (initial->type != jbvString) + return jperError; + + jspGetLeftArg(jsp, &elem); + res = recursiveExecuteAndUnwrap(cxt, &elem, jb, &lseq); + if (jperIsError(res)) + return jperError; + + while ((whole = JsonValueListNext(&lseq, &lit))) + { + JsonbValue wholebuf; + + if (JsonbType(whole) == jbvScalar) + whole = JsonbExtractScalar(whole->val.binary.data, &wholebuf); + + if (whole->type != jbvString) + { + if (!cxt->lax) + return jperError; + + error = true; + } + else if (whole->val.string.len >= initial->val.string.len && + !memcmp(whole->val.string.val, + initial->val.string.val, + initial->val.string.len)) + { + if (cxt->lax) + return jperOk; + + found = true; + } + } + + if (found) /* possible only in strict mode */ + return jperOk; + + if (error) /* possible only in lax mode */ + return jperError; + + return jperNotFound; +} + +static JsonPathExecResult +executeLikeRegexPredicate(JsonPathExecContext *cxt, JsonPathItem *jsp, + JsonbValue *jb) +{ + JsonPathExecResult res; + JsonPathItem elem; + JsonValueList seq = { 0 }; + JsonValueListIterator it = { 0 }; + JsonbValue *str; + text *regex; + uint32 flags = jsp->content.like_regex.flags; + int cflags = REG_ADVANCED; + bool error = false; + bool found = false; + + if (flags & JSP_REGEX_ICASE) + cflags |= REG_ICASE; + if (flags & JSP_REGEX_MLINE) + cflags |= REG_NEWLINE; + if (flags & JSP_REGEX_SLINE) + cflags &= ~REG_NEWLINE; + if (flags & JSP_REGEX_WSPACE) + cflags |= REG_EXPANDED; + + regex = cstring_to_text_with_len(jsp->content.like_regex.pattern, + jsp->content.like_regex.patternlen); + + jspInitByBuffer(&elem, jsp->base, jsp->content.like_regex.expr); + res = recursiveExecuteAndUnwrap(cxt, &elem, jb, &seq); + if (jperIsError(res)) + return jperError; + + while ((str = JsonValueListNext(&seq, &it))) + { + JsonbValue strbuf; + + if (JsonbType(str) == jbvScalar) + str = JsonbExtractScalar(str->val.binary.data, &strbuf); + + if (str->type != jbvString) + { + if (!cxt->lax) + return jperError; + + error = true; + } + else if (RE_compile_and_execute(regex, str->val.string.val, + str->val.string.len, cflags, + DEFAULT_COLLATION_OID, 0, NULL)) + { + if (cxt->lax) + return jperOk; + + found = true; + } + } + + if (found) /* possible only in strict mode */ + return jperOk; + + if (error) /* possible only in lax mode */ + return jperError; + + return jperNotFound; +} + +/* + * Try to parse datetime text with the specified datetime template and + * default time-zone 'tzname'. + * Returns 'value' datum, its type 'typid' and 'typmod'. + */ +static bool +tryToParseDatetime(const char *fmt, int fmtlen, text *datetime, char *tzname, + bool strict, Datum *value, Oid *typid, int32 *typmod, int *tz) +{ + MemoryContext mcxt = CurrentMemoryContext; + bool ok = false; + + PG_TRY(); + { + *value = to_datetime(datetime, fmt, fmtlen, tzname, strict, + typid, typmod, tz); + ok = true; + } + PG_CATCH(); + { + if (ERRCODE_TO_CATEGORY(geterrcode()) != ERRCODE_DATA_EXCEPTION) + PG_RE_THROW(); + + FlushErrorState(); + MemoryContextSwitchTo(mcxt); + } + PG_END_TRY(); + + return ok; +} + +/* + * Convert execution status 'res' to a boolean JSON item and execute next + * jsonpath if 'needBool' is false: + * - jperOk => true + * - jperNotFound => false + * - jperError => null (errors are converted to NULL values) + */ +static inline JsonPathExecResult +appendBoolResult(JsonPathExecContext *cxt, JsonPathItem *jsp, + JsonValueList *found, JsonPathExecResult res, bool needBool) +{ + JsonPathItem next; + JsonbValue jbv; + bool hasNext = jspGetNext(jsp, &next); + + if (needBool) + { + Assert(!hasNext); + return res; /* simply return status */ + } + + if (!found && !hasNext) + return jperOk; /* found singleton boolean value */ + + if (jperIsError(res)) + jbv.type = jbvNull; + else + { + jbv.type = jbvBool; + jbv.val.boolean = res == jperOk; + } + + return recursiveExecuteNext(cxt, jsp, &next, &jbv, found, true); +} + +/* + * Main executor function: walks on jsonpath structure and tries to find + * correspoding parts of jsonb. Note, jsonb and jsonpath values should be + * avaliable and untoasted during work because JsonPathItem, JsonbValue + * and found could have pointers into input values. If caller wants just to + * check matching of json by jsonpath then it doesn't provide a found arg. + * In this case executor works till first positive result and does not check + * the rest if it is possible. In other case it tries to find all satisfied + * results + */ +static JsonPathExecResult +recursiveExecuteNoUnwrap(JsonPathExecContext *cxt, JsonPathItem *jsp, + JsonbValue *jb, JsonValueList *found, bool needBool) +{ + JsonPathItem elem; + JsonPathExecResult res = jperNotFound; + bool hasNext; + + check_stack_depth(); + + switch(jsp->type) { + case jpiAnd: + jspGetLeftArg(jsp, &elem); + res = recursiveExecuteBool(cxt, &elem, jb); + if (res != jperNotFound) + { + JsonPathExecResult res2; + + /* + * SQL/JSON says that we should check second arg + * in case of jperError + */ + + jspGetRightArg(jsp, &elem); + res2 = recursiveExecuteBool(cxt, &elem, jb); + + res = (res2 == jperOk) ? res : res2; + } + res = appendBoolResult(cxt, jsp, found, res, needBool); + break; + case jpiOr: + jspGetLeftArg(jsp, &elem); + res = recursiveExecuteBool(cxt, &elem, jb); + if (res != jperOk) + { + JsonPathExecResult res2; + + jspGetRightArg(jsp, &elem); + res2 = recursiveExecuteBool(cxt, &elem, jb); + + res = (res2 == jperNotFound) ? res : res2; + } + res = appendBoolResult(cxt, jsp, found, res, needBool); + break; + case jpiNot: + jspGetArg(jsp, &elem); + switch ((res = recursiveExecuteBool(cxt, &elem, jb))) + { + case jperOk: + res = jperNotFound; + break; + case jperNotFound: + res = jperOk; + break; + default: + break; + } + res = appendBoolResult(cxt, jsp, found, res, needBool); + break; + case jpiIsUnknown: + jspGetArg(jsp, &elem); + res = recursiveExecuteBool(cxt, &elem, jb); + res = jperIsError(res) ? jperOk : jperNotFound; + res = appendBoolResult(cxt, jsp, found, res, needBool); + break; + case jpiKey: + if (JsonbType(jb) == jbvObject) + { + JsonbValue *v, key; + JsonbValue obj; + + if (jb->type == jbvObject) + jb = JsonbWrapInBinary(jb, &obj); + + key.type = jbvString; + key.val.string.val = jspGetString(jsp, &key.val.string.len); + + v = findJsonbValueFromContainer(jb->val.binary.data, JB_FOBJECT, &key); + + if (v != NULL) + { + res = recursiveExecuteNext(cxt, jsp, NULL, v, found, false); + + if (jspHasNext(jsp) || !found) + pfree(v); /* free value if it was not added to found list */ + } + else if (!cxt->lax) + { + Assert(found); + res = jperMakeError(ERRCODE_JSON_MEMBER_NOT_FOUND); + } + } + else if (!cxt->lax) + { + Assert(found); + res = jperMakeError(ERRCODE_JSON_MEMBER_NOT_FOUND); + } + break; + case jpiRoot: + jb = cxt->root; + /* fall through */ + case jpiCurrent: + { + JsonbValue *v; + JsonbValue vbuf; + bool copy = true; + + if (JsonbType(jb) == jbvScalar) + { + if (jspHasNext(jsp)) + v = &vbuf; + else + { + v = palloc(sizeof(*v)); + copy = false; + } + + JsonbExtractScalar(jb->val.binary.data, v); + } + else + v = jb; + + res = recursiveExecuteNext(cxt, jsp, NULL, v, found, copy); + break; + } + case jpiAnyArray: + if (JsonbType(jb) == jbvArray) + { + hasNext = jspGetNext(jsp, &elem); + + if (jb->type == jbvArray) + { + JsonbValue *el = jb->val.array.elems; + JsonbValue *last_el = el + jb->val.array.nElems; + + for (; el < last_el; el++) + { + res = recursiveExecuteNext(cxt, jsp, &elem, el, found, true); + + if (jperIsError(res)) + break; + + if (res == jperOk && !found) + break; + } + } + else + { + JsonbValue v; + JsonbIterator *it; + JsonbIteratorToken r; + + it = JsonbIteratorInit(jb->val.binary.data); + + while((r = JsonbIteratorNext(&it, &v, true)) != WJB_DONE) + { + if (r == WJB_ELEM) + { + res = recursiveExecuteNext(cxt, jsp, &elem, &v, found, true); + + if (jperIsError(res)) + break; + + if (res == jperOk && !found) + break; + } + } + } + } + else + res = jperMakeError(ERRCODE_JSON_ARRAY_NOT_FOUND); + break; + + case jpiIndexArray: + if (JsonbType(jb) == jbvArray) + { + int innermostArraySize = cxt->innermostArraySize; + int i; + int size = JsonbArraySize(jb); + bool binary = jb->type == jbvBinary; + + cxt->innermostArraySize = size; /* for LAST evaluation */ + + hasNext = jspGetNext(jsp, &elem); + + for (i = 0; i < jsp->content.array.nelems; i++) + { + JsonPathItem from; + JsonPathItem to; + int32 index; + int32 index_from; + int32 index_to; + bool range = jspGetArraySubscript(jsp, &from, &to, i); + + res = getArrayIndex(cxt, &from, jb, &index_from); + + if (jperIsError(res)) + break; + + if (range) + { + res = getArrayIndex(cxt, &to, jb, &index_to); + + if (jperIsError(res)) + break; + } + else + index_to = index_from; + + if (!cxt->lax && + (index_from < 0 || + index_from > index_to || + index_to >= size)) + { + res = jperMakeError(ERRCODE_INVALID_JSON_SUBSCRIPT); + break; + } + + if (index_from < 0) + index_from = 0; + + if (index_to >= size) + index_to = size - 1; + + res = jperNotFound; + + for (index = index_from; index <= index_to; index++) + { + JsonbValue *v = binary ? + getIthJsonbValueFromContainer(jb->val.binary.data, + (uint32) index) : + &jb->val.array.elems[index]; + + if (v == NULL) + continue; + + res = recursiveExecuteNext(cxt, jsp, &elem, v, found, + !binary); + + if (jperIsError(res)) + break; + + if (res == jperOk && !found) + break; + } + + if (jperIsError(res)) + break; + + if (res == jperOk && !found) + break; + } + + cxt->innermostArraySize = innermostArraySize; + } + else + res = jperMakeError(ERRCODE_JSON_ARRAY_NOT_FOUND); + break; + + case jpiLast: + { + JsonbValue tmpjbv; + JsonbValue *lastjbv; + int last; + bool hasNext; + + if (cxt->innermostArraySize < 0) + elog(ERROR, + "evaluating jsonpath LAST outside of array subscript"); + + hasNext = jspGetNext(jsp, &elem); + + if (!hasNext && !found) + { + res = jperOk; + break; + } + + last = cxt->innermostArraySize - 1; + + lastjbv = hasNext ? &tmpjbv : palloc(sizeof(*lastjbv)); + + lastjbv->type = jbvNumeric; + lastjbv->val.numeric = DatumGetNumeric(DirectFunctionCall1( + int4_numeric, Int32GetDatum(last))); + + res = recursiveExecuteNext(cxt, jsp, &elem, lastjbv, found, hasNext); + } + break; + case jpiAnyKey: + if (JsonbType(jb) == jbvObject) + { + JsonbIterator *it; + int32 r; + JsonbValue v; + JsonbValue bin; + + if (jb->type == jbvObject) + jb = JsonbWrapInBinary(jb, &bin); + + hasNext = jspGetNext(jsp, &elem); + it = JsonbIteratorInit(jb->val.binary.data); + + while((r = JsonbIteratorNext(&it, &v, true)) != WJB_DONE) + { + if (r == WJB_VALUE) + { + res = recursiveExecuteNext(cxt, jsp, &elem, &v, found, true); + + if (jperIsError(res)) + break; + + if (res == jperOk && !found) + break; + } + } + } + else if (!cxt->lax) + { + Assert(found); + res = jperMakeError(ERRCODE_JSON_OBJECT_NOT_FOUND); + } + break; + case jpiEqual: + case jpiNotEqual: + case jpiLess: + case jpiGreater: + case jpiLessOrEqual: + case jpiGreaterOrEqual: + res = executeExpr(cxt, jsp, jb); + res = appendBoolResult(cxt, jsp, found, res, needBool); + break; + case jpiAdd: + case jpiSub: + case jpiMul: + case jpiDiv: + case jpiMod: + res = executeBinaryArithmExpr(cxt, jsp, jb, found); + break; + case jpiPlus: + case jpiMinus: + res = executeUnaryArithmExpr(cxt, jsp, jb, found); + break; + case jpiFilter: + jspGetArg(jsp, &elem); + res = recursiveExecuteBool(cxt, &elem, jb); + if (res != jperOk) + res = jperNotFound; + else + res = recursiveExecuteNext(cxt, jsp, NULL, jb, found, true); + break; + case jpiAny: + { + JsonbValue jbvbuf; + + hasNext = jspGetNext(jsp, &elem); + + /* first try without any intermediate steps */ + if (jsp->content.anybounds.first == 0) + { + res = recursiveExecuteNext(cxt, jsp, &elem, jb, found, true); + + if (res == jperOk && !found) + break; + } + + if (jb->type == jbvArray || jb->type == jbvObject) + jb = JsonbWrapInBinary(jb, &jbvbuf); + + if (jb->type == jbvBinary) + res = recursiveAny(cxt, hasNext ? &elem : NULL, jb, found, + 1, + jsp->content.anybounds.first, + jsp->content.anybounds.last); + break; + } + case jpiExists: + jspGetArg(jsp, &elem); + + if (cxt->lax) + res = recursiveExecute(cxt, &elem, jb, NULL); + else + { + JsonValueList vals = { 0 }; + + /* + * In strict mode we must get a complete list of values + * to check that there are no errors at all. + */ + res = recursiveExecute(cxt, &elem, jb, &vals); + + if (!jperIsError(res)) + res = JsonValueListIsEmpty(&vals) ? jperNotFound : jperOk; + } + + res = appendBoolResult(cxt, jsp, found, res, needBool); + break; + case jpiNull: + case jpiBool: + case jpiNumeric: + case jpiString: + case jpiVariable: + { + JsonbValue vbuf; + JsonbValue *v; + bool hasNext = jspGetNext(jsp, &elem); + + if (!hasNext && !found) + { + res = jperOk; /* skip evaluation */ + break; + } + + v = hasNext ? &vbuf : palloc(sizeof(*v)); + + computeJsonPathItem(cxt, jsp, v); + + res = recursiveExecuteNext(cxt, jsp, &elem, v, found, hasNext); + } + break; + case jpiType: + { + JsonbValue *jbv = palloc(sizeof(*jbv)); + + jbv->type = jbvString; + jbv->val.string.val = pstrdup(JsonbTypeName(jb)); + jbv->val.string.len = strlen(jbv->val.string.val); + + res = recursiveExecuteNext(cxt, jsp, NULL, jbv, found, false); + } + break; + case jpiSize: + { + int size = JsonbArraySize(jb); + + if (size < 0) + { + if (!cxt->lax) + { + res = jperMakeError(ERRCODE_JSON_ARRAY_NOT_FOUND); + break; + } + + size = 1; + } + + jb = palloc(sizeof(*jb)); + + jb->type = jbvNumeric; + jb->val.numeric = + DatumGetNumeric(DirectFunctionCall1(int4_numeric, + Int32GetDatum(size))); + + res = recursiveExecuteNext(cxt, jsp, NULL, jb, found, false); + } + break; + case jpiAbs: + case jpiFloor: + case jpiCeiling: + { + JsonbValue jbvbuf; + + if (JsonbType(jb) == jbvScalar) + jb = JsonbExtractScalar(jb->val.binary.data, &jbvbuf); + + if (jb->type == jbvNumeric) + { + Datum datum = NumericGetDatum(jb->val.numeric); + + switch (jsp->type) + { + case jpiAbs: + datum = DirectFunctionCall1(numeric_abs, datum); + break; + case jpiFloor: + datum = DirectFunctionCall1(numeric_floor, datum); + break; + case jpiCeiling: + datum = DirectFunctionCall1(numeric_ceil, datum); + break; + default: + break; + } + + jb = palloc(sizeof(*jb)); + + jb->type = jbvNumeric; + jb->val.numeric = DatumGetNumeric(datum); + + res = recursiveExecuteNext(cxt, jsp, NULL, jb, found, false); + } + else + res = jperMakeError(ERRCODE_NON_NUMERIC_JSON_ITEM); + } + break; + case jpiDouble: + { + JsonbValue jbv; + MemoryContext mcxt = CurrentMemoryContext; + + if (JsonbType(jb) == jbvScalar) + jb = JsonbExtractScalar(jb->val.binary.data, &jbv); + + PG_TRY(); + { + if (jb->type == jbvNumeric) + { + /* only check success of numeric to double cast */ + DirectFunctionCall1(numeric_float8, + NumericGetDatum(jb->val.numeric)); + res = jperOk; + } + else if (jb->type == jbvString) + { + /* cast string as double */ + char *str = pnstrdup(jb->val.string.val, + jb->val.string.len); + Datum val = DirectFunctionCall1( + float8in, CStringGetDatum(str)); + pfree(str); + + jb = &jbv; + jb->type = jbvNumeric; + jb->val.numeric = DatumGetNumeric(DirectFunctionCall1( + float8_numeric, val)); + res = jperOk; + + } + else + res = jperMakeError(ERRCODE_NON_NUMERIC_JSON_ITEM); + } + PG_CATCH(); + { + if (ERRCODE_TO_CATEGORY(geterrcode()) != + ERRCODE_DATA_EXCEPTION) + PG_RE_THROW(); + + FlushErrorState(); + MemoryContextSwitchTo(mcxt); + res = jperMakeError(ERRCODE_NON_NUMERIC_JSON_ITEM); + } + PG_END_TRY(); + + if (res == jperOk) + res = recursiveExecuteNext(cxt, jsp, NULL, jb, found, true); + } + break; + case jpiDatetime: + { + JsonbValue jbvbuf; + Datum value; + text *datetime; + Oid typid; + int32 typmod = -1; + int tz; + bool hasNext; + + if (JsonbType(jb) == jbvScalar) + jb = JsonbExtractScalar(jb->val.binary.data, &jbvbuf); + + res = jperMakeError(ERRCODE_INVALID_ARGUMENT_FOR_JSON_DATETIME_FUNCTION); + + if (jb->type != jbvString) + break; + + datetime = cstring_to_text_with_len(jb->val.string.val, + jb->val.string.len); + + if (jsp->content.args.left) + { + char *template_str; + int template_len; + char *tzname = NULL; + + jspGetLeftArg(jsp, &elem); + + if (elem.type != jpiString) + elog(ERROR, "invalid jsonpath item type for .datetime() argument"); + + template_str = jspGetString(&elem, &template_len); + + if (jsp->content.args.right) + { + JsonValueList tzlist = { 0 }; + JsonPathExecResult tzres; + JsonbValue *tzjbv; + + jspGetRightArg(jsp, &elem); + tzres = recursiveExecuteNoUnwrap(cxt, &elem, jb, + &tzlist, false); + + if (jperIsError(tzres)) + return tzres; + + if (JsonValueListLength(&tzlist) != 1) + break; + + tzjbv = JsonValueListHead(&tzlist); + + if (tzjbv->type != jbvString) + break; + + tzname = pnstrdup(tzjbv->val.string.val, + tzjbv->val.string.len); + } + + if (tryToParseDatetime(template_str, template_len, datetime, + tzname, false, + &value, &typid, &typmod, &tz)) + res = jperOk; + + 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(templates[i], -1, datetime, + NULL, true, &value, &typid, + &typmod, &tz)) + { + res = jperOk; + break; + } + } + } + + pfree(datetime); + + if (jperIsError(res)) + break; + + hasNext = jspGetNext(jsp, &elem); + + if (!hasNext && !found) + break; + + jb = hasNext ? &jbvbuf : palloc(sizeof(*jb)); + + jb->type = jbvDatetime; + jb->val.datetime.value = value; + jb->val.datetime.typid = typid; + jb->val.datetime.typmod = typmod; + jb->val.datetime.tz = tz; + + res = recursiveExecuteNext(cxt, jsp, &elem, jb, found, hasNext); + } + break; + case jpiKeyValue: + if (JsonbType(jb) != jbvObject) + res = jperMakeError(ERRCODE_JSON_OBJECT_NOT_FOUND); + else + { + int32 r; + JsonbValue bin; + JsonbValue key; + JsonbValue val; + JsonbValue obj; + JsonbValue keystr; + JsonbValue valstr; + JsonbIterator *it; + JsonbParseState *ps = NULL; + + hasNext = jspGetNext(jsp, &elem); + + if (jb->type == jbvBinary + ? !JsonContainerSize(jb->val.binary.data) + : !jb->val.object.nPairs) + { + res = jperNotFound; + break; + } + + /* make template object */ + obj.type = jbvBinary; + + keystr.type = jbvString; + keystr.val.string.val = "key"; + keystr.val.string.len = 3; + + valstr.type = jbvString; + valstr.val.string.val = "value"; + valstr.val.string.len = 5; + + if (jb->type == jbvObject) + jb = JsonbWrapInBinary(jb, &bin); + + it = JsonbIteratorInit(jb->val.binary.data); + + while ((r = JsonbIteratorNext(&it, &key, true)) != WJB_DONE) + { + if (r == WJB_KEY) + { + Jsonb *jsonb; + JsonbValue *keyval; + + res = jperOk; + + if (!hasNext && !found) + break; + + r = JsonbIteratorNext(&it, &val, true); + Assert(r == WJB_VALUE); + + pushJsonbValue(&ps, WJB_BEGIN_OBJECT, NULL); + + pushJsonbValue(&ps, WJB_KEY, &keystr); + pushJsonbValue(&ps, WJB_VALUE, &key); + + + pushJsonbValue(&ps, WJB_KEY, &valstr); + pushJsonbValue(&ps, WJB_VALUE, &val); + + keyval = pushJsonbValue(&ps, WJB_END_OBJECT, NULL); + + jsonb = JsonbValueToJsonb(keyval); + + JsonbInitBinary(&obj, jsonb); + + res = recursiveExecuteNext(cxt, jsp, &elem, &obj, found, true); + + if (jperIsError(res)) + break; + + if (res == jperOk && !found) + break; + } + } + } + break; + case jpiStartsWith: + res = executeStartsWithPredicate(cxt, jsp, jb); + res = appendBoolResult(cxt, jsp, found, res, needBool); + break; + case jpiLikeRegex: + res = executeLikeRegexPredicate(cxt, jsp, jb); + res = appendBoolResult(cxt, jsp, found, res, needBool); + break; + default: + elog(ERROR, "unrecognized jsonpath item type: %d", jsp->type); + } + + return res; +} + +/* + * Unwrap current array item and execute jsonpath for each of its elements. + */ +static JsonPathExecResult +recursiveExecuteUnwrapArray(JsonPathExecContext *cxt, JsonPathItem *jsp, + JsonbValue *jb, JsonValueList *found) +{ + JsonPathExecResult res = jperNotFound; + + if (jb->type == jbvArray) + { + JsonbValue *elem = jb->val.array.elems; + JsonbValue *last = elem + jb->val.array.nElems; + + for (; elem < last; elem++) + { + res = recursiveExecuteNoUnwrap(cxt, jsp, elem, found, false); + + if (jperIsError(res)) + break; + if (res == jperOk && !found) + break; + } + } + else + { + JsonbValue v; + JsonbIterator *it; + JsonbIteratorToken tok; + + it = JsonbIteratorInit(jb->val.binary.data); + + while ((tok = JsonbIteratorNext(&it, &v, true)) != WJB_DONE) + { + if (tok == WJB_ELEM) + { + res = recursiveExecuteNoUnwrap(cxt, jsp, &v, found, false); + if (jperIsError(res)) + break; + if (res == jperOk && !found) + break; + } + } + } + + return res; +} + +/* + * Execute jsonpath with unwrapping of current item if it is an array. + */ +static inline JsonPathExecResult +recursiveExecuteUnwrap(JsonPathExecContext *cxt, JsonPathItem *jsp, + JsonbValue *jb, JsonValueList *found) +{ + if (cxt->lax && JsonbType(jb) == jbvArray) + return recursiveExecuteUnwrapArray(cxt, jsp, jb, found); + + return recursiveExecuteNoUnwrap(cxt, jsp, jb, found, false); +} + +/* + * Wrap a non-array SQL/JSON item into an array for applying array subscription + * path steps in lax mode. + */ +static inline JsonbValue * +wrapItem(JsonbValue *jbv) +{ + JsonbParseState *ps = NULL; + JsonbValue jbvbuf; + + switch (JsonbType(jbv)) + { + case jbvArray: + /* Simply return an array item. */ + return jbv; + + case jbvScalar: + /* Extract scalar value from singleton pseudo-array. */ + jbv = JsonbExtractScalar(jbv->val.binary.data, &jbvbuf); + break; + + case jbvObject: + /* + * Need to wrap object into a binary JsonbValue for its unpacking + * in pushJsonbValue(). + */ + if (jbv->type != jbvBinary) + jbv = JsonbWrapInBinary(jbv, &jbvbuf); + break; + + default: + /* Ordinary scalars can be pushed directly. */ + break; + } + + pushJsonbValue(&ps, WJB_BEGIN_ARRAY, NULL); + pushJsonbValue(&ps, WJB_ELEM, jbv); + jbv = pushJsonbValue(&ps, WJB_END_ARRAY, NULL); + + return JsonbWrapInBinary(jbv, NULL); +} + +/* + * Execute jsonpath with automatic unwrapping of current item in lax mode. + */ +static inline JsonPathExecResult +recursiveExecute(JsonPathExecContext *cxt, JsonPathItem *jsp, JsonbValue *jb, + JsonValueList *found) +{ + if (cxt->lax) + { + switch (jsp->type) + { + case jpiKey: + case jpiAnyKey: + /* case jpiAny: */ + case jpiFilter: + /* all methods excluding type() and size() */ + case jpiAbs: + case jpiFloor: + case jpiCeiling: + case jpiDouble: + case jpiDatetime: + case jpiKeyValue: + return recursiveExecuteUnwrap(cxt, jsp, jb, found); + + case jpiAnyArray: + case jpiIndexArray: + jb = wrapItem(jb); + break; + + default: + break; + } + } + + return recursiveExecuteNoUnwrap(cxt, jsp, jb, found, false); +} + +/* + * Execute boolean-valued jsonpath expression. Boolean items are not appended + * to the result list, only return code determines result: + * - jperOk => true + * - jperNotFound => false + * - jperError => NULL (errors are converted to NULL values) + */ +static inline JsonPathExecResult +recursiveExecuteBool(JsonPathExecContext *cxt, JsonPathItem *jsp, + JsonbValue *jb) +{ + if (jspHasNext(jsp)) + elog(ERROR, "boolean jsonpath item can not have next item"); + + switch (jsp->type) + { + case jpiAnd: + case jpiOr: + case jpiNot: + case jpiIsUnknown: + case jpiEqual: + case jpiNotEqual: + case jpiGreater: + case jpiGreaterOrEqual: + case jpiLess: + case jpiLessOrEqual: + case jpiExists: + case jpiStartsWith: + case jpiLikeRegex: + break; + + default: + elog(ERROR, "invalid boolean jsonpath item type: %d", jsp->type); + break; + } + + return recursiveExecuteNoUnwrap(cxt, jsp, jb, NULL, true); +} + +/* + * Public interface to jsonpath executor + */ +JsonPathExecResult +executeJsonPath(JsonPath *path, List *vars, Jsonb *json, JsonValueList *foundJson) +{ + JsonPathExecContext cxt; + JsonPathItem jsp; + JsonbValue jbv; + + jspInit(&jsp, path); + + cxt.vars = vars; + cxt.lax = (path->header & JSONPATH_LAX) != 0; + cxt.root = JsonbInitBinary(&jbv, json); + cxt.innermostArraySize = -1; + + if (!cxt.lax && !foundJson) + { + /* + * In strict mode we must get a complete list of values to check + * that there are no errors at all. + */ + JsonValueList vals = { 0 }; + JsonPathExecResult res = recursiveExecute(&cxt, &jsp, &jbv, &vals); + + if (jperIsError(res)) + return res; + + return JsonValueListIsEmpty(&vals) ? jperNotFound : jperOk; + } + + return recursiveExecute(&cxt, &jsp, &jbv, foundJson); +} + +static Datum +returnDATUM(void *arg, bool *isNull) +{ + *isNull = false; + return PointerGetDatum(arg); +} + +static Datum +returnNULL(void *arg, bool *isNull) +{ + *isNull = true; + return Int32GetDatum(0); +} + +/* + * Convert jsonb object into list of vars for executor + */ +static List* +makePassingVars(Jsonb *jb) +{ + JsonbValue v; + JsonbIterator *it; + int32 r; + List *vars = NIL; + + it = JsonbIteratorInit(&jb->root); + + r = JsonbIteratorNext(&it, &v, true); + + if (r != WJB_BEGIN_OBJECT) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("passing variable json is not a object"))); + + while((r = JsonbIteratorNext(&it, &v, true)) != WJB_DONE) + { + if (r == WJB_KEY) + { + JsonPathVariable *jpv = palloc0(sizeof(*jpv)); + + jpv->varName = cstring_to_text_with_len(v.val.string.val, + v.val.string.len); + + JsonbIteratorNext(&it, &v, true); + + jpv->cb = returnDATUM; + + switch(v.type) + { + case jbvBool: + jpv->typid = BOOLOID; + jpv->cb_arg = DatumGetPointer(BoolGetDatum(v.val.boolean)); + break; + case jbvNull: + jpv->cb = returnNULL; + break; + case jbvString: + jpv->typid = TEXTOID; + jpv->cb_arg = cstring_to_text_with_len(v.val.string.val, + v.val.string.len); + break; + case jbvNumeric: + jpv->typid = NUMERICOID; + jpv->cb_arg = v.val.numeric; + break; + case jbvBinary: + jpv->typid = JSONBOID; + jpv->cb_arg = DatumGetPointer(JsonbPGetDatum(JsonbValueToJsonb(&v))); + break; + default: + elog(ERROR, "unsupported type in passing variable json"); + } + + vars = lappend(vars, jpv); + } + } + + return vars; +} + +static void +throwJsonPathError(JsonPathExecResult res) +{ + if (!jperIsError(res)) + return; + + switch (jperGetError(res)) + { + case ERRCODE_JSON_ARRAY_NOT_FOUND: + ereport(ERROR, + (errcode(jperGetError(res)), + errmsg("SQL/JSON array not found"))); + break; + case ERRCODE_JSON_OBJECT_NOT_FOUND: + ereport(ERROR, + (errcode(jperGetError(res)), + errmsg("SQL/JSON object not found"))); + break; + case ERRCODE_JSON_MEMBER_NOT_FOUND: + ereport(ERROR, + (errcode(jperGetError(res)), + errmsg("SQL/JSON member not found"))); + break; + case ERRCODE_JSON_NUMBER_NOT_FOUND: + ereport(ERROR, + (errcode(jperGetError(res)), + errmsg("SQL/JSON number not found"))); + break; + case ERRCODE_JSON_SCALAR_REQUIRED: + ereport(ERROR, + (errcode(jperGetError(res)), + errmsg("SQL/JSON scalar required"))); + break; + case ERRCODE_SINGLETON_JSON_ITEM_REQUIRED: + ereport(ERROR, + (errcode(jperGetError(res)), + errmsg("Singleton SQL/JSON item required"))); + break; + case ERRCODE_NON_NUMERIC_JSON_ITEM: + ereport(ERROR, + (errcode(jperGetError(res)), + errmsg("Non-numeric SQL/JSON item"))); + break; + case ERRCODE_INVALID_JSON_SUBSCRIPT: + ereport(ERROR, + (errcode(jperGetError(res)), + errmsg("Invalid SQL/JSON subscript"))); + break; + case ERRCODE_INVALID_ARGUMENT_FOR_JSON_DATETIME_FUNCTION: + ereport(ERROR, + (errcode(jperGetError(res)), + errmsg("Invalid argument for SQL/JSON datetime function"))); + break; + default: + ereport(ERROR, + (errcode(jperGetError(res)), + errmsg("Unknown SQL/JSON error"))); + break; + } +} + +static Datum +jsonb_jsonpath_exists(PG_FUNCTION_ARGS) +{ + Jsonb *jb = PG_GETARG_JSONB_P(0); + JsonPath *jp = PG_GETARG_JSONPATH_P(1); + JsonPathExecResult res; + List *vars = NIL; + + if (PG_NARGS() == 3) + vars = makePassingVars(PG_GETARG_JSONB_P(2)); + + res = executeJsonPath(jp, vars, jb, NULL); + + PG_FREE_IF_COPY(jb, 0); + PG_FREE_IF_COPY(jp, 1); + + if (jperIsError(res)) + PG_RETURN_NULL(); + + PG_RETURN_BOOL(res == jperOk); +} + +Datum +jsonb_jsonpath_exists2(PG_FUNCTION_ARGS) +{ + return jsonb_jsonpath_exists(fcinfo); +} + +Datum +jsonb_jsonpath_exists3(PG_FUNCTION_ARGS) +{ + return jsonb_jsonpath_exists(fcinfo); +} + +static inline Datum +jsonb_jsonpath_predicate(FunctionCallInfo fcinfo, List *vars) +{ + Jsonb *jb = PG_GETARG_JSONB_P(0); + JsonPath *jp = PG_GETARG_JSONPATH_P(1); + JsonbValue *jbv; + JsonValueList found = { 0 }; + JsonPathExecResult res; + + res = executeJsonPath(jp, vars, jb, &found); + + throwJsonPathError(res); + + if (JsonValueListLength(&found) != 1) + throwJsonPathError(jperMakeError(ERRCODE_SINGLETON_JSON_ITEM_REQUIRED)); + + jbv = JsonValueListHead(&found); + + if (JsonbType(jbv) == jbvScalar) + JsonbExtractScalar(jbv->val.binary.data, jbv); + + PG_FREE_IF_COPY(jb, 0); + PG_FREE_IF_COPY(jp, 1); + + if (jbv->type == jbvNull) + PG_RETURN_NULL(); + + if (jbv->type != jbvBool) + PG_RETURN_NULL(); /* XXX */ + + PG_RETURN_BOOL(jbv->val.boolean); +} + +Datum +jsonb_jsonpath_predicate2(PG_FUNCTION_ARGS) +{ + return jsonb_jsonpath_predicate(fcinfo, NIL); +} + +Datum +jsonb_jsonpath_predicate3(PG_FUNCTION_ARGS) +{ + return jsonb_jsonpath_predicate(fcinfo, + makePassingVars(PG_GETARG_JSONB_P(2))); +} + +static Datum +jsonb_jsonpath_query(FunctionCallInfo fcinfo) +{ + FuncCallContext *funcctx; + List *found; + JsonbValue *v; + ListCell *c; + + if (SRF_IS_FIRSTCALL()) + { + JsonPath *jp = PG_GETARG_JSONPATH_P(1); + Jsonb *jb; + JsonPathExecResult res; + MemoryContext oldcontext; + List *vars = NIL; + JsonValueList found = { 0 }; + + funcctx = SRF_FIRSTCALL_INIT(); + oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); + + jb = PG_GETARG_JSONB_P_COPY(0); + if (PG_NARGS() == 3) + vars = makePassingVars(PG_GETARG_JSONB_P(2)); + + res = executeJsonPath(jp, vars, jb, &found); + + if (jperIsError(res)) + throwJsonPathError(res); + + PG_FREE_IF_COPY(jp, 1); + + funcctx->user_fctx = JsonValueListGetList(&found); + + MemoryContextSwitchTo(oldcontext); + } + + funcctx = SRF_PERCALL_SETUP(); + found = funcctx->user_fctx; + + c = list_head(found); + + if (c == NULL) + SRF_RETURN_DONE(funcctx); + + v = lfirst(c); + funcctx->user_fctx = list_delete_first(found); + + SRF_RETURN_NEXT(funcctx, JsonbPGetDatum(JsonbValueToJsonb(v))); +} + +Datum +jsonb_jsonpath_query2(PG_FUNCTION_ARGS) +{ + return jsonb_jsonpath_query(fcinfo); +} + +Datum +jsonb_jsonpath_query3(PG_FUNCTION_ARGS) +{ + return jsonb_jsonpath_query(fcinfo); +} + +static Datum +jsonb_jsonpath_query_wrapped(FunctionCallInfo fcinfo, List *vars) +{ + Jsonb *jb = PG_GETARG_JSONB_P(0); + JsonPath *jp = PG_GETARG_JSONPATH_P(1); + JsonValueList found = { 0 }; + JsonPathExecResult res; + int size; + + res = executeJsonPath(jp, vars, jb, &found); + + if (jperIsError(res)) + throwJsonPathError(res); + + size = JsonValueListLength(&found); + + if (size == 0) + PG_RETURN_NULL(); + + if (size == 1) + PG_RETURN_JSONB_P(JsonbValueToJsonb(JsonValueListHead(&found))); + + PG_RETURN_JSONB_P(JsonbValueToJsonb(wrapItemsInArray(&found))); +} + +Datum +jsonb_jsonpath_query_wrapped2(PG_FUNCTION_ARGS) +{ + return jsonb_jsonpath_query_wrapped(fcinfo, NIL); +} + +Datum +jsonb_jsonpath_query_wrapped3(PG_FUNCTION_ARGS) +{ + return jsonb_jsonpath_query_wrapped(fcinfo, + makePassingVars(PG_GETARG_JSONB_P(2))); +} + +/* Construct a JSON array from the item list */ +static inline JsonbValue * +wrapItemsInArray(const JsonValueList *items) +{ + JsonbParseState *ps = NULL; + JsonValueListIterator it = { 0 }; + JsonbValue *jbv; + + pushJsonbValue(&ps, WJB_BEGIN_ARRAY, NULL); + + while ((jbv = JsonValueListNext(items, &it))) + { + JsonbValue bin; + + if (jbv->type == jbvBinary && + JsonContainerIsScalar(jbv->val.binary.data)) + JsonbExtractScalar(jbv->val.binary.data, jbv); + + if (jbv->type == jbvObject || jbv->type == jbvArray) + jbv = JsonbWrapInBinary(jbv, &bin); + + pushJsonbValue(&ps, WJB_ELEM, jbv); + } + + return pushJsonbValue(&ps, WJB_END_ARRAY, NULL); +} diff --git a/src/backend/utils/adt/jsonpath_gram.y b/src/backend/utils/adt/jsonpath_gram.y new file mode 100644 index 0000000..7300f76 --- /dev/null +++ b/src/backend/utils/adt/jsonpath_gram.y @@ -0,0 +1,489 @@ +/*------------------------------------------------------------------------- + * + * jsonpath_gram.y + * Grammar definitions for jsonpath datatype + * + * Copyright (c) 2017, PostgreSQL Global Development Group + * + * IDENTIFICATION + * src/backend/utils/adt/jsonpath_gram.y + * + *------------------------------------------------------------------------- + */ + +%{ +#include "postgres.h" + +#include "fmgr.h" +#include "catalog/pg_collation.h" +#include "nodes/pg_list.h" +#include "regex/regex.h" +#include "utils/builtins.h" +#include "utils/jsonpath.h" + +#include "utils/jsonpath_scanner.h" + +/* + * Bison doesn't allocate anything that needs to live across parser calls, + * so we can easily have it use palloc instead of malloc. This prevents + * memory leaks if we error out during parsing. Note this only works with + * bison >= 2.0. However, in bison 1.875 the default is to use alloca() + * if possible, so there's not really much problem anyhow, at least if + * you're building with gcc. + */ +#define YYMALLOC palloc +#define YYFREE pfree + +static JsonPathParseItem* +makeItemType(int type) +{ + JsonPathParseItem* v = palloc(sizeof(*v)); + + v->type = type; + v->next = NULL; + + return v; +} + +static JsonPathParseItem* +makeItemString(string *s) +{ + JsonPathParseItem *v; + + if (s == NULL) + { + v = makeItemType(jpiNull); + } + else + { + v = makeItemType(jpiString); + v->value.string.val = s->val; + v->value.string.len = s->len; + } + + return v; +} + +static JsonPathParseItem* +makeItemVariable(string *s) +{ + JsonPathParseItem *v; + + v = makeItemType(jpiVariable); + v->value.string.val = s->val; + v->value.string.len = s->len; + + return v; +} + +static JsonPathParseItem* +makeItemKey(string *s) +{ + JsonPathParseItem *v; + + v = makeItemString(s); + v->type = jpiKey; + + return v; +} + +static JsonPathParseItem* +makeItemNumeric(string *s) +{ + JsonPathParseItem *v; + + v = makeItemType(jpiNumeric); + v->value.numeric = + DatumGetNumeric(DirectFunctionCall3(numeric_in, CStringGetDatum(s->val), 0, -1)); + + return v; +} + +static JsonPathParseItem* +makeItemBool(bool val) { + JsonPathParseItem *v = makeItemType(jpiBool); + + v->value.boolean = val; + + return v; +} + +static JsonPathParseItem* +makeItemBinary(int type, JsonPathParseItem* la, JsonPathParseItem *ra) +{ + JsonPathParseItem *v = makeItemType(type); + + v->value.args.left = la; + v->value.args.right = ra; + + return v; +} + +static JsonPathParseItem* +makeItemUnary(int type, JsonPathParseItem* a) +{ + JsonPathParseItem *v; + + if (type == jpiPlus && a->type == jpiNumeric && !a->next) + return a; + + if (type == jpiMinus && a->type == jpiNumeric && !a->next) + { + v = makeItemType(jpiNumeric); + v->value.numeric = + DatumGetNumeric(DirectFunctionCall1(numeric_uminus, + NumericGetDatum(a->value.numeric))); + return v; + } + + v = makeItemType(type); + + v->value.arg = a; + + return v; +} + +static JsonPathParseItem* +makeItemList(List *list) +{ + JsonPathParseItem *head, *end; + ListCell *cell = list_head(list); + + head = end = (JsonPathParseItem *) lfirst(cell); + + if (!lnext(cell)) + return head; + + /* append items to the end of already existing list */ + while (end->next) + end = end->next; + + for_each_cell(cell, lnext(cell)) + { + JsonPathParseItem *c = (JsonPathParseItem *) lfirst(cell); + + end->next = c; + end = c; + } + + return head; +} + +static JsonPathParseItem* +makeIndexArray(List *list) +{ + JsonPathParseItem *v = makeItemType(jpiIndexArray); + ListCell *cell; + int i = 0; + + Assert(list_length(list) > 0); + v->value.array.nelems = list_length(list); + + v->value.array.elems = palloc(sizeof(v->value.array.elems[0]) * v->value.array.nelems); + + foreach(cell, list) + { + JsonPathParseItem *jpi = lfirst(cell); + + Assert(jpi->type == jpiSubscript); + + v->value.array.elems[i].from = jpi->value.args.left; + v->value.array.elems[i++].to = jpi->value.args.right; + } + + return v; +} + +static JsonPathParseItem* +makeAny(int first, int last) +{ + JsonPathParseItem *v = makeItemType(jpiAny); + + v->value.anybounds.first = (first > 0) ? first : 0; + v->value.anybounds.last = (last >= 0) ? last : PG_UINT32_MAX; + + return v; +} + +static JsonPathParseItem * +makeItemLikeRegex(JsonPathParseItem *expr, string *pattern, string *flags) +{ + JsonPathParseItem *v = makeItemType(jpiLikeRegex); + int i; + int cflags = REG_ADVANCED; + + v->value.like_regex.expr = expr; + v->value.like_regex.pattern = pattern->val; + v->value.like_regex.patternlen = pattern->len; + v->value.like_regex.flags = 0; + + for (i = 0; flags && i < flags->len; i++) + { + switch (flags->val[i]) + { + case 'i': + v->value.like_regex.flags |= JSP_REGEX_ICASE; + cflags |= REG_ICASE; + break; + case 's': + v->value.like_regex.flags &= ~JSP_REGEX_MLINE; + v->value.like_regex.flags |= JSP_REGEX_SLINE; + cflags |= REG_NEWLINE; + break; + case 'm': + v->value.like_regex.flags &= ~JSP_REGEX_SLINE; + v->value.like_regex.flags |= JSP_REGEX_MLINE; + cflags &= ~REG_NEWLINE; + break; + case 'x': + v->value.like_regex.flags |= JSP_REGEX_WSPACE; + cflags |= REG_EXPANDED; + break; + default: + yyerror(NULL, "unrecognized flag of LIKE_REGEX predicate"); + break; + } + } + + /* check regex validity */ + (void) RE_compile_and_cache(cstring_to_text_with_len(pattern->val, pattern->len), + cflags, DEFAULT_COLLATION_OID); + + return v; +} + +%} + +/* BISON Declarations */ +%pure-parser +%expect 0 +%name-prefix="jsonpath_yy" +%error-verbose +%parse-param {JsonPathParseResult **result} + +%union { + string str; + List *elems; /* list of JsonPathParseItem */ + List *indexs; /* list of integers */ + JsonPathParseItem *value; + JsonPathParseResult *result; + JsonPathItemType optype; + bool boolean; +} + +%token TO_P NULL_P TRUE_P FALSE_P IS_P UNKNOWN_P EXISTS_P +%token IDENT_P STRING_P NUMERIC_P INT_P VARIABLE_P +%token OR_P AND_P NOT_P +%token LESS_P LESSEQUAL_P EQUAL_P NOTEQUAL_P GREATEREQUAL_P GREATER_P +%token ANY_P STRICT_P LAX_P LAST_P STARTS_P WITH_P LIKE_REGEX_P FLAG_P +%token ABS_P SIZE_P TYPE_P FLOOR_P DOUBLE_P CEILING_P DATETIME_P +%token KEYVALUE_P + +%type result + +%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 + +%type accessor_expr + +%type index_list + +%type comp_op method + +%type mode + +%type key_name + + +%left OR_P +%left AND_P +%right NOT_P +%left '+' '-' +%left '*' '/' '%' +%left UMINUS +%nonassoc '(' ')' + +/* Grammar follows */ +%% + +result: + mode expr_or_predicate { + *result = palloc(sizeof(JsonPathParseResult)); + (*result)->expr = $2; + (*result)->lax = $1; + } + | /* EMPTY */ { *result = NULL; } + ; + +expr_or_predicate: + expr { $$ = $1; } + | predicate { $$ = $1; } + ; + +mode: + STRICT_P { $$ = false; } + | LAX_P { $$ = true; } + | /* EMPTY */ { $$ = true; } + ; + +scalar_value: + STRING_P { $$ = makeItemString(&$1); } + | NULL_P { $$ = makeItemString(NULL); } + | TRUE_P { $$ = makeItemBool(true); } + | FALSE_P { $$ = makeItemBool(false); } + | NUMERIC_P { $$ = makeItemNumeric(&$1); } + | INT_P { $$ = makeItemNumeric(&$1); } + | VARIABLE_P { $$ = makeItemVariable(&$1); } + ; + +comp_op: + EQUAL_P { $$ = jpiEqual; } + | NOTEQUAL_P { $$ = jpiNotEqual; } + | LESS_P { $$ = jpiLess; } + | GREATER_P { $$ = jpiGreater; } + | LESSEQUAL_P { $$ = jpiLessOrEqual; } + | GREATEREQUAL_P { $$ = jpiGreaterOrEqual; } + ; + +delimited_predicate: + '(' predicate ')' { $$ = $2; } + | EXISTS_P '(' expr ')' { $$ = makeItemUnary(jpiExists, $3); } + ; + +predicate: + delimited_predicate { $$ = $1; } + | expr comp_op expr { $$ = makeItemBinary($2, $1, $3); } + | predicate AND_P predicate { $$ = makeItemBinary(jpiAnd, $1, $3); } + | predicate OR_P predicate { $$ = makeItemBinary(jpiOr, $1, $3); } + | NOT_P delimited_predicate { $$ = makeItemUnary(jpiNot, $2); } + | '(' predicate ')' IS_P UNKNOWN_P { $$ = makeItemUnary(jpiIsUnknown, $2); } + | expr STARTS_P WITH_P starts_with_initial + { $$ = makeItemBinary(jpiStartsWith, $1, $4); } + | expr LIKE_REGEX_P STRING_P { $$ = makeItemLikeRegex($1, &$3, NULL); }; + | expr LIKE_REGEX_P STRING_P FLAG_P STRING_P + { $$ = makeItemLikeRegex($1, &$3, &$5); }; + ; + +starts_with_initial: + STRING_P { $$ = makeItemString(&$1); } + | VARIABLE_P { $$ = makeItemVariable(&$1); } + ; + +path_primary: + scalar_value { $$ = $1; } + | '$' { $$ = makeItemType(jpiRoot); } + | '@' { $$ = makeItemType(jpiCurrent); } + | LAST_P { $$ = makeItemType(jpiLast); } + ; + +accessor_expr: + path_primary { $$ = list_make1($1); } + | '.' key { $$ = list_make2(makeItemType(jpiCurrent), $2); } + | '(' expr ')' accessor_op { $$ = list_make2($2, $4); } + | '(' predicate ')' accessor_op { $$ = list_make2($2, $4); } + | accessor_expr accessor_op { $$ = lappend($1, $2); } + ; + +expr: + accessor_expr { $$ = makeItemList($1); } + | '(' expr ')' { $$ = $2; } + | '+' expr %prec UMINUS { $$ = makeItemUnary(jpiPlus, $2); } + | '-' expr %prec UMINUS { $$ = makeItemUnary(jpiMinus, $2); } + | expr '+' expr { $$ = makeItemBinary(jpiAdd, $1, $3); } + | expr '-' expr { $$ = makeItemBinary(jpiSub, $1, $3); } + | expr '*' expr { $$ = makeItemBinary(jpiMul, $1, $3); } + | expr '/' expr { $$ = makeItemBinary(jpiDiv, $1, $3); } + | expr '%' expr { $$ = makeItemBinary(jpiMod, $1, $3); } + ; + +index_elem: + expr { $$ = makeItemBinary(jpiSubscript, $1, NULL); } + | expr TO_P expr { $$ = makeItemBinary(jpiSubscript, $1, $3); } + ; + +index_list: + index_elem { $$ = list_make1($1); } + | index_list ',' index_elem { $$ = lappend($1, $3); } + ; + +array_accessor: + '[' '*' ']' { $$ = makeItemType(jpiAnyArray); } + | '[' index_list ']' { $$ = makeIndexArray($2); } + ; + +any_path: + ANY_P { $$ = makeAny(-1, -1); } + | ANY_P '{' INT_P '}' { $$ = makeAny(pg_atoi($3.val, 4, 0), + pg_atoi($3.val, 4, 0)); } + | ANY_P '{' ',' INT_P '}' { $$ = makeAny(-1, pg_atoi($4.val, 4, 0)); } + | ANY_P '{' INT_P ',' '}' { $$ = makeAny(pg_atoi($3.val, 4, 0), -1); } + | ANY_P '{' INT_P ',' INT_P '}' { $$ = makeAny(pg_atoi($3.val, 4, 0), + pg_atoi($5.val, 4, 0)); } + ; + +accessor_op: + '.' key { $$ = $2; } + | '.' '*' { $$ = makeItemType(jpiAnyKey); } + | array_accessor { $$ = $1; } + | '.' any_path { $$ = $2; } + | '.' method '(' ')' { $$ = makeItemType($2); } + | '.' DATETIME_P '(' opt_datetime_template ')' + { $$ = makeItemBinary(jpiDatetime, $4, NULL); } + | '.' DATETIME_P '(' datetime_template ',' expr ')' + { $$ = makeItemBinary(jpiDatetime, $4, $6); } + | '?' '(' predicate ')' { $$ = makeItemUnary(jpiFilter, $3); } + ; + +datetime_template: + STRING_P { $$ = makeItemString(&$1); } + ; + +opt_datetime_template: + datetime_template { $$ = $1; } + | /* EMPTY */ { $$ = NULL; } + ; + +key: + key_name { $$ = makeItemKey(&$1); } + ; + +key_name: + IDENT_P + | STRING_P + | TO_P + | NULL_P + | TRUE_P + | FALSE_P + | IS_P + | UNKNOWN_P + | EXISTS_P + | STRICT_P + | LAX_P + | ABS_P + | SIZE_P + | TYPE_P + | FLOOR_P + | DOUBLE_P + | CEILING_P + | DATETIME_P + | KEYVALUE_P + | LAST_P + | STARTS_P + | WITH_P + | LIKE_REGEX_P + | FLAG_P + ; + +method: + ABS_P { $$ = jpiAbs; } + | SIZE_P { $$ = jpiSize; } + | TYPE_P { $$ = jpiType; } + | FLOOR_P { $$ = jpiFloor; } + | DOUBLE_P { $$ = jpiDouble; } + | CEILING_P { $$ = jpiCeiling; } + | KEYVALUE_P { $$ = jpiKeyValue; } + ; +%% + diff --git a/src/backend/utils/adt/jsonpath_scan.l b/src/backend/utils/adt/jsonpath_scan.l new file mode 100644 index 0000000..aad4aa2 --- /dev/null +++ b/src/backend/utils/adt/jsonpath_scan.l @@ -0,0 +1,557 @@ +/*------------------------------------------------------------------------- + * + * jsonpath_scan.l + * Lexical parser for jsonpath datatype + * + * Copyright (c) 2017, PostgreSQL Global Development Group + * + * IDENTIFICATION + * src/backend/utils/adt/jsonpath_scan.l + * + *------------------------------------------------------------------------- + */ + +%{ +#include "postgres.h" +#include "mb/pg_wchar.h" +#include "nodes/pg_list.h" +#include "utils/jsonpath_scanner.h" + +static string scanstring; + +/* No reason to constrain amount of data slurped */ +/* #define YY_READ_BUF_SIZE 16777216 */ + +/* Handles to the buffer that the lexer uses internally */ +static YY_BUFFER_STATE scanbufhandle; +static char *scanbuf; +static int scanbuflen; + +static void addstring(bool init, char *s, int l); +static void addchar(bool init, char s); +static int checkSpecialVal(void); /* examine scanstring for the special value */ + +static void parseUnicode(char *s, int l); + +/* Avoid exit() on fatal scanner errors (a bit ugly -- see yy_fatal_error) */ +#undef fprintf +#define fprintf(file, fmt, msg) fprintf_to_ereport(fmt, msg) + +static void +fprintf_to_ereport(const char *fmt, const char *msg) +{ + ereport(ERROR, (errmsg_internal("%s", msg))); +} + +#define yyerror jsonpath_yyerror +%} + +%option 8bit +%option never-interactive +%option nodefault +%option noinput +%option nounput +%option noyywrap +%option warn +%option prefix="jsonpath_yy" +%option bison-bridge +%option noyyalloc +%option noyyrealloc +%option noyyfree + +%x xQUOTED +%x xNONQUOTED +%x xVARQUOTED +%x xCOMMENT + +special [\?\%\$\.\[\]\{\}\(\)\|\&\!\=\<\>\@\#\,\*:\-\+\/] +any [^\?\%\$\.\[\]\{\}\(\)\|\&\!\=\<\>\@\#\,\*:\-\+\/\\\" \t\n\r\f] +blank [ \t\n\r\f] +unicode \\u[0-9A-Fa-f]{4} + +%% + +\&\& { return AND_P; } + +\|\| { return OR_P; } + +\! { return NOT_P; } + +\*\* { return ANY_P; } + +\< { return LESS_P; } + +\<\= { return LESSEQUAL_P; } + +\=\= { return EQUAL_P; } + +\<\> { return NOTEQUAL_P; } + +\!\= { return NOTEQUAL_P; } + +\>\= { return GREATEREQUAL_P; } + +\> { return GREATER_P; } + +\${any}+ { + addstring(true, yytext + 1, yyleng - 1); + addchar(false, '\0'); + yylval->str = scanstring; + return VARIABLE_P; + } + +\$\" { + addchar(true, '\0'); + BEGIN xVARQUOTED; + } + +{special} { return *yytext; } + +{blank}+ { /* ignore */ } + +\/\* { + addchar(true, '\0'); + BEGIN xCOMMENT; + } + +[0-9]+(\.[0-9]+)?[eE][+-]?[0-9]+ /* float */ { + addstring(true, yytext, yyleng); + addchar(false, '\0'); + yylval->str = scanstring; + return NUMERIC_P; + } + +\.[0-9]+[eE][+-]?[0-9]+ /* float */ { + addstring(true, yytext, yyleng); + addchar(false, '\0'); + yylval->str = scanstring; + return NUMERIC_P; + } + +([0-9]+)?\.[0-9]+ { + addstring(true, yytext, yyleng); + addchar(false, '\0'); + yylval->str = scanstring; + return NUMERIC_P; + } + +[0-9]+ { + addstring(true, yytext, yyleng); + addchar(false, '\0'); + yylval->str = scanstring; + return INT_P; + } + +{any}+ { + addstring(true, yytext, yyleng); + BEGIN xNONQUOTED; + } + +\" { + addchar(true, '\0'); + BEGIN xQUOTED; + } + +\\ { + yyless(0); + addchar(true, '\0'); + BEGIN xNONQUOTED; + } + +{any}+ { + addstring(false, yytext, yyleng); + } + +{blank}+ { + yylval->str = scanstring; + BEGIN INITIAL; + return checkSpecialVal(); + } + + +\/\* { + yylval->str = scanstring; + BEGIN xCOMMENT; + } + +({special}|\") { + yylval->str = scanstring; + yyless(0); + BEGIN INITIAL; + return checkSpecialVal(); + } + +<> { + yylval->str = scanstring; + BEGIN INITIAL; + return checkSpecialVal(); + } + +\\[\"\\] { addchar(false, yytext[1]); } + +\\b { addchar(false, '\b'); } + +\\f { addchar(false, '\f'); } + +\\n { addchar(false, '\n'); } + +\\r { addchar(false, '\r'); } + +\\t { addchar(false, '\t'); } + +{unicode}+ { parseUnicode(yytext, yyleng); } + +\\u { yyerror(NULL, "Unicode sequence is invalid"); } + +\\. { yyerror(NULL, "Escape sequence is invalid"); } + +\\ { yyerror(NULL, "Unexpected end after backslash"); } + +<> { yyerror(NULL, "Unexpected end of quoted string"); } + +\" { + yylval->str = scanstring; + BEGIN INITIAL; + return STRING_P; + } +\" { + yylval->str = scanstring; + BEGIN INITIAL; + return VARIABLE_P; + } + +[^\\\"]+ { addstring(false, yytext, yyleng); } + +<> { yyterminate(); } + +\*\/ { BEGIN INITIAL; } + +[^\*]+ { } + +\* { } + +<> { yyerror(NULL, "Unexpected end of comment"); } + +%% + +void +jsonpath_yyerror(JsonPathParseResult **result, const char *message) +{ + if (*yytext == YY_END_OF_BUFFER_CHAR) + { + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("bad jsonpath representation"), + /* translator: %s is typically "syntax error" */ + errdetail("%s at end of input", message))); + } + else + { + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("bad jsonpath representation"), + /* translator: first %s is typically "syntax error" */ + errdetail("%s at or near \"%s\"", message, yytext))); + } +} + +typedef struct keyword +{ + int16 len; + bool lowercase; + int val; + char *keyword; +} keyword; + +/* + * Array of key words should be sorted by length and then + * alphabetical order + */ + +static keyword keywords[] = { + { 2, false, IS_P, "is"}, + { 2, false, TO_P, "to"}, + { 3, false, ABS_P, "abs"}, + { 3, false, LAX_P, "lax"}, + { 4, false, FLAG_P, "flag"}, + { 4, false, LAST_P, "last"}, + { 4, true, NULL_P, "null"}, + { 4, false, SIZE_P, "size"}, + { 4, true, TRUE_P, "true"}, + { 4, false, TYPE_P, "type"}, + { 4, false, WITH_P, "with"}, + { 5, true, FALSE_P, "false"}, + { 5, false, FLOOR_P, "floor"}, + { 6, false, DOUBLE_P, "double"}, + { 6, false, EXISTS_P, "exists"}, + { 6, false, STARTS_P, "starts"}, + { 6, false, STRICT_P, "strict"}, + { 7, false, CEILING_P, "ceiling"}, + { 7, false, UNKNOWN_P, "unknown"}, + { 8, false, DATETIME_P, "datetime"}, + { 8, false, KEYVALUE_P, "keyvalue"}, + { 10,false, LIKE_REGEX_P, "like_regex"}, +}; + +static int +checkSpecialVal() +{ + int res = IDENT_P; + int diff; + keyword *StopLow = keywords, + *StopHigh = keywords + lengthof(keywords), + *StopMiddle; + + if (scanstring.len > keywords[lengthof(keywords) - 1].len) + return res; + + while(StopLow < StopHigh) + { + StopMiddle = StopLow + ((StopHigh - StopLow) >> 1); + + if (StopMiddle->len == scanstring.len) + diff = pg_strncasecmp(StopMiddle->keyword, scanstring.val, scanstring.len); + else + diff = StopMiddle->len - scanstring.len; + + if (diff < 0) + StopLow = StopMiddle + 1; + else if (diff > 0) + StopHigh = StopMiddle; + else + { + if (StopMiddle->lowercase) + diff = strncmp(StopMiddle->keyword, scanstring.val, scanstring.len); + + if (diff == 0) + res = StopMiddle->val; + + break; + } + } + + return res; +} + +/* + * Called before any actual parsing is done + */ +static void +jsonpath_scanner_init(const char *str, int slen) +{ + if (slen <= 0) + slen = strlen(str); + + /* + * Might be left over after ereport() + */ + yy_init_globals(); + + /* + * Make a scan buffer with special termination needed by flex. + */ + + scanbuflen = slen; + scanbuf = palloc(slen + 2); + memcpy(scanbuf, str, slen); + scanbuf[slen] = scanbuf[slen + 1] = YY_END_OF_BUFFER_CHAR; + scanbufhandle = yy_scan_buffer(scanbuf, slen + 2); + + BEGIN(INITIAL); +} + + +/* + * Called after parsing is done to clean up after jsonpath_scanner_init() + */ +static void +jsonpath_scanner_finish(void) +{ + yy_delete_buffer(scanbufhandle); + pfree(scanbuf); +} + +static void +addstring(bool init, char *s, int l) { + if (init) { + scanstring.total = 32; + scanstring.val = palloc(scanstring.total); + scanstring.len = 0; + } + + if (s && l) { + while(scanstring.len + l + 1 >= scanstring.total) { + scanstring.total *= 2; + scanstring.val = repalloc(scanstring.val, scanstring.total); + } + + memcpy(scanstring.val + scanstring.len, s, l); + scanstring.len += l; + } +} + +static void +addchar(bool init, char s) { + if (init) + { + scanstring.total = 32; + scanstring.val = palloc(scanstring.total); + scanstring.len = 0; + } + else if(scanstring.len + 1 >= scanstring.total) + { + scanstring.total *= 2; + scanstring.val = repalloc(scanstring.val, scanstring.total); + } + + scanstring.val[ scanstring.len ] = s; + if (s != '\0') + scanstring.len++; +} + +JsonPathParseResult * +parsejsonpath(const char *str, int len) { + JsonPathParseResult *parseresult; + + jsonpath_scanner_init(str, len); + + if (jsonpath_yyparse((void*)&parseresult) != 0) + jsonpath_yyerror(NULL, "bugus input"); + + jsonpath_scanner_finish(); + + return parseresult; +} + +static int +hexval(char c) +{ + if (c >= '0' && c <= '9') + return c - '0'; + if (c >= 'a' && c <= 'f') + return c - 'a' + 0xA; + if (c >= 'A' && c <= 'F') + return c - 'A' + 0xA; + elog(ERROR, "invalid hexadecimal digit"); + return 0; /* not reached */ +} + +/* + * parseUnicode was adopted from json_lex_string() in + * src/backend/utils/adt/json.c + */ +static void +parseUnicode(char *s, int l) +{ + int i, j; + int ch = 0; + int hi_surrogate = -1; + + Assert(l % 6 /* \uXXXX */ == 0); + + for(i = 0; i < l / 6; i++) + { + ch = 0; + + for(j=0; j<4; j++) + ch = (ch << 4) | hexval(s[ i*6 + 2 + j]); + + if (ch >= 0xd800 && ch <= 0xdbff) + { + if (hi_surrogate != -1) + ereport(ERROR, + (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION), + errmsg("invalid input syntax for type jsonpath"), + errdetail("Unicode high surrogate must not follow a high surrogate."))); + hi_surrogate = (ch & 0x3ff) << 10; + continue; + } + else if (ch >= 0xdc00 && ch <= 0xdfff) + { + if (hi_surrogate == -1) + ereport(ERROR, + (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION), + errmsg("invalid input syntax for type jsonpath"), + errdetail("Unicode low surrogate must follow a high surrogate."))); + ch = 0x10000 + hi_surrogate + (ch & 0x3ff); + hi_surrogate = -1; + } + + if (hi_surrogate != -1) + ereport(ERROR, + (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION), + errmsg("invalid input syntax for type jsonpath"), + errdetail("Unicode low surrogate must follow a high surrogate."))); + + /* + * For UTF8, replace the escape sequence by the actual + * utf8 character in lex->strval. Do this also for other + * encodings if the escape designates an ASCII character, + * otherwise raise an error. + */ + + if (ch == 0) + { + /* We can't allow this, since our TEXT type doesn't */ + ereport(ERROR, + (errcode(ERRCODE_UNTRANSLATABLE_CHARACTER), + errmsg("unsupported Unicode escape sequence"), + errdetail("\\u0000 cannot be converted to text."))); + } + else if (GetDatabaseEncoding() == PG_UTF8) + { + char utf8str[5]; + int utf8len; + + unicode_to_utf8(ch, (unsigned char *) utf8str); + utf8len = pg_utf_mblen((unsigned char *) utf8str); + addstring(false, utf8str, utf8len); + } + else if (ch <= 0x007f) + { + /* + * This is the only way to designate things like a + * form feed character in JSON, so it's useful in all + * encodings. + */ + addchar(false, (char) ch); + } + else + { + ereport(ERROR, + (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION), + errmsg("invalid input syntax for type jsonpath"), + errdetail("Unicode escape values cannot be used for code point values above 007F when the server encoding is not UTF8."))); + } + + hi_surrogate = -1; + } +} + +/* + * Interface functions to make flex use palloc() instead of malloc(). + * It'd be better to make these static, but flex insists otherwise. + */ + +void * +jsonpath_yyalloc(yy_size_t bytes) +{ + return palloc(bytes); +} + +void * +jsonpath_yyrealloc(void *ptr, yy_size_t bytes) +{ + if (ptr) + return repalloc(ptr, bytes); + else + return palloc(bytes); +} + +void +jsonpath_yyfree(void *ptr) +{ + if (ptr) + pfree(ptr); +} + diff --git a/src/backend/utils/adt/regexp.c b/src/backend/utils/adt/regexp.c index 5025a44..dfe8993 100644 --- a/src/backend/utils/adt/regexp.c +++ b/src/backend/utils/adt/regexp.c @@ -129,7 +129,7 @@ static Datum build_regexp_split_result(regexp_matches_ctx *splitctx); * Pattern is given in the database encoding. We internally convert to * an array of pg_wchar, which is what Spencer's regex package wants. */ -static regex_t * +regex_t * RE_compile_and_cache(text *text_re, int cflags, Oid collation) { int text_re_len = VARSIZE_ANY_EXHDR(text_re); @@ -335,7 +335,7 @@ RE_execute(regex_t *re, char *dat, int dat_len, * Both pattern and data are given in the database encoding. We internally * convert to array of pg_wchar which is what Spencer's regex package wants. */ -static bool +bool RE_compile_and_execute(text *text_re, char *dat, int dat_len, int cflags, Oid collation, int nmatch, regmatch_t *pmatch) diff --git a/src/backend/utils/errcodes.txt b/src/backend/utils/errcodes.txt index 9871d1e..27948ec 100644 --- a/src/backend/utils/errcodes.txt +++ b/src/backend/utils/errcodes.txt @@ -206,6 +206,22 @@ Section: Class 22 - Data Exception 2200N E ERRCODE_INVALID_XML_CONTENT invalid_xml_content 2200S E ERRCODE_INVALID_XML_COMMENT invalid_xml_comment 2200T E ERRCODE_INVALID_XML_PROCESSING_INSTRUCTION invalid_xml_processing_instruction +22030 E ERRCODE_DUPLICATE_JSON_OBJECT_KEY_VALUE duplicate_json_object_key_value +22031 E ERRCODE_INVALID_ARGUMENT_FOR_JSON_DATETIME_FUNCTION invalid_argument_for_json_datetime_function +22032 E ERRCODE_INVALID_JSON_TEXT invalid_json_text +22033 E ERRCODE_INVALID_JSON_SUBSCRIPT invalid_json_subscript +22034 E ERRCODE_MORE_THAN_ONE_JSON_ITEM more_than_one_json_item +22035 E ERRCODE_NO_JSON_ITEM no_json_item +22036 E ERRCODE_NON_NUMERIC_JSON_ITEM non_numeric_json_item +22037 E ERRCODE_NON_UNIQUE_KEYS_IN_JSON_OBJECT non_unique_keys_in_json_object +22038 E ERRCODE_SINGLETON_JSON_ITEM_REQUIRED singleton_json_item_required +22039 E ERRCODE_JSON_ARRAY_NOT_FOUND json_array_not_found +2203A E ERRCODE_JSON_MEMBER_NOT_FOUND json_member_not_found +2203B E ERRCODE_JSON_NUMBER_NOT_FOUND json_number_not_found +2203C E ERRCODE_JSON_OBJECT_NOT_FOUND object_not_found +2203F E ERRCODE_JSON_SCALAR_REQUIRED json_scalar_required +2203D E ERRCODE_TOO_MANY_JSON_ARRAY_ELEMENTS too_many_json_array_elements +2203E E ERRCODE_TOO_MANY_JSON_OBJECT_MEMBERS too_many_json_object_members Section: Class 23 - Integrity Constraint Violation diff --git a/src/include/catalog/pg_operator.h b/src/include/catalog/pg_operator.h index e74f963..af4dedc 100644 --- a/src/include/catalog/pg_operator.h +++ b/src/include/catalog/pg_operator.h @@ -1853,5 +1853,13 @@ DATA(insert OID = 3286 ( "-" PGNSP PGUID b f f 3802 23 3802 0 0 3303 - - )); DESCR("delete array element"); DATA(insert OID = 3287 ( "#-" PGNSP PGUID b f f 3802 1009 3802 0 0 jsonb_delete_path - - )); DESCR("delete path"); +DATA(insert OID = 6075 ( "@*" PGNSP PGUID b f f 3802 6050 3802 0 0 6055 - - )); +DESCR("jsonpath items"); +DATA(insert OID = 6076 ( "@?" PGNSP PGUID b f f 3802 6050 16 0 0 6054 contsel contjoinsel )); +DESCR("jsonpath exists"); +DATA(insert OID = 6107 ( "@~" PGNSP PGUID b f f 3802 6050 16 0 0 6073 contsel contjoinsel )); +DESCR("jsonpath predicate"); +DATA(insert OID = 6122 ( "@#" PGNSP PGUID b f f 3802 6050 3802 0 0 6124 - - )); +DESCR("jsonpath items wrapped"); #endif /* PG_OPERATOR_H */ diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index c00d055..8056652 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -5575,6 +5575,28 @@ DESCR("list of files in the WAL directory"); DATA(insert OID = 5028 ( satisfies_hash_partition PGNSP PGUID 12 1 0 2276 0 f f f f f f i s 4 0 16 "26 23 23 2276" _null_ "{i,i,i,v}" _null_ _null_ _null_ satisfies_hash_partition _null_ _null_ _null_ )); DESCR("hash partition CHECK constraint"); +/* jsonpath */ +DATA(insert OID = 6052 ( jsonpath_in PGNSP PGUID 12 1 0 0 0 f f f f t f i s 1 0 6050 "2275" _null_ _null_ _null_ _null_ _null_ jsonpath_in _null_ _null_ _null_ )); +DESCR("I/O"); +DATA(insert OID = 6053 ( jsonpath_out PGNSP PGUID 12 1 0 0 0 f f f f t f i s 1 0 2275 "6050" _null_ _null_ _null_ _null_ _null_ jsonpath_out _null_ _null_ _null_ )); +DESCR("I/O"); +DATA(insert OID = 6054 ( jsonpath_exists PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 16 "3802 6050" _null_ _null_ _null_ _null_ _null_ jsonb_jsonpath_exists2 _null_ _null_ _null_ )); +DESCR("implementation of @? operator"); +DATA(insert OID = 6055 ( jsonpath_query PGNSP PGUID 12 1 1000 0 0 f f f f t t i s 2 0 3802 "3802 6050" _null_ _null_ _null_ _null_ _null_ jsonb_jsonpath_query2 _null_ _null_ _null_ )); +DESCR("implementation of @* operator"); +DATA(insert OID = 6124 ( jsonpath_query_wrapped PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 3802 "3802 6050" _null_ _null_ _null_ _null_ _null_ jsonb_jsonpath_query_wrapped2 _null_ _null_ _null_ )); +DESCR("implementation of @# operator"); +DATA(insert OID = 6056 ( jsonpath_exists PGNSP PGUID 12 1 0 0 0 f f f f t f i s 3 0 16 "3802 6050 3802" _null_ _null_ _null_ _null_ _null_ jsonb_jsonpath_exists3 _null_ _null_ _null_ )); +DESCR("jsonpath exists test"); +DATA(insert OID = 6057 ( jsonpath_query PGNSP PGUID 12 1 1000 0 0 f f f f t t i s 3 0 3802 "3802 6050 3802" _null_ _null_ _null_ _null_ _null_ jsonb_jsonpath_query3 _null_ _null_ _null_ )); +DESCR("jsonpath object test"); +DATA(insert OID = 6125 ( jsonpath_query_wrapped PGNSP PGUID 12 1 0 0 0 f f f f t f i s 3 0 3802 "3802 6050 3802" _null_ _null_ _null_ _null_ _null_ jsonb_jsonpath_query_wrapped3 _null_ _null_ _null_ )); +DESCR("jsonpath query with conditional wrapper"); +DATA(insert OID = 6073 ( jsonpath_predicate PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 16 "3802 6050" _null_ _null_ _null_ _null_ _null_ jsonb_jsonpath_predicate2 _null_ _null_ _null_ )); +DESCR("implementation of @~ operator"); +DATA(insert OID = 6074 ( jsonpath_predicate PGNSP PGUID 12 1 0 0 0 f f f f t f i s 3 0 16 "3802 6050 3802" _null_ _null_ _null_ _null_ _null_ jsonb_jsonpath_predicate3 _null_ _null_ _null_ )); +DESCR("jsonpath predicate test"); + /* * Symbolic values for provolatile column: these indicate whether the result * of a function is dependent *only* on the values of its explicit arguments, diff --git a/src/include/catalog/pg_type.h b/src/include/catalog/pg_type.h index 5b5b121..5103ef9 100644 --- a/src/include/catalog/pg_type.h +++ b/src/include/catalog/pg_type.h @@ -638,6 +638,12 @@ DESCR("Binary JSON"); #define JSONBOID 3802 DATA(insert OID = 3807 ( _jsonb PGNSP PGUID -1 f b A f t \054 0 3802 0 array_in array_out array_recv array_send - - array_typanalyze i x f 0 -1 0 0 _null_ _null_ _null_ )); +/* jsonpath */ +DATA(insert OID = 6050 ( jsonpath PGNSP PGUID -1 f b U f t \054 0 0 6051 jsonpath_in jsonpath_out - - - - - i x f 0 -1 0 0 _null_ _null_ _null_ )); +DESCR("JSON Path"); +#define JSONPATHOID 6050 +DATA(insert OID = 6051 ( _jsonpath PGNSP PGUID -1 f b A f t \054 0 6050 0 array_in array_out array_recv array_send - - array_typanalyze i x f 0 -1 0 0 _null_ _null_ _null_ )); + DATA(insert OID = 2970 ( txid_snapshot PGNSP PGUID -1 f b U f t \054 0 0 2949 txid_snapshot_in txid_snapshot_out txid_snapshot_recv txid_snapshot_send - - - d x f 0 -1 0 0 _null_ _null_ _null_ )); DESCR("txid snapshot"); DATA(insert OID = 2949 ( _txid_snapshot PGNSP PGUID -1 f b A f t \054 0 2970 0 array_in array_out array_recv array_send - - array_typanalyze d x f 0 -1 0 0 _null_ _null_ _null_ )); diff --git a/src/include/lib/stringinfo.h b/src/include/lib/stringinfo.h index 8551237..ff1ecb2 100644 --- a/src/include/lib/stringinfo.h +++ b/src/include/lib/stringinfo.h @@ -157,4 +157,10 @@ extern void appendBinaryStringInfoNT(StringInfo str, */ extern void enlargeStringInfo(StringInfo str, int needed); +/*------------------------ + * alignStringInfoInt + * Add padding zero bytes to align StringInfo + */ +extern void alignStringInfoInt(StringInfo buf); + #endif /* STRINGINFO_H */ diff --git a/src/include/regex/regex.h b/src/include/regex/regex.h index 27fdc09..4b1e80d 100644 --- a/src/include/regex/regex.h +++ b/src/include/regex/regex.h @@ -173,4 +173,9 @@ extern int pg_regprefix(regex_t *, pg_wchar **, size_t *); extern void pg_regfree(regex_t *); extern size_t pg_regerror(int, const regex_t *, char *, size_t); +extern regex_t *RE_compile_and_cache(text *text_re, int cflags, Oid collation); +extern bool RE_compile_and_execute(text *text_re, char *dat, int dat_len, + int cflags, Oid collation, + int nmatch, regmatch_t *pmatch); + #endif /* _REGEX_H_ */ diff --git a/src/include/utils/formatting.h b/src/include/utils/formatting.h index 208cc00..6db5b3f 100644 --- a/src/include/utils/formatting.h +++ b/src/include/utils/formatting.h @@ -28,7 +28,7 @@ extern char *asc_tolower(const char *buff, size_t nbytes); extern char *asc_toupper(const char *buff, size_t nbytes); extern char *asc_initcap(const char *buff, size_t nbytes); -extern Datum to_datetime(text *date_txt, const char *fmt, int fmt_len, - bool strict, Oid *typid, int32 *typmod); +extern Datum to_datetime(text *datetxt, const char *fmt, int fmt_len, char *tzn, + bool strict, Oid *typid, int32 *typmod, int *tz); #endif diff --git a/src/include/utils/jsonapi.h b/src/include/utils/jsonapi.h index e39572e..4629c45 100644 --- a/src/include/utils/jsonapi.h +++ b/src/include/utils/jsonapi.h @@ -147,6 +147,6 @@ extern Jsonb *transform_jsonb_string_values(Jsonb *jsonb, void *action_state, extern text *transform_json_string_values(text *json, void *action_state, JsonTransformStringValuesAction transform_action); -extern char *JsonEncodeDateTime(char *buf, Datum value, Oid typid); +extern char *JsonEncodeDateTime(char *buf, Datum value, Oid typid, int *tz); #endif /* JSONAPI_H */ diff --git a/src/include/utils/jsonb.h b/src/include/utils/jsonb.h index 27873d4..b306b26 100644 --- a/src/include/utils/jsonb.h +++ b/src/include/utils/jsonb.h @@ -66,8 +66,10 @@ typedef enum /* Convenience macros */ #define DatumGetJsonbP(d) ((Jsonb *) PG_DETOAST_DATUM(d)) +#define DatumGetJsonbPCopy(d) ((Jsonb *) PG_DETOAST_DATUM_COPY(d)) #define JsonbPGetDatum(p) PointerGetDatum(p) #define PG_GETARG_JSONB_P(x) DatumGetJsonbP(PG_GETARG_DATUM(x)) +#define PG_GETARG_JSONB_P_COPY(x) DatumGetJsonbPCopy(PG_GETARG_DATUM(x)) #define PG_RETURN_JSONB_P(x) PG_RETURN_POINTER(x) typedef struct JsonbPair JsonbPair; @@ -236,7 +238,9 @@ enum jbvType jbvArray = 0x10, jbvObject, /* Binary (i.e. struct Jsonb) jbvArray/jbvObject */ - jbvBinary + jbvBinary, + /* Virtual types */ + jbvDatetime = 0x20, }; /* @@ -277,11 +281,20 @@ struct JsonbValue int len; JsonbContainer *data; } binary; /* Array or object, in on-disk format */ + + struct + { + Datum value; + Oid typid; + int32 typmod; + int tz; + } datetime; } val; }; -#define IsAJsonbScalar(jsonbval) ((jsonbval)->type >= jbvNull && \ - (jsonbval)->type <= jbvBool) +#define IsAJsonbScalar(jsonbval) (((jsonbval)->type >= jbvNull && \ + (jsonbval)->type <= jbvBool) || \ + (jsonbval)->type == jbvDatetime) /* * Key/value pair within an Object. @@ -379,5 +392,6 @@ extern char *JsonbToCString(StringInfo out, JsonbContainer *in, extern char *JsonbToCStringIndent(StringInfo out, JsonbContainer *in, int estimated_len); +extern JsonbValue *JsonbExtractScalar(JsonbContainer *jbc, JsonbValue *res); #endif /* __JSONB_H__ */ diff --git a/src/include/utils/jsonpath.h b/src/include/utils/jsonpath.h new file mode 100644 index 0000000..d06bb14 --- /dev/null +++ b/src/include/utils/jsonpath.h @@ -0,0 +1,270 @@ +/*------------------------------------------------------------------------- + * + * jsonpath.h + * Definitions of jsonpath datatype + * + * Copyright (c) 2017, PostgreSQL Global Development Group + * + * IDENTIFICATION + * src/include/utils/jsonpath.h + * + *------------------------------------------------------------------------- + */ + +#ifndef JSONPATH_H +#define JSONPATH_H + +#include "fmgr.h" +#include "utils/jsonb.h" +#include "nodes/pg_list.h" + +typedef struct +{ + int32 vl_len_;/* varlena header (do not touch directly!) */ + uint32 header; /* just version, other bits are reservedfor future use */ + char data[FLEXIBLE_ARRAY_MEMBER]; +} JsonPath; + +#define JSONPATH_VERSION (0x01) +#define JSONPATH_LAX (0x80000000) +#define JSONPATH_HDRSZ (offsetof(JsonPath, data)) + +#define DatumGetJsonPathP(d) ((JsonPath *) DatumGetPointer(PG_DETOAST_DATUM(d))) +#define DatumGetJsonPathPCopy(d) ((JsonPath *) DatumGetPointer(PG_DETOAST_DATUM_COPY(d))) +#define PG_GETARG_JSONPATH_P(x) DatumGetJsonPathP(PG_GETARG_DATUM(x)) +#define PG_GETARG_JSONPATH_P_COPY(x) DatumGetJsonPathPCopy(PG_GETARG_DATUM(x)) +#define PG_RETURN_JSONPATH_P(p) PG_RETURN_POINTER(p) + +/* + * All node's type of jsonpath expression + */ +typedef enum JsonPathItemType { + jpiNull = jbvNull, + jpiString = jbvString, + jpiNumeric = jbvNumeric, + jpiBool = jbvBool, + jpiAnd, + jpiOr, + jpiNot, + jpiIsUnknown, + jpiEqual, + jpiNotEqual, + jpiLess, + jpiGreater, + jpiLessOrEqual, + jpiGreaterOrEqual, + jpiAdd, + jpiSub, + jpiMul, + jpiDiv, + jpiMod, + jpiPlus, + jpiMinus, + jpiAnyArray, + jpiAnyKey, + jpiIndexArray, + jpiAny, + jpiKey, + jpiCurrent, + jpiRoot, + jpiVariable, + jpiFilter, + jpiExists, + jpiType, + jpiSize, + jpiAbs, + jpiFloor, + jpiCeiling, + jpiDouble, + jpiDatetime, + jpiKeyValue, + jpiSubscript, + jpiLast, + jpiStartsWith, + jpiLikeRegex, +} JsonPathItemType; + +/* XQuery regex mode flags for LIKE_REGEX predicate */ +#define JSP_REGEX_ICASE 0x01 /* i flag, case insensitive */ +#define JSP_REGEX_SLINE 0x02 /* s flag, single-line mode */ +#define JSP_REGEX_MLINE 0x04 /* m flag, multi-line mode */ +#define JSP_REGEX_WSPACE 0x08 /* x flag, expanded syntax */ + +/* + * Support functions to parse/construct binary value. + * Unlike many other representation of expression the first/main + * node is not an operation but left operand of expression. That + * allows to implement cheep follow-path descending in jsonb + * structure and then execute operator with right operand + */ + +typedef struct JsonPathItem { + JsonPathItemType type; + + /* position form base to next node */ + int32 nextPos; + + /* + * pointer into JsonPath value to current node, all + * positions of current are relative to this base + */ + char *base; + + union { + /* classic operator with two operands: and, or etc */ + struct { + int32 left; + int32 right; + } args; + + /* any unary operation */ + int32 arg; + + /* storage for jpiIndexArray: indexes of array */ + struct { + int32 nelems; + struct { + int32 from; + int32 to; + } *elems; + } array; + + /* jpiAny: levels */ + struct { + uint32 first; + uint32 last; + } anybounds; + + struct { + char *data; /* for bool, numeric and string/key */ + int32 datalen; /* filled only for string/key */ + } value; + + struct { + int32 expr; + char *pattern; + int32 patternlen; + uint32 flags; + } like_regex; + } content; +} JsonPathItem; + +#define jspHasNext(jsp) ((jsp)->nextPos > 0) + +extern void jspInit(JsonPathItem *v, JsonPath *js); +extern void jspInitByBuffer(JsonPathItem *v, char *base, int32 pos); +extern bool jspGetNext(JsonPathItem *v, JsonPathItem *a); +extern void jspGetArg(JsonPathItem *v, JsonPathItem *a); +extern void jspGetLeftArg(JsonPathItem *v, JsonPathItem *a); +extern void jspGetRightArg(JsonPathItem *v, JsonPathItem *a); +extern Numeric jspGetNumeric(JsonPathItem *v); +extern bool jspGetBool(JsonPathItem *v); +extern char * jspGetString(JsonPathItem *v, int32 *len); +extern bool jspGetArraySubscript(JsonPathItem *v, JsonPathItem *from, + JsonPathItem *to, int i); + +/* + * Parsing + */ + +typedef struct JsonPathParseItem JsonPathParseItem; + +struct JsonPathParseItem { + JsonPathItemType type; + JsonPathParseItem *next; /* next in path */ + + union { + + /* classic operator with two operands: and, or etc */ + struct { + JsonPathParseItem *left; + JsonPathParseItem *right; + } args; + + /* any unary operation */ + JsonPathParseItem *arg; + + /* storage for jpiIndexArray: indexes of array */ + struct { + int nelems; + struct + { + JsonPathParseItem *from; + JsonPathParseItem *to; + } *elems; + } array; + + /* jpiAny: levels */ + struct { + uint32 first; + uint32 last; + } anybounds; + + struct { + JsonPathParseItem *expr; + char *pattern; /* could not be not null-terminated */ + uint32 patternlen; + uint32 flags; + } like_regex; + + /* scalars */ + Numeric numeric; + bool boolean; + struct { + uint32 len; + char *val; /* could not be not null-terminated */ + } string; + } value; +}; + +typedef struct JsonPathParseResult +{ + JsonPathParseItem *expr; + bool lax; +} JsonPathParseResult; + +extern JsonPathParseResult* parsejsonpath(const char *str, int len); + +/* + * Evaluation of jsonpath + */ + +typedef enum JsonPathExecStatus +{ + jperOk = 0, + jperError, + jperFatalError, + jperNotFound +} JsonPathExecStatus; + +typedef uint64 JsonPathExecResult; + +#define jperStatus(jper) ((JsonPathExecStatus)(uint32)(jper)) +#define jperIsError(jper) (jperStatus(jper) == jperError) +#define jperGetError(jper) ((uint32)((jper) >> 32)) +#define jperMakeError(err) (((uint64)(err) << 32) | jperError) + +typedef Datum (*JsonPathVariable_cb)(void *, bool *); + +typedef struct JsonPathVariable { + text *varName; + Oid typid; + int32 typmod; + JsonPathVariable_cb cb; + void *cb_arg; +} JsonPathVariable; + + + +typedef struct JsonValueList +{ + JsonbValue *singleton; + List *list; +} JsonValueList; + +JsonPathExecResult executeJsonPath(JsonPath *path, + List *vars, /* list of JsonPathVariable */ + Jsonb *json, + JsonValueList *foundJson); + +#endif diff --git a/src/include/utils/jsonpath_scanner.h b/src/include/utils/jsonpath_scanner.h new file mode 100644 index 0000000..1c8447f --- /dev/null +++ b/src/include/utils/jsonpath_scanner.h @@ -0,0 +1,30 @@ +/*------------------------------------------------------------------------- + * + * jsonpath_scanner.h + * jsonpath scanner & parser support + * + * Portions Copyright (c) 1996-2017, PostgreSQL Global Development Group + * + * src/include/utils/jsonpath_scanner.h + * + *------------------------------------------------------------------------- + */ + +#ifndef JSONPATH_SCANNER_H +#define JSONPATH_SCANNER_H + +/* struct string is shared between scan and gram */ +typedef struct string { + char *val; + int len; + int total; +} string; + +#include "utils/jsonpath.h" +#include "utils/jsonpath_gram.h" + +/* flex 2.5.4 doesn't bother with a decl for this */ +extern int jsonpath_yylex(YYSTYPE * yylval_param); +extern void jsonpath_yyerror(JsonPathParseResult **result, const char *message); + +#endif diff --git a/src/test/regress/expected/jsonb_jsonpath.out b/src/test/regress/expected/jsonb_jsonpath.out new file mode 100644 index 0000000..033e5af --- /dev/null +++ b/src/test/regress/expected/jsonb_jsonpath.out @@ -0,0 +1,1697 @@ +select jsonb '{"a": 12}' @? '$.a.b'; + ?column? +---------- + f +(1 row) + +select jsonb '{"a": 12}' @? '$.b'; + ?column? +---------- + f +(1 row) + +select jsonb '{"a": {"a": 12}}' @? '$.a.a'; + ?column? +---------- + t +(1 row) + +select jsonb '{"a": {"a": 12}}' @? '$.*.a'; + ?column? +---------- + t +(1 row) + +select jsonb '{"b": {"a": 12}}' @? '$.*.a'; + ?column? +---------- + t +(1 row) + +select jsonb '{}' @? '$.*'; + ?column? +---------- + f +(1 row) + +select jsonb '{"a": 1}' @? '$.*'; + ?column? +---------- + t +(1 row) + +select jsonb '{"a": {"b": 1}}' @? 'lax $.**{1}'; + ?column? +---------- + t +(1 row) + +select jsonb '{"a": {"b": 1}}' @? 'lax $.**{2}'; + ?column? +---------- + t +(1 row) + +select jsonb '{"a": {"b": 1}}' @? 'lax $.**{3}'; + ?column? +---------- + f +(1 row) + +select jsonb '[]' @? '$[*]'; + ?column? +---------- + f +(1 row) + +select jsonb '[1]' @? '$[*]'; + ?column? +---------- + t +(1 row) + +select jsonb '[1]' @? '$[1]'; + ?column? +---------- + f +(1 row) + +select jsonb '[1]' @? 'strict $[1]'; + ?column? +---------- + +(1 row) + +select jsonb '[1]' @* 'strict $[1]'; +ERROR: Invalid SQL/JSON subscript +select jsonb '[1]' @? '$[0]'; + ?column? +---------- + t +(1 row) + +select jsonb '[1]' @? '$[0.3]'; + ?column? +---------- + t +(1 row) + +select jsonb '[1]' @? '$[0.5]'; + ?column? +---------- + t +(1 row) + +select jsonb '[1]' @? '$[0.9]'; + ?column? +---------- + t +(1 row) + +select jsonb '[1]' @? '$[1.2]'; + ?column? +---------- + f +(1 row) + +select jsonb '[1]' @? 'strict $[1.2]'; + ?column? +---------- + +(1 row) + +select jsonb '{"a": [1,2,3], "b": [3,4,5]}' @? '$ ? (@.a[*] > @.b[*])'; + ?column? +---------- + f +(1 row) + +select jsonb '{"a": [1,2,3], "b": [3,4,5]}' @? '$ ? (@.a[*] >= @.b[*])'; + ?column? +---------- + t +(1 row) + +select jsonb '{"a": [1,2,3], "b": [3,4,"5"]}' @? '$ ? (@.a[*] >= @.b[*])'; + ?column? +---------- + t +(1 row) + +select jsonb '{"a": [1,2,3], "b": [3,4,"5"]}' @? 'strict $ ? (@.a[*] >= @.b[*])'; + ?column? +---------- + f +(1 row) + +select jsonb '{"a": [1,2,3], "b": [3,4,null]}' @? '$ ? (@.a[*] >= @.b[*])'; + ?column? +---------- + t +(1 row) + +select jsonb '1' @? '$ ? ((@ == "1") is unknown)'; + ?column? +---------- + t +(1 row) + +select jsonb '1' @? '$ ? ((@ == 1) is unknown)'; + ?column? +---------- + f +(1 row) + +select jsonb '[{"a": 1}, {"a": 2}]' @? '$[0 to 1] ? (@.a > 1)'; + ?column? +---------- + t +(1 row) + +select jsonb '{"a": 12, "b": {"a": 13}}' @* '$.a'; + ?column? +---------- + 12 +(1 row) + +select jsonb '{"a": 12, "b": {"a": 13}}' @* '$.b'; + ?column? +----------- + {"a": 13} +(1 row) + +select jsonb '{"a": 12, "b": {"a": 13}}' @* '$.*'; + ?column? +----------- + 12 + {"a": 13} +(2 rows) + +select jsonb '{"a": 12, "b": {"a": 13}}' @* 'lax $.*.a'; + ?column? +---------- + 13 +(1 row) + +select jsonb '[12, {"a": 13}, {"b": 14}]' @* 'lax $[*].a'; + ?column? +---------- + 13 +(1 row) + +select jsonb '[12, {"a": 13}, {"b": 14}]' @* 'lax $[*].*'; + ?column? +---------- + 13 + 14 +(2 rows) + +select jsonb '[12, {"a": 13}, {"b": 14}]' @* 'lax $[0].a'; + ?column? +---------- +(0 rows) + +select jsonb '[12, {"a": 13}, {"b": 14}]' @* 'lax $[1].a'; + ?column? +---------- + 13 +(1 row) + +select jsonb '[12, {"a": 13}, {"b": 14}]' @* 'lax $[2].a'; + ?column? +---------- +(0 rows) + +select jsonb '[12, {"a": 13}, {"b": 14}]' @* 'lax $[0,1].a'; + ?column? +---------- + 13 +(1 row) + +select jsonb '[12, {"a": 13}, {"b": 14}]' @* 'lax $[0 to 10].a'; + ?column? +---------- + 13 +(1 row) + +select jsonb '[12, {"a": 13}, {"b": 14}, "ccc", true]' @* '$[2.5 - 1 to @.size() - 2]'; + ?column? +----------- + {"a": 13} + {"b": 14} + "ccc" +(3 rows) + +select jsonb '1' @* 'lax $[0]'; + ?column? +---------- + 1 +(1 row) + +select jsonb '1' @* 'lax $[*]'; + ?column? +---------- + 1 +(1 row) + +select jsonb '[1]' @* 'lax $[0]'; + ?column? +---------- + 1 +(1 row) + +select jsonb '[1]' @* 'lax $[*]'; + ?column? +---------- + 1 +(1 row) + +select jsonb '[1,2,3]' @* 'lax $[*]'; + ?column? +---------- + 1 + 2 + 3 +(3 rows) + +select jsonb '[]' @* '$[last]'; + ?column? +---------- +(0 rows) + +select jsonb '[]' @* 'strict $[last]'; +ERROR: Invalid SQL/JSON subscript +select jsonb '[1]' @* '$[last]'; + ?column? +---------- + 1 +(1 row) + +select jsonb '[1,2,3]' @* '$[last]'; + ?column? +---------- + 3 +(1 row) + +select jsonb '[1,2,3]' @* '$[last - 1]'; + ?column? +---------- + 2 +(1 row) + +select jsonb '[1,2,3]' @* '$[last ? (@.type() == "number")]'; + ?column? +---------- + 3 +(1 row) + +select jsonb '[1,2,3]' @* '$[last ? (@.type() == "string")]'; +ERROR: Invalid SQL/JSON subscript +select * from jsonpath_query(jsonb '{"a": 10}', '$'); + jsonpath_query +---------------- + {"a": 10} +(1 row) + +select * from jsonpath_query(jsonb '{"a": 10}', '$ ? (.a < $value)'); +ERROR: could not find 'value' passed variable +select * from jsonpath_query(jsonb '{"a": 10}', '$ ? (.a < $value)', '{"value" : 13}'); + jsonpath_query +---------------- + {"a": 10} +(1 row) + +select * from jsonpath_query(jsonb '{"a": 10}', '$ ? (.a < $value)', '{"value" : 8}'); + jsonpath_query +---------------- +(0 rows) + +select * from jsonpath_query(jsonb '{"a": 10}', '$.a ? (@ < $value)', '{"value" : 13}'); + jsonpath_query +---------------- + 10 +(1 row) + +select * from jsonpath_query(jsonb '[10,11,12,13,14,15]', '$[*] ? (@ < $value)', '{"value" : 13}'); + jsonpath_query +---------------- + 10 + 11 + 12 +(3 rows) + +select * from jsonpath_query(jsonb '[10,11,12,13,14,15]', '$[0,1] ? (@ < $value)', '{"value" : 13}'); + jsonpath_query +---------------- + 10 + 11 +(2 rows) + +select * from jsonpath_query(jsonb '[10,11,12,13,14,15]', '$[0 to 2] ? (@ < $value)', '{"value" : 15}'); + jsonpath_query +---------------- + 10 + 11 + 12 +(3 rows) + +select * from jsonpath_query(jsonb '[1,"1",2,"2",null]', '$[*] ? (@ == "1")'); + jsonpath_query +---------------- + "1" +(1 row) + +select * from jsonpath_query(jsonb '[1,"1",2,"2",null]', '$[*] ? (@ == $value)', '{"value" : "1"}'); + jsonpath_query +---------------- + "1" +(1 row) + +select * from jsonpath_query(jsonb '[1, "2", null]', '$[*] ? (@ != null)'); + jsonpath_query +---------------- + 1 + "2" +(2 rows) + +select * from jsonpath_query(jsonb '[1, "2", null]', '$[*] ? (@ == null)'); + jsonpath_query +---------------- + null +(1 row) + +select jsonb '{"a": {"b": 1}}' @* 'lax $.**'; + ?column? +----------------- + {"a": {"b": 1}} + {"b": 1} + 1 +(3 rows) + +select jsonb '{"a": {"b": 1}}' @* 'lax $.**{1}'; + ?column? +---------- + {"b": 1} +(1 row) + +select jsonb '{"a": {"b": 1}}' @* 'lax $.**{1,}'; + ?column? +---------- + {"b": 1} + 1 +(2 rows) + +select jsonb '{"a": {"b": 1}}' @* 'lax $.**{2}'; + ?column? +---------- + 1 +(1 row) + +select jsonb '{"a": {"b": 1}}' @* 'lax $.**{2,}'; + ?column? +---------- + 1 +(1 row) + +select jsonb '{"a": {"b": 1}}' @* 'lax $.**{3,}'; + ?column? +---------- +(0 rows) + +select jsonb '{"a": {"b": 1}}' @* 'lax $.**.b ? (@ > 0)'; + ?column? +---------- + 1 +(1 row) + +select jsonb '{"a": {"b": 1}}' @* 'lax $.**{0}.b ? (@ > 0)'; + ?column? +---------- +(0 rows) + +select jsonb '{"a": {"b": 1}}' @* 'lax $.**{1}.b ? (@ > 0)'; + ?column? +---------- + 1 +(1 row) + +select jsonb '{"a": {"b": 1}}' @* 'lax $.**{0,}.b ? (@ > 0)'; + ?column? +---------- + 1 +(1 row) + +select jsonb '{"a": {"b": 1}}' @* 'lax $.**{1,}.b ? (@ > 0)'; + ?column? +---------- + 1 +(1 row) + +select jsonb '{"a": {"b": 1}}' @* 'lax $.**{1,2}.b ? (@ > 0)'; + ?column? +---------- + 1 +(1 row) + +select jsonb '{"a": {"c": {"b": 1}}}' @* 'lax $.**.b ? (@ > 0)'; + ?column? +---------- + 1 +(1 row) + +select jsonb '{"a": {"c": {"b": 1}}}' @* 'lax $.**{0}.b ? (@ > 0)'; + ?column? +---------- +(0 rows) + +select jsonb '{"a": {"c": {"b": 1}}}' @* 'lax $.**{1}.b ? (@ > 0)'; + ?column? +---------- +(0 rows) + +select jsonb '{"a": {"c": {"b": 1}}}' @* 'lax $.**{0,}.b ? (@ > 0)'; + ?column? +---------- + 1 +(1 row) + +select jsonb '{"a": {"c": {"b": 1}}}' @* 'lax $.**{1,}.b ? (@ > 0)'; + ?column? +---------- + 1 +(1 row) + +select jsonb '{"a": {"c": {"b": 1}}}' @* 'lax $.**{1,2}.b ? (@ > 0)'; + ?column? +---------- + 1 +(1 row) + +select jsonb '{"a": {"c": {"b": 1}}}' @* 'lax $.**{2,3}.b ? (@ > 0)'; + ?column? +---------- + 1 +(1 row) + +select jsonb '{"a": {"b": 1}}' @? '$.**.b ? ( @ > 0)'; + ?column? +---------- + t +(1 row) + +select jsonb '{"a": {"b": 1}}' @? '$.**{0}.b ? ( @ > 0)'; + ?column? +---------- + f +(1 row) + +select jsonb '{"a": {"b": 1}}' @? '$.**{1}.b ? ( @ > 0)'; + ?column? +---------- + t +(1 row) + +select jsonb '{"a": {"b": 1}}' @? '$.**{0,}.b ? ( @ > 0)'; + ?column? +---------- + t +(1 row) + +select jsonb '{"a": {"b": 1}}' @? '$.**{1,}.b ? ( @ > 0)'; + ?column? +---------- + t +(1 row) + +select jsonb '{"a": {"b": 1}}' @? '$.**{1,2}.b ? ( @ > 0)'; + ?column? +---------- + t +(1 row) + +select jsonb '{"a": {"c": {"b": 1}}}' @? '$.**.b ? ( @ > 0)'; + ?column? +---------- + t +(1 row) + +select jsonb '{"a": {"c": {"b": 1}}}' @? '$.**{0}.b ? ( @ > 0)'; + ?column? +---------- + f +(1 row) + +select jsonb '{"a": {"c": {"b": 1}}}' @? '$.**{1}.b ? ( @ > 0)'; + ?column? +---------- + f +(1 row) + +select jsonb '{"a": {"c": {"b": 1}}}' @? '$.**{0,}.b ? ( @ > 0)'; + ?column? +---------- + t +(1 row) + +select jsonb '{"a": {"c": {"b": 1}}}' @? '$.**{1,}.b ? ( @ > 0)'; + ?column? +---------- + t +(1 row) + +select jsonb '{"a": {"c": {"b": 1}}}' @? '$.**{1,2}.b ? ( @ > 0)'; + ?column? +---------- + t +(1 row) + +select jsonb '{"a": {"c": {"b": 1}}}' @? '$.**{2,3}.b ? ( @ > 0)'; + ?column? +---------- + t +(1 row) + +select jsonb '{"g": {"x": 2}}' @* '$.g ? (exists (@.x))'; + ?column? +---------- + {"x": 2} +(1 row) + +select jsonb '{"g": {"x": 2}}' @* '$.g ? (exists (@.y))'; + ?column? +---------- +(0 rows) + +select jsonb '{"g": {"x": 2}}' @* '$.g ? (exists (@.x ? (@ >= 2) ))'; + ?column? +---------- + {"x": 2} +(1 row) + +--test ternary logic +select + x, y, + jsonpath_query( + jsonb '[true, false, null]', + '$[*] ? (@ == true && ($x == true && $y == true) || + @ == false && !($x == true && $y == true) || + @ == null && ($x == true && $y == true) is unknown)', + jsonb_build_object('x', x, 'y', y) + ) as "x && y" +from + (values (jsonb 'true'), ('false'), ('"null"')) x(x), + (values (jsonb 'true'), ('false'), ('"null"')) y(y); + x | y | x && y +--------+--------+-------- + true | true | true + true | false | false + true | "null" | null + false | true | false + false | false | false + false | "null" | false + "null" | true | null + "null" | false | false + "null" | "null" | null +(9 rows) + +select + x, y, + jsonpath_query( + jsonb '[true, false, null]', + '$[*] ? (@ == true && ($x == true || $y == true) || + @ == false && !($x == true || $y == true) || + @ == null && ($x == true || $y == true) is unknown)', + jsonb_build_object('x', x, 'y', y) + ) as "x || y" +from + (values (jsonb 'true'), ('false'), ('"null"')) x(x), + (values (jsonb 'true'), ('false'), ('"null"')) y(y); + x | y | x || y +--------+--------+-------- + true | true | true + true | false | true + true | "null" | true + false | true | true + false | false | false + false | "null" | null + "null" | true | true + "null" | false | null + "null" | "null" | null +(9 rows) + +select jsonb '{"a": 1, "b":1}' @? '$ ? (.a == .b)'; + ?column? +---------- + t +(1 row) + +select jsonb '{"c": {"a": 1, "b":1}}' @? '$ ? (.a == .b)'; + ?column? +---------- + f +(1 row) + +select jsonb '{"c": {"a": 1, "b":1}}' @? '$.c ? (.a == .b)'; + ?column? +---------- + t +(1 row) + +select jsonb '{"c": {"a": 1, "b":1}}' @? '$.c ? ($.c.a == .b)'; + ?column? +---------- + t +(1 row) + +select jsonb '{"c": {"a": 1, "b":1}}' @? '$.* ? (.a == .b)'; + ?column? +---------- + t +(1 row) + +select jsonb '{"a": 1, "b":1}' @? '$.** ? (.a == .b)'; + ?column? +---------- + t +(1 row) + +select jsonb '{"c": {"a": 1, "b":1}}' @? '$.** ? (.a == .b)'; + ?column? +---------- + t +(1 row) + +select jsonb '{"c": {"a": 2, "b":1}}' @* '$.** ? (.a == 1 + 1)'; + ?column? +------------------ + {"a": 2, "b": 1} +(1 row) + +select jsonb '{"c": {"a": 2, "b":1}}' @* '$.** ? (.a == (1 + 1))'; + ?column? +------------------ + {"a": 2, "b": 1} +(1 row) + +select jsonb '{"c": {"a": 2, "b":1}}' @* '$.** ? (.a == .b + 1)'; + ?column? +------------------ + {"a": 2, "b": 1} +(1 row) + +select jsonb '{"c": {"a": 2, "b":1}}' @* '$.** ? (.a == (.b + 1))'; + ?column? +------------------ + {"a": 2, "b": 1} +(1 row) + +select jsonb '{"c": {"a": -1, "b":1}}' @? '$.** ? (.a == - 1)'; + ?column? +---------- + t +(1 row) + +select jsonb '{"c": {"a": -1, "b":1}}' @? '$.** ? (.a == -1)'; + ?column? +---------- + t +(1 row) + +select jsonb '{"c": {"a": -1, "b":1}}' @? '$.** ? (.a == -.b)'; + ?column? +---------- + t +(1 row) + +select jsonb '{"c": {"a": -1, "b":1}}' @? '$.** ? (.a == - .b)'; + ?column? +---------- + t +(1 row) + +select jsonb '{"c": {"a": 0, "b":1}}' @? '$.** ? (.a == 1 - .b)'; + ?column? +---------- + t +(1 row) + +select jsonb '{"c": {"a": 2, "b":1}}' @? '$.** ? (.a == 1 - - .b)'; + ?column? +---------- + t +(1 row) + +select jsonb '{"c": {"a": 0, "b":1}}' @? '$.** ? (.a == 1 - +.b)'; + ?column? +---------- + t +(1 row) + +select jsonb '[1,2,3]' @? '$ ? (+@[*] > +2)'; + ?column? +---------- + t +(1 row) + +select jsonb '[1,2,3]' @? '$ ? (+@[*] > +3)'; + ?column? +---------- + f +(1 row) + +select jsonb '[1,2,3]' @? '$ ? (-@[*] < -2)'; + ?column? +---------- + t +(1 row) + +select jsonb '[1,2,3]' @? '$ ? (-@[*] < -3)'; + ?column? +---------- + f +(1 row) + +select jsonb '1' @? '$ ? ($ > 0)'; + ?column? +---------- + t +(1 row) + +-- arithmetic errors +select jsonb '[1,2,0,3]' @* '$[*] ? (2 / @ > 0)'; + ?column? +---------- + 1 + 2 + 3 +(3 rows) + +select jsonb '[1,2,0,3]' @* '$[*] ? ((2 / @ > 0) is unknown)'; + ?column? +---------- + 0 +(1 row) + +select jsonb '0' @* '1 / $'; +ERROR: Unknown SQL/JSON error +-- unwrapping of operator arguments in lax mode +select jsonb '{"a": [2]}' @* 'lax $.a * 3'; + ?column? +---------- + 6 +(1 row) + +select jsonb '{"a": [2]}' @* 'lax $.a + 3'; + ?column? +---------- + 5 +(1 row) + +select jsonb '{"a": [2, 3, 4]}' @* 'lax -$.a'; + ?column? +---------- + -2 + -3 + -4 +(3 rows) + +-- should fail +select jsonb '{"a": [1, 2]}' @* 'lax $.a * 3'; +ERROR: Singleton SQL/JSON item required +-- extension: boolean expressions +select jsonb '2' @* '$ > 1'; + ?column? +---------- + true +(1 row) + +select jsonb '2' @* '$ <= 1'; + ?column? +---------- + false +(1 row) + +select jsonb '2' @* '$ == "2"'; + ?column? +---------- + null +(1 row) + +select jsonb '2' @~ '$ > 1'; + ?column? +---------- + t +(1 row) + +select jsonb '2' @~ '$ <= 1'; + ?column? +---------- + f +(1 row) + +select jsonb '2' @~ '$ == "2"'; + ?column? +---------- + +(1 row) + +select jsonb '2' @~ '1'; + ?column? +---------- + +(1 row) + +select jsonb '{}' @~ '$'; + ?column? +---------- + +(1 row) + +select jsonb '[]' @~ '$'; + ?column? +---------- + +(1 row) + +select jsonb '[1,2,3]' @~ '$[*]'; +ERROR: Singleton SQL/JSON item required +select jsonb '[]' @~ '$[*]'; +ERROR: Singleton SQL/JSON item required +select jsonpath_predicate(jsonb '[[1, true], [2, false]]', 'strict $[*] ? (@[0] > $x) [1]', '{"x": 1}'); + jsonpath_predicate +-------------------- + f +(1 row) + +select jsonpath_predicate(jsonb '[[1, true], [2, false]]', 'strict $[*] ? (@[0] < $x) [1]', '{"x": 2}'); + jsonpath_predicate +-------------------- + t +(1 row) + +select jsonb '[null,1,true,"a",[],{}]' @* '$.type()'; + ?column? +---------- + "array" +(1 row) + +select jsonb '[null,1,true,"a",[],{}]' @* 'lax $.type()'; + ?column? +---------- + "array" +(1 row) + +select jsonb '[null,1,true,"a",[],{}]' @* '$[*].type()'; + ?column? +----------- + "null" + "number" + "boolean" + "string" + "array" + "object" +(6 rows) + +select jsonb 'null' @* 'null.type()'; + ?column? +---------- + "null" +(1 row) + +select jsonb 'null' @* 'true.type()'; + ?column? +----------- + "boolean" +(1 row) + +select jsonb 'null' @* '123.type()'; + ?column? +---------- + "number" +(1 row) + +select jsonb 'null' @* '"123".type()'; + ?column? +---------- + "string" +(1 row) + +select jsonb '{"a": 2}' @* '($.a - 5).abs() + 10'; + ?column? +---------- + 13 +(1 row) + +select jsonb '{"a": 2.5}' @* '-($.a * $.a).floor() + 10'; + ?column? +---------- + 4 +(1 row) + +select jsonb '[1, 2, 3]' @* '($[*] > 2) ? (@ == true)'; + ?column? +---------- + true +(1 row) + +select jsonb '[1, 2, 3]' @* '($[*] > 3).type()'; + ?column? +----------- + "boolean" +(1 row) + +select jsonb '[1, 2, 3]' @* '($[*].a > 3).type()'; + ?column? +----------- + "boolean" +(1 row) + +select jsonb '[1, 2, 3]' @* 'strict ($[*].a > 3).type()'; + ?column? +---------- + "null" +(1 row) + +select jsonb '[1,null,true,"11",[],[1],[1,2,3],{},{"a":1,"b":2}]' @* 'strict $[*].size()'; +ERROR: SQL/JSON array not found +select jsonb '[1,null,true,"11",[],[1],[1,2,3],{},{"a":1,"b":2}]' @* 'lax $[*].size()'; + ?column? +---------- + 1 + 1 + 1 + 1 + 0 + 1 + 3 + 1 + 1 +(9 rows) + +select jsonb '[0, 1, -2, -3.4, 5.6]' @* '$[*].abs()'; + ?column? +---------- + 0 + 1 + 2 + 3.4 + 5.6 +(5 rows) + +select jsonb '[0, 1, -2, -3.4, 5.6]' @* '$[*].floor()'; + ?column? +---------- + 0 + 1 + -2 + -4 + 5 +(5 rows) + +select jsonb '[0, 1, -2, -3.4, 5.6]' @* '$[*].ceiling()'; + ?column? +---------- + 0 + 1 + -2 + -3 + 6 +(5 rows) + +select jsonb '[0, 1, -2, -3.4, 5.6]' @* '$[*].ceiling().abs()'; + ?column? +---------- + 0 + 1 + 2 + 3 + 6 +(5 rows) + +select jsonb '[0, 1, -2, -3.4, 5.6]' @* '$[*].ceiling().abs().type()'; + ?column? +---------- + "number" + "number" + "number" + "number" + "number" +(5 rows) + +select jsonb '[{},1]' @* '$[*].keyvalue()'; +ERROR: SQL/JSON object not found +select jsonb '{}' @* '$.keyvalue()'; + ?column? +---------- +(0 rows) + +select jsonb '{"a": 1, "b": [1, 2], "c": {"a": "bbb"}}' @* '$.keyvalue()'; + ?column? +------------------------------------- + {"key": "a", "value": 1} + {"key": "b", "value": [1, 2]} + {"key": "c", "value": {"a": "bbb"}} +(3 rows) + +select jsonb '[{"a": 1, "b": [1, 2]}, {"c": {"a": "bbb"}}]' @* '$[*].keyvalue()'; + ?column? +------------------------------------- + {"key": "a", "value": 1} + {"key": "b", "value": [1, 2]} + {"key": "c", "value": {"a": "bbb"}} +(3 rows) + +select jsonb '[{"a": 1, "b": [1, 2]}, {"c": {"a": "bbb"}}]' @* 'strict $.keyvalue()'; +ERROR: SQL/JSON object not found +select jsonb '[{"a": 1, "b": [1, 2]}, {"c": {"a": "bbb"}}]' @* 'lax $.keyvalue()'; + ?column? +------------------------------------- + {"key": "a", "value": 1} + {"key": "b", "value": [1, 2]} + {"key": "c", "value": {"a": "bbb"}} +(3 rows) + +select jsonb 'null' @* '$.double()'; +ERROR: Non-numeric SQL/JSON item +select jsonb 'true' @* '$.double()'; +ERROR: Non-numeric SQL/JSON item +select jsonb '[]' @* '$.double()'; + ?column? +---------- +(0 rows) + +select jsonb '[]' @* 'strict $.double()'; +ERROR: Non-numeric SQL/JSON item +select jsonb '{}' @* '$.double()'; +ERROR: Non-numeric SQL/JSON item +select jsonb '1.23' @* '$.double()'; + ?column? +---------- + 1.23 +(1 row) + +select jsonb '"1.23"' @* '$.double()'; + ?column? +---------- + 1.23 +(1 row) + +select jsonb '"1.23aaa"' @* '$.double()'; +ERROR: Non-numeric SQL/JSON item +select jsonb '["", "a", "abc", "abcabc"]' @* '$[*] ? (@ starts with "abc")'; + ?column? +---------- + "abc" + "abcabc" +(2 rows) + +select jsonb '["", "a", "abc", "abcabc"]' @* 'strict $ ? (@[*] starts with "abc")'; + ?column? +---------------------------- + ["", "a", "abc", "abcabc"] +(1 row) + +select jsonb '["", "a", "abd", "abdabc"]' @* 'strict $ ? (@[*] starts with "abc")'; + ?column? +---------- +(0 rows) + +select jsonb '["abc", "abcabc", null, 1]' @* 'strict $ ? (@[*] starts with "abc")'; + ?column? +---------- +(0 rows) + +select jsonb '["abc", "abcabc", null, 1]' @* 'strict $ ? ((@[*] starts with "abc") is unknown)'; + ?column? +---------------------------- + ["abc", "abcabc", null, 1] +(1 row) + +select jsonb '[[null, 1, "abc", "abcabc"]]' @* 'lax $ ? (@[*] starts with "abc")'; + ?column? +---------------------------- + [null, 1, "abc", "abcabc"] +(1 row) + +select jsonb '[[null, 1, "abd", "abdabc"]]' @* 'lax $ ? ((@[*] starts with "abc") is unknown)'; + ?column? +---------------------------- + [null, 1, "abd", "abdabc"] +(1 row) + +select jsonb '[null, 1, "abd", "abdabc"]' @* 'lax $[*] ? ((@ starts with "abc") is unknown)'; + ?column? +---------- + null + 1 +(2 rows) + +select jsonb '[null, 1, "abc", "abd", "aBdC", "abdacb", "babc"]' @* 'lax $[*] ? (@ like_regex "^ab.*c")'; + ?column? +---------- + "abc" + "abdacb" +(2 rows) + +select jsonb '[null, 1, "abc", "abd", "aBdC", "abdacb", "babc"]' @* 'lax $[*] ? (@ like_regex "^ab.*c" flag "i")'; + ?column? +---------- + "abc" + "aBdC" + "abdacb" +(3 rows) + +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? +---------- +(0 rows) + +select jsonb '[]' @* 'strict $.datetime()'; +ERROR: Invalid argument for SQL/JSON datetime function +select jsonb '{}' @* '$.datetime()'; +ERROR: Invalid argument for SQL/JSON datetime function +select jsonb '""' @* '$.datetime()'; +ERROR: Invalid argument for SQL/JSON datetime function +select jsonb '"10-03-2017"' @* '$.datetime("dd-mm-yyyy")'; + ?column? +-------------- + "2017-03-10" +(1 row) + +select jsonb '"10-03-2017"' @* '$.datetime("dd-mm-yyyy").type()'; + ?column? +---------- + "date" +(1 row) + +select jsonb '"10-03-2017 12:34"' @* '$.datetime("dd-mm-yyyy")'; + ?column? +-------------- + "2017-03-10" +(1 row) + +select jsonb '"10-03-2017 12:34"' @* '$.datetime("dd-mm-yyyy").type()'; + ?column? +---------- + "date" +(1 row) + +select jsonb '"10-03-2017 12:34"' @* ' $.datetime("dd-mm-yyyy HH24:MI").type()'; + ?column? +------------------------------- + "timestamp without time zone" +(1 row) + +select jsonb '"10-03-2017 12:34 +05:20"' @* '$.datetime("dd-mm-yyyy HH24:MI TZH:TZM").type()'; + ?column? +---------------------------- + "timestamp with time zone" +(1 row) + +select jsonb '"12:34:56"' @* '$.datetime("HH24:MI:SS").type()'; + ?column? +-------------------------- + "time without time zone" +(1 row) + +select jsonb '"12:34:56 +05:20"' @* '$.datetime("HH24:MI:SS TZH:TZM").type()'; + ?column? +----------------------- + "time with time zone" +(1 row) + +set time zone '+00'; +select jsonb '"10-03-2017 12:34"' @* '$.datetime("dd-mm-yyyy HH24:MI")'; + ?column? +----------------------- + "2017-03-10T12:34:00" +(1 row) + +select jsonb '"10-03-2017 12:34"' @* '$.datetime("dd-mm-yyyy HH24:MI TZH")'; +ERROR: Invalid argument for SQL/JSON datetime function +select jsonb '"10-03-2017 12:34"' @* '$.datetime("dd-mm-yyyy HH24:MI TZH", "+00")'; + ?column? +----------------------------- + "2017-03-10T12:34:00+00:00" +(1 row) + +select jsonb '"10-03-2017 12:34"' @* '$.datetime("dd-mm-yyyy HH24:MI TZH", "+00:12")'; + ?column? +----------------------------- + "2017-03-10T12:34:00+00:12" +(1 row) + +select jsonb '"10-03-2017 12:34"' @* '$.datetime("dd-mm-yyyy HH24:MI TZH", "-00:12:34")'; + ?column? +-------------------------------- + "2017-03-10T12:34:00-00:12:34" +(1 row) + +select jsonb '"10-03-2017 12:34"' @* '$.datetime("dd-mm-yyyy HH24:MI TZH", "UTC")'; +ERROR: Invalid argument for SQL/JSON datetime function +select jsonb '"10-03-2017 12:34 +05"' @* '$.datetime("dd-mm-yyyy HH24:MI TZH")'; + ?column? +----------------------------- + "2017-03-10T12:34:00+05:00" +(1 row) + +select jsonb '"10-03-2017 12:34 -05"' @* '$.datetime("dd-mm-yyyy HH24:MI TZH")'; + ?column? +----------------------------- + "2017-03-10T12:34:00-05:00" +(1 row) + +select jsonb '"10-03-2017 12:34 +05:20"' @* '$.datetime("dd-mm-yyyy HH24:MI TZH:TZM")'; + ?column? +----------------------------- + "2017-03-10T12:34:00+05:20" +(1 row) + +select jsonb '"10-03-2017 12:34 -05:20"' @* '$.datetime("dd-mm-yyyy HH24:MI TZH:TZM")'; + ?column? +----------------------------- + "2017-03-10T12:34:00-05:20" +(1 row) + +select jsonb '"12:34"' @* '$.datetime("HH24:MI")'; + ?column? +------------ + "12:34:00" +(1 row) + +select jsonb '"12:34"' @* '$.datetime("HH24:MI TZH")'; +ERROR: Invalid argument for SQL/JSON datetime function +select jsonb '"12:34"' @* '$.datetime("HH24:MI TZH", "+00")'; + ?column? +------------------ + "12:34:00+00:00" +(1 row) + +select jsonb '"12:34 +05"' @* '$.datetime("HH24:MI TZH")'; + ?column? +------------------ + "12:34:00+05:00" +(1 row) + +select jsonb '"12:34 -05"' @* '$.datetime("HH24:MI TZH")'; + ?column? +------------------ + "12:34:00-05:00" +(1 row) + +select jsonb '"12:34 +05:20"' @* '$.datetime("HH24:MI TZH:TZM")'; + ?column? +------------------ + "12:34:00+05:20" +(1 row) + +select jsonb '"12:34 -05:20"' @* '$.datetime("HH24:MI TZH:TZM")'; + ?column? +------------------ + "12:34:00-05:20" +(1 row) + +set time zone '+10'; +select jsonb '"10-03-2017 12:34"' @* '$.datetime("dd-mm-yyyy HH24:MI")'; + ?column? +----------------------- + "2017-03-10T12:34:00" +(1 row) + +select jsonb '"10-03-2017 12:34"' @* '$.datetime("dd-mm-yyyy HH24:MI TZH")'; +ERROR: Invalid argument for SQL/JSON datetime function +select jsonb '"10-03-2017 12:34"' @* '$.datetime("dd-mm-yyyy HH24:MI TZH", "+10")'; + ?column? +----------------------------- + "2017-03-10T12:34:00+10:00" +(1 row) + +select jsonb '"10-03-2017 12:34 +05"' @* '$.datetime("dd-mm-yyyy HH24:MI TZH")'; + ?column? +----------------------------- + "2017-03-10T12:34:00+05:00" +(1 row) + +select jsonb '"10-03-2017 12:34 -05"' @* '$.datetime("dd-mm-yyyy HH24:MI TZH")'; + ?column? +----------------------------- + "2017-03-10T12:34:00-05:00" +(1 row) + +select jsonb '"10-03-2017 12:34 +05:20"' @* '$.datetime("dd-mm-yyyy HH24:MI TZH:TZM")'; + ?column? +----------------------------- + "2017-03-10T12:34:00+05:20" +(1 row) + +select jsonb '"10-03-2017 12:34 -05:20"' @* '$.datetime("dd-mm-yyyy HH24:MI TZH:TZM")'; + ?column? +----------------------------- + "2017-03-10T12:34:00-05:20" +(1 row) + +select jsonb '"12:34"' @* '$.datetime("HH24:MI")'; + ?column? +------------ + "12:34:00" +(1 row) + +select jsonb '"12:34"' @* '$.datetime("HH24:MI TZH")'; +ERROR: Invalid argument for SQL/JSON datetime function +select jsonb '"12:34"' @* '$.datetime("HH24:MI TZH", "+10")'; + ?column? +------------------ + "12:34:00+10:00" +(1 row) + +select jsonb '"12:34 +05"' @* '$.datetime("HH24:MI TZH")'; + ?column? +------------------ + "12:34:00+05:00" +(1 row) + +select jsonb '"12:34 -05"' @* '$.datetime("HH24:MI TZH")'; + ?column? +------------------ + "12:34:00-05:00" +(1 row) + +select jsonb '"12:34 +05:20"' @* '$.datetime("HH24:MI TZH:TZM")'; + ?column? +------------------ + "12:34:00+05:20" +(1 row) + +select jsonb '"12:34 -05:20"' @* '$.datetime("HH24:MI TZH:TZM")'; + ?column? +------------------ + "12:34:00-05:20" +(1 row) + +set time zone default; +select jsonb '"2017-03-10"' @* '$.datetime().type()'; + ?column? +---------- + "date" +(1 row) + +select jsonb '"2017-03-10"' @* '$.datetime()'; + ?column? +-------------- + "2017-03-10" +(1 row) + +select jsonb '"2017-03-10 12:34:56"' @* '$.datetime().type()'; + ?column? +------------------------------- + "timestamp without time zone" +(1 row) + +select jsonb '"2017-03-10 12:34:56"' @* '$.datetime()'; + ?column? +----------------------- + "2017-03-10T12:34:56" +(1 row) + +select jsonb '"2017-03-10 12:34:56 +3"' @* '$.datetime().type()'; + ?column? +---------------------------- + "timestamp with time zone" +(1 row) + +select jsonb '"2017-03-10 12:34:56 +3"' @* '$.datetime()'; + ?column? +----------------------------- + "2017-03-10T12:34:56+03:00" +(1 row) + +select jsonb '"2017-03-10 12:34:56 +3:10"' @* '$.datetime().type()'; + ?column? +---------------------------- + "timestamp with time zone" +(1 row) + +select jsonb '"2017-03-10 12:34:56 +3:10"' @* '$.datetime()'; + ?column? +----------------------------- + "2017-03-10T12:34:56+03:10" +(1 row) + +select jsonb '"12:34:56"' @* '$.datetime().type()'; + ?column? +-------------------------- + "time without time zone" +(1 row) + +select jsonb '"12:34:56"' @* '$.datetime()'; + ?column? +------------ + "12:34:56" +(1 row) + +select jsonb '"12:34:56 +3"' @* '$.datetime().type()'; + ?column? +----------------------- + "time with time zone" +(1 row) + +select jsonb '"12:34:56 +3"' @* '$.datetime()'; + ?column? +------------------ + "12:34:56+03:00" +(1 row) + +select jsonb '"12:34:56 +3:10"' @* '$.datetime().type()'; + ?column? +----------------------- + "time with time zone" +(1 row) + +select jsonb '"12:34:56 +3:10"' @* '$.datetime()'; + ?column? +------------------ + "12:34:56+03:10" +(1 row) + +set time zone '+00'; +-- date comparison +select jsonb + '["2017-03-10", "2017-03-11", "2017-03-09", "12:34:56", "01:02:03 +04", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03 +04", "2017-03-10 03:00:00 +03"]' @* + '$[*].datetime() ? (@ == "10.03.2017".datetime("dd.mm.yyyy"))'; + ?column? +----------------------------- + "2017-03-10" + "2017-03-10T00:00:00" + "2017-03-10T03:00:00+03:00" +(3 rows) + +select jsonb + '["2017-03-10", "2017-03-11", "2017-03-09", "12:34:56", "01:02:03 +04", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03 +04", "2017-03-10 03:00:00 +03"]' @* + '$[*].datetime() ? (@ >= "10.03.2017".datetime("dd.mm.yyyy"))'; + ?column? +----------------------------- + "2017-03-10" + "2017-03-11" + "2017-03-10T00:00:00" + "2017-03-10T12:34:56" + "2017-03-10T03:00:00+03:00" +(5 rows) + +select jsonb + '["2017-03-10", "2017-03-11", "2017-03-09", "12:34:56", "01:02:03 +04", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03 +04", "2017-03-10 03:00:00 +03"]' @* + '$[*].datetime() ? (@ < "10.03.2017".datetime("dd.mm.yyyy"))'; + ?column? +----------------------------- + "2017-03-09" + "2017-03-10T01:02:03+04:00" +(2 rows) + +-- time comparison +select jsonb + '["12:34:00", "12:35:00", "12:36:00", "12:35:00 +00", "12:35:00 +01", "13:35:00 +01", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +01"]' @* + '$[*].datetime() ? (@ == "12:35".datetime("HH24:MI"))'; + ?column? +------------------ + "12:35:00" + "12:35:00+00:00" +(2 rows) + +select jsonb + '["12:34:00", "12:35:00", "12:36:00", "12:35:00 +00", "12:35:00 +01", "13:35:00 +01", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +01"]' @* + '$[*].datetime() ? (@ >= "12:35".datetime("HH24:MI"))'; + ?column? +------------------ + "12:35:00" + "12:36:00" + "12:35:00+00:00" +(3 rows) + +select jsonb + '["12:34:00", "12:35:00", "12:36:00", "12:35:00 +00", "12:35:00 +01", "13:35:00 +01", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +01"]' @* + '$[*].datetime() ? (@ < "12:35".datetime("HH24:MI"))'; + ?column? +------------------ + "12:34:00" + "12:35:00+01:00" + "13:35:00+01:00" +(3 rows) + +-- timetz comparison +select jsonb + '["12:34:00 +01", "12:35:00 +01", "12:36:00 +01", "12:35:00 +02", "12:35:00 -02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +1"]' @* + '$[*].datetime() ? (@ == "12:35 +1".datetime("HH24:MI TZH"))'; + ?column? +------------------ + "12:35:00+01:00" +(1 row) + +select jsonb + '["12:34:00 +01", "12:35:00 +01", "12:36:00 +01", "12:35:00 +02", "12:35:00 -02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +1"]' @* + '$[*].datetime() ? (@ >= "12:35 +1".datetime("HH24:MI TZH"))'; + ?column? +------------------ + "12:35:00+01:00" + "12:36:00+01:00" + "12:35:00-02:00" + "11:35:00" + "12:35:00" +(5 rows) + +select jsonb + '["12:34:00 +01", "12:35:00 +01", "12:36:00 +01", "12:35:00 +02", "12:35:00 -02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +1"]' @* + '$[*].datetime() ? (@ < "12:35 +1".datetime("HH24:MI TZH"))'; + ?column? +------------------ + "12:34:00+01:00" + "12:35:00+02:00" + "10:35:00" +(3 rows) + +-- timestamp comparison +select jsonb + '["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00 +01", "2017-03-10 13:35:00 +01", "2017-03-10 12:35:00 -01", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56 +01"]' @* + '$[*].datetime() ? (@ == "10.03.2017 12:35".datetime("dd.mm.yyyy HH24:MI"))'; + ?column? +----------------------------- + "2017-03-10T12:35:00" + "2017-03-10T13:35:00+01:00" +(2 rows) + +select jsonb + '["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00 +01", "2017-03-10 13:35:00 +01", "2017-03-10 12:35:00 -01", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56 +01"]' @* + '$[*].datetime() ? (@ >= "10.03.2017 12:35".datetime("dd.mm.yyyy HH24:MI"))'; + ?column? +----------------------------- + "2017-03-10T12:35:00" + "2017-03-10T12:36:00" + "2017-03-10T13:35:00+01:00" + "2017-03-10T12:35:00-01:00" + "2017-03-11" +(5 rows) + +select jsonb + '["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00 +01", "2017-03-10 13:35:00 +01", "2017-03-10 12:35:00 -01", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56 +01"]' @* + '$[*].datetime() ? (@ < "10.03.2017 12:35".datetime("dd.mm.yyyy HH24:MI"))'; + ?column? +----------------------------- + "2017-03-10T12:34:00" + "2017-03-10T12:35:00+01:00" + "2017-03-10" +(3 rows) + +-- timestamptz comparison +select jsonb + '["2017-03-10 12:34:00 +01", "2017-03-10 12:35:00 +01", "2017-03-10 12:36:00 +01", "2017-03-10 12:35:00 +02", "2017-03-10 12:35:00 -02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56 +01"]' @* + '$[*].datetime() ? (@ == "10.03.2017 12:35 +1".datetime("dd.mm.yyyy HH24:MI TZH"))'; + ?column? +----------------------------- + "2017-03-10T12:35:00+01:00" + "2017-03-10T11:35:00" +(2 rows) + +select jsonb + '["2017-03-10 12:34:00 +01", "2017-03-10 12:35:00 +01", "2017-03-10 12:36:00 +01", "2017-03-10 12:35:00 +02", "2017-03-10 12:35:00 -02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56 +01"]' @* + '$[*].datetime() ? (@ >= "10.03.2017 12:35 +1".datetime("dd.mm.yyyy HH24:MI TZH"))'; + ?column? +----------------------------- + "2017-03-10T12:35:00+01:00" + "2017-03-10T12:36:00+01:00" + "2017-03-10T12:35:00-02:00" + "2017-03-10T11:35:00" + "2017-03-10T12:35:00" + "2017-03-11" +(6 rows) + +select jsonb + '["2017-03-10 12:34:00 +01", "2017-03-10 12:35:00 +01", "2017-03-10 12:36:00 +01", "2017-03-10 12:35:00 +02", "2017-03-10 12:35:00 -02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56 +01"]' @* + '$[*].datetime() ? (@ < "10.03.2017 12:35 +1".datetime("dd.mm.yyyy HH24:MI TZH"))'; + ?column? +----------------------------- + "2017-03-10T12:34:00+01:00" + "2017-03-10T12:35:00+02:00" + "2017-03-10T10:35:00" + "2017-03-10" +(4 rows) + +set time zone default; +-- jsonpath operators +SELECT jsonb '[{"a": 1}, {"a": 2}]' @* '$[*]'; + ?column? +---------- + {"a": 1} + {"a": 2} +(2 rows) + +SELECT jsonb '[{"a": 1}, {"a": 2}]' @* '$[*] ? (@.a > 10)'; + ?column? +---------- +(0 rows) + +SELECT jsonb '[{"a": 1}, {"a": 2}]' @# '$[*].a'; + ?column? +---------- + [1, 2] +(1 row) + +SELECT jsonb '[{"a": 1}, {"a": 2}]' @# '$[*].a ? (@ == 1)'; + ?column? +---------- + 1 +(1 row) + +SELECT jsonb '[{"a": 1}, {"a": 2}]' @# '$[*].a ? (@ > 10)'; + ?column? +---------- + +(1 row) + +SELECT jsonb '[{"a": 1}, {"a": 2}]' @? '$[*].a ? (@ > 1)'; + ?column? +---------- + t +(1 row) + +SELECT jsonb '[{"a": 1}, {"a": 2}]' @? '$[*] ? (@.a > 2)'; + ?column? +---------- + f +(1 row) + +SELECT jsonb '[{"a": 1}, {"a": 2}]' @~ '$[*].a > 1'; + ?column? +---------- + t +(1 row) + +SELECT jsonb '[{"a": 1}, {"a": 2}]' @~ '$[*].a > 2'; + ?column? +---------- + f +(1 row) + diff --git a/src/test/regress/expected/jsonpath.out b/src/test/regress/expected/jsonpath.out new file mode 100644 index 0000000..0b2f1bb --- /dev/null +++ b/src/test/regress/expected/jsonpath.out @@ -0,0 +1,770 @@ +--jsonpath io +select ''::jsonpath; +ERROR: invalid input syntax for jsonpath: "" +LINE 1: select ''::jsonpath; + ^ +select '$'::jsonpath; + jsonpath +---------- + $ +(1 row) + +select 'strict $'::jsonpath; + jsonpath +---------- + strict $ +(1 row) + +select 'lax $'::jsonpath; + jsonpath +---------- + $ +(1 row) + +select '$.a'::jsonpath; + jsonpath +---------- + $."a" +(1 row) + +select '$.a.v'::jsonpath; + jsonpath +----------- + $."a"."v" +(1 row) + +select '$.a.*'::jsonpath; + jsonpath +---------- + $."a".* +(1 row) + +select '$.*[*]'::jsonpath; + jsonpath +---------- + $.*[*] +(1 row) + +select '$.a[*]'::jsonpath; + jsonpath +---------- + $."a"[*] +(1 row) + +select '$.a[*][*]'::jsonpath; + jsonpath +------------- + $."a"[*][*] +(1 row) + +select '$[*]'::jsonpath; + jsonpath +---------- + $[*] +(1 row) + +select '$[0]'::jsonpath; + jsonpath +---------- + $[0] +(1 row) + +select '$[*][0]'::jsonpath; + jsonpath +---------- + $[*][0] +(1 row) + +select '$[*].a'::jsonpath; + jsonpath +---------- + $[*]."a" +(1 row) + +select '$[*][0].a.b'::jsonpath; + jsonpath +----------------- + $[*][0]."a"."b" +(1 row) + +select '$.a.**.b'::jsonpath; + jsonpath +-------------- + $."a".**."b" +(1 row) + +select '$.a.**{2}.b'::jsonpath; + jsonpath +----------------- + $."a".**{2}."b" +(1 row) + +select '$.a.**{2,2}.b'::jsonpath; + jsonpath +----------------- + $."a".**{2}."b" +(1 row) + +select '$.a.**{2,5}.b'::jsonpath; + jsonpath +------------------- + $."a".**{2,5}."b" +(1 row) + +select '$.a.**{,5}.b'::jsonpath; + jsonpath +------------------ + $."a".**{,5}."b" +(1 row) + +select '$.a.**{5,}.b'::jsonpath; + jsonpath +------------------ + $."a".**{5,}."b" +(1 row) + +select '$+1'::jsonpath; + jsonpath +---------- + ($ + 1) +(1 row) + +select '$-1'::jsonpath; + jsonpath +---------- + ($ - 1) +(1 row) + +select '$--+1'::jsonpath; + jsonpath +---------- + ($ - -1) +(1 row) + +select '$.a/+-1'::jsonpath; + jsonpath +-------------- + ($."a" / -1) +(1 row) + +select '$.g ? ($.a == 1)'::jsonpath; + jsonpath +-------------------- + $."g"?($."a" == 1) +(1 row) + +select '$.g ? (@ == 1)'::jsonpath; + jsonpath +---------------- + $."g"?(@ == 1) +(1 row) + +select '$.g ? (.a == 1)'::jsonpath; + jsonpath +-------------------- + $."g"?(@."a" == 1) +(1 row) + +select '$.g ? (@.a == 1)'::jsonpath; + jsonpath +-------------------- + $."g"?(@."a" == 1) +(1 row) + +select '$.g ? (@.a == 1 || @.a == 4)'::jsonpath; + jsonpath +---------------------------------- + $."g"?(@."a" == 1 || @."a" == 4) +(1 row) + +select '$.g ? (@.a == 1 && @.a == 4)'::jsonpath; + jsonpath +---------------------------------- + $."g"?(@."a" == 1 && @."a" == 4) +(1 row) + +select '$.g ? (@.a == 1 || @.a == 4 && @.b == 7)'::jsonpath; + jsonpath +------------------------------------------------ + $."g"?(@."a" == 1 || @."a" == 4 && @."b" == 7) +(1 row) + +select '$.g ? (@.a == 1 || !(@.a == 4) && @.b == 7)'::jsonpath; + jsonpath +--------------------------------------------------- + $."g"?(@."a" == 1 || !(@."a" == 4) && @."b" == 7) +(1 row) + +select '$.g ? (@.a == 1 || !(@.x >= 123 || @.a == 4) && @.b == 7)'::jsonpath; + jsonpath +------------------------------------------------------------------- + $."g"?(@."a" == 1 || !(@."x" >= 123 || @."a" == 4) && @."b" == 7) +(1 row) + +select '$.g ? (.x >= @[*]?(@.a > "abc"))'::jsonpath; + jsonpath +--------------------------------------- + $."g"?(@."x" >= @[*]?(@."a" > "abc")) +(1 row) + +select '$.g ? ((@.x >= 123 || @.a == 4) is unknown)'::jsonpath; + jsonpath +------------------------------------------------- + $."g"?((@."x" >= 123 || @."a" == 4) is unknown) +(1 row) + +select '$.g ? (exists (.x))'::jsonpath; + jsonpath +------------------------ + $."g"?(exists (@."x")) +(1 row) + +select '$.g ? (exists (@.x ? (@ == 14)))'::jsonpath; + jsonpath +---------------------------------- + $."g"?(exists (@."x"?(@ == 14))) +(1 row) + +select '$.g ? (exists (.x ? (@ == 14)))'::jsonpath; + jsonpath +---------------------------------- + $."g"?(exists (@."x"?(@ == 14))) +(1 row) + +select '$.g ? ((@.x >= 123 || @.a == 4) && exists (.x ? (@ == 14)))'::jsonpath; + jsonpath +------------------------------------------------------------------ + $."g"?((@."x" >= 123 || @."a" == 4) && exists (@."x"?(@ == 14))) +(1 row) + +select '$.g ? (+@.x >= +-(+@.a + 2))'::jsonpath; + jsonpath +------------------------------------ + $."g"?(+@."x" >= +(-(+@."a" + 2))) +(1 row) + +select '$a'::jsonpath; + jsonpath +---------- + $"a" +(1 row) + +select '$a.b'::jsonpath; + jsonpath +---------- + $"a"."b" +(1 row) + +select '$a[*]'::jsonpath; + jsonpath +---------- + $"a"[*] +(1 row) + +select '$.g ? (@.zip == $zip)'::jsonpath; + jsonpath +--------------------------- + $."g"?(@."zip" == $"zip") +(1 row) + +select '$.a[1,2, 3 to 16]'::jsonpath; + jsonpath +-------------------- + $."a"[1,2,3 to 16] +(1 row) + +select '$.a[$a + 1, ($b[*]) to -(@[0] * 2)]'::jsonpath; + jsonpath +---------------------------------------- + $."a"[$"a" + 1,$"b"[*] to -(@[0] * 2)] +(1 row) + +select '$.a[$.a.size() - 3]'::jsonpath; + jsonpath +------------------------- + $."a"[$."a".size() - 3] +(1 row) + +select 'last'::jsonpath; +ERROR: LAST is allowed only in array subscripts +LINE 1: select 'last'::jsonpath; + ^ +select '"last"'::jsonpath; + jsonpath +---------- + "last" +(1 row) + +select '$.last'::jsonpath; + jsonpath +---------- + $."last" +(1 row) + +select '$ ? (last > 0)'::jsonpath; +ERROR: LAST is allowed only in array subscripts +LINE 1: select '$ ? (last > 0)'::jsonpath; + ^ +select '$[last]'::jsonpath; + jsonpath +---------- + $[last] +(1 row) + +select '$[@ ? (last > 0)]'::jsonpath; + jsonpath +----------------- + $[@?(last > 0)] +(1 row) + +select 'null.type()'::jsonpath; + jsonpath +------------- + null.type() +(1 row) + +select '1.type()'::jsonpath; + jsonpath +---------- + 1.type() +(1 row) + +select '"aaa".type()'::jsonpath; + jsonpath +-------------- + "aaa".type() +(1 row) + +select 'true.type()'::jsonpath; + jsonpath +------------- + true.type() +(1 row) + +select '$.datetime()'::jsonpath; + jsonpath +-------------- + $.datetime() +(1 row) + +select '$.datetime("datetime template")'::jsonpath; + jsonpath +--------------------------------- + $.datetime("datetime template") +(1 row) + +select '$ ? (@ starts with "abc")'::jsonpath; + jsonpath +------------------------- + $?(@ starts with "abc") +(1 row) + +select '$ ? (@ starts with $var)'::jsonpath; + jsonpath +-------------------------- + $?(@ starts with $"var") +(1 row) + +select '$ ? (@ like_regex "(invalid pattern")'::jsonpath; +ERROR: invalid regular expression: parentheses () not balanced +LINE 1: select '$ ? (@ like_regex "(invalid pattern")'::jsonpath; + ^ +select '$ ? (@ like_regex "pattern")'::jsonpath; + jsonpath +---------------------------- + $?(@ like_regex "pattern") +(1 row) + +select '$ ? (@ like_regex "pattern" flag "")'::jsonpath; + jsonpath +---------------------------- + $?(@ like_regex "pattern") +(1 row) + +select '$ ? (@ like_regex "pattern" flag "i")'::jsonpath; + jsonpath +------------------------------------- + $?(@ like_regex "pattern" flag "i") +(1 row) + +select '$ ? (@ like_regex "pattern" flag "is")'::jsonpath; + jsonpath +-------------------------------------- + $?(@ like_regex "pattern" flag "is") +(1 row) + +select '$ ? (@ like_regex "pattern" flag "isim")'::jsonpath; + jsonpath +-------------------------------------- + $?(@ like_regex "pattern" flag "im") +(1 row) + +select '$ ? (@ like_regex "pattern" flag "xsms")'::jsonpath; + jsonpath +-------------------------------------- + $?(@ like_regex "pattern" flag "sx") +(1 row) + +select '$ ? (@ like_regex "pattern" flag "a")'::jsonpath; +ERROR: bad jsonpath representation +LINE 1: select '$ ? (@ like_regex "pattern" flag "a")'::jsonpath; + ^ +DETAIL: unrecognized flag of LIKE_REGEX predicate at or near """ +select '$ < 1'::jsonpath; + jsonpath +---------- + ($ < 1) +(1 row) + +select '($ < 1) || $.a.b <= $x'::jsonpath; + jsonpath +------------------------------ + ($ < 1 || $."a"."b" <= $"x") +(1 row) + +select '@ + 1'::jsonpath; +ERROR: @ is not allowed in root expressions +LINE 1: select '@ + 1'::jsonpath; + ^ +select '($).a.b'::jsonpath; + jsonpath +----------- + $."a"."b" +(1 row) + +select '($.a.b).c.d'::jsonpath; + jsonpath +------------------- + $."a"."b"."c"."d" +(1 row) + +select '($.a.b + -$.x.y).c.d'::jsonpath; + jsonpath +---------------------------------- + ($."a"."b" + -$."x"."y")."c"."d" +(1 row) + +select '(-+$.a.b).c.d'::jsonpath; + jsonpath +------------------------- + (-(+$."a"."b"))."c"."d" +(1 row) + +select '1 + ($.a.b + 2).c.d'::jsonpath; + jsonpath +------------------------------- + (1 + ($."a"."b" + 2)."c"."d") +(1 row) + +select '1 + ($.a.b > 2).c.d'::jsonpath; + jsonpath +------------------------------- + (1 + ($."a"."b" > 2)."c"."d") +(1 row) + +select '($)'::jsonpath; + jsonpath +---------- + $ +(1 row) + +select '(($))'::jsonpath; + jsonpath +---------- + $ +(1 row) + +select '((($ + 1)).a + ((2)).b ? ((((@ > 1)) || (exists(@.c)))))'::jsonpath; + jsonpath +------------------------------------------------- + (($ + 1)."a" + 2."b"?(@ > 1 || exists (@."c"))) +(1 row) + +select '$ ? (@.a < 1)'::jsonpath; + jsonpath +--------------- + $?(@."a" < 1) +(1 row) + +select '$ ? (@.a < -1)'::jsonpath; + jsonpath +---------------- + $?(@."a" < -1) +(1 row) + +select '$ ? (@.a < +1)'::jsonpath; + jsonpath +--------------- + $?(@."a" < 1) +(1 row) + +select '$ ? (@.a < .1)'::jsonpath; + jsonpath +----------------- + $?(@."a" < 0.1) +(1 row) + +select '$ ? (@.a < -.1)'::jsonpath; + jsonpath +------------------ + $?(@."a" < -0.1) +(1 row) + +select '$ ? (@.a < +.1)'::jsonpath; + jsonpath +----------------- + $?(@."a" < 0.1) +(1 row) + +select '$ ? (@.a < 0.1)'::jsonpath; + jsonpath +----------------- + $?(@."a" < 0.1) +(1 row) + +select '$ ? (@.a < -0.1)'::jsonpath; + jsonpath +------------------ + $?(@."a" < -0.1) +(1 row) + +select '$ ? (@.a < +0.1)'::jsonpath; + jsonpath +----------------- + $?(@."a" < 0.1) +(1 row) + +select '$ ? (@.a < 10.1)'::jsonpath; + jsonpath +------------------ + $?(@."a" < 10.1) +(1 row) + +select '$ ? (@.a < -10.1)'::jsonpath; + jsonpath +------------------- + $?(@."a" < -10.1) +(1 row) + +select '$ ? (@.a < +10.1)'::jsonpath; + jsonpath +------------------ + $?(@."a" < 10.1) +(1 row) + +select '$ ? (@.a < 1e1)'::jsonpath; + jsonpath +---------------- + $?(@."a" < 10) +(1 row) + +select '$ ? (@.a < -1e1)'::jsonpath; + jsonpath +----------------- + $?(@."a" < -10) +(1 row) + +select '$ ? (@.a < +1e1)'::jsonpath; + jsonpath +---------------- + $?(@."a" < 10) +(1 row) + +select '$ ? (@.a < .1e1)'::jsonpath; + jsonpath +--------------- + $?(@."a" < 1) +(1 row) + +select '$ ? (@.a < -.1e1)'::jsonpath; + jsonpath +---------------- + $?(@."a" < -1) +(1 row) + +select '$ ? (@.a < +.1e1)'::jsonpath; + jsonpath +--------------- + $?(@."a" < 1) +(1 row) + +select '$ ? (@.a < 0.1e1)'::jsonpath; + jsonpath +--------------- + $?(@."a" < 1) +(1 row) + +select '$ ? (@.a < -0.1e1)'::jsonpath; + jsonpath +---------------- + $?(@."a" < -1) +(1 row) + +select '$ ? (@.a < +0.1e1)'::jsonpath; + jsonpath +--------------- + $?(@."a" < 1) +(1 row) + +select '$ ? (@.a < 10.1e1)'::jsonpath; + jsonpath +----------------- + $?(@."a" < 101) +(1 row) + +select '$ ? (@.a < -10.1e1)'::jsonpath; + jsonpath +------------------ + $?(@."a" < -101) +(1 row) + +select '$ ? (@.a < +10.1e1)'::jsonpath; + jsonpath +----------------- + $?(@."a" < 101) +(1 row) + +select '$ ? (@.a < 1e-1)'::jsonpath; + jsonpath +----------------- + $?(@."a" < 0.1) +(1 row) + +select '$ ? (@.a < -1e-1)'::jsonpath; + jsonpath +------------------ + $?(@."a" < -0.1) +(1 row) + +select '$ ? (@.a < +1e-1)'::jsonpath; + jsonpath +----------------- + $?(@."a" < 0.1) +(1 row) + +select '$ ? (@.a < .1e-1)'::jsonpath; + jsonpath +------------------ + $?(@."a" < 0.01) +(1 row) + +select '$ ? (@.a < -.1e-1)'::jsonpath; + jsonpath +------------------- + $?(@."a" < -0.01) +(1 row) + +select '$ ? (@.a < +.1e-1)'::jsonpath; + jsonpath +------------------ + $?(@."a" < 0.01) +(1 row) + +select '$ ? (@.a < 0.1e-1)'::jsonpath; + jsonpath +------------------ + $?(@."a" < 0.01) +(1 row) + +select '$ ? (@.a < -0.1e-1)'::jsonpath; + jsonpath +------------------- + $?(@."a" < -0.01) +(1 row) + +select '$ ? (@.a < +0.1e-1)'::jsonpath; + jsonpath +------------------ + $?(@."a" < 0.01) +(1 row) + +select '$ ? (@.a < 10.1e-1)'::jsonpath; + jsonpath +------------------ + $?(@."a" < 1.01) +(1 row) + +select '$ ? (@.a < -10.1e-1)'::jsonpath; + jsonpath +------------------- + $?(@."a" < -1.01) +(1 row) + +select '$ ? (@.a < +10.1e-1)'::jsonpath; + jsonpath +------------------ + $?(@."a" < 1.01) +(1 row) + +select '$ ? (@.a < 1e+1)'::jsonpath; + jsonpath +---------------- + $?(@."a" < 10) +(1 row) + +select '$ ? (@.a < -1e+1)'::jsonpath; + jsonpath +----------------- + $?(@."a" < -10) +(1 row) + +select '$ ? (@.a < +1e+1)'::jsonpath; + jsonpath +---------------- + $?(@."a" < 10) +(1 row) + +select '$ ? (@.a < .1e+1)'::jsonpath; + jsonpath +--------------- + $?(@."a" < 1) +(1 row) + +select '$ ? (@.a < -.1e+1)'::jsonpath; + jsonpath +---------------- + $?(@."a" < -1) +(1 row) + +select '$ ? (@.a < +.1e+1)'::jsonpath; + jsonpath +--------------- + $?(@."a" < 1) +(1 row) + +select '$ ? (@.a < 0.1e+1)'::jsonpath; + jsonpath +--------------- + $?(@."a" < 1) +(1 row) + +select '$ ? (@.a < -0.1e+1)'::jsonpath; + jsonpath +---------------- + $?(@."a" < -1) +(1 row) + +select '$ ? (@.a < +0.1e+1)'::jsonpath; + jsonpath +--------------- + $?(@."a" < 1) +(1 row) + +select '$ ? (@.a < 10.1e+1)'::jsonpath; + jsonpath +----------------- + $?(@."a" < 101) +(1 row) + +select '$ ? (@.a < -10.1e+1)'::jsonpath; + jsonpath +------------------ + $?(@."a" < -101) +(1 row) + +select '$ ? (@.a < +10.1e+1)'::jsonpath; + jsonpath +----------------- + $?(@."a" < 101) +(1 row) + diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index ad9434f..20b2391 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -104,7 +104,12 @@ test: publication subscription # ---------- # Another group of parallel tests # ---------- -test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combocid tsearch tsdicts foreign_data window xmlmap functional_deps advisory_lock json jsonb json_encoding indirect_toast equivclass +test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combocid tsearch tsdicts foreign_data window xmlmap functional_deps advisory_lock indirect_toast equivclass + +# ---------- +# Another group of parallel tests (JSON related) +# ---------- +test: json jsonb json_encoding jsonpath jsonb_jsonpath # ---------- # Another group of parallel tests diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index 27cd498..93bb283 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -158,6 +158,8 @@ test: advisory_lock test: json test: jsonb test: json_encoding +test: jsonpath +test: jsonb_jsonpath test: indirect_toast test: equivclass test: plancache diff --git a/src/test/regress/sql/jsonb_jsonpath.sql b/src/test/regress/sql/jsonb_jsonpath.sql new file mode 100644 index 0000000..8b36a30 --- /dev/null +++ b/src/test/regress/sql/jsonb_jsonpath.sql @@ -0,0 +1,383 @@ +select jsonb '{"a": 12}' @? '$.a.b'; +select jsonb '{"a": 12}' @? '$.b'; +select jsonb '{"a": {"a": 12}}' @? '$.a.a'; +select jsonb '{"a": {"a": 12}}' @? '$.*.a'; +select jsonb '{"b": {"a": 12}}' @? '$.*.a'; +select jsonb '{}' @? '$.*'; +select jsonb '{"a": 1}' @? '$.*'; +select jsonb '{"a": {"b": 1}}' @? 'lax $.**{1}'; +select jsonb '{"a": {"b": 1}}' @? 'lax $.**{2}'; +select jsonb '{"a": {"b": 1}}' @? 'lax $.**{3}'; +select jsonb '[]' @? '$[*]'; +select jsonb '[1]' @? '$[*]'; +select jsonb '[1]' @? '$[1]'; +select jsonb '[1]' @? 'strict $[1]'; +select jsonb '[1]' @* 'strict $[1]'; +select jsonb '[1]' @? '$[0]'; +select jsonb '[1]' @? '$[0.3]'; +select jsonb '[1]' @? '$[0.5]'; +select jsonb '[1]' @? '$[0.9]'; +select jsonb '[1]' @? '$[1.2]'; +select jsonb '[1]' @? 'strict $[1.2]'; +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[*])'; +select jsonb '{"a": [1,2,3], "b": [3,4,"5"]}' @? 'strict $ ? (@.a[*] >= @.b[*])'; +select jsonb '{"a": [1,2,3], "b": [3,4,null]}' @? '$ ? (@.a[*] >= @.b[*])'; +select jsonb '1' @? '$ ? ((@ == "1") is unknown)'; +select jsonb '1' @? '$ ? ((@ == 1) is unknown)'; +select jsonb '[{"a": 1}, {"a": 2}]' @? '$[0 to 1] ? (@.a > 1)'; + +select jsonb '{"a": 12, "b": {"a": 13}}' @* '$.a'; +select jsonb '{"a": 12, "b": {"a": 13}}' @* '$.b'; +select jsonb '{"a": 12, "b": {"a": 13}}' @* '$.*'; +select jsonb '{"a": 12, "b": {"a": 13}}' @* 'lax $.*.a'; +select jsonb '[12, {"a": 13}, {"b": 14}]' @* 'lax $[*].a'; +select jsonb '[12, {"a": 13}, {"b": 14}]' @* 'lax $[*].*'; +select jsonb '[12, {"a": 13}, {"b": 14}]' @* 'lax $[0].a'; +select jsonb '[12, {"a": 13}, {"b": 14}]' @* 'lax $[1].a'; +select jsonb '[12, {"a": 13}, {"b": 14}]' @* 'lax $[2].a'; +select jsonb '[12, {"a": 13}, {"b": 14}]' @* 'lax $[0,1].a'; +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 '[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 '[1,2,3]' @* '$[last]'; +select jsonb '[1,2,3]' @* '$[last - 1]'; +select jsonb '[1,2,3]' @* '$[last ? (@.type() == "number")]'; +select jsonb '[1,2,3]' @* '$[last ? (@.type() == "string")]'; + +select * from jsonpath_query(jsonb '{"a": 10}', '$'); +select * from jsonpath_query(jsonb '{"a": 10}', '$ ? (.a < $value)'); +select * from jsonpath_query(jsonb '{"a": 10}', '$ ? (.a < $value)', '{"value" : 13}'); +select * from jsonpath_query(jsonb '{"a": 10}', '$ ? (.a < $value)', '{"value" : 8}'); +select * from jsonpath_query(jsonb '{"a": 10}', '$.a ? (@ < $value)', '{"value" : 13}'); +select * from jsonpath_query(jsonb '[10,11,12,13,14,15]', '$[*] ? (@ < $value)', '{"value" : 13}'); +select * from jsonpath_query(jsonb '[10,11,12,13,14,15]', '$[0,1] ? (@ < $value)', '{"value" : 13}'); +select * from jsonpath_query(jsonb '[10,11,12,13,14,15]', '$[0 to 2] ? (@ < $value)', '{"value" : 15}'); +select * from jsonpath_query(jsonb '[1,"1",2,"2",null]', '$[*] ? (@ == "1")'); +select * from jsonpath_query(jsonb '[1,"1",2,"2",null]', '$[*] ? (@ == $value)', '{"value" : "1"}'); +select * from jsonpath_query(jsonb '[1, "2", null]', '$[*] ? (@ != null)'); +select * from jsonpath_query(jsonb '[1, "2", null]', '$[*] ? (@ == null)'); + +select jsonb '{"a": {"b": 1}}' @* 'lax $.**'; +select jsonb '{"a": {"b": 1}}' @* 'lax $.**{1}'; +select jsonb '{"a": {"b": 1}}' @* 'lax $.**{1,}'; +select jsonb '{"a": {"b": 1}}' @* 'lax $.**{2}'; +select jsonb '{"a": {"b": 1}}' @* 'lax $.**{2,}'; +select jsonb '{"a": {"b": 1}}' @* 'lax $.**{3,}'; +select jsonb '{"a": {"b": 1}}' @* 'lax $.**.b ? (@ > 0)'; +select jsonb '{"a": {"b": 1}}' @* 'lax $.**{0}.b ? (@ > 0)'; +select jsonb '{"a": {"b": 1}}' @* 'lax $.**{1}.b ? (@ > 0)'; +select jsonb '{"a": {"b": 1}}' @* 'lax $.**{0,}.b ? (@ > 0)'; +select jsonb '{"a": {"b": 1}}' @* 'lax $.**{1,}.b ? (@ > 0)'; +select jsonb '{"a": {"b": 1}}' @* 'lax $.**{1,2}.b ? (@ > 0)'; +select jsonb '{"a": {"c": {"b": 1}}}' @* 'lax $.**.b ? (@ > 0)'; +select jsonb '{"a": {"c": {"b": 1}}}' @* 'lax $.**{0}.b ? (@ > 0)'; +select jsonb '{"a": {"c": {"b": 1}}}' @* 'lax $.**{1}.b ? (@ > 0)'; +select jsonb '{"a": {"c": {"b": 1}}}' @* 'lax $.**{0,}.b ? (@ > 0)'; +select jsonb '{"a": {"c": {"b": 1}}}' @* 'lax $.**{1,}.b ? (@ > 0)'; +select jsonb '{"a": {"c": {"b": 1}}}' @* 'lax $.**{1,2}.b ? (@ > 0)'; +select jsonb '{"a": {"c": {"b": 1}}}' @* 'lax $.**{2,3}.b ? (@ > 0)'; + +select jsonb '{"a": {"b": 1}}' @? '$.**.b ? ( @ > 0)'; +select jsonb '{"a": {"b": 1}}' @? '$.**{0}.b ? ( @ > 0)'; +select jsonb '{"a": {"b": 1}}' @? '$.**{1}.b ? ( @ > 0)'; +select jsonb '{"a": {"b": 1}}' @? '$.**{0,}.b ? ( @ > 0)'; +select jsonb '{"a": {"b": 1}}' @? '$.**{1,}.b ? ( @ > 0)'; +select jsonb '{"a": {"b": 1}}' @? '$.**{1,2}.b ? ( @ > 0)'; +select jsonb '{"a": {"c": {"b": 1}}}' @? '$.**.b ? ( @ > 0)'; +select jsonb '{"a": {"c": {"b": 1}}}' @? '$.**{0}.b ? ( @ > 0)'; +select jsonb '{"a": {"c": {"b": 1}}}' @? '$.**{1}.b ? ( @ > 0)'; +select jsonb '{"a": {"c": {"b": 1}}}' @? '$.**{0,}.b ? ( @ > 0)'; +select jsonb '{"a": {"c": {"b": 1}}}' @? '$.**{1,}.b ? ( @ > 0)'; +select jsonb '{"a": {"c": {"b": 1}}}' @? '$.**{1,2}.b ? ( @ > 0)'; +select jsonb '{"a": {"c": {"b": 1}}}' @? '$.**{2,3}.b ? ( @ > 0)'; + +select jsonb '{"g": {"x": 2}}' @* '$.g ? (exists (@.x))'; +select jsonb '{"g": {"x": 2}}' @* '$.g ? (exists (@.y))'; +select jsonb '{"g": {"x": 2}}' @* '$.g ? (exists (@.x ? (@ >= 2) ))'; + +--test ternary logic +select + x, y, + jsonpath_query( + jsonb '[true, false, null]', + '$[*] ? (@ == true && ($x == true && $y == true) || + @ == false && !($x == true && $y == true) || + @ == null && ($x == true && $y == true) is unknown)', + jsonb_build_object('x', x, 'y', y) + ) as "x && y" +from + (values (jsonb 'true'), ('false'), ('"null"')) x(x), + (values (jsonb 'true'), ('false'), ('"null"')) y(y); + +select + x, y, + jsonpath_query( + jsonb '[true, false, null]', + '$[*] ? (@ == true && ($x == true || $y == true) || + @ == false && !($x == true || $y == true) || + @ == null && ($x == true || $y == true) is unknown)', + jsonb_build_object('x', x, 'y', y) + ) as "x || y" +from + (values (jsonb 'true'), ('false'), ('"null"')) x(x), + (values (jsonb 'true'), ('false'), ('"null"')) y(y); + +select jsonb '{"a": 1, "b":1}' @? '$ ? (.a == .b)'; +select jsonb '{"c": {"a": 1, "b":1}}' @? '$ ? (.a == .b)'; +select jsonb '{"c": {"a": 1, "b":1}}' @? '$.c ? (.a == .b)'; +select jsonb '{"c": {"a": 1, "b":1}}' @? '$.c ? ($.c.a == .b)'; +select jsonb '{"c": {"a": 1, "b":1}}' @? '$.* ? (.a == .b)'; +select jsonb '{"a": 1, "b":1}' @? '$.** ? (.a == .b)'; +select jsonb '{"c": {"a": 1, "b":1}}' @? '$.** ? (.a == .b)'; + +select jsonb '{"c": {"a": 2, "b":1}}' @* '$.** ? (.a == 1 + 1)'; +select jsonb '{"c": {"a": 2, "b":1}}' @* '$.** ? (.a == (1 + 1))'; +select jsonb '{"c": {"a": 2, "b":1}}' @* '$.** ? (.a == .b + 1)'; +select jsonb '{"c": {"a": 2, "b":1}}' @* '$.** ? (.a == (.b + 1))'; +select jsonb '{"c": {"a": -1, "b":1}}' @? '$.** ? (.a == - 1)'; +select jsonb '{"c": {"a": -1, "b":1}}' @? '$.** ? (.a == -1)'; +select jsonb '{"c": {"a": -1, "b":1}}' @? '$.** ? (.a == -.b)'; +select jsonb '{"c": {"a": -1, "b":1}}' @? '$.** ? (.a == - .b)'; +select jsonb '{"c": {"a": 0, "b":1}}' @? '$.** ? (.a == 1 - .b)'; +select jsonb '{"c": {"a": 2, "b":1}}' @? '$.** ? (.a == 1 - - .b)'; +select jsonb '{"c": {"a": 0, "b":1}}' @? '$.** ? (.a == 1 - +.b)'; +select jsonb '[1,2,3]' @? '$ ? (+@[*] > +2)'; +select jsonb '[1,2,3]' @? '$ ? (+@[*] > +3)'; +select jsonb '[1,2,3]' @? '$ ? (-@[*] < -2)'; +select jsonb '[1,2,3]' @? '$ ? (-@[*] < -3)'; +select jsonb '1' @? '$ ? ($ > 0)'; + +-- arithmetic errors +select jsonb '[1,2,0,3]' @* '$[*] ? (2 / @ > 0)'; +select jsonb '[1,2,0,3]' @* '$[*] ? ((2 / @ > 0) is unknown)'; +select jsonb '0' @* '1 / $'; + +-- unwrapping of operator arguments in lax mode +select jsonb '{"a": [2]}' @* 'lax $.a * 3'; +select jsonb '{"a": [2]}' @* 'lax $.a + 3'; +select jsonb '{"a": [2, 3, 4]}' @* 'lax -$.a'; +-- should fail +select jsonb '{"a": [1, 2]}' @* 'lax $.a * 3'; + +-- extension: boolean expressions +select jsonb '2' @* '$ > 1'; +select jsonb '2' @* '$ <= 1'; +select jsonb '2' @* '$ == "2"'; + +select jsonb '2' @~ '$ > 1'; +select jsonb '2' @~ '$ <= 1'; +select jsonb '2' @~ '$ == "2"'; +select jsonb '2' @~ '1'; +select jsonb '{}' @~ '$'; +select jsonb '[]' @~ '$'; +select jsonb '[1,2,3]' @~ '$[*]'; +select jsonb '[]' @~ '$[*]'; +select jsonpath_predicate(jsonb '[[1, true], [2, false]]', 'strict $[*] ? (@[0] > $x) [1]', '{"x": 1}'); +select jsonpath_predicate(jsonb '[[1, true], [2, false]]', 'strict $[*] ? (@[0] < $x) [1]', '{"x": 2}'); + +select jsonb '[null,1,true,"a",[],{}]' @* '$.type()'; +select jsonb '[null,1,true,"a",[],{}]' @* 'lax $.type()'; +select jsonb '[null,1,true,"a",[],{}]' @* '$[*].type()'; +select jsonb 'null' @* 'null.type()'; +select jsonb 'null' @* 'true.type()'; +select jsonb 'null' @* '123.type()'; +select jsonb 'null' @* '"123".type()'; + +select jsonb '{"a": 2}' @* '($.a - 5).abs() + 10'; +select jsonb '{"a": 2.5}' @* '-($.a * $.a).floor() + 10'; +select jsonb '[1, 2, 3]' @* '($[*] > 2) ? (@ == true)'; +select jsonb '[1, 2, 3]' @* '($[*] > 3).type()'; +select jsonb '[1, 2, 3]' @* '($[*].a > 3).type()'; +select jsonb '[1, 2, 3]' @* 'strict ($[*].a > 3).type()'; + +select jsonb '[1,null,true,"11",[],[1],[1,2,3],{},{"a":1,"b":2}]' @* 'strict $[*].size()'; +select jsonb '[1,null,true,"11",[],[1],[1,2,3],{},{"a":1,"b":2}]' @* 'lax $[*].size()'; + +select jsonb '[0, 1, -2, -3.4, 5.6]' @* '$[*].abs()'; +select jsonb '[0, 1, -2, -3.4, 5.6]' @* '$[*].floor()'; +select jsonb '[0, 1, -2, -3.4, 5.6]' @* '$[*].ceiling()'; +select jsonb '[0, 1, -2, -3.4, 5.6]' @* '$[*].ceiling().abs()'; +select jsonb '[0, 1, -2, -3.4, 5.6]' @* '$[*].ceiling().abs().type()'; + +select jsonb '[{},1]' @* '$[*].keyvalue()'; +select jsonb '{}' @* '$.keyvalue()'; +select jsonb '{"a": 1, "b": [1, 2], "c": {"a": "bbb"}}' @* '$.keyvalue()'; +select jsonb '[{"a": 1, "b": [1, 2]}, {"c": {"a": "bbb"}}]' @* '$[*].keyvalue()'; +select jsonb '[{"a": 1, "b": [1, 2]}, {"c": {"a": "bbb"}}]' @* 'strict $.keyvalue()'; +select jsonb '[{"a": 1, "b": [1, 2]}, {"c": {"a": "bbb"}}]' @* 'lax $.keyvalue()'; + +select jsonb 'null' @* '$.double()'; +select jsonb 'true' @* '$.double()'; +select jsonb '[]' @* '$.double()'; +select jsonb '[]' @* 'strict $.double()'; +select jsonb '{}' @* '$.double()'; +select jsonb '1.23' @* '$.double()'; +select jsonb '"1.23"' @* '$.double()'; +select jsonb '"1.23aaa"' @* '$.double()'; + +select jsonb '["", "a", "abc", "abcabc"]' @* '$[*] ? (@ starts with "abc")'; +select jsonb '["", "a", "abc", "abcabc"]' @* 'strict $ ? (@[*] starts with "abc")'; +select jsonb '["", "a", "abd", "abdabc"]' @* 'strict $ ? (@[*] starts with "abc")'; +select jsonb '["abc", "abcabc", null, 1]' @* 'strict $ ? (@[*] starts with "abc")'; +select jsonb '["abc", "abcabc", null, 1]' @* 'strict $ ? ((@[*] starts with "abc") is unknown)'; +select jsonb '[[null, 1, "abc", "abcabc"]]' @* 'lax $ ? (@[*] starts with "abc")'; +select jsonb '[[null, 1, "abd", "abdabc"]]' @* 'lax $ ? ((@[*] starts with "abc") is unknown)'; +select jsonb '[null, 1, "abd", "abdabc"]' @* 'lax $[*] ? ((@ starts with "abc") is unknown)'; + +select jsonb '[null, 1, "abc", "abd", "aBdC", "abdacb", "babc"]' @* 'lax $[*] ? (@ like_regex "^ab.*c")'; +select jsonb '[null, 1, "abc", "abd", "aBdC", "abdacb", "babc"]' @* 'lax $[*] ? (@ like_regex "^ab.*c" flag "i")'; + +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()'; + +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")'; +select jsonb '"10-03-2017 12:34"' @* '$.datetime("dd-mm-yyyy").type()'; + +select jsonb '"10-03-2017 12:34"' @* ' $.datetime("dd-mm-yyyy HH24:MI").type()'; +select jsonb '"10-03-2017 12:34 +05:20"' @* '$.datetime("dd-mm-yyyy HH24:MI TZH:TZM").type()'; +select jsonb '"12:34:56"' @* '$.datetime("HH24:MI:SS").type()'; +select jsonb '"12:34:56 +05:20"' @* '$.datetime("HH24:MI:SS TZH:TZM").type()'; + +set time zone '+00'; + +select jsonb '"10-03-2017 12:34"' @* '$.datetime("dd-mm-yyyy HH24:MI")'; +select jsonb '"10-03-2017 12:34"' @* '$.datetime("dd-mm-yyyy HH24:MI TZH")'; +select jsonb '"10-03-2017 12:34"' @* '$.datetime("dd-mm-yyyy HH24:MI TZH", "+00")'; +select jsonb '"10-03-2017 12:34"' @* '$.datetime("dd-mm-yyyy HH24:MI TZH", "+00:12")'; +select jsonb '"10-03-2017 12:34"' @* '$.datetime("dd-mm-yyyy HH24:MI TZH", "-00:12:34")'; +select jsonb '"10-03-2017 12:34"' @* '$.datetime("dd-mm-yyyy HH24:MI TZH", "UTC")'; +select jsonb '"10-03-2017 12:34 +05"' @* '$.datetime("dd-mm-yyyy HH24:MI TZH")'; +select jsonb '"10-03-2017 12:34 -05"' @* '$.datetime("dd-mm-yyyy HH24:MI TZH")'; +select jsonb '"10-03-2017 12:34 +05:20"' @* '$.datetime("dd-mm-yyyy HH24:MI TZH:TZM")'; +select jsonb '"10-03-2017 12:34 -05:20"' @* '$.datetime("dd-mm-yyyy HH24:MI TZH:TZM")'; +select jsonb '"12:34"' @* '$.datetime("HH24:MI")'; +select jsonb '"12:34"' @* '$.datetime("HH24:MI TZH")'; +select jsonb '"12:34"' @* '$.datetime("HH24:MI TZH", "+00")'; +select jsonb '"12:34 +05"' @* '$.datetime("HH24:MI TZH")'; +select jsonb '"12:34 -05"' @* '$.datetime("HH24:MI TZH")'; +select jsonb '"12:34 +05:20"' @* '$.datetime("HH24:MI TZH:TZM")'; +select jsonb '"12:34 -05:20"' @* '$.datetime("HH24:MI TZH:TZM")'; + +set time zone '+10'; + +select jsonb '"10-03-2017 12:34"' @* '$.datetime("dd-mm-yyyy HH24:MI")'; +select jsonb '"10-03-2017 12:34"' @* '$.datetime("dd-mm-yyyy HH24:MI TZH")'; +select jsonb '"10-03-2017 12:34"' @* '$.datetime("dd-mm-yyyy HH24:MI TZH", "+10")'; +select jsonb '"10-03-2017 12:34 +05"' @* '$.datetime("dd-mm-yyyy HH24:MI TZH")'; +select jsonb '"10-03-2017 12:34 -05"' @* '$.datetime("dd-mm-yyyy HH24:MI TZH")'; +select jsonb '"10-03-2017 12:34 +05:20"' @* '$.datetime("dd-mm-yyyy HH24:MI TZH:TZM")'; +select jsonb '"10-03-2017 12:34 -05:20"' @* '$.datetime("dd-mm-yyyy HH24:MI TZH:TZM")'; +select jsonb '"12:34"' @* '$.datetime("HH24:MI")'; +select jsonb '"12:34"' @* '$.datetime("HH24:MI TZH")'; +select jsonb '"12:34"' @* '$.datetime("HH24:MI TZH", "+10")'; +select jsonb '"12:34 +05"' @* '$.datetime("HH24:MI TZH")'; +select jsonb '"12:34 -05"' @* '$.datetime("HH24:MI TZH")'; +select jsonb '"12:34 +05:20"' @* '$.datetime("HH24:MI TZH:TZM")'; +select jsonb '"12:34 -05:20"' @* '$.datetime("HH24:MI TZH:TZM")'; + +set time zone default; + +select jsonb '"2017-03-10"' @* '$.datetime().type()'; +select jsonb '"2017-03-10"' @* '$.datetime()'; +select jsonb '"2017-03-10 12:34:56"' @* '$.datetime().type()'; +select jsonb '"2017-03-10 12:34:56"' @* '$.datetime()'; +select jsonb '"2017-03-10 12:34:56 +3"' @* '$.datetime().type()'; +select jsonb '"2017-03-10 12:34:56 +3"' @* '$.datetime()'; +select jsonb '"2017-03-10 12:34:56 +3:10"' @* '$.datetime().type()'; +select jsonb '"2017-03-10 12:34:56 +3:10"' @* '$.datetime()'; +select jsonb '"12:34:56"' @* '$.datetime().type()'; +select jsonb '"12:34:56"' @* '$.datetime()'; +select jsonb '"12:34:56 +3"' @* '$.datetime().type()'; +select jsonb '"12:34:56 +3"' @* '$.datetime()'; +select jsonb '"12:34:56 +3:10"' @* '$.datetime().type()'; +select jsonb '"12:34:56 +3:10"' @* '$.datetime()'; + +set time zone '+00'; + +-- date comparison +select jsonb + '["2017-03-10", "2017-03-11", "2017-03-09", "12:34:56", "01:02:03 +04", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03 +04", "2017-03-10 03:00:00 +03"]' @* + '$[*].datetime() ? (@ == "10.03.2017".datetime("dd.mm.yyyy"))'; +select jsonb + '["2017-03-10", "2017-03-11", "2017-03-09", "12:34:56", "01:02:03 +04", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03 +04", "2017-03-10 03:00:00 +03"]' @* + '$[*].datetime() ? (@ >= "10.03.2017".datetime("dd.mm.yyyy"))'; +select jsonb + '["2017-03-10", "2017-03-11", "2017-03-09", "12:34:56", "01:02:03 +04", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03 +04", "2017-03-10 03:00:00 +03"]' @* + '$[*].datetime() ? (@ < "10.03.2017".datetime("dd.mm.yyyy"))'; + +-- time comparison +select jsonb + '["12:34:00", "12:35:00", "12:36:00", "12:35:00 +00", "12:35:00 +01", "13:35:00 +01", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +01"]' @* + '$[*].datetime() ? (@ == "12:35".datetime("HH24:MI"))'; +select jsonb + '["12:34:00", "12:35:00", "12:36:00", "12:35:00 +00", "12:35:00 +01", "13:35:00 +01", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +01"]' @* + '$[*].datetime() ? (@ >= "12:35".datetime("HH24:MI"))'; +select jsonb + '["12:34:00", "12:35:00", "12:36:00", "12:35:00 +00", "12:35:00 +01", "13:35:00 +01", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +01"]' @* + '$[*].datetime() ? (@ < "12:35".datetime("HH24:MI"))'; + +-- timetz comparison +select jsonb + '["12:34:00 +01", "12:35:00 +01", "12:36:00 +01", "12:35:00 +02", "12:35:00 -02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +1"]' @* + '$[*].datetime() ? (@ == "12:35 +1".datetime("HH24:MI TZH"))'; +select jsonb + '["12:34:00 +01", "12:35:00 +01", "12:36:00 +01", "12:35:00 +02", "12:35:00 -02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +1"]' @* + '$[*].datetime() ? (@ >= "12:35 +1".datetime("HH24:MI TZH"))'; +select jsonb + '["12:34:00 +01", "12:35:00 +01", "12:36:00 +01", "12:35:00 +02", "12:35:00 -02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +1"]' @* + '$[*].datetime() ? (@ < "12:35 +1".datetime("HH24:MI TZH"))'; + +-- timestamp comparison +select jsonb + '["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00 +01", "2017-03-10 13:35:00 +01", "2017-03-10 12:35:00 -01", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56 +01"]' @* + '$[*].datetime() ? (@ == "10.03.2017 12:35".datetime("dd.mm.yyyy HH24:MI"))'; +select jsonb + '["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00 +01", "2017-03-10 13:35:00 +01", "2017-03-10 12:35:00 -01", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56 +01"]' @* + '$[*].datetime() ? (@ >= "10.03.2017 12:35".datetime("dd.mm.yyyy HH24:MI"))'; +select jsonb + '["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00 +01", "2017-03-10 13:35:00 +01", "2017-03-10 12:35:00 -01", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56 +01"]' @* + '$[*].datetime() ? (@ < "10.03.2017 12:35".datetime("dd.mm.yyyy HH24:MI"))'; + +-- timestamptz comparison +select jsonb + '["2017-03-10 12:34:00 +01", "2017-03-10 12:35:00 +01", "2017-03-10 12:36:00 +01", "2017-03-10 12:35:00 +02", "2017-03-10 12:35:00 -02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56 +01"]' @* + '$[*].datetime() ? (@ == "10.03.2017 12:35 +1".datetime("dd.mm.yyyy HH24:MI TZH"))'; +select jsonb + '["2017-03-10 12:34:00 +01", "2017-03-10 12:35:00 +01", "2017-03-10 12:36:00 +01", "2017-03-10 12:35:00 +02", "2017-03-10 12:35:00 -02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56 +01"]' @* + '$[*].datetime() ? (@ >= "10.03.2017 12:35 +1".datetime("dd.mm.yyyy HH24:MI TZH"))'; +select jsonb + '["2017-03-10 12:34:00 +01", "2017-03-10 12:35:00 +01", "2017-03-10 12:36:00 +01", "2017-03-10 12:35:00 +02", "2017-03-10 12:35:00 -02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56 +01"]' @* + '$[*].datetime() ? (@ < "10.03.2017 12:35 +1".datetime("dd.mm.yyyy HH24:MI TZH"))'; + +set time zone default; + +-- jsonpath operators + +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 ? (@ == 1)'; +SELECT jsonb '[{"a": 1}, {"a": 2}]' @# '$[*].a ? (@ > 10)'; + +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'; diff --git a/src/test/regress/sql/jsonpath.sql b/src/test/regress/sql/jsonpath.sql new file mode 100644 index 0000000..32c33f1 --- /dev/null +++ b/src/test/regress/sql/jsonpath.sql @@ -0,0 +1,140 @@ +--jsonpath io + +select ''::jsonpath; +select '$'::jsonpath; +select 'strict $'::jsonpath; +select 'lax $'::jsonpath; +select '$.a'::jsonpath; +select '$.a.v'::jsonpath; +select '$.a.*'::jsonpath; +select '$.*[*]'::jsonpath; +select '$.a[*]'::jsonpath; +select '$.a[*][*]'::jsonpath; +select '$[*]'::jsonpath; +select '$[0]'::jsonpath; +select '$[*][0]'::jsonpath; +select '$[*].a'::jsonpath; +select '$[*][0].a.b'::jsonpath; +select '$.a.**.b'::jsonpath; +select '$.a.**{2}.b'::jsonpath; +select '$.a.**{2,2}.b'::jsonpath; +select '$.a.**{2,5}.b'::jsonpath; +select '$.a.**{,5}.b'::jsonpath; +select '$.a.**{5,}.b'::jsonpath; +select '$+1'::jsonpath; +select '$-1'::jsonpath; +select '$--+1'::jsonpath; +select '$.a/+-1'::jsonpath; + +select '$.g ? ($.a == 1)'::jsonpath; +select '$.g ? (@ == 1)'::jsonpath; +select '$.g ? (.a == 1)'::jsonpath; +select '$.g ? (@.a == 1)'::jsonpath; +select '$.g ? (@.a == 1 || @.a == 4)'::jsonpath; +select '$.g ? (@.a == 1 && @.a == 4)'::jsonpath; +select '$.g ? (@.a == 1 || @.a == 4 && @.b == 7)'::jsonpath; +select '$.g ? (@.a == 1 || !(@.a == 4) && @.b == 7)'::jsonpath; +select '$.g ? (@.a == 1 || !(@.x >= 123 || @.a == 4) && @.b == 7)'::jsonpath; +select '$.g ? (.x >= @[*]?(@.a > "abc"))'::jsonpath; +select '$.g ? ((@.x >= 123 || @.a == 4) is unknown)'::jsonpath; +select '$.g ? (exists (.x))'::jsonpath; +select '$.g ? (exists (@.x ? (@ == 14)))'::jsonpath; +select '$.g ? (exists (.x ? (@ == 14)))'::jsonpath; +select '$.g ? ((@.x >= 123 || @.a == 4) && exists (.x ? (@ == 14)))'::jsonpath; +select '$.g ? (+@.x >= +-(+@.a + 2))'::jsonpath; + +select '$a'::jsonpath; +select '$a.b'::jsonpath; +select '$a[*]'::jsonpath; +select '$.g ? (@.zip == $zip)'::jsonpath; +select '$.a[1,2, 3 to 16]'::jsonpath; +select '$.a[$a + 1, ($b[*]) to -(@[0] * 2)]'::jsonpath; +select '$.a[$.a.size() - 3]'::jsonpath; +select 'last'::jsonpath; +select '"last"'::jsonpath; +select '$.last'::jsonpath; +select '$ ? (last > 0)'::jsonpath; +select '$[last]'::jsonpath; +select '$[@ ? (last > 0)]'::jsonpath; + +select 'null.type()'::jsonpath; +select '1.type()'::jsonpath; +select '"aaa".type()'::jsonpath; +select 'true.type()'::jsonpath; +select '$.datetime()'::jsonpath; +select '$.datetime("datetime template")'::jsonpath; + +select '$ ? (@ starts with "abc")'::jsonpath; +select '$ ? (@ starts with $var)'::jsonpath; + +select '$ ? (@ like_regex "(invalid pattern")'::jsonpath; +select '$ ? (@ like_regex "pattern")'::jsonpath; +select '$ ? (@ like_regex "pattern" flag "")'::jsonpath; +select '$ ? (@ like_regex "pattern" flag "i")'::jsonpath; +select '$ ? (@ like_regex "pattern" flag "is")'::jsonpath; +select '$ ? (@ like_regex "pattern" flag "isim")'::jsonpath; +select '$ ? (@ like_regex "pattern" flag "xsms")'::jsonpath; +select '$ ? (@ like_regex "pattern" flag "a")'::jsonpath; + +select '$ < 1'::jsonpath; +select '($ < 1) || $.a.b <= $x'::jsonpath; +select '@ + 1'::jsonpath; + +select '($).a.b'::jsonpath; +select '($.a.b).c.d'::jsonpath; +select '($.a.b + -$.x.y).c.d'::jsonpath; +select '(-+$.a.b).c.d'::jsonpath; +select '1 + ($.a.b + 2).c.d'::jsonpath; +select '1 + ($.a.b > 2).c.d'::jsonpath; +select '($)'::jsonpath; +select '(($))'::jsonpath; +select '((($ + 1)).a + ((2)).b ? ((((@ > 1)) || (exists(@.c)))))'::jsonpath; + +select '$ ? (@.a < 1)'::jsonpath; +select '$ ? (@.a < -1)'::jsonpath; +select '$ ? (@.a < +1)'::jsonpath; +select '$ ? (@.a < .1)'::jsonpath; +select '$ ? (@.a < -.1)'::jsonpath; +select '$ ? (@.a < +.1)'::jsonpath; +select '$ ? (@.a < 0.1)'::jsonpath; +select '$ ? (@.a < -0.1)'::jsonpath; +select '$ ? (@.a < +0.1)'::jsonpath; +select '$ ? (@.a < 10.1)'::jsonpath; +select '$ ? (@.a < -10.1)'::jsonpath; +select '$ ? (@.a < +10.1)'::jsonpath; +select '$ ? (@.a < 1e1)'::jsonpath; +select '$ ? (@.a < -1e1)'::jsonpath; +select '$ ? (@.a < +1e1)'::jsonpath; +select '$ ? (@.a < .1e1)'::jsonpath; +select '$ ? (@.a < -.1e1)'::jsonpath; +select '$ ? (@.a < +.1e1)'::jsonpath; +select '$ ? (@.a < 0.1e1)'::jsonpath; +select '$ ? (@.a < -0.1e1)'::jsonpath; +select '$ ? (@.a < +0.1e1)'::jsonpath; +select '$ ? (@.a < 10.1e1)'::jsonpath; +select '$ ? (@.a < -10.1e1)'::jsonpath; +select '$ ? (@.a < +10.1e1)'::jsonpath; +select '$ ? (@.a < 1e-1)'::jsonpath; +select '$ ? (@.a < -1e-1)'::jsonpath; +select '$ ? (@.a < +1e-1)'::jsonpath; +select '$ ? (@.a < .1e-1)'::jsonpath; +select '$ ? (@.a < -.1e-1)'::jsonpath; +select '$ ? (@.a < +.1e-1)'::jsonpath; +select '$ ? (@.a < 0.1e-1)'::jsonpath; +select '$ ? (@.a < -0.1e-1)'::jsonpath; +select '$ ? (@.a < +0.1e-1)'::jsonpath; +select '$ ? (@.a < 10.1e-1)'::jsonpath; +select '$ ? (@.a < -10.1e-1)'::jsonpath; +select '$ ? (@.a < +10.1e-1)'::jsonpath; +select '$ ? (@.a < 1e+1)'::jsonpath; +select '$ ? (@.a < -1e+1)'::jsonpath; +select '$ ? (@.a < +1e+1)'::jsonpath; +select '$ ? (@.a < .1e+1)'::jsonpath; +select '$ ? (@.a < -.1e+1)'::jsonpath; +select '$ ? (@.a < +.1e+1)'::jsonpath; +select '$ ? (@.a < 0.1e+1)'::jsonpath; +select '$ ? (@.a < -0.1e+1)'::jsonpath; +select '$ ? (@.a < +0.1e+1)'::jsonpath; +select '$ ? (@.a < 10.1e+1)'::jsonpath; +select '$ ? (@.a < -10.1e+1)'::jsonpath; +select '$ ? (@.a < +10.1e+1)'::jsonpath;