From: Nikita Glukhov Date: Fri, 1 Mar 2019 03:15:18 +0300 Subject: [PATCH 04/13] Jsonpath support for json --- src/backend/catalog/system_views.sql | 58 + src/backend/utils/adt/json.c | 855 ++++++++++- src/backend/utils/adt/jsonb.c | 18 - src/backend/utils/adt/jsonb_util.c | 33 +- src/backend/utils/adt/jsonpath_exec.c | 1516 +++++++++++++------ src/include/catalog/pg_operator.dat | 8 + src/include/catalog/pg_proc.dat | 34 + src/include/utils/jsonapi.h | 69 + src/include/utils/jsonb.h | 33 +- src/test/regress/expected/json_jsonpath.out | 2120 +++++++++++++++++++++++++++ src/test/regress/parallel_schedule | 2 +- src/test/regress/serial_schedule | 1 + src/test/regress/sql/json_jsonpath.sql | 477 ++++++ src/tools/pgindent/typedefs.list | 2 + 14 files changed, 4728 insertions(+), 498 deletions(-) create mode 100644 src/test/regress/expected/json_jsonpath.out create mode 100644 src/test/regress/sql/json_jsonpath.sql diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index f5d11d5..5ac1fab 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -1167,6 +1167,64 @@ LANGUAGE INTERNAL STRICT IMMUTABLE PARALLEL SAFE AS 'jsonb_path_query_first'; +CREATE OR REPLACE FUNCTION + jsonb_path_query_first_text(target jsonb, path jsonpath, vars jsonb DEFAULT '{}', + silent boolean DEFAULT false) +RETURNS text +LANGUAGE INTERNAL +STRICT IMMUTABLE PARALLEL SAFE +AS 'jsonb_path_query_first_text'; + + + +CREATE OR REPLACE FUNCTION + json_path_exists(target json, path jsonpath, vars json DEFAULT '{}', + silent boolean DEFAULT false) +RETURNS boolean +LANGUAGE INTERNAL +STRICT IMMUTABLE PARALLEL SAFE +AS 'json_path_exists'; + +CREATE OR REPLACE FUNCTION + json_path_match(target json, path jsonpath, vars json DEFAULT '{}', + silent boolean DEFAULT false) +RETURNS boolean +LANGUAGE INTERNAL +STRICT IMMUTABLE PARALLEL SAFE +AS 'json_path_match'; + +CREATE OR REPLACE FUNCTION + json_path_query(target json, path jsonpath, vars json DEFAULT '{}', + silent boolean DEFAULT false) +RETURNS SETOF json +LANGUAGE INTERNAL +STRICT IMMUTABLE PARALLEL SAFE +AS 'json_path_query'; + +CREATE OR REPLACE FUNCTION + json_path_query_array(target json, path jsonpath, vars json DEFAULT '{}', + silent boolean DEFAULT false) +RETURNS json +LANGUAGE INTERNAL +STRICT IMMUTABLE PARALLEL SAFE +AS 'json_path_query_array'; + +CREATE OR REPLACE FUNCTION + json_path_query_first(target json, path jsonpath, vars json DEFAULT '{}', + silent boolean DEFAULT false) +RETURNS json +LANGUAGE INTERNAL +STRICT IMMUTABLE PARALLEL SAFE +AS 'json_path_query_first'; + +CREATE OR REPLACE FUNCTION + json_path_query_first_text(target json, path jsonpath, vars json DEFAULT '{}', + silent boolean DEFAULT false) +RETURNS text +LANGUAGE INTERNAL +STRICT IMMUTABLE PARALLEL SAFE +AS 'json_path_query_first_text'; + -- -- The default permissions for functions mean that anyone can execute them. -- A number of functions shouldn't be executable by just anyone, but rather diff --git a/src/backend/utils/adt/json.c b/src/backend/utils/adt/json.c index 5239903..803d478 100644 --- a/src/backend/utils/adt/json.c +++ b/src/backend/utils/adt/json.c @@ -106,6 +106,9 @@ static void add_json(Datum val, bool is_null, StringInfo result, Oid val_type, bool key_scalar); static text *catenate_stringinfo_string(StringInfo buffer, const char *addon); +static JsonIterator *JsonIteratorInitFromLex(JsonContainer *jc, + JsonLexContext *lex, JsonIterator *parent); + /* the null action object used for pure validation */ static JsonSemAction nullSemAction = { @@ -127,6 +130,27 @@ lex_peek(JsonLexContext *lex) } /* + * lex_peek_value + * + * get the current look_ahead de-escaped lexeme. +*/ +static inline char * +lex_peek_value(JsonLexContext *lex) +{ + if (lex->token_type == JSON_TOKEN_STRING) + return lex->strval ? pstrdup(lex->strval->data) : NULL; + else + { + int len = lex->token_terminator - lex->token_start; + char *tokstr = palloc(len + 1); + + memcpy(tokstr, lex->token_start, len); + tokstr[len] = '\0'; + return tokstr; + } +} + +/* * lex_accept * * accept the look_ahead token and move the lexer to the next token if the @@ -141,22 +165,8 @@ lex_accept(JsonLexContext *lex, JsonTokenType token, char **lexeme) if (lex->token_type == token) { if (lexeme != NULL) - { - if (lex->token_type == JSON_TOKEN_STRING) - { - if (lex->strval != NULL) - *lexeme = pstrdup(lex->strval->data); - } - else - { - int len = (lex->token_terminator - lex->token_start); - char *tokstr = palloc(len + 1); + *lexeme = lex_peek_value(lex); - memcpy(tokstr, lex->token_start, len); - tokstr[len] = '\0'; - *lexeme = tokstr; - } - } json_lex(lex); return true; } @@ -2573,3 +2583,818 @@ json_typeof(PG_FUNCTION_ARGS) PG_RETURN_TEXT_P(cstring_to_text(type)); } + +/* + * Initialize a JsonContainer from a json text, its type and size. + * 'type' can be JB_FOBJECT, JB_FARRAY, (JB_FARRAY | JB_FSCALAR). + * 'size' is a number of elements/pairs in array/object, or -1 if unknown. + */ +static void +jsonInitContainer(JsonContainerData *jc, char *json, int len, int type, + int size) +{ + if (size < 0 || size > JB_CMASK) + size = JB_CMASK; /* unknown size */ + + jc->data = json; + jc->len = len; + jc->header = type | size; +} + +/* + * Initialize a JsonContainer from a text datum. + */ +static void +jsonInit(JsonContainerData *jc, Datum value) +{ + text *json = DatumGetTextP(value); + JsonLexContext *lex = makeJsonLexContext(json, false); + JsonTokenType tok; + int type; + int size = -1; + + /* Lex exactly one token from the input and check its type. */ + json_lex(lex); + tok = lex_peek(lex); + + switch (tok) + { + case JSON_TOKEN_OBJECT_START: + type = JB_FOBJECT; + lex_accept(lex, tok, NULL); + if (lex_peek(lex) == JSON_TOKEN_OBJECT_END) + size = 0; + break; + case JSON_TOKEN_ARRAY_START: + type = JB_FARRAY; + lex_accept(lex, tok, NULL); + if (lex_peek(lex) == JSON_TOKEN_ARRAY_END) + size = 0; + break; + case JSON_TOKEN_STRING: + case JSON_TOKEN_NUMBER: + case JSON_TOKEN_TRUE: + case JSON_TOKEN_FALSE: + case JSON_TOKEN_NULL: + type = JB_FARRAY | JB_FSCALAR; + size = 1; + break; + default: + elog(ERROR, "unexpected json token: %d", tok); + type = jbvNull; + break; + } + + pfree(lex); + + jsonInitContainer(jc, VARDATA(json), VARSIZE(json) - VARHDRSZ, type, size); +} + +/* + * Wrap JSON text into a palloc()'d Json structure. + */ +Json * +JsonCreate(text *json) +{ + Json *res = palloc0(sizeof(*res)); + + jsonInit((JsonContainerData *) &res->root, PointerGetDatum(json)); + + return res; +} + +/* + * Fill JsonbValue from the current iterator token. + * Returns true if recursion into nested object or array is needed (in this case + * child iterator is created and put into *pit). + */ +static bool +jsonFillValue(JsonIterator **pit, JsonbValue *res, bool skipNested, + JsontIterState nextState) +{ + JsonIterator *it = *pit; + JsonLexContext *lex = it->lex; + JsonTokenType tok = lex_peek(lex); + + switch (tok) + { + case JSON_TOKEN_NULL: + res->type = jbvNull; + break; + + case JSON_TOKEN_TRUE: + res->type = jbvBool; + res->val.boolean = true; + break; + + case JSON_TOKEN_FALSE: + res->type = jbvBool; + res->val.boolean = false; + break; + + case JSON_TOKEN_STRING: + { + char *token = lex_peek_value(lex); + res->type = jbvString; + res->val.string.val = token; + res->val.string.len = strlen(token); + break; + } + + case JSON_TOKEN_NUMBER: + { + char *token = lex_peek_value(lex); + res->type = jbvNumeric; + res->val.numeric = DatumGetNumeric(DirectFunctionCall3( + numeric_in, CStringGetDatum(token), 0, -1)); + break; + } + + case JSON_TOKEN_OBJECT_START: + case JSON_TOKEN_ARRAY_START: + { + JsonContainerData *cont = palloc(sizeof(*cont)); + char *token_start = lex->token_start; + int len; + + if (skipNested) + { + /* find the end of a container for its length calculation */ + if (tok == JSON_TOKEN_OBJECT_START) + parse_object(lex, &nullSemAction); + else + parse_array(lex, &nullSemAction); + + len = lex->token_start - token_start; + } + else + len = lex->input_length - (lex->token_start - lex->input); + + jsonInitContainer(cont, + token_start, len, + tok == JSON_TOKEN_OBJECT_START ? + JB_FOBJECT : JB_FARRAY, + -1); + + res->type = jbvBinary; + res->val.binary.data = (JsonbContainer *) cont; + res->val.binary.len = len; + + if (skipNested) + return false; + + /* recurse into container */ + it->state = nextState; + *pit = JsonIteratorInitFromLex(cont, lex, *pit); + return true; + } + + default: + report_parse_error(JSON_PARSE_VALUE, lex); + } + + lex_accept(lex, tok, NULL); + + return false; +} + +/* + * Free the topmost entry in the stack of JsonIterators. + */ +static inline JsonIterator * +JsonIteratorFreeAndGetParent(JsonIterator *it) +{ + JsonIterator *parent = it->parent; + + pfree(it); + + return parent; +} + +/* + * Free the entire stack of JsonIterators. + */ +void +JsonIteratorFree(JsonIterator *it) +{ + while (it) + it = JsonIteratorFreeAndGetParent(it); +} + +/* + * Get next JsonbValue while iterating through JsonContainer. + * + * For more details, see JsonbIteratorNext(). + */ +JsonbIteratorToken +JsonIteratorNext(JsonIterator **pit, JsonbValue *val, bool skipNested) +{ + JsonIterator *it; + + if (*pit == NULL) + return WJB_DONE; + +recurse: + it = *pit; + + /* parse by recursive descent */ + switch (it->state) + { + case JTI_ARRAY_START: + val->type = jbvArray; + val->val.array.nElems = it->isScalar ? 1 : -1; + val->val.array.rawScalar = it->isScalar; + val->val.array.elems = NULL; + it->state = it->isScalar ? JTI_ARRAY_ELEM_SCALAR : JTI_ARRAY_ELEM; + return WJB_BEGIN_ARRAY; + + case JTI_ARRAY_ELEM_SCALAR: + { + (void) jsonFillValue(pit, val, skipNested, JTI_ARRAY_END); + it->state = JTI_ARRAY_END; + return WJB_ELEM; + } + + case JTI_ARRAY_END: + if (!it->parent && lex_peek(it->lex) != JSON_TOKEN_END) + report_parse_error(JSON_PARSE_END, it->lex); + *pit = JsonIteratorFreeAndGetParent(*pit); + return WJB_END_ARRAY; + + case JTI_ARRAY_ELEM: + if (lex_accept(it->lex, JSON_TOKEN_ARRAY_END, NULL)) + { + it->state = JTI_ARRAY_END; + goto recurse; + } + + if (jsonFillValue(pit, val, skipNested, JTI_ARRAY_ELEM_AFTER)) + goto recurse; + + /* fall through */ + + case JTI_ARRAY_ELEM_AFTER: + if (!lex_accept(it->lex, JSON_TOKEN_COMMA, NULL)) + { + if (lex_peek(it->lex) != JSON_TOKEN_ARRAY_END) + report_parse_error(JSON_PARSE_ARRAY_NEXT, it->lex); + } + + if (it->state == JTI_ARRAY_ELEM_AFTER) + { + it->state = JTI_ARRAY_ELEM; + goto recurse; + } + + return WJB_ELEM; + + case JTI_OBJECT_START: + val->type = jbvObject; + val->val.object.nPairs = -1; + val->val.object.pairs = NULL; + val->val.object.uniquify = false; + it->state = JTI_OBJECT_KEY; + return WJB_BEGIN_OBJECT; + + case JTI_OBJECT_KEY: + if (lex_accept(it->lex, JSON_TOKEN_OBJECT_END, NULL)) + { + if (!it->parent && lex_peek(it->lex) != JSON_TOKEN_END) + report_parse_error(JSON_PARSE_END, it->lex); + *pit = JsonIteratorFreeAndGetParent(*pit); + return WJB_END_OBJECT; + } + + if (lex_peek(it->lex) != JSON_TOKEN_STRING) + report_parse_error(JSON_PARSE_OBJECT_START, it->lex); + + (void) jsonFillValue(pit, val, true, JTI_OBJECT_VALUE); + + if (!lex_accept(it->lex, JSON_TOKEN_COLON, NULL)) + report_parse_error(JSON_PARSE_OBJECT_LABEL, it->lex); + + it->state = JTI_OBJECT_VALUE; + return WJB_KEY; + + case JTI_OBJECT_VALUE: + if (jsonFillValue(pit, val, skipNested, JTI_OBJECT_VALUE_AFTER)) + goto recurse; + + /* fall through */ + + case JTI_OBJECT_VALUE_AFTER: + if (!lex_accept(it->lex, JSON_TOKEN_COMMA, NULL)) + { + if (lex_peek(it->lex) != JSON_TOKEN_OBJECT_END) + report_parse_error(JSON_PARSE_OBJECT_NEXT, it->lex); + } + + if (it->state == JTI_OBJECT_VALUE_AFTER) + { + it->state = JTI_OBJECT_KEY; + goto recurse; + } + + it->state = JTI_OBJECT_KEY; + return WJB_VALUE; + + default: + break; + } + + return WJB_DONE; +} + +/* Initialize JsonIterator from json lexer which onto the first token. */ +static JsonIterator * +JsonIteratorInitFromLex(JsonContainer *jc, JsonLexContext *lex, + JsonIterator *parent) +{ + JsonIterator *it = palloc(sizeof(JsonIterator)); + JsonTokenType tok; + + it->container = jc; + it->parent = parent; + it->lex = lex; + + tok = lex_peek(it->lex); + + switch (tok) + { + case JSON_TOKEN_OBJECT_START: + it->isScalar = false; + it->state = JTI_OBJECT_START; + lex_accept(it->lex, tok, NULL); + break; + case JSON_TOKEN_ARRAY_START: + it->isScalar = false; + it->state = JTI_ARRAY_START; + lex_accept(it->lex, tok, NULL); + break; + case JSON_TOKEN_STRING: + case JSON_TOKEN_NUMBER: + case JSON_TOKEN_TRUE: + case JSON_TOKEN_FALSE: + case JSON_TOKEN_NULL: + it->isScalar = true; + it->state = JTI_ARRAY_START; + break; + default: + report_parse_error(JSON_PARSE_VALUE, it->lex); + } + + return it; +} + +/* + * Given a JsonContainer, expand to JsonIterator to iterate over items + * fully expanded to in-memory representation for manipulation. + * + * See JsonbIteratorNext() for notes on memory management. + */ +JsonIterator * +JsonIteratorInit(JsonContainer *jc) +{ + JsonLexContext *lex = makeJsonLexContextCstringLen(jc->data, jc->len, true); + json_lex(lex); + return JsonIteratorInitFromLex(jc, lex, NULL); +} + +/* + * Serialize a single JsonbValue into text buffer. + */ +static void +JsonEncodeJsonbValue(StringInfo buf, JsonbValue *jbv) +{ + check_stack_depth(); + + switch (jbv->type) + { + case jbvNull: + appendBinaryStringInfo(buf, "null", 4); + break; + + case jbvBool: + if (jbv->val.boolean) + appendBinaryStringInfo(buf, "true", 4); + else + appendBinaryStringInfo(buf, "false", 5); + break; + + case jbvNumeric: + /* replace numeric NaN with string "NaN" */ + if (numeric_is_nan(jbv->val.numeric)) + appendBinaryStringInfo(buf, "\"NaN\"", 5); + else + { + Datum str = DirectFunctionCall1(numeric_out, + NumericGetDatum(jbv->val.numeric)); + + appendStringInfoString(buf, DatumGetCString(str)); + } + break; + + case jbvString: + { + char *str = jbv->val.string.len < 0 ? jbv->val.string.val : + pnstrdup(jbv->val.string.val, jbv->val.string.len); + + escape_json(buf, str); + + if (jbv->val.string.len >= 0) + pfree(str); + + break; + } + + case jbvArray: + { + int i; + + if (!jbv->val.array.rawScalar) + appendStringInfoChar(buf, '['); + + for (i = 0; i < jbv->val.array.nElems; i++) + { + if (i > 0) + appendBinaryStringInfo(buf, ", ", 2); + + JsonEncodeJsonbValue(buf, &jbv->val.array.elems[i]); + } + + if (!jbv->val.array.rawScalar) + appendStringInfoChar(buf, ']'); + + break; + } + + case jbvObject: + { + int i; + + appendStringInfoChar(buf, '{'); + + for (i = 0; i < jbv->val.object.nPairs; i++) + { + if (i > 0) + appendBinaryStringInfo(buf, ", ", 2); + + JsonEncodeJsonbValue(buf, &jbv->val.object.pairs[i].key); + appendBinaryStringInfo(buf, ": ", 2); + JsonEncodeJsonbValue(buf, &jbv->val.object.pairs[i].value); + } + + appendStringInfoChar(buf, '}'); + break; + } + + case jbvBinary: + { + JsonContainer *json = (JsonContainer *) jbv->val.binary.data; + + appendBinaryStringInfo(buf, json->data, json->len); + break; + } + + default: + elog(ERROR, "unknown jsonb value type: %d", jbv->type); + break; + } +} + +/* + * Turn an in-memory JsonbValue into a json for on-disk storage. + */ +Json * +JsonbValueToJson(JsonbValue *jbv) +{ + StringInfoData buf; + Json *json = palloc0(sizeof(*json)); + int type; + int size; + + if (jbv->type == jbvBinary) + { + /* simply copy the whole container and its data */ + JsonContainer *src = (JsonContainer *) jbv->val.binary.data; + JsonContainerData *dst = (JsonContainerData *) &json->root; + + *dst = *src; + dst->data = memcpy(palloc(src->len), src->data, src->len); + + return json; + } + + initStringInfo(&buf); + + JsonEncodeJsonbValue(&buf, jbv); + + switch (jbv->type) + { + case jbvArray: + type = JB_FARRAY; + size = jbv->val.array.nElems; + break; + + case jbvObject: + type = JB_FOBJECT; + size = jbv->val.object.nPairs; + break; + + default: /* scalar */ + type = JB_FARRAY | JB_FSCALAR; + size = 1; + break; + } + + jsonInitContainer((JsonContainerData *) &json->root, + buf.data, buf.len, type, size); + + return json; +} + +/* Context and semantic actions for JsonGetArraySize() */ +typedef struct JsonGetArraySizeState +{ + int level; + uint32 size; +} JsonGetArraySizeState; + +static void +JsonGetArraySize_array_start(void *state) +{ + ((JsonGetArraySizeState *) state)->level++; +} + +static void +JsonGetArraySize_array_end(void *state) +{ + ((JsonGetArraySizeState *) state)->level--; +} + +static void +JsonGetArraySize_array_element_start(void *state, bool isnull) +{ + JsonGetArraySizeState *s = state; + if (s->level == 1) + s->size++; +} + +/* + * Calculate the size of a json array by iterating through its elements. + */ +uint32 +JsonGetArraySize(JsonContainer *jc) +{ + JsonLexContext *lex = makeJsonLexContextCstringLen(jc->data, jc->len, false); + JsonSemAction sem; + JsonGetArraySizeState state; + + state.level = 0; + state.size = 0; + + memset(&sem, 0, sizeof(sem)); + sem.semstate = &state; + sem.array_start = JsonGetArraySize_array_start; + sem.array_end = JsonGetArraySize_array_end; + sem.array_element_end = JsonGetArraySize_array_element_start; + + json_lex(lex); + parse_array(lex, &sem); + + return state.size; +} + +/* + * Find last key in a json object by name. Returns palloc()'d copy of the + * corresponding value, or NULL if is not found. + */ +static inline JsonbValue * +jsonFindLastKeyInObject(JsonContainer *obj, const JsonbValue *key) +{ + JsonbValue *res = NULL; + JsonbValue jbv; + JsonIterator *it; + JsonbIteratorToken tok; + + Assert(JsonContainerIsObject(obj)); + Assert(key->type == jbvString); + + it = JsonIteratorInit(obj); + + while ((tok = JsonIteratorNext(&it, &jbv, true)) != WJB_DONE) + { + if (tok == WJB_KEY && !lengthCompareJsonbStringValue(key, &jbv)) + { + if (!res) + res = palloc(sizeof(*res)); + + tok = JsonIteratorNext(&it, res, true); + Assert(tok == WJB_VALUE); + } + } + + return res; +} + +/* + * Find scalar element in a array. Returns palloc()'d copy of value or NULL. + */ +static JsonbValue * +jsonFindValueInArray(JsonContainer *array, const JsonbValue *elem) +{ + JsonbValue *val = palloc(sizeof(*val)); + JsonIterator *it; + JsonbIteratorToken tok; + + Assert(JsonContainerIsArray(array)); + Assert(IsAJsonbScalar(elem)); + + it = JsonIteratorInit(array); + + while ((tok = JsonIteratorNext(&it, val, true)) != WJB_DONE) + { + if (tok == WJB_ELEM && val->type == elem->type && + equalsJsonbScalarValue(val, (JsonbValue *) elem)) + { + JsonIteratorFree(it); + return val; + } + } + + pfree(val); + return NULL; +} + +/* + * Find value in object (i.e. the "value" part of some key/value pair in an + * object), or find a matching element if we're looking through an array. + * The "flags" argument allows the caller to specify which container types are + * of interest. If we cannot find the value, return NULL. Otherwise, return + * palloc()'d copy of value. + * + * For more details, see findJsonbValueFromContainer(). + */ +JsonbValue * +findJsonValueFromContainer(JsonContainer *jc, uint32 flags, JsonbValue *key) +{ + Assert((flags & ~(JB_FARRAY | JB_FOBJECT)) == 0); + + if (!JsonContainerSize(jc)) + return NULL; + + if ((flags & JB_FARRAY) && JsonContainerIsArray(jc)) + return jsonFindValueInArray(jc, key); + + if ((flags & JB_FOBJECT) && JsonContainerIsObject(jc)) + return jsonFindLastKeyInObject(jc, key); + + /* Not found */ + return NULL; +} + +/* + * Get i-th element of a json array. + * + * Returns palloc()'d copy of the value, or NULL if it does not exist. + */ +JsonbValue * +getIthJsonValueFromContainer(JsonContainer *array, uint32 index) +{ + JsonbValue *val = palloc(sizeof(JsonbValue)); + JsonIterator *it; + JsonbIteratorToken tok; + + Assert(JsonContainerIsArray(array)); + + it = JsonIteratorInit(array); + + while ((tok = JsonIteratorNext(&it, val, true)) != WJB_DONE) + { + if (tok == WJB_ELEM) + { + if (index-- == 0) + { + JsonIteratorFree(it); + return val; + } + } + } + + pfree(val); + + return NULL; +} + +/* + * Push json JsonbValue into JsonbParseState. + * + * Used for converting an in-memory JsonbValue to a json. For more details, + * see pushJsonbValue(). This function differs from pushJsonbValue() only by + * resetting "uniquify" flag in objects. + */ +JsonbValue * +pushJsonValue(JsonbParseState **pstate, JsonbIteratorToken seq, + JsonbValue *jbval) +{ + JsonIterator *it; + JsonbValue *res = NULL; + JsonbValue v; + JsonbIteratorToken tok; + + if (!jbval || (seq != WJB_ELEM && seq != WJB_VALUE) || + jbval->type != jbvBinary) + { + /* drop through */ + res = pushJsonbValueScalar(pstate, seq, jbval); + + /* reset "uniquify" flag of objects */ + if (seq == WJB_BEGIN_OBJECT) + (*pstate)->contVal.val.object.uniquify = false; + + return res; + } + + /* unpack the binary and add each piece to the pstate */ + it = JsonIteratorInit((JsonContainer *) jbval->val.binary.data); + while ((tok = JsonIteratorNext(&it, &v, false)) != WJB_DONE) + { + res = pushJsonbValueScalar(pstate, tok, + tok < WJB_BEGIN_ARRAY ? &v : NULL); + + /* reset "uniquify" flag of objects */ + if (tok == WJB_BEGIN_OBJECT) + (*pstate)->contVal.val.object.uniquify = false; + } + + return res; +} + +/* + * Extract scalar JsonbValue from a scalar json. + */ +bool +JsonExtractScalar(JsonContainer *jbc, JsonbValue *res) +{ + JsonIterator *it = JsonIteratorInit(jbc); + JsonbIteratorToken tok PG_USED_FOR_ASSERTS_ONLY; + JsonbValue tmp; + + if (!JsonContainerIsScalar(jbc)) + return false; + + tok = JsonIteratorNext(&it, &tmp, true); + Assert(tok == WJB_BEGIN_ARRAY); + Assert(tmp.val.array.nElems == 1 && tmp.val.array.rawScalar); + + tok = JsonIteratorNext(&it, res, true); + Assert(tok == WJB_ELEM); + Assert(IsAJsonbScalar(res)); + + tok = JsonIteratorNext(&it, &tmp, true); + Assert(tok == WJB_END_ARRAY); + + return true; +} + +/* + * Turn a Json into its C-string representation with stripping quotes from + * scalar strings. + */ +char * +JsonUnquote(Json *jb) +{ + if (JsonContainerIsScalar(&jb->root)) + { + JsonbValue v; + + JsonExtractScalar(&jb->root, &v); + + if (v.type == jbvString) + return pnstrdup(v.val.string.val, v.val.string.len); + } + + return JsonToCString(NULL, &jb->root, 0); +} + +/* + * Turn a JsonContainer into its C-string representation. + */ +char * +JsonToCString(StringInfo out, JsonContainer *jc, int estimated_len) +{ + if (out) + { + appendBinaryStringInfo(out, jc->data, jc->len); + return out->data; + } + else + { + char *str = palloc(jc->len + 1); + + memcpy(str, jc->data, jc->len); + str[jc->len] = 0; + + return str; + } +} diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c index 87d3a8d..71f0679 100644 --- a/src/backend/utils/adt/jsonb.c +++ b/src/backend/utils/adt/jsonb.c @@ -206,24 +206,6 @@ JsonbTypeName(JsonbValue *jbv) return "boolean"; case jbvNull: return "null"; - case jbvDatetime: - switch (jbv->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, "unrecognized jsonb value datetime type: %d", - jbv->val.datetime.typid); - } - return "unknown"; default: elog(ERROR, "unrecognized jsonb value type: %d", jbv->type); return "unknown"; diff --git a/src/backend/utils/adt/jsonb_util.c b/src/backend/utils/adt/jsonb_util.c index 5618d39..def7c8f 100644 --- a/src/backend/utils/adt/jsonb_util.c +++ b/src/backend/utils/adt/jsonb_util.c @@ -39,7 +39,6 @@ static void fillJsonbValue(JsonbContainer *container, int index, char *base_addr, uint32 offset, JsonbValue *result); -static bool equalsJsonbScalarValue(JsonbValue *a, JsonbValue *b); static int compareJsonbScalarValue(JsonbValue *a, JsonbValue *b); static Jsonb *convertToJsonb(JsonbValue *val); static void convertJsonbValue(StringInfo buffer, JEntry *header, JsonbValue *val, int level); @@ -58,12 +57,8 @@ static JsonbParseState *pushState(JsonbParseState **pstate); static void appendKey(JsonbParseState *pstate, JsonbValue *scalarVal); static void appendValue(JsonbParseState *pstate, JsonbValue *scalarVal); static void appendElement(JsonbParseState *pstate, JsonbValue *scalarVal); -static int lengthCompareJsonbStringValue(const void *a, const void *b); static int lengthCompareJsonbPair(const void *a, const void *b, void *arg); static void uniqueifyJsonbObject(JsonbValue *object); -static JsonbValue *pushJsonbValueScalar(JsonbParseState **pstate, - JsonbIteratorToken seq, - JsonbValue *scalarVal); /* * Turn an in-memory JsonbValue into a Jsonb for on-disk storage. @@ -244,7 +239,6 @@ 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"); } } @@ -546,7 +540,7 @@ pushJsonbValue(JsonbParseState **pstate, JsonbIteratorToken seq, * Do the actual pushing, with only scalar or pseudo-scalar-array values * accepted. */ -static JsonbValue * +JsonbValue * pushJsonbValueScalar(JsonbParseState **pstate, JsonbIteratorToken seq, JsonbValue *scalarVal) { @@ -584,6 +578,7 @@ pushJsonbValueScalar(JsonbParseState **pstate, JsonbIteratorToken seq, (*pstate)->size = 4; (*pstate)->contVal.val.object.pairs = palloc(sizeof(JsonbPair) * (*pstate)->size); + (*pstate)->contVal.val.object.uniquify = true; break; case WJB_KEY: Assert(scalarVal->type == jbvString); @@ -826,6 +821,7 @@ recurse: /* Set v to object on first object call */ val->type = jbvObject; val->val.object.nPairs = (*it)->nElems; + val->val.object.uniquify = true; /* * v->val.object.pairs is not actually set, because we aren't @@ -1299,7 +1295,7 @@ JsonbHashScalarValueExtended(const JsonbValue *scalarVal, uint64 *hash, /* * Are two scalar JsonbValues of the same type a and b equal? */ -static bool +bool equalsJsonbScalarValue(JsonbValue *aScalar, JsonbValue *bScalar) { if (aScalar->type == bScalar->type) @@ -1753,22 +1749,6 @@ 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"); } @@ -1786,7 +1766,7 @@ convertJsonbScalar(StringInfo buffer, JEntry *jentry, JsonbValue *scalarVal) * a and b are first sorted based on their length. If a tie-breaker is * required, only then do we consider string binary equality. */ -static int +int lengthCompareJsonbStringValue(const void *a, const void *b) { const JsonbValue *va = (const JsonbValue *) a; @@ -1850,6 +1830,9 @@ uniqueifyJsonbObject(JsonbValue *object) Assert(object->type == jbvObject); + if (!object->val.object.uniquify) + return; + if (object->val.object.nPairs > 1) qsort_arg(object->val.object.pairs, object->val.object.nPairs, sizeof(JsonbPair), lengthCompareJsonbPair, &hasNonUniq); diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c index 7d84ef9..68668ea 100644 --- a/src/backend/utils/adt/jsonpath_exec.c +++ b/src/backend/utils/adt/jsonpath_exec.c @@ -72,12 +72,12 @@ #include "utils/float.h" #include "utils/guc.h" #include "utils/json.h" +#include "utils/jsonapi.h" #include "utils/jsonpath.h" #include "utils/date.h" #include "utils/timestamp.h" #include "utils/varlena.h" - /* Standard error message for SQL/JSON errors */ #define ERRMSG_JSON_ARRAY_NOT_FOUND "SQL/JSON array not found" #define ERRMSG_JSON_OBJECT_NOT_FOUND "SQL/JSON object not found" @@ -90,23 +90,128 @@ #define ERRMSG_INVALID_ARGUMENT_FOR_JSON_DATETIME_FUNCTION \ "invalid argument for SQL/JSON datetime function" +typedef enum JsonItemType +{ + /* Scalar types */ + jsiNull = jbvNull, + jsiString = jbvString, + jsiNumeric = jbvNumeric, + jsiBool = jbvBool, + /* Composite types */ + jsiArray = jbvArray, + jsiObject = jbvObject, + /* Binary (i.e. struct Jsonb) jbvArray/jbvObject */ + jsiBinary = jbvBinary, + + /* + * Virtual types. + * + * These types are used only for in-memory JSON processing and serialized + * into JSON strings when outputted to json/jsonb. + */ + jsiDatetime = 0x20 +} JsonItemType; + +/* SQL/JSON item */ +typedef struct JsonItem +{ + struct JsonItem *next; + + union + { + int type; /* XXX JsonItemType */ + + JsonbValue jbv; + + struct + { + int type; + Datum value; + Oid typid; + int32 typmod; + int tz; + } datetime; + } val; +} JsonItem; + +#define JsonItemJbv(jsi) (&(jsi)->val.jbv) +#define JsonItemBool(jsi) (JsonItemJbv(jsi)->val.boolean) +#define JsonItemNumeric(jsi) (JsonItemJbv(jsi)->val.numeric) +#define JsonItemNumericDatum(jsi) NumericGetDatum(JsonItemNumeric(jsi)) +#define JsonItemString(jsi) (JsonItemJbv(jsi)->val.string) +#define JsonItemBinary(jsi) (JsonItemJbv(jsi)->val.binary) +#define JsonItemArray(jsi) (JsonItemJbv(jsi)->val.array) +#define JsonItemObject(jsi) (JsonItemJbv(jsi)->val.object) +#define JsonItemDatetime(jsi) ((jsi)->val.datetime) + +#define JsonItemGetType(jsi) ((jsi)->val.type) +#define JsonItemIsNull(jsi) (JsonItemGetType(jsi) == jsiNull) +#define JsonItemIsBool(jsi) (JsonItemGetType(jsi) == jsiBool) +#define JsonItemIsNumeric(jsi) (JsonItemGetType(jsi) == jsiNumeric) +#define JsonItemIsString(jsi) (JsonItemGetType(jsi) == jsiString) +#define JsonItemIsBinary(jsi) (JsonItemGetType(jsi) == jsiBinary) +#define JsonItemIsArray(jsi) (JsonItemGetType(jsi) == jsiArray) +#define JsonItemIsObject(jsi) (JsonItemGetType(jsi) == jsiObject) +#define JsonItemIsDatetime(jsi) (JsonItemGetType(jsi) == jsiDatetime) +#define JsonItemIsScalar(jsi) (IsAJsonbScalar(JsonItemJbv(jsi)) || \ + JsonItemIsDatetime(jsi)) + +typedef union Jsonx +{ + Jsonb jb; + Json js; +} Jsonx; + +#define DatumGetJsonxP(datum, isJsonb) \ + ((isJsonb) ? (Jsonx *) DatumGetJsonbP(datum) : (Jsonx *) DatumGetJsonP(datum)) + +typedef JsonbContainer JsonxContainer; + +typedef struct JsonxIterator +{ + bool isJsonb; + union + { + JsonbIterator *jb; + JsonIterator *js; + } it; +} JsonxIterator; + /* * Represents "base object" and it's "id" for .keyvalue() evaluation. */ typedef struct JsonBaseObjectInfo { - JsonbContainer *jbc; + JsonxContainer *jbc; int id; } JsonBaseObjectInfo; /* + * Special data structure representing stack of current items. We use it + * instead of regular list in order to evade extra memory allocation. These + * items are always allocated in local variables. + */ +typedef struct JsonItemStackEntry +{ + JsonItem *item; + struct JsonItemStackEntry *parent; +} JsonItemStackEntry; + +typedef JsonItemStackEntry *JsonItemStack; + +typedef int (*JsonPathVarCallback) (void *vars, bool isJsonb, + char *varName, int varNameLen, + JsonItem *val, JsonbValue *baseObject); + +/* * Context of jsonpath execution. */ typedef struct JsonPathExecContext { - Jsonb *vars; /* variables to substitute into jsonpath */ - JsonbValue *root; /* for $ evaluation */ - JsonbValue *current; /* for @ evaluation */ + void *vars; /* variables to substitute into jsonpath */ + JsonPathVarCallback getVar; + JsonItem *root; /* for $ evaluation */ + JsonItemStack stack; /* for @ evaluation */ JsonBaseObjectInfo baseObject; /* "base object" for .keyvalue() * evaluation */ int lastGeneratedObjectId; /* "id" counter for .keyvalue() @@ -120,6 +225,7 @@ typedef struct JsonPathExecContext * ignored */ bool throwErrors; /* with "false" all suppressible errors are * suppressed */ + bool isJsonb; } JsonPathExecContext; /* Context for LIKE_REGEX execution. */ @@ -148,20 +254,34 @@ typedef enum JsonPathExecResult #define jperIsError(jper) ((jper) == jperError) /* - * List of jsonb values with shortcut for single-value list. + * List of SQL/JSON items with shortcut for single-value list. */ typedef struct JsonValueList { - JsonbValue *singleton; - List *list; + JsonItem *head; + JsonItem *tail; + int length; } JsonValueList; typedef struct JsonValueListIterator { - JsonbValue *value; - ListCell *next; + JsonItem *next; } JsonValueListIterator; +/* + * Context for execution of jsonb_path_*(jsonb, jsonpath [, vars jsonb]) user + * functions. + */ +typedef struct JsonPathUserFuncContext +{ + FunctionCallInfo fcinfo; + void *js; /* first jsonb function argument */ + Json *json; + JsonPath *jp; /* second jsonpath function argument */ + void *vars; /* third vars function argument */ + JsonValueList found; /* resulting item list */ +} JsonPathUserFuncContext; + /* strict/lax flags is decomposed into four [un]wrap/error flags */ #define jspStrictAbsenseOfErrors(cxt) (!(cxt)->laxMode) #define jspAutoUnwrap(cxt) ((cxt)->laxMode) @@ -178,86 +298,131 @@ do { \ } while (0) typedef JsonPathBool (*JsonPathPredicateCallback) (JsonPathItem *jsp, - JsonbValue *larg, - JsonbValue *rarg, + JsonItem *larg, + JsonItem *rarg, void *param); typedef Numeric (*BinaryArithmFunc) (Numeric num1, Numeric num2, bool *error); -static JsonPathExecResult executeJsonPath(JsonPath *path, Jsonb *vars, - Jsonb *json, bool throwErrors, JsonValueList *result); +typedef JsonbValue *(*JsonBuilderFunc) (JsonbParseState **, + JsonbIteratorToken, + JsonbValue *); + +static void freeUserFuncContext(JsonPathUserFuncContext *cxt); +static JsonPathExecResult executeUserFunc(FunctionCallInfo fcinfo, + JsonPathUserFuncContext *cxt, bool isJsonb, bool invertSilent, + bool copy); + +static JsonPathExecResult executeJsonPath(JsonPath *path, void *vars, + JsonPathVarCallback getVar, Jsonx *json, bool isJsonb, + bool throwErrors, JsonValueList *result); static JsonPathExecResult recursiveExecute(JsonPathExecContext *cxt, - JsonPathItem *jsp, JsonbValue *jb, JsonValueList *found); + JsonPathItem *jsp, JsonItem *jb, JsonValueList *found); static JsonPathExecResult recursiveExecuteUnwrap(JsonPathExecContext *cxt, - JsonPathItem *jsp, JsonbValue *jb, bool unwrap, JsonValueList *found); + JsonPathItem *jsp, JsonItem *jb, bool unwrap, + JsonValueList *found); + static JsonPathExecResult recursiveExecuteUnwrapArray(JsonPathExecContext *cxt, - JsonPathItem *jsp, JsonbValue *jb, + JsonPathItem *jsp, JsonItem *jb, JsonValueList *found, bool unwrapElements); static JsonPathExecResult recursiveExecuteNext(JsonPathExecContext *cxt, JsonPathItem *cur, JsonPathItem *next, - JsonbValue *v, JsonValueList *found, bool copy); + JsonItem *v, JsonValueList *found, bool copy); static JsonPathExecResult recursiveExecuteAndUnwrap(JsonPathExecContext *cxt, - JsonPathItem *jsp, JsonbValue *jb, bool unwrap, JsonValueList *found); + JsonPathItem *jsp, JsonItem *jb, bool unwrap, JsonValueList *found); static JsonPathExecResult recursiveExecuteAndUnwrapNoThrow( JsonPathExecContext *cxt, JsonPathItem *jsp, - JsonbValue *jb, bool unwrap, JsonValueList *found); + JsonItem *jb, bool unwrap, JsonValueList *found); static JsonPathBool recursiveExecuteBool(JsonPathExecContext *cxt, - JsonPathItem *jsp, JsonbValue *jb, bool canHaveNext); + JsonPathItem *jsp, JsonItem *jb, bool canHaveNext); static JsonPathBool recursiveExecuteBoolNested(JsonPathExecContext *cxt, - JsonPathItem *jsp, JsonbValue *jb); + JsonPathItem *jsp, JsonItem *jb); static JsonPathExecResult recursiveAny(JsonPathExecContext *cxt, JsonPathItem *jsp, JsonbContainer *jbc, JsonValueList *found, uint32 level, uint32 first, uint32 last, bool ignoreStructuralErrors, bool unwrapNext); static JsonPathBool executePredicate(JsonPathExecContext *cxt, JsonPathItem *pred, JsonPathItem *larg, JsonPathItem *rarg, - JsonbValue *jb, bool unwrapRightArg, + JsonItem *jb, bool unwrapRightArg, JsonPathPredicateCallback exec, void *param); static JsonPathExecResult executeBinaryArithmExpr(JsonPathExecContext *cxt, - JsonPathItem *jsp, JsonbValue *jb, BinaryArithmFunc func, + JsonPathItem *jsp, JsonItem *jb, BinaryArithmFunc func, JsonValueList *found); static JsonPathExecResult executeUnaryArithmExpr(JsonPathExecContext *cxt, - JsonPathItem *jsp, JsonbValue *jb, PGFunction func, + JsonPathItem *jsp, JsonItem *jb, PGFunction func, JsonValueList *found); static JsonPathBool executeStartsWith(JsonPathItem *jsp, - JsonbValue *whole, JsonbValue *initial, void *param); -static JsonPathBool executeLikeRegex(JsonPathItem *jsp, JsonbValue *str, - JsonbValue *rarg, void *param); + JsonItem *whole, JsonItem *initial, void *param); +static JsonPathBool executeLikeRegex(JsonPathItem *jsp, JsonItem *str, + JsonItem *rarg, void *param); static JsonPathExecResult executeNumericItemMethod(JsonPathExecContext *cxt, - JsonPathItem *jsp, JsonbValue *jb, bool unwrap, PGFunction func, + JsonPathItem *jsp, JsonItem *jb, bool unwrap, PGFunction func, JsonValueList *found); static JsonPathExecResult executeKeyValueMethod(JsonPathExecContext *cxt, - JsonPathItem *jsp, JsonbValue *jb, JsonValueList *found); + JsonPathItem *jsp, JsonItem *jb, JsonValueList *found); static JsonPathExecResult appendBoolResult(JsonPathExecContext *cxt, JsonPathItem *jsp, JsonValueList *found, JsonPathBool res); static void getJsonPathItem(JsonPathExecContext *cxt, JsonPathItem *item, - JsonbValue *value); + JsonItem *value); static void getJsonPathVariable(JsonPathExecContext *cxt, - JsonPathItem *variable, Jsonb *vars, JsonbValue *value); -static int JsonbArraySize(JsonbValue *jb); -static JsonPathBool executeComparison(JsonPathItem *cmp, JsonbValue *lv, - JsonbValue *rv, void *p); -static JsonPathBool compareItems(int32 op, JsonbValue *jb1, JsonbValue *jb2); + JsonPathItem *variable, JsonItem *value); +static int getJsonPathVariableFromJsonx(void *varsJsonb, bool isJsonb, + char *varName, int varNameLen, JsonItem *val, + JsonbValue *baseObject); +static int JsonxArraySize(JsonItem *jb, bool isJsonb); +static JsonPathBool executeComparison(JsonPathItem *cmp, JsonItem *lv, + JsonItem *rv, void *p); +static JsonPathBool compareItems(int32 op, JsonItem *jb1, JsonItem *jb2); static int compareNumeric(Numeric a, Numeric b); -static JsonbValue *copyJsonbValue(JsonbValue *src); + +static void JsonItemInitNull(JsonItem *item); +static void JsonItemInitBool(JsonItem *item, bool val); +static void JsonItemInitNumeric(JsonItem *item, Numeric val); +#define JsonItemInitNumericDatum(item, val) \ + JsonItemInitNumeric(item, DatumGetNumeric(val)) +static void JsonItemInitString(JsonItem *item, char *str, int len); +static void JsonItemInitDatetime(JsonItem *item, Datum val, Oid typid, + int32 typmod, int tz); + +static JsonItem *copyJsonItem(JsonItem *src); +static JsonItem *JsonbValueToJsonItem(JsonbValue *jbv, JsonItem *jsi); +static JsonbValue *JsonItemToJsonbValue(JsonItem *jsi, JsonbValue *jbv); +static Jsonb *JsonItemToJsonb(JsonItem *jsi); +static const char *JsonItemTypeName(JsonItem *jsi); static JsonPathExecResult getArrayIndex(JsonPathExecContext *cxt, - JsonPathItem *jsp, JsonbValue *jb, int32 *index); + JsonPathItem *jsp, JsonItem *jb, int32 *index); static JsonBaseObjectInfo setBaseObject(JsonPathExecContext *cxt, - JsonbValue *jbv, int32 id); -static void JsonValueListAppend(JsonValueList *jvl, JsonbValue *jbv); + JsonItem *jsi, int32 id); +static void JsonValueListAppend(JsonValueList *jvl, JsonItem *jbv); static int JsonValueListLength(const JsonValueList *jvl); static bool JsonValueListIsEmpty(JsonValueList *jvl); -static JsonbValue *JsonValueListHead(JsonValueList *jvl); +static JsonItem *JsonValueListHead(JsonValueList *jvl); static List *JsonValueListGetList(JsonValueList *jvl); static void JsonValueListInitIterator(const JsonValueList *jvl, JsonValueListIterator *it); -static JsonbValue *JsonValueListNext(const JsonValueList *jvl, +static JsonItem *JsonValueListNext(const JsonValueList *jvl, JsonValueListIterator *it); -static int JsonbType(JsonbValue *jb); +static int JsonbType(JsonItem *jb); static JsonbValue *JsonbInitBinary(JsonbValue *jbv, Jsonb *jb); -static int JsonbType(JsonbValue *jb); -static JsonbValue *getScalar(JsonbValue *scalar, enum jbvType type); -static JsonbValue *wrapItemsInArray(const JsonValueList *items); +static inline JsonbValue *JsonInitBinary(JsonbValue *jbv, Json *js); +static JsonItem *getScalar(JsonItem *scalar, enum jbvType type); +static JsonbValue *wrapItemsInArray(const JsonValueList *items, bool isJsonb); +static text *JsonItemUnquoteText(JsonItem *jsi, bool isJsonb); + +static JsonItem *getJsonObjectKey(JsonItem *jb, char *keystr, int keylen, + bool isJsonb, JsonItem *val); +static JsonItem *getJsonArrayElement(JsonItem *jb, uint32 index, bool isJsonb, + JsonItem *elem); + +static void JsonxIteratorInit(JsonxIterator *it, JsonxContainer *jxc, + bool isJsonb); +static JsonbIteratorToken JsonxIteratorNext(JsonxIterator *it, JsonbValue *jbv, + bool skipNested); +static JsonbValue *JsonItemToJsonbValue(JsonItem *jsi, JsonbValue *jbv); +static Json *JsonItemToJson(JsonItem *jsi); +static Jsonx *JsonbValueToJsonx(JsonbValue *jbv, bool isJsonb); +static Datum JsonbValueToJsonxDatum(JsonbValue *jbv, bool isJsonb); +static Datum JsonItemToJsonxDatum(JsonItem *jsi, bool isJsonb); static bool tryToParseDatetime(text *fmt, text *datetime, char *tzname, bool strict, Datum *value, Oid *typid, @@ -266,10 +431,14 @@ static int compareDatetime(Datum val1, Oid typid1, int tz1, Datum val2, Oid typid2, int tz2, bool *error); +static void pushJsonItem(JsonItemStack *stack, + JsonItemStackEntry *entry, JsonItem *item); +static void popJsonItem(JsonItemStack *stack); + /****************** User interface to JsonPath executor ********************/ /* - * jsonb_path_exists + * json[b]_path_exists * Returns true if jsonpath returns at least one item for the specified * jsonb value. This function and jsonb_path_match() are used to * implement @? and @@ operators, which in turn are intended to have an @@ -280,25 +449,10 @@ static int compareDatetime(Datum val1, Oid typid1, int tz1, * SQL/JSON. Regarding jsonb_path_match(), this function doesn't have * an analogy in SQL/JSON, so we define its behavior on our own. */ -Datum -jsonb_path_exists(PG_FUNCTION_ARGS) +static Datum +jsonx_path_exists(PG_FUNCTION_ARGS, bool isJsonb) { - Jsonb *jb = PG_GETARG_JSONB_P(0); - JsonPath *jp = PG_GETARG_JSONPATH_P(1); - JsonPathExecResult res; - Jsonb *vars = NULL; - bool silent = true; - - if (PG_NARGS() == 4) - { - vars = PG_GETARG_JSONB_P(2); - silent = !PG_GETARG_BOOL(3); - } - - res = executeJsonPath(jp, vars, jb, !silent, NULL); - - PG_FREE_IF_COPY(jb, 0); - PG_FREE_IF_COPY(jp, 1); + JsonPathExecResult res = executeUserFunc(fcinfo, NULL, isJsonb, true, false); if (jperIsError(res)) PG_RETURN_NULL(); @@ -306,99 +460,121 @@ jsonb_path_exists(PG_FUNCTION_ARGS) PG_RETURN_BOOL(res == jperOk); } +Datum +jsonb_path_exists(PG_FUNCTION_ARGS) +{ + return jsonx_path_exists(fcinfo, true); +} + +Datum +json_path_exists(PG_FUNCTION_ARGS) +{ + return jsonx_path_exists(fcinfo, false); +} + /* - * jsonb_path_exists_novars - * Implements the 2-argument version of jsonb_path_exists + * json[b]_path_exists_opr + * Implements the 2-argument version of json[b]_path_exists */ Datum jsonb_path_exists_opr(PG_FUNCTION_ARGS) { - /* just call the other one -- it can handle both cases */ - return jsonb_path_exists(fcinfo); + return jsonx_path_exists(fcinfo, true); +} + +Datum +json_path_exists_opr(PG_FUNCTION_ARGS) +{ + return jsonx_path_exists(fcinfo, false); } /* - * jsonb_path_match + * json[b]_path_match * Returns jsonpath predicate result item for the specified jsonb value. * See jsonb_path_exists() comment for details regarding error handling. */ -Datum -jsonb_path_match(PG_FUNCTION_ARGS) +static Datum +jsonx_path_match(PG_FUNCTION_ARGS, bool isJsonb) { - Jsonb *jb = PG_GETARG_JSONB_P(0); - JsonPath *jp = PG_GETARG_JSONPATH_P(1); - JsonbValue *jbv; - JsonValueList found = {0}; - Jsonb *vars = NULL; - bool silent = true; + JsonPathUserFuncContext cxt; + JsonItem *res; - if (PG_NARGS() == 4) - { - vars = PG_GETARG_JSONB_P(2); - silent = !PG_GETARG_BOOL(3); - } - - (void) executeJsonPath(jp, vars, jb, !silent, &found); + (void) executeUserFunc(fcinfo, &cxt, isJsonb, true, false); - if (JsonValueListLength(&found) < 1) + if (JsonValueListLength(&cxt.found) < 1) PG_RETURN_NULL(); - jbv = JsonValueListHead(&found); + res = JsonValueListHead(&cxt.found); - PG_FREE_IF_COPY(jb, 0); - PG_FREE_IF_COPY(jp, 1); + freeUserFuncContext(&cxt); - if (jbv->type != jbvBool) + if (!JsonItemIsBool(res)) PG_RETURN_NULL(); - PG_RETURN_BOOL(jbv->val.boolean); + PG_RETURN_BOOL(JsonItemBool(res)); +} + +Datum +jsonb_path_match(PG_FUNCTION_ARGS) +{ + return jsonx_path_match(fcinfo, true); +} + +Datum +json_path_match(PG_FUNCTION_ARGS) +{ + return jsonx_path_match(fcinfo, false); } /* - * jsonb_path_match_novars - * Implements the 2-argument version of jsonb_path_match + * json[b]_path_match_opr + * Implements the 2-argument version of json[b]_path_match */ Datum jsonb_path_match_opr(PG_FUNCTION_ARGS) { /* just call the other one -- it can handle both cases */ - return jsonb_path_match(fcinfo); + return jsonx_path_match(fcinfo, true); +} + +Datum +json_path_match_opr(PG_FUNCTION_ARGS) +{ + /* just call the other one -- it can handle both cases */ + return jsonx_path_match(fcinfo, false); } /* - * jsonb_path_query + * json[b]_path_query * Executes jsonpath for given jsonb document and returns result as * rowset. */ -Datum -jsonb_path_query(PG_FUNCTION_ARGS) +static Datum +jsonx_path_query(PG_FUNCTION_ARGS, bool isJsonb) { FuncCallContext *funcctx; List *found; - JsonbValue *v; + JsonItem *v; ListCell *c; + Datum res; if (SRF_IS_FIRSTCALL()) { - JsonPath *jp; - Jsonb *jb; + JsonPathUserFuncContext jspcxt; MemoryContext oldcontext; - Jsonb *vars; - bool silent; - JsonValueList found = {0}; funcctx = SRF_FIRSTCALL_INIT(); oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); - jb = PG_GETARG_JSONB_P_COPY(0); - jp = PG_GETARG_JSONPATH_P_COPY(1); - vars = PG_GETARG_JSONB_P_COPY(2); - silent = PG_GETARG_BOOL(3); + /* jsonb and jsonpath arguments are copied into SRF context. */ + (void) executeUserFunc(fcinfo, &jspcxt, isJsonb, false, true); - (void) executeJsonPath(jp, vars, jb, !silent, &found); - - funcctx->user_fctx = JsonValueListGetList(&found); + /* + * Don't free jspcxt because items in jspcxt.found can reference + * untoasted copies of jsonb and jsonpath arguments. + */ + funcctx->user_fctx = JsonValueListGetList(&jspcxt.found); MemoryContextSwitchTo(oldcontext); } @@ -413,50 +589,214 @@ jsonb_path_query(PG_FUNCTION_ARGS) v = lfirst(c); funcctx->user_fctx = list_delete_first(found); - SRF_RETURN_NEXT(funcctx, JsonbPGetDatum(JsonbValueToJsonb(v))); + res = isJsonb ? + JsonbPGetDatum(JsonItemToJsonb(v)) : + JsonPGetDatum(JsonItemToJson(v)); + + SRF_RETURN_NEXT(funcctx, res); +} + +Datum +jsonb_path_query(PG_FUNCTION_ARGS) +{ + return jsonx_path_query(fcinfo, true); +} + +Datum +json_path_query(PG_FUNCTION_ARGS) +{ + return jsonx_path_query(fcinfo, false); } /* - * jsonb_path_query_array + * json[b]_path_query_array * Executes jsonpath for given jsonb document and returns result as * jsonb array. */ -Datum -jsonb_path_query_array(FunctionCallInfo fcinfo) +static Datum +jsonx_path_query_array(PG_FUNCTION_ARGS, bool isJsonb) { - Jsonb *jb = PG_GETARG_JSONB_P(0); - JsonPath *jp = PG_GETARG_JSONPATH_P(1); - JsonValueList found = {0}; - Jsonb *vars = PG_GETARG_JSONB_P(2); - bool silent = PG_GETARG_BOOL(3); + JsonPathUserFuncContext cxt; + Datum res; - (void) executeJsonPath(jp, vars, jb, !silent, &found); + (void) executeUserFunc(fcinfo, &cxt, isJsonb, false, false); - PG_RETURN_JSONB_P(JsonbValueToJsonb(wrapItemsInArray(&found))); + res = JsonbValueToJsonxDatum(wrapItemsInArray(&cxt.found, isJsonb), isJsonb); + + freeUserFuncContext(&cxt); + + PG_RETURN_DATUM(res); +} + +Datum +jsonb_path_query_array(PG_FUNCTION_ARGS) +{ + return jsonx_path_query_array(fcinfo, true); +} + +Datum +json_path_query_array(PG_FUNCTION_ARGS) +{ + return jsonx_path_query_array(fcinfo, false); } /* - * jsonb_path_query_first + * json[b]_path_query_first * Executes jsonpath for given jsonb document and returns first result * item. If there are no items, NULL returned. */ +static Datum +jsonx_path_query_first(PG_FUNCTION_ARGS, bool isJsonb) +{ + JsonPathUserFuncContext cxt; + Datum res; + + (void) executeUserFunc(fcinfo, &cxt, isJsonb, false, false); + + if (JsonValueListLength(&cxt.found) >= 1) + res = JsonItemToJsonxDatum(JsonValueListHead(&cxt.found), isJsonb); + else + res = (Datum) 0; + + freeUserFuncContext(&cxt); + + if (res) + PG_RETURN_DATUM(res); + else + PG_RETURN_NULL(); +} + Datum -jsonb_path_query_first(FunctionCallInfo fcinfo) +jsonb_path_query_first(PG_FUNCTION_ARGS) { - Jsonb *jb = PG_GETARG_JSONB_P(0); - JsonPath *jp = PG_GETARG_JSONPATH_P(1); - JsonValueList found = {0}; - Jsonb *vars = PG_GETARG_JSONB_P(2); - bool silent = PG_GETARG_BOOL(3); + return jsonx_path_query_first(fcinfo, true); +} - (void) executeJsonPath(jp, vars, jb, !silent, &found); +Datum +json_path_query_first(PG_FUNCTION_ARGS) +{ + return jsonx_path_query_first(fcinfo, false); +} - if (JsonValueListLength(&found) >= 1) - PG_RETURN_JSONB_P(JsonbValueToJsonb(JsonValueListHead(&found))); +/* + * json[b]_path_query_first_text + * Executes jsonpath for given jsonb document and returns first result + * item as text. If there are no items, NULL returned. + */ +static Datum +jsonx_path_query_first_text(PG_FUNCTION_ARGS, bool isJsonb) +{ + JsonPathUserFuncContext cxt; + text *txt; + + (void) executeUserFunc(fcinfo, &cxt, isJsonb, false, false); + + if (JsonValueListLength(&cxt.found) >= 1) + txt = JsonItemUnquoteText(JsonValueListHead(&cxt.found), isJsonb); + else + txt = NULL; + + freeUserFuncContext(&cxt); + + if (txt) + PG_RETURN_TEXT_P(txt); else PG_RETURN_NULL(); } +Datum +jsonb_path_query_first_text(PG_FUNCTION_ARGS) +{ + return jsonx_path_query_first_text(fcinfo, true); +} + +Datum +json_path_query_first_text(PG_FUNCTION_ARGS) +{ + return jsonx_path_query_first_text(fcinfo, false); +} + +/* Free untoasted copies of jsonb and jsonpath arguments. */ +static void +freeUserFuncContext(JsonPathUserFuncContext *cxt) +{ + FunctionCallInfo fcinfo = cxt->fcinfo; + + PG_FREE_IF_COPY(cxt->js, 0); + PG_FREE_IF_COPY(cxt->jp, 1); + if (cxt->vars) + PG_FREE_IF_COPY(cxt->vars, 2); + if (cxt->json) + pfree(cxt->json); +} + +/* + * Common code for jsonb_path_*(jsonb, jsonpath [, vars jsonb, silent bool]) + * user functions. + * + * 'copy' flag enables copying of first three arguments into the current memory + * context. + */ +static JsonPathExecResult +executeUserFunc(FunctionCallInfo fcinfo, JsonPathUserFuncContext *cxt, + bool isJsonb, bool invertSilent, bool copy) +{ + Datum js_toasted = PG_GETARG_DATUM(0); + struct varlena *js_detoasted = copy ? + PG_DETOAST_DATUM(js_toasted) : + PG_DETOAST_DATUM_COPY(js_toasted); + Jsonx *js = DatumGetJsonxP(js_detoasted, isJsonb); + JsonPath *jp = copy ? PG_GETARG_JSONPATH_P_COPY(1) : PG_GETARG_JSONPATH_P(1); + struct varlena *vars_detoasted = NULL; + Jsonx *vars = NULL; + bool silent = true; + JsonPathExecResult res; + + if (PG_NARGS() == 4) + { + Datum vars_toasted = PG_GETARG_DATUM(2); + + vars_detoasted = copy ? + PG_DETOAST_DATUM(vars_toasted) : + PG_DETOAST_DATUM_COPY(vars_toasted); + + vars = DatumGetJsonxP(vars_detoasted, isJsonb); + + silent = PG_GETARG_BOOL(3) ^ invertSilent; + } + + if (cxt) + { + cxt->fcinfo = fcinfo; + cxt->js = js_detoasted; + cxt->jp = jp; + cxt->vars = vars_detoasted; + cxt->json = isJsonb ? NULL : &js->js; + memset(&cxt->found, 0, sizeof(cxt->found)); + } + + res = executeJsonPath(jp, vars, getJsonPathVariableFromJsonx, + js, isJsonb, !silent, cxt ? &cxt->found : NULL); + + if (!cxt && !copy) + { + PG_FREE_IF_COPY(js_detoasted, 0); + PG_FREE_IF_COPY(jp, 1); + + if (vars_detoasted) + PG_FREE_IF_COPY(vars_detoasted, 2); + + if (!isJsonb) + { + pfree(js); + if (vars) + pfree(vars); + } + } + + return res; +} + /********************Execute functions for JsonPath**************************/ /* @@ -478,37 +818,45 @@ jsonb_path_query_first(FunctionCallInfo fcinfo) * In other case it tries to find all the satisfied result items. */ static JsonPathExecResult -executeJsonPath(JsonPath *path, Jsonb *vars, Jsonb *json, bool throwErrors, +executeJsonPath(JsonPath *path, void *vars, JsonPathVarCallback getVar, + Jsonx *json, bool isJsonb, bool throwErrors, JsonValueList *result) { JsonPathExecContext cxt; JsonPathExecResult res; JsonPathItem jsp; - JsonbValue jbv; + JsonItem jsi; + JsonbValue *jbv = JsonItemJbv(&jsi); + JsonItemStackEntry root; jspInit(&jsp, path); - if (!JsonbExtractScalar(&json->root, &jbv)) - JsonbInitBinary(&jbv, json); - - if (vars && !JsonContainerIsObject(&vars->root)) + if (isJsonb) { - ereport(ERROR, - (errcode(ERRCODE_INVALID_PARAMETER_VALUE), - errmsg("jsonb containing jsonpath variables " - "is not an object"))); + if (!JsonbExtractScalar(&json->jb.root, jbv)) + JsonbInitBinary(jbv, &json->jb); + } + else + { + if (!JsonExtractScalar(&json->js.root, jbv)) + JsonInitBinary(jbv, &json->js); } cxt.vars = vars; + cxt.getVar = getVar; cxt.laxMode = (path->header & JSONPATH_LAX) != 0; cxt.ignoreStructuralErrors = cxt.laxMode; - cxt.root = &jbv; - cxt.current = &jbv; + cxt.root = &jsi; + cxt.stack = NULL; cxt.baseObject.jbc = NULL; cxt.baseObject.id = 0; - cxt.lastGeneratedObjectId = vars ? 2 : 1; + /* 1 + number of base objects in vars */ + cxt.lastGeneratedObjectId = 1 + getVar(vars, isJsonb, NULL, 0, NULL, NULL); cxt.innermostArraySize = -1; cxt.throwErrors = throwErrors; + cxt.isJsonb = isJsonb; + + pushJsonItem(&cxt.stack, &root, cxt.root); if (jspStrictAbsenseOfErrors(&cxt) && !result) { @@ -520,7 +868,7 @@ executeJsonPath(JsonPath *path, Jsonb *vars, Jsonb *json, bool throwErrors, Assert(!throwErrors); - res = recursiveExecute(&cxt, &jsp, &jbv, &vals); + res = recursiveExecute(&cxt, &jsp, &jsi, &vals); if (jperIsError(res)) return res; @@ -528,7 +876,7 @@ executeJsonPath(JsonPath *path, Jsonb *vars, Jsonb *json, bool throwErrors, return JsonValueListIsEmpty(&vals) ? jperNotFound : jperOk; } - res = recursiveExecute(&cxt, &jsp, &jbv, result); + res = recursiveExecute(&cxt, &jsp, &jsi, result); Assert(!throwErrors || !jperIsError(res)); @@ -540,7 +888,7 @@ executeJsonPath(JsonPath *path, Jsonb *vars, Jsonb *json, bool throwErrors, */ static JsonPathExecResult recursiveExecute(JsonPathExecContext *cxt, JsonPathItem *jsp, - JsonbValue *jb, JsonValueList *found) + JsonItem *jb, JsonValueList *found) { return recursiveExecuteUnwrap(cxt, jsp, jb, jspAutoUnwrap(cxt), found); } @@ -552,7 +900,7 @@ recursiveExecute(JsonPathExecContext *cxt, JsonPathItem *jsp, */ static JsonPathExecResult recursiveExecuteUnwrap(JsonPathExecContext *cxt, JsonPathItem *jsp, - JsonbValue *jb, bool unwrap, JsonValueList *found) + JsonItem *jb, bool unwrap, JsonValueList *found) { JsonPathItem elem; JsonPathExecResult res = jperNotFound; @@ -587,23 +935,16 @@ recursiveExecuteUnwrap(JsonPathExecContext *cxt, JsonPathItem *jsp, case jpiKey: if (JsonbType(jb) == jbvObject) { - JsonbValue *v; - JsonbValue key; - - key.type = jbvString; - key.val.string.val = jspGetString(jsp, &key.val.string.len); + JsonItem val; + int keylen; + char *key = jspGetString(jsp, &keylen); - v = findJsonbValueFromContainer(jb->val.binary.data, - JB_FOBJECT, &key); + jb = getJsonObjectKey(jb, key, keylen, cxt->isJsonb, &val); - if (v != NULL) + if (jb != NULL) { res = recursiveExecuteNext(cxt, jsp, NULL, - v, found, false); - - /* free value if it was not added to found list */ - if (jspHasNext(jsp) || !found) - pfree(v); + jb, found, true); } else if (!jspIgnoreStructuralErrors(cxt)) { @@ -617,7 +958,7 @@ recursiveExecuteUnwrap(JsonPathExecContext *cxt, JsonPathItem *jsp, initStringInfo(&keybuf); - keystr = pnstrdup(key.val.string.val, key.val.string.len); + keystr = pnstrdup(key, keylen); escape_json(&keybuf, keystr); ereport(ERROR, @@ -648,7 +989,7 @@ recursiveExecuteUnwrap(JsonPathExecContext *cxt, JsonPathItem *jsp, break; case jpiCurrent: - res = recursiveExecuteNext(cxt, jsp, NULL, cxt->current, + res = recursiveExecuteNext(cxt, jsp, NULL, cxt->stack->item, found, true); break; @@ -675,7 +1016,7 @@ recursiveExecuteUnwrap(JsonPathExecContext *cxt, JsonPathItem *jsp, { int innermostArraySize = cxt->innermostArraySize; int i; - int size = JsonbArraySize(jb); + int size = JsonxArraySize(jb, cxt->isJsonb); bool singleton = size < 0; bool hasNext = jspGetNext(jsp, &elem); @@ -729,30 +1070,27 @@ recursiveExecuteUnwrap(JsonPathExecContext *cxt, JsonPathItem *jsp, for (index = index_from; index <= index_to; index++) { - JsonbValue *v; - bool copy; + JsonItem jsibuf; + JsonItem *jsi; if (singleton) { - v = jb; - copy = true; + jsi = jb; } else { - v = getIthJsonbValueFromContainer(jb->val.binary.data, - (uint32) index); + jsi = getJsonArrayElement(jb, (uint32) index, + cxt->isJsonb, &jsibuf); - if (v == NULL) + if (jsi == NULL) continue; - - copy = false; } if (!hasNext && !found) return jperOk; - res = recursiveExecuteNext(cxt, jsp, &elem, v, found, - copy); + res = recursiveExecuteNext(cxt, jsp, &elem, jsi, found, + true); if (jperIsError(res)) break; @@ -782,8 +1120,8 @@ recursiveExecuteUnwrap(JsonPathExecContext *cxt, JsonPathItem *jsp, case jpiLast: { - JsonbValue tmpjbv; - JsonbValue *lastjbv; + JsonItem tmpjsi; + JsonItem *lastjsi; int last; bool hasNext = jspGetNext(jsp, &elem); @@ -799,15 +1137,14 @@ recursiveExecuteUnwrap(JsonPathExecContext *cxt, JsonPathItem *jsp, last = cxt->innermostArraySize - 1; - lastjbv = hasNext ? &tmpjbv : palloc(sizeof(*lastjbv)); + lastjsi = hasNext ? &tmpjsi : palloc(sizeof(*lastjsi)); - lastjbv->type = jbvNumeric; - lastjbv->val.numeric = - DatumGetNumeric(DirectFunctionCall1(int4_numeric, - Int32GetDatum(last))); + JsonItemInitNumericDatum(lastjsi, + DirectFunctionCall1(int4_numeric, + Int32GetDatum(last))); res = recursiveExecuteNext(cxt, jsp, &elem, - lastjbv, found, hasNext); + lastjsi, found, hasNext); } break; @@ -816,11 +1153,12 @@ recursiveExecuteUnwrap(JsonPathExecContext *cxt, JsonPathItem *jsp, { bool hasNext = jspGetNext(jsp, &elem); - if (jb->type != jbvBinary) - elog(ERROR, "invalid jsonb object type: %d", jb->type); + if (!JsonItemIsBinary(jb)) + elog(ERROR, "invalid jsonb object type: %d", + JsonItemGetType(jb)); return recursiveAny(cxt, hasNext ? &elem : NULL, - jb->val.binary.data, found, 1, 1, 1, + JsonItemBinary(jb).data, found, 1, 1, 1, false, jspAutoUnwrap(cxt)); } else if (unwrap && JsonbType(jb) == jbvArray) @@ -900,9 +1238,9 @@ recursiveExecuteUnwrap(JsonPathExecContext *cxt, JsonPathItem *jsp, break; } - if (jb->type == jbvBinary) + if (JsonItemIsBinary(jb)) res = recursiveAny(cxt, hasNext ? &elem : NULL, - jb->val.binary.data, found, + JsonItemBinary(jb).data, found, 1, jsp->content.anybounds.first, jsp->content.anybounds.last, @@ -916,8 +1254,8 @@ recursiveExecuteUnwrap(JsonPathExecContext *cxt, JsonPathItem *jsp, case jpiString: case jpiVariable: { - JsonbValue vbuf; - JsonbValue *v; + JsonItem vbuf; + JsonItem *v; bool hasNext = jspGetNext(jsp, &elem); if (!hasNext && !found) @@ -925,7 +1263,6 @@ recursiveExecuteUnwrap(JsonPathExecContext *cxt, JsonPathItem *jsp, res = jperOk; /* skip evaluation */ break; } - v = hasNext ? &vbuf : palloc(sizeof(*v)); baseObject = cxt->baseObject; @@ -939,20 +1276,18 @@ recursiveExecuteUnwrap(JsonPathExecContext *cxt, JsonPathItem *jsp, case jpiType: { - JsonbValue *jbv = palloc(sizeof(*jbv)); + JsonItem jsi; + const char *typname = JsonItemTypeName(jb); - jbv->type = jbvString; - jbv->val.string.val = pstrdup(JsonbTypeName(jb)); - jbv->val.string.len = strlen(jbv->val.string.val); + JsonItemInitString(&jsi, pstrdup(typname), strlen(typname)); - res = recursiveExecuteNext(cxt, jsp, NULL, jbv, - found, false); + res = recursiveExecuteNext(cxt, jsp, NULL, &jsi, found, true); } break; case jpiSize: { - int size = JsonbArraySize(jb); + int size = JsonxArraySize(jb, cxt->isJsonb); if (size < 0) { @@ -973,10 +1308,8 @@ recursiveExecuteUnwrap(JsonPathExecContext *cxt, JsonPathItem *jsp, jb = palloc(sizeof(*jb)); - jb->type = jbvNumeric; - jb->val.numeric = - DatumGetNumeric(DirectFunctionCall1(int4_numeric, - Int32GetDatum(size))); + JsonItemInitNumericDatum(jb, DirectFunctionCall1(int4_numeric, + Int32GetDatum(size))); res = recursiveExecuteNext(cxt, jsp, NULL, jb, found, false); } @@ -996,16 +1329,16 @@ recursiveExecuteUnwrap(JsonPathExecContext *cxt, JsonPathItem *jsp, case jpiDouble: { - JsonbValue jbv; + JsonItem jsi; if (unwrap && JsonbType(jb) == jbvArray) return recursiveExecuteUnwrapArray(cxt, jsp, jb, found, false); - if (jb->type == jbvNumeric) + if (JsonItemIsNumeric(jb)) { char *tmp = DatumGetCString(DirectFunctionCall1(numeric_out, - NumericGetDatum(jb->val.numeric))); + NumericGetDatum(JsonItemNumeric(jb)))); bool error = false; (void) float8in_internal_error(tmp, @@ -1024,13 +1357,13 @@ recursiveExecuteUnwrap(JsonPathExecContext *cxt, JsonPathItem *jsp, res = jperOk; } - else if (jb->type == jbvString) + else if (JsonItemIsString(jb)) { /* cast string as double */ bool error = false; double val; - char *tmp = pnstrdup(jb->val.string.val, - jb->val.string.len); + char *tmp = pnstrdup(JsonItemString(jb).val, + JsonItemString(jb).len); val = float8in_internal_error(tmp, NULL, @@ -1046,10 +1379,9 @@ recursiveExecuteUnwrap(JsonPathExecContext *cxt, JsonPathItem *jsp, "applied to not a numeric value", jspOperationName(jsp->type))))); - jb = &jbv; - jb->type = jbvNumeric; - jb->val.numeric = DatumGetNumeric(DirectFunctionCall1(float8_numeric, - Float8GetDatum(val))); + jb = &jsi; + JsonItemInitNumericDatum(jb, DirectFunctionCall1(float8_numeric, + Float8GetDatum(val))); res = jperOk; } @@ -1089,8 +1421,8 @@ recursiveExecuteUnwrap(JsonPathExecContext *cxt, JsonPathItem *jsp, "applied to not a string", jspOperationName(jsp->type))))); - datetime = cstring_to_text_with_len(jb->val.string.val, - jb->val.string.len); + datetime = cstring_to_text_with_len(JsonItemString(jb).val, + JsonItemString(jb).len); if (jsp->content.args.left) { @@ -1110,7 +1442,7 @@ recursiveExecuteUnwrap(JsonPathExecContext *cxt, JsonPathItem *jsp, { JsonValueList tzlist = {0}; JsonPathExecResult tzres; - JsonbValue *tzjbv; + JsonItem *tzjsi; jspGetRightArg(jsp, &elem); tzres = recursiveExecute(cxt, &elem, jb, &tzlist); @@ -1118,8 +1450,8 @@ recursiveExecuteUnwrap(JsonPathExecContext *cxt, JsonPathItem *jsp, return tzres; if (JsonValueListLength(&tzlist) != 1 || - ((tzjbv = JsonValueListHead(&tzlist))->type != jbvString && - tzjbv->type != jbvNumeric)) + (!JsonItemIsString((tzjsi = JsonValueListHead(&tzlist))) && + !JsonItemIsNumeric(tzjsi))) RETURN_ERROR(ereport(ERROR, (errcode(ERRCODE_INVALID_ARGUMENT_FOR_JSON_DATETIME_FUNCTION), errmsg(ERRMSG_INVALID_ARGUMENT_FOR_JSON_DATETIME_FUNCTION), @@ -1128,14 +1460,14 @@ recursiveExecuteUnwrap(JsonPathExecContext *cxt, JsonPathItem *jsp, "is not a singleton string or number", jspOperationName(jsp->type))))); - if (tzjbv->type == jbvString) - tzname = pnstrdup(tzjbv->val.string.val, - tzjbv->val.string.len); + if (JsonItemIsString(tzjsi)) + tzname = pnstrdup(JsonItemString(tzjsi).val, + JsonItemString(tzjsi).len); else { bool error = false; - tz = numeric_int4_error(tzjbv->val.numeric, &error); + tz = numeric_int4_error(JsonItemNumeric(tzjsi), &error); if (error || tz == PG_INT32_MIN) RETURN_ERROR(ereport(ERROR, @@ -1227,11 +1559,7 @@ recursiveExecuteUnwrap(JsonPathExecContext *cxt, JsonPathItem *jsp, 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; + JsonItemInitDatetime(jb, value, typid, typmod, tz); res = recursiveExecuteNext(cxt, jsp, &elem, jb, found, hasNext); } @@ -1255,16 +1583,16 @@ recursiveExecuteUnwrap(JsonPathExecContext *cxt, JsonPathItem *jsp, */ static JsonPathExecResult recursiveExecuteUnwrapArray(JsonPathExecContext *cxt, JsonPathItem *jsp, - JsonbValue *jb, JsonValueList *found, + JsonItem *jb, JsonValueList *found, bool unwrapElements) { - if (jb->type != jbvBinary) + if (!JsonItemIsBinary(jb)) { - Assert(jb->type != jbvArray); - elog(ERROR, "invalid jsonb array value type: %d", jb->type); + Assert(!JsonItemIsArray(jb)); + elog(ERROR, "invalid jsonb array value type: %d", JsonItemGetType(jb)); } - return recursiveAny(cxt, jsp, jb->val.binary.data, found, 1, 1, 1, + return recursiveAny(cxt, jsp, JsonItemBinary(jb).data, found, 1, 1, 1, false, unwrapElements); } @@ -1274,7 +1602,7 @@ recursiveExecuteUnwrapArray(JsonPathExecContext *cxt, JsonPathItem *jsp, static JsonPathExecResult recursiveExecuteNext(JsonPathExecContext *cxt, JsonPathItem *cur, JsonPathItem *next, - JsonbValue *v, JsonValueList *found, bool copy) + JsonItem *v, JsonValueList *found, bool copy) { JsonPathItem elem; bool hasNext; @@ -1293,7 +1621,7 @@ recursiveExecuteNext(JsonPathExecContext *cxt, return recursiveExecute(cxt, next, v, found); if (found) - JsonValueListAppend(found, copy ? copyJsonbValue(v) : v); + JsonValueListAppend(found, copy ? copyJsonItem(v) : v); return jperOk; } @@ -1304,22 +1632,40 @@ recursiveExecuteNext(JsonPathExecContext *cxt, */ static JsonPathExecResult recursiveExecuteAndUnwrap(JsonPathExecContext *cxt, JsonPathItem *jsp, - JsonbValue *jb, bool unwrap, JsonValueList *found) + JsonItem *jb, bool unwrap, JsonValueList *found) { if (unwrap && jspAutoUnwrap(cxt)) { JsonValueList seq = {0}; JsonValueListIterator it; JsonPathExecResult res = recursiveExecute(cxt, jsp, jb, &seq); - JsonbValue *item; + JsonItem *item; + int count; if (jperIsError(res)) return res; + count = JsonValueListLength(&seq); + + if (!count) + return jperNotFound; + + /* Optimize copying of singleton item into empty list */ + if (count == 1 && + JsonbType((item = JsonValueListHead(&seq))) != jbvArray) + { + if (JsonValueListIsEmpty(found)) + *found = seq; + else + JsonValueListAppend(found, item); + + return jperOk; + } + JsonValueListInitIterator(&seq, &it); while ((item = JsonValueListNext(&seq, &it))) { - Assert(item->type != jbvArray); + Assert(!JsonItemIsArray(item)); if (JsonbType(item) == jbvArray) recursiveExecuteUnwrapArray(cxt, NULL, item, found, false); @@ -1335,7 +1681,7 @@ recursiveExecuteAndUnwrap(JsonPathExecContext *cxt, JsonPathItem *jsp, static JsonPathExecResult recursiveExecuteAndUnwrapNoThrow(JsonPathExecContext *cxt, JsonPathItem *jsp, - JsonbValue *jb, bool unwrap, + JsonItem *jb, bool unwrap, JsonValueList *found) { JsonPathExecResult res; @@ -1351,7 +1697,7 @@ recursiveExecuteAndUnwrapNoThrow(JsonPathExecContext *cxt, JsonPathItem *jsp, /* Execute boolean-valued jsonpath expression. */ static JsonPathBool recursiveExecuteBool(JsonPathExecContext *cxt, JsonPathItem *jsp, - JsonbValue *jb, bool canHaveNext) + JsonItem *jb, bool canHaveNext) { JsonPathItem larg; JsonPathItem rarg; @@ -1482,15 +1828,14 @@ recursiveExecuteBool(JsonPathExecContext *cxt, JsonPathItem *jsp, */ static JsonPathBool recursiveExecuteBoolNested(JsonPathExecContext *cxt, JsonPathItem *jsp, - JsonbValue *jb) + JsonItem *jb) { - JsonbValue *prev; + JsonItemStackEntry current; JsonPathBool res; - prev = cxt->current; - cxt->current = jb; + pushJsonItem(&cxt->stack, ¤t, jb); res = recursiveExecuteBool(cxt, jsp, jb, false); - cxt->current = prev; + popJsonItem(&cxt->stack); return res; } @@ -1507,25 +1852,26 @@ recursiveAny(JsonPathExecContext *cxt, JsonPathItem *jsp, JsonbContainer *jbc, bool ignoreStructuralErrors, bool unwrapNext) { JsonPathExecResult res = jperNotFound; - JsonbIterator *it; + JsonxIterator it; int32 r; - JsonbValue v; + JsonItem v; check_stack_depth(); if (level > last) return res; - it = JsonbIteratorInit(jbc); + + JsonxIteratorInit(&it, jbc, cxt->isJsonb); /* * Recursively iterate over jsonb objects/arrays */ - while ((r = JsonbIteratorNext(&it, &v, true)) != WJB_DONE) + while ((r = JsonxIteratorNext(&it, JsonItemJbv(&v), true)) != WJB_DONE) { if (r == WJB_KEY) { - r = JsonbIteratorNext(&it, &v, true); + r = JsonxIteratorNext(&it, JsonItemJbv(&v), true); Assert(r == WJB_VALUE); } @@ -1534,7 +1880,7 @@ recursiveAny(JsonPathExecContext *cxt, JsonPathItem *jsp, JsonbContainer *jbc, if (level >= first || (first == PG_UINT32_MAX && last == PG_UINT32_MAX && - v.type != jbvBinary)) /* leaves only requested */ + !JsonItemIsBinary(&v))) /* leaves only requested */ { /* check expression */ if (jsp) @@ -1558,14 +1904,14 @@ recursiveAny(JsonPathExecContext *cxt, JsonPathItem *jsp, JsonbContainer *jbc, break; } else if (found) - JsonValueListAppend(found, copyJsonbValue(&v)); + JsonValueListAppend(found, copyJsonItem(&v)); else return jperOk; } - if (level < last && v.type == jbvBinary) + if (level < last && JsonItemIsBinary(&v)) { - res = recursiveAny(cxt, jsp, v.val.binary.data, found, + res = recursiveAny(cxt, jsp, JsonItemBinary(&v).data, found, level + 1, first, last, ignoreStructuralErrors, unwrapNext); @@ -1593,7 +1939,7 @@ recursiveAny(JsonPathExecContext *cxt, JsonPathItem *jsp, JsonbContainer *jbc, */ static JsonPathBool executePredicate(JsonPathExecContext *cxt, JsonPathItem *pred, - JsonPathItem *larg, JsonPathItem *rarg, JsonbValue *jb, + JsonPathItem *larg, JsonPathItem *rarg, JsonItem *jb, bool unwrapRightArg, JsonPathPredicateCallback exec, void *param) { @@ -1601,7 +1947,7 @@ executePredicate(JsonPathExecContext *cxt, JsonPathItem *pred, JsonValueListIterator lseqit; JsonValueList lseq = {0}; JsonValueList rseq = {0}; - JsonbValue *lval; + JsonItem *lval; bool error = false; bool found = false; @@ -1623,7 +1969,7 @@ executePredicate(JsonPathExecContext *cxt, JsonPathItem *pred, while ((lval = JsonValueListNext(&lseq, &lseqit))) { JsonValueListIterator rseqit; - JsonbValue *rval; + JsonItem *rval; bool first = true; if (rarg) @@ -1633,6 +1979,7 @@ executePredicate(JsonPathExecContext *cxt, JsonPathItem *pred, } else { + rseqit.next = NULL; rval = NULL; } @@ -1677,15 +2024,15 @@ executePredicate(JsonPathExecContext *cxt, JsonPathItem *pred, */ static JsonPathExecResult executeBinaryArithmExpr(JsonPathExecContext *cxt, JsonPathItem *jsp, - JsonbValue *jb, BinaryArithmFunc func, + JsonItem *jb, BinaryArithmFunc func, JsonValueList *found) { JsonPathExecResult jper; JsonPathItem elem; JsonValueList lseq = {0}; JsonValueList rseq = {0}; - JsonbValue *lval; - JsonbValue *rval; + JsonItem *lval; + JsonItem *rval; Numeric res; jspGetLeftArg(jsp, &elem); @@ -1724,13 +2071,13 @@ executeBinaryArithmExpr(JsonPathExecContext *cxt, JsonPathItem *jsp, if (jspThrowErrors(cxt)) { - res = func(lval->val.numeric, rval->val.numeric, NULL); + res = func(JsonItemNumeric(lval), JsonItemNumeric(rval), NULL); } else { bool error = false; - res = func(lval->val.numeric, rval->val.numeric, &error); + res = func(JsonItemNumeric(lval), JsonItemNumeric(rval), &error); if (error) return jperError; @@ -1740,8 +2087,7 @@ executeBinaryArithmExpr(JsonPathExecContext *cxt, JsonPathItem *jsp, return jperOk; lval = palloc(sizeof(*lval)); - lval->type = jbvNumeric; - lval->val.numeric = res; + JsonItemInitNumeric(lval, res); return recursiveExecuteNext(cxt, jsp, &elem, lval, found, false); } @@ -1752,14 +2098,14 @@ executeBinaryArithmExpr(JsonPathExecContext *cxt, JsonPathItem *jsp, */ static JsonPathExecResult executeUnaryArithmExpr(JsonPathExecContext *cxt, JsonPathItem *jsp, - JsonbValue *jb, PGFunction func, JsonValueList *found) + JsonItem *jb, PGFunction func, JsonValueList *found) { JsonPathExecResult jper; JsonPathExecResult jper2; JsonPathItem elem; JsonValueList seq = {0}; JsonValueListIterator it; - JsonbValue *val; + JsonItem *val; bool hasNext; jspGetArg(jsp, &elem); @@ -1794,9 +2140,9 @@ executeUnaryArithmExpr(JsonPathExecContext *cxt, JsonPathItem *jsp, } if (func) - val->val.numeric = + JsonItemNumeric(val) = DatumGetNumeric(DirectFunctionCall1(func, - NumericGetDatum(val->val.numeric))); + NumericGetDatum(JsonItemNumeric(val)))); jper2 = recursiveExecuteNext(cxt, jsp, &elem, val, found, false); @@ -1820,7 +2166,7 @@ executeUnaryArithmExpr(JsonPathExecContext *cxt, JsonPathItem *jsp, * Check if the 'whole' string starts from 'initial' string. */ static JsonPathBool -executeStartsWith(JsonPathItem *jsp, JsonbValue *whole, JsonbValue *initial, +executeStartsWith(JsonPathItem *jsp, JsonItem *whole, JsonItem *initial, void *param) { if (!(whole = getScalar(whole, jbvString))) @@ -1829,10 +2175,10 @@ executeStartsWith(JsonPathItem *jsp, JsonbValue *whole, JsonbValue *initial, if (!(initial = getScalar(initial, jbvString))) return jpbUnknown; /* error */ - if (whole->val.string.len >= initial->val.string.len && - !memcmp(whole->val.string.val, - initial->val.string.val, - initial->val.string.len)) + if (JsonItemString(whole).len >= JsonItemString(initial).len && + !memcmp(JsonItemString(whole).val, + JsonItemString(initial).val, + JsonItemString(initial).len)) return jpbTrue; return jpbFalse; @@ -1844,7 +2190,7 @@ executeStartsWith(JsonPathItem *jsp, JsonbValue *whole, JsonbValue *initial, * Check if the string matches regex pattern. */ static JsonPathBool -executeLikeRegex(JsonPathItem *jsp, JsonbValue *str, JsonbValue *rarg, +executeLikeRegex(JsonPathItem *jsp, JsonItem *str, JsonItem *rarg, void *param) { JsonLikeRegexContext *cxt = param; @@ -1874,8 +2220,8 @@ executeLikeRegex(JsonPathItem *jsp, JsonbValue *str, JsonbValue *rarg, cxt->cflags |= REG_EXPANDED; } - if (RE_compile_and_execute(cxt->regex, str->val.string.val, - str->val.string.len, + if (RE_compile_and_execute(cxt->regex, JsonItemString(str).val, + JsonItemString(str).len, cxt->cflags, DEFAULT_COLLATION_OID, 0, NULL)) return jpbTrue; @@ -1884,7 +2230,7 @@ executeLikeRegex(JsonPathItem *jsp, JsonbValue *str, JsonbValue *rarg, static JsonPathExecResult executeNumericItemMethod(JsonPathExecContext *cxt, JsonPathItem *jsp, - JsonbValue *jb, bool unwrap, PGFunction func, + JsonItem *jb, bool unwrap, PGFunction func, JsonValueList *found) { JsonPathItem next; @@ -1901,15 +2247,14 @@ executeNumericItemMethod(JsonPathExecContext *cxt, JsonPathItem *jsp, "not a numeric value", jspOperationName(jsp->type))))); - datum = NumericGetDatum(jb->val.numeric); + datum = NumericGetDatum(JsonItemNumeric(jb)); datum = DirectFunctionCall1(func, datum); if (!jspGetNext(jsp, &next) && !found) return jperOk; jb = palloc(sizeof(*jb)); - jb->type = jbvNumeric; - jb->val.numeric = DatumGetNumeric(datum); + JsonItemInitNumericDatum(jb, datum); return recursiveExecuteNext(cxt, jsp, &next, jb, found, false); } @@ -1939,7 +2284,7 @@ executeNumericItemMethod(JsonPathExecContext *cxt, JsonPathItem *jsp, */ static JsonPathExecResult executeKeyValueMethod(JsonPathExecContext *cxt, JsonPathItem *jsp, - JsonbValue *jb, JsonValueList *found) + JsonItem *jb, JsonValueList *found) { JsonPathExecResult res = jperNotFound; JsonPathItem next; @@ -1950,12 +2295,13 @@ executeKeyValueMethod(JsonPathExecContext *cxt, JsonPathItem *jsp, JsonbValue keystr; JsonbValue valstr; JsonbValue idstr; - JsonbIterator *it; + JsonxIterator it; JsonbIteratorToken tok; + JsonBuilderFunc push; int64 id; bool hasNext; - if (JsonbType(jb) != jbvObject || jb->type != jbvBinary) + if (JsonbType(jb) != jbvObject || !JsonItemIsBinary(jb)) RETURN_ERROR(ereport(ERROR, (errcode(ERRCODE_JSON_OBJECT_NOT_FOUND), errmsg(ERRMSG_JSON_OBJECT_NOT_FOUND), @@ -1963,7 +2309,7 @@ executeKeyValueMethod(JsonPathExecContext *cxt, JsonPathItem *jsp, "to not an object", jspOperationName(jsp->type))))); - jbc = jb->val.binary.data; + jbc = JsonItemBinary(jb).data; if (!JsonContainerSize(jbc)) return jperNotFound; /* no key-value pairs */ @@ -1983,23 +2329,29 @@ executeKeyValueMethod(JsonPathExecContext *cxt, JsonPathItem *jsp, idstr.val.string.len = 2; /* construct object id from its base object and offset inside that */ - id = jb->type != jbvBinary ? 0 : - (int64) ((char *) jbc - (char *) cxt->baseObject.jbc); + id = cxt->isJsonb ? + (int64) ((char *)(JsonContainer *) jbc - + (char *)(JsonContainer *) cxt->baseObject.jbc) : + (int64) (((JsonContainer *) jbc)->data - + ((JsonContainer *) cxt->baseObject.jbc)->data); + id += (int64) cxt->baseObject.id * INT64CONST(10000000000); idval.type = jbvNumeric; idval.val.numeric = DatumGetNumeric(DirectFunctionCall1(int8_numeric, Int64GetDatum(id))); - it = JsonbIteratorInit(jbc); + push = cxt->isJsonb ? pushJsonbValue : pushJsonValue; - while ((tok = JsonbIteratorNext(&it, &key, true)) != WJB_DONE) + JsonxIteratorInit(&it, jbc, cxt->isJsonb); + + while ((tok = JsonxIteratorNext(&it, &key, true)) != WJB_DONE) { JsonBaseObjectInfo baseObject; - JsonbValue obj; + JsonItem obj; JsonbParseState *ps; JsonbValue *keyval; - Jsonb *jsonb; + Jsonx *jsonx; if (tok != WJB_KEY) continue; @@ -2009,26 +2361,29 @@ executeKeyValueMethod(JsonPathExecContext *cxt, JsonPathItem *jsp, if (!hasNext && !found) break; - tok = JsonbIteratorNext(&it, &val, true); + tok = JsonxIteratorNext(&it, &val, true); Assert(tok == WJB_VALUE); ps = NULL; - pushJsonbValue(&ps, WJB_BEGIN_OBJECT, NULL); + push(&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); + push(&ps, WJB_VALUE, &val); pushJsonbValue(&ps, WJB_KEY, &idstr); pushJsonbValue(&ps, WJB_VALUE, &idval); keyval = pushJsonbValue(&ps, WJB_END_OBJECT, NULL); - jsonb = JsonbValueToJsonb(keyval); + jsonx = JsonbValueToJsonx(keyval, cxt->isJsonb); - JsonbInitBinary(&obj, jsonb); + if (cxt->isJsonb) + JsonbInitBinary(JsonItemJbv(&obj), &jsonx->jb); + else + JsonInitBinary(JsonItemJbv(&obj), &jsonx->js); baseObject = setBaseObject(cxt, &obj, cxt->lastGeneratedObjectId++); @@ -2055,22 +2410,17 @@ appendBoolResult(JsonPathExecContext *cxt, JsonPathItem *jsp, JsonValueList *found, JsonPathBool res) { JsonPathItem next; - JsonbValue jbv; + JsonItem jsi; if (!jspGetNext(jsp, &next) && !found) return jperOk; /* found singleton boolean value */ if (res == jpbUnknown) - { - jbv.type = jbvNull; - } + JsonItemInitNull(&jsi); else - { - jbv.type = jbvBool; - jbv.val.boolean = res == jpbTrue; - } + JsonItemInitBool(&jsi, res == jpbTrue); - return recursiveExecuteNext(cxt, jsp, &next, &jbv, found, true); + return recursiveExecuteNext(cxt, jsp, &next, &jsi, found, true); } /* @@ -2080,28 +2430,29 @@ appendBoolResult(JsonPathExecContext *cxt, JsonPathItem *jsp, */ static void getJsonPathItem(JsonPathExecContext *cxt, JsonPathItem *item, - JsonbValue *value) + JsonItem *value) { switch (item->type) { case jpiNull: - value->type = jbvNull; + JsonItemInitNull(value); break; case jpiBool: - value->type = jbvBool; - value->val.boolean = jspGetBool(item); + JsonItemInitBool(value, jspGetBool(item)); break; case jpiNumeric: - value->type = jbvNumeric; - value->val.numeric = jspGetNumeric(item); + JsonItemInitNumeric(value, jspGetNumeric(item)); break; case jpiString: - value->type = jbvString; - value->val.string.val = jspGetString(item, - &value->val.string.len); - break; + { + int len; + char *str = jspGetString(item, &len); + + JsonItemInitString(value, str, len); + break; + } case jpiVariable: - getJsonPathVariable(cxt, item, cxt->vars, value); + getJsonPathVariable(cxt, item, value); return; default: elog(ERROR, "unexpected jsonpath item type"); @@ -2113,42 +2464,86 @@ getJsonPathItem(JsonPathExecContext *cxt, JsonPathItem *item, */ static void getJsonPathVariable(JsonPathExecContext *cxt, JsonPathItem *variable, - Jsonb *vars, JsonbValue *value) + JsonItem *value) { char *varName; int varNameLength; - JsonbValue tmp; - JsonbValue *v; + JsonItem baseObject; + int baseObjectId; - if (!vars) + Assert(variable->type == jpiVariable); + varName = jspGetString(variable, &varNameLength); + + if (!cxt->vars || + (baseObjectId = cxt->getVar(cxt->vars, cxt->isJsonb, + varName, varNameLength, + value, JsonItemJbv(&baseObject))) < 0) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("cannot find jsonpath variable '%s'", + pnstrdup(varName, varNameLength)))); + + if (baseObjectId > 0) + setBaseObject(cxt, &baseObject, baseObjectId); +} + +static int +getJsonPathVariableFromJsonx(void *varsJsonx, bool isJsonb, + char *varName, int varNameLength, + JsonItem *value, JsonbValue *baseObject) +{ + Jsonx *vars = varsJsonx; + JsonbValue *val; + JsonbValue key; + + if (!varName) { - value->type = jbvNull; - return; + if (vars && + !(isJsonb ? + JsonContainerIsObject(&vars->jb.root) : + JsonContainerIsObject(&vars->js.root))) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("%s containing jsonpath variables is not an object", + isJsonb ? "jsonb" : "json"))); + + return vars ? 1 : 0; /* count of base objects */ } - Assert(variable->type == jpiVariable); - varName = jspGetString(variable, &varNameLength); - tmp.type = jbvString; - tmp.val.string.val = varName; - tmp.val.string.len = varNameLength; + if (!vars) + return -1; - v = findJsonbValueFromContainer(&vars->root, JB_FOBJECT, &tmp); + key.type = jbvString; + key.val.string.val = varName; + key.val.string.len = varNameLength; - if (v) + if (isJsonb) { - *value = *v; - pfree(v); + Jsonb *jb = &vars->jb; + + val = findJsonbValueFromContainer(&jb->root, JB_FOBJECT, &key); + + if (!val) + return -1; + + JsonbInitBinary(baseObject, jb); } else { - ereport(ERROR, - (errcode(ERRCODE_UNDEFINED_OBJECT), - errmsg("cannot find jsonpath variable '%s'", - pnstrdup(varName, varNameLength)))); + Json *js = &vars->js; + + val = findJsonValueFromContainer(&js->root, JB_FOBJECT, &key); + + if (!val) + return -1; + + JsonInitBinary(baseObject, js); } - JsonbInitBinary(&tmp, vars); - setBaseObject(cxt, &tmp, 1); + *JsonItemJbv(value) = *val; + pfree(val); + + return 1; } /**************** Support functions for JsonPath execution *****************/ @@ -2157,16 +2552,26 @@ getJsonPathVariable(JsonPathExecContext *cxt, JsonPathItem *variable, * Returns the size of an array item, or -1 if item is not an array. */ static int -JsonbArraySize(JsonbValue *jb) +JsonxArraySize(JsonItem *jb, bool isJsonb) { - Assert(jb->type != jbvArray); + Assert(!JsonItemIsArray(jb)); - if (jb->type == jbvBinary) + if (JsonItemIsBinary(jb)) { - JsonbContainer *jbc = jb->val.binary.data; + if (isJsonb) + { + JsonbContainer *jbc = JsonItemBinary(jb).data; - if (JsonContainerIsArray(jbc) && !JsonContainerIsScalar(jbc)) - return JsonContainerSize(jbc); + if (JsonContainerIsArray(jbc) && !JsonContainerIsScalar(jbc)) + return JsonContainerSize(jbc); + } + else + { + JsonContainer *jc = (JsonContainer *) JsonItemBinary(jb).data; + + if (JsonContainerIsArray(jc) && !JsonContainerIsScalar(jc)) + return JsonTextContainerSize(jc); + } } return -1; @@ -2174,7 +2579,7 @@ JsonbArraySize(JsonbValue *jb) /* Comparison predicate callback. */ static JsonPathBool -executeComparison(JsonPathItem *cmp, JsonbValue *lv, JsonbValue *rv, void *p) +executeComparison(JsonPathItem *cmp, JsonItem *lv, JsonItem *rv, void *p) { return compareItems(cmp->type, lv, rv); } @@ -2183,14 +2588,16 @@ executeComparison(JsonPathItem *cmp, JsonbValue *lv, JsonbValue *rv, void *p) * Compare two SQL/JSON items using comparison operation 'op'. */ static JsonPathBool -compareItems(int32 op, JsonbValue *jb1, JsonbValue *jb2) +compareItems(int32 op, JsonItem *jsi1, JsonItem *jsi2) { + JsonbValue *jb1 = JsonItemJbv(jsi1); + JsonbValue *jb2 = JsonItemJbv(jsi2); int cmp; bool res; - if (jb1->type != jb2->type) + if (JsonItemGetType(jsi1) != JsonItemGetType(jsi2)) { - if (jb1->type == jbvNull || jb2->type == jbvNull) + if (JsonItemIsNull(jsi1) || JsonItemIsNull(jsi2)) /* * Equality and order comparison of nulls to non-nulls returns @@ -2202,7 +2609,7 @@ compareItems(int32 op, JsonbValue *jb1, JsonbValue *jb2) return jpbUnknown; } - switch (jb1->type) + switch (JsonItemGetType(jsi1)) { case jbvNull: cmp = 0; @@ -2225,16 +2632,16 @@ compareItems(int32 op, JsonbValue *jb1, JsonbValue *jb2) jb2->val.string.val, jb2->val.string.len, DEFAULT_COLLATION_OID); break; - case jbvDatetime: + case jsiDatetime: { bool error = false; - cmp = compareDatetime(jb1->val.datetime.value, - jb1->val.datetime.typid, - jb1->val.datetime.tz, - jb2->val.datetime.value, - jb2->val.datetime.typid, - jb2->val.datetime.tz, + cmp = compareDatetime(JsonItemDatetime(jsi1).value, + JsonItemDatetime(jsi1).typid, + JsonItemDatetime(jsi1).tz, + JsonItemDatetime(jsi2).value, + JsonItemDatetime(jsi2).typid, + JsonItemDatetime(jsi2).tz, &error); if (error) @@ -2248,7 +2655,7 @@ compareItems(int32 op, JsonbValue *jb1, JsonbValue *jb2) return jpbUnknown; /* non-scalars are not comparable */ default: - elog(ERROR, "invalid jsonb value type %d", jb1->type); + elog(ERROR, "invalid jsonb value type %d", JsonItemGetType(jsi1)); } switch (op) @@ -2288,25 +2695,88 @@ compareNumeric(Numeric a, Numeric b) PointerGetDatum(b))); } -static JsonbValue * -copyJsonbValue(JsonbValue *src) +static JsonItem * +copyJsonItem(JsonItem *src) { - JsonbValue *dst = palloc(sizeof(*dst)); + JsonItem *dst = palloc(sizeof(*dst)); *dst = *src; return dst; } +static JsonItem * +JsonbValueToJsonItem(JsonbValue *jbv, JsonItem *jsi) +{ + *JsonItemJbv(jsi) = *jbv; + return jsi; +} + +static JsonbValue * +JsonItemToJsonbValue(JsonItem *jsi, JsonbValue *jbv) +{ + switch (JsonItemGetType(jsi)) + { + case jsiDatetime: + jbv->type = jbvString; + jbv->val.string.val = JsonEncodeDateTime(NULL, + JsonItemDatetime(jsi).value, + JsonItemDatetime(jsi).typid, + &JsonItemDatetime(jsi).tz); + jbv->val.string.len = strlen(jbv->val.string.val); + return jbv; + + default: + return JsonItemJbv(jsi); + } +} + +static Jsonb * +JsonItemToJsonb(JsonItem *jsi) +{ + JsonbValue jbv; + + return JsonbValueToJsonb(JsonItemToJsonbValue(jsi, &jbv)); +} + +static const char * +JsonItemTypeName(JsonItem *jsi) +{ + switch (JsonItemGetType(jsi)) + { + case jsiDatetime: + switch (JsonItemDatetime(jsi).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, "unrecognized jsonb value datetime type: %d", + JsonItemDatetime(jsi).typid); + return "unknown"; + } + + default: + return JsonbTypeName(JsonItemJbv(jsi)); + } +} + /* * Execute array subscript expression and convert resulting numeric item to * the integer type with truncation. */ static JsonPathExecResult -getArrayIndex(JsonPathExecContext *cxt, JsonPathItem *jsp, JsonbValue *jb, +getArrayIndex(JsonPathExecContext *cxt, JsonPathItem *jsp, JsonItem *jb, int32 *index) { - JsonbValue *jbv; + JsonItem *jbv; JsonValueList found = {0}; JsonPathExecResult res = recursiveExecute(cxt, jsp, jb, &found); Datum numeric_index; @@ -2324,7 +2794,7 @@ getArrayIndex(JsonPathExecContext *cxt, JsonPathItem *jsp, JsonbValue *jb, "singleton numeric value")))); numeric_index = DirectFunctionCall2(numeric_trunc, - NumericGetDatum(jbv->val.numeric), + NumericGetDatum(JsonItemNumeric(jbv)), Int32GetDatum(0)); *index = numeric_int4_error(DatumGetNumeric(numeric_index), &error); @@ -2341,95 +2811,82 @@ getArrayIndex(JsonPathExecContext *cxt, JsonPathItem *jsp, JsonbValue *jb, /* Save base object and its id needed for the execution of .keyvalue(). */ static JsonBaseObjectInfo -setBaseObject(JsonPathExecContext *cxt, JsonbValue *jbv, int32 id) +setBaseObject(JsonPathExecContext *cxt, JsonItem *jbv, int32 id) { JsonBaseObjectInfo baseObject = cxt->baseObject; - cxt->baseObject.jbc = jbv->type != jbvBinary ? NULL : - (JsonbContainer *) jbv->val.binary.data; + cxt->baseObject.jbc = !JsonItemIsBinary(jbv) ? NULL : + (JsonbContainer *) JsonItemBinary(jbv).data; cxt->baseObject.id = id; return baseObject; } static void -JsonValueListAppend(JsonValueList *jvl, JsonbValue *jbv) +JsonValueListAppend(JsonValueList *jvl, JsonItem *jsi) { - if (jvl->singleton) + jsi->next = NULL; + + if (jvl->tail) { - jvl->list = list_make2(jvl->singleton, jbv); - jvl->singleton = NULL; + jvl->tail->next = jsi; + jvl->tail = jsi; } - else if (!jvl->list) - jvl->singleton = jbv; else - jvl->list = lappend(jvl->list, jbv); + { + Assert(!jvl->head); + jvl->head = jvl->tail = jsi; + } + + jvl->length++; } static int JsonValueListLength(const JsonValueList *jvl) { - return jvl->singleton ? 1 : list_length(jvl->list); + return jvl->length; } static bool JsonValueListIsEmpty(JsonValueList *jvl) { - return !jvl->singleton && list_length(jvl->list) <= 0; + return !jvl->length; } -static JsonbValue * +static JsonItem * JsonValueListHead(JsonValueList *jvl) { - return jvl->singleton ? jvl->singleton : linitial(jvl->list); + return jvl->head; } static List * JsonValueListGetList(JsonValueList *jvl) { - if (jvl->singleton) - return list_make1(jvl->singleton); + List *list = NIL; + JsonItem *jsi; - return jvl->list; + for (jsi = jvl->head; jsi; jsi = jsi->next) + list = lappend(list, jsi); + + return list; } static void JsonValueListInitIterator(const JsonValueList *jvl, JsonValueListIterator *it) { - if (jvl->singleton) - { - it->value = jvl->singleton; - it->next = NULL; - } - else if (list_head(jvl->list) != NULL) - { - it->value = (JsonbValue *) linitial(jvl->list); - it->next = lnext(list_head(jvl->list)); - } - else - { - it->value = NULL; - it->next = NULL; - } + it->next = jvl->head; } /* * Get the next item from the sequence advancing iterator. */ -static JsonbValue * +static JsonItem * JsonValueListNext(const JsonValueList *jvl, JsonValueListIterator *it) { - JsonbValue *result = it->value; + JsonItem *result = it->next; - if (it->next) - { - it->value = lfirst(it->next); - it->next = lnext(it->next); - } - else - { - it->value = NULL; - } + if (result) + it->next = result->next; return result; } @@ -2448,16 +2905,29 @@ JsonbInitBinary(JsonbValue *jbv, Jsonb *jb) } /* + * Initialize a binary JsonbValue with the given json container. + */ +static inline JsonbValue * +JsonInitBinary(JsonbValue *jbv, Json *js) +{ + jbv->type = jbvBinary; + jbv->val.binary.data = (void *) &js->root; + jbv->val.binary.len = js->root.len; + + return jbv; +} + +/* * Returns jbv* type of of JsonbValue. Note, it never returns jbvBinary as is. */ static int -JsonbType(JsonbValue *jb) +JsonbType(JsonItem *jb) { - int type = jb->type; + int type = JsonItemGetType(jb); - if (jb->type == jbvBinary) + if (type == jbvBinary) { - JsonbContainer *jbc = (void *) jb->val.binary.data; + JsonbContainer *jbc = (void *) JsonItemBinary(jb).data; /* Scalars should be always extracted during jsonpath execution. */ Assert(!JsonContainerIsScalar(jbc)); @@ -2473,32 +2943,204 @@ JsonbType(JsonbValue *jb) return type; } +/* + * Convert jsonb to a C-string stripping quotes from scalar strings. + */ +static char * +JsonbValueUnquote(JsonbValue *jbv, int *len, bool isJsonb) +{ + switch (jbv->type) + { + case jbvString: + *len = jbv->val.string.len; + return jbv->val.string.val; + + case jbvBool: + *len = jbv->val.boolean ? 4 : 5; + return jbv->val.boolean ? "true" : "false"; + + case jbvNumeric: + *len = -1; + return DatumGetCString(DirectFunctionCall1(numeric_out, + PointerGetDatum(jbv->val.numeric))); + + case jbvNull: + *len = 4; + return "null"; + + case jbvBinary: + { + JsonbValue jbvbuf; + + if (isJsonb ? + JsonbExtractScalar(jbv->val.binary.data, &jbvbuf) : + JsonExtractScalar((JsonContainer *) jbv->val.binary.data, &jbvbuf)) + return JsonbValueUnquote(&jbvbuf, len, isJsonb); + + *len = -1; + return isJsonb ? + JsonbToCString(NULL, jbv->val.binary.data, jbv->val.binary.len) : + JsonToCString(NULL, (JsonContainer *) jbv->val.binary.data, jbv->val.binary.len); + } + + default: + elog(ERROR, "unexpected jsonb value type: %d", jbv->type); + return NULL; + } +} + +static char * +JsonItemUnquote(JsonItem *jsi, int *len, bool isJsonb) +{ + switch (JsonItemGetType(jsi)) + { + case jsiDatetime: + *len = -1; + return JsonEncodeDateTime(NULL, + JsonItemDatetime(jsi).value, + JsonItemDatetime(jsi).typid, + &JsonItemDatetime(jsi).tz); + + default: + return JsonbValueUnquote(JsonItemJbv(jsi), len, isJsonb); + } +} + +static text * +JsonItemUnquoteText(JsonItem *jsi, bool isJsonb) +{ + int len; + char *str = JsonItemUnquote(jsi, &len, isJsonb); + + if (len < 0) + return cstring_to_text(str); + else + return cstring_to_text_with_len(str, len); +} + +static JsonItem * +getJsonObjectKey(JsonItem *jsi, char *keystr, int keylen, bool isJsonb, + JsonItem *res) +{ + JsonbContainer *jbc = JsonItemBinary(jsi).data; + JsonbValue *val; + JsonbValue key; + + key.type = jbvString; + key.val.string.val = keystr; + key.val.string.len = keylen; + + val = isJsonb ? + findJsonbValueFromContainer(jbc, JB_FOBJECT, &key) : + findJsonValueFromContainer((JsonContainer *) jbc, JB_FOBJECT, &key); + + return val ? JsonbValueToJsonItem(val, res) : NULL; +} + +static JsonItem * +getJsonArrayElement(JsonItem *jb, uint32 index, bool isJsonb, JsonItem *res) +{ + JsonbContainer *jbc = JsonItemBinary(jb).data; + JsonbValue *elem = isJsonb ? + getIthJsonbValueFromContainer(jbc, index) : + getIthJsonValueFromContainer((JsonContainer *) jbc, index); + + return elem ? JsonbValueToJsonItem(elem, res) : NULL; +} + +static inline void +JsonxIteratorInit(JsonxIterator *it, JsonxContainer *jxc, bool isJsonb) +{ + it->isJsonb = isJsonb; + if (isJsonb) + it->it.jb = JsonbIteratorInit((JsonbContainer *) jxc); + else + it->it.js = JsonIteratorInit((JsonContainer *) jxc); +} + +static JsonbIteratorToken +JsonxIteratorNext(JsonxIterator *it, JsonbValue *jbv, bool skipNested) +{ + return it->isJsonb ? + JsonbIteratorNext(&it->it.jb, jbv, skipNested) : + JsonIteratorNext(&it->it.js, jbv, skipNested); +} + +static Json * +JsonItemToJson(JsonItem *jsi) +{ + JsonbValue jbv; + + return JsonbValueToJson(JsonItemToJsonbValue(jsi, &jbv)); +} + +static Jsonx * +JsonbValueToJsonx(JsonbValue *jbv, bool isJsonb) +{ + return isJsonb ? + (Jsonx *) JsonbValueToJsonb(jbv) : + (Jsonx *) JsonbValueToJson(jbv); +} + +static Datum +JsonbValueToJsonxDatum(JsonbValue *jbv, bool isJsonb) +{ + return isJsonb ? + JsonbPGetDatum(JsonbValueToJsonb(jbv)) : + JsonPGetDatum(JsonbValueToJson(jbv)); +} + +static Datum +JsonItemToJsonxDatum(JsonItem *jsi, bool isJsonb) +{ + JsonbValue jbv; + + return JsonbValueToJsonxDatum(JsonItemToJsonbValue(jsi, &jbv), isJsonb); +} + /* Get scalar of given type or NULL on type mismatch */ -static JsonbValue * -getScalar(JsonbValue *scalar, enum jbvType type) +static JsonItem * +getScalar(JsonItem *scalar, enum jbvType type) { /* Scalars should be always extracted during jsonpath execution. */ - Assert(scalar->type != jbvBinary || - !JsonContainerIsScalar(scalar->val.binary.data)); + Assert(!JsonItemIsBinary(scalar) || + !JsonContainerIsScalar(JsonItemBinary(scalar).data)); - return scalar->type == type ? scalar : NULL; + return JsonItemGetType(scalar) == type ? scalar : NULL; } /* Construct a JSON array from the item list */ static JsonbValue * -wrapItemsInArray(const JsonValueList *items) +wrapItemsInArray(const JsonValueList *items, bool isJsonb) { JsonbParseState *ps = NULL; JsonValueListIterator it; - JsonbValue *jbv; + JsonItem *jsi; + JsonbValue jbv; + JsonBuilderFunc push = isJsonb ? pushJsonbValue : pushJsonValue; - pushJsonbValue(&ps, WJB_BEGIN_ARRAY, NULL); + push(&ps, WJB_BEGIN_ARRAY, NULL); JsonValueListInitIterator(items, &it); - while ((jbv = JsonValueListNext(items, &it))) - pushJsonbValue(&ps, WJB_ELEM, jbv); - return pushJsonbValue(&ps, WJB_END_ARRAY, NULL); + while ((jsi = JsonValueListNext(items, &it))) + push(&ps, WJB_ELEM, JsonItemToJsonbValue(jsi, &jbv)); + + return push(&ps, WJB_END_ARRAY, NULL); +} + +static void +pushJsonItem(JsonItemStack *stack, JsonItemStackEntry *entry, JsonItem *item) +{ + entry->item = item; + entry->parent = *stack; + *stack = entry; +} + +static void +popJsonItem(JsonItemStack *stack) +{ + *stack = (*stack)->parent; } static inline Datum @@ -2739,3 +3381,43 @@ tryToParseDatetime(text *fmt, text *datetime, char *tzname, bool strict, return !error; } + +static void +JsonItemInitNull(JsonItem *item) +{ + item->val.type = jbvNull; +} + +static void +JsonItemInitBool(JsonItem *item, bool val) +{ + item->val.type = jbvBool; + JsonItemBool(item) = val; +} + +static void +JsonItemInitNumeric(JsonItem *item, Numeric val) +{ + item->val.type = jbvNumeric; + JsonItemNumeric(item) = val; +} + +#define JsonItemInitNumericDatum(item, val) JsonItemInitNumeric(item, DatumGetNumeric(val)) + +static void +JsonItemInitString(JsonItem *item, char *str, int len) +{ + item->val.type = jbvString; + JsonItemString(item).val = str; + JsonItemString(item).len = len; +} + +static void +JsonItemInitDatetime(JsonItem *item, Datum val, Oid typid, int32 typmod, int tz) +{ + item->val.type = jsiDatetime; + JsonItemDatetime(item).value = val; + JsonItemDatetime(item).typid = typid; + JsonItemDatetime(item).typmod = typmod; + JsonItemDatetime(item).tz = tz; +} diff --git a/src/include/catalog/pg_operator.dat b/src/include/catalog/pg_operator.dat index 1c7af92..5e249dd 100644 --- a/src/include/catalog/pg_operator.dat +++ b/src/include/catalog/pg_operator.dat @@ -3263,5 +3263,13 @@ oprname => '@@', oprleft => 'jsonb', oprright => 'jsonpath', oprresult => 'bool', oprcode => 'jsonb_path_match(jsonb,jsonpath)', oprrest => 'contsel', oprjoin => 'contjoinsel' }, +{ oid => '6071', descr => 'jsonpath exists', + oprname => '@?', oprleft => 'json', oprright => 'jsonpath', + oprresult => 'bool', oprcode => 'json_path_exists(json,jsonpath)', + oprrest => 'contsel', oprjoin => 'contjoinsel' }, +{ oid => '6108', descr => 'jsonpath predicate', + oprname => '@@', oprleft => 'json', oprright => 'jsonpath', + oprresult => 'bool', oprcode => 'json_path_match(json,jsonpath)', + oprrest => 'contsel', oprjoin => 'contjoinsel' }, ] diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 9a7a5db..4f80b95 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -9213,6 +9213,10 @@ { oid => '6057', descr => 'jsonpath query first item', proname => 'jsonb_path_query_first', prorettype => 'jsonb', proargtypes => 'jsonb jsonpath jsonb bool', prosrc => 'jsonb_path_query_first' }, +{ oid => '6127', descr => 'jsonpath query first item text', + proname => 'jsonb_path_query_first_text', prorettype => 'text', + proargtypes => 'jsonb jsonpath jsonb bool', + prosrc => 'jsonb_path_query_first_text' }, { oid => '6058', descr => 'jsonpath match', proname => 'jsonb_path_match', prorettype => 'bool', proargtypes => 'jsonb jsonpath jsonb bool', prosrc => 'jsonb_path_match' }, @@ -9224,6 +9228,36 @@ proname => 'jsonb_path_match', prorettype => 'bool', proargtypes => 'jsonb jsonpath', prosrc => 'jsonb_path_match_opr' }, +{ oid => '6043', descr => 'implementation of @? operator', + proname => 'json_path_exists', prorettype => 'bool', + proargtypes => 'json jsonpath', prosrc => 'json_path_exists_opr' }, +{ oid => '6047', descr => 'implementation of @@ operator', + proname => 'json_path_match', prorettype => 'bool', + proargtypes => 'json jsonpath', prosrc => 'json_path_match_opr' }, + +{ oid => '6045', descr => 'jsonpath exists test', + proname => 'json_path_exists', prorettype => 'bool', + proargtypes => 'json jsonpath json bool', prosrc => 'json_path_exists' }, +{ oid => '6046', descr => 'jsonpath query', + proname => 'json_path_query', prorows => '1000', proretset => 't', + prorettype => 'json', proargtypes => 'json jsonpath json bool', + prosrc => 'json_path_query' }, +{ oid => '6129', descr => 'jsonpath query with conditional wrapper', + proname => 'json_path_query_array', prorettype => 'json', + proargtypes => 'json jsonpath json bool', + prosrc => 'json_path_query_array' }, +{ oid => '6069', descr => 'jsonpath match test', + proname => 'json_path_match', prorettype => 'bool', + proargtypes => 'json jsonpath json bool', prosrc => 'json_path_match' }, +{ oid => '6109', descr => 'jsonpath query first item', + proname => 'json_path_query_first', prorettype => 'json', + proargtypes => 'json jsonpath json bool', + prosrc => 'json_path_query_first' }, +{ oid => '6044', descr => 'jsonpath query first item text', + proname => 'json_path_query_first_text', prorettype => 'text', + proargtypes => 'json jsonpath json bool', + prosrc => 'json_path_query_first_text' }, + # txid { oid => '2939', descr => 'I/O', proname => 'txid_snapshot_in', prorettype => 'txid_snapshot', diff --git a/src/include/utils/jsonapi.h b/src/include/utils/jsonapi.h index 9eda9a3..5535aba2 100644 --- a/src/include/utils/jsonapi.h +++ b/src/include/utils/jsonapi.h @@ -15,6 +15,7 @@ #define JSONAPI_H #include "jsonb.h" +#include "access/htup.h" #include "lib/stringinfo.h" typedef enum @@ -93,6 +94,56 @@ typedef struct JsonSemAction json_scalar_action scalar; } JsonSemAction; +typedef enum +{ + JTI_ARRAY_START, + JTI_ARRAY_ELEM, + JTI_ARRAY_ELEM_SCALAR, + JTI_ARRAY_ELEM_AFTER, + JTI_ARRAY_END, + JTI_OBJECT_START, + JTI_OBJECT_KEY, + JTI_OBJECT_VALUE, + JTI_OBJECT_VALUE_AFTER, +} JsontIterState; + +typedef struct JsonContainerData +{ + uint32 header; + int len; + char *data; +} JsonContainerData; + +typedef const JsonContainerData JsonContainer; + +#define JsonTextContainerSize(jc) \ + (((jc)->header & JB_CMASK) == JB_CMASK && JsonContainerIsArray(jc) \ + ? JsonGetArraySize(jc) : (jc)->header & JB_CMASK) + +typedef struct Json +{ + JsonContainer root; +} Json; + +typedef struct JsonIterator +{ + struct JsonIterator *parent; + JsonContainer *container; + JsonLexContext *lex; + JsontIterState state; + bool isScalar; +} JsonIterator; + +#define DatumGetJsonP(datum) JsonCreate(DatumGetTextP(datum)) +#define DatumGetJsonPCopy(datum) JsonCreate(DatumGetTextPCopy(datum)) + +#define JsonPGetDatum(json) \ + PointerGetDatum(cstring_to_text_with_len((json)->root.data, (json)->root.len)) + +#define PG_GETARG_JSON_P(n) DatumGetJsonP(PG_GETARG_DATUM(n)) +#define PG_GETARG_JSON_P_COPY(n) DatumGetJsonPCopy(PG_GETARG_DATUM(n)) +#define PG_RETURN_JSON_P(json) PG_RETURN_DATUM(JsonPGetDatum(json)) + /* * parse_json will parse the string in the lex calling the * action functions in sem at the appropriate points. It is @@ -164,4 +215,22 @@ extern text *transform_json_string_values(text *json, void *action_state, extern char *JsonEncodeDateTime(char *buf, Datum value, Oid typid, const int *tzp); +extern Json *JsonCreate(text *json); +extern JsonbIteratorToken JsonIteratorNext(JsonIterator **pit, JsonbValue *val, + bool skipNested); +extern JsonIterator *JsonIteratorInit(JsonContainer *jc); +extern void JsonIteratorFree(JsonIterator *it); +extern uint32 JsonGetArraySize(JsonContainer *jc); +extern Json *JsonbValueToJson(JsonbValue *jbv); +extern bool JsonExtractScalar(JsonContainer *jbc, JsonbValue *res); +extern char *JsonUnquote(Json *jb); +extern char *JsonToCString(StringInfo out, JsonContainer *jc, + int estimated_len); +extern JsonbValue *pushJsonValue(JsonbParseState **pstate, + JsonbIteratorToken tok, JsonbValue *jbv); +extern JsonbValue *findJsonValueFromContainer(JsonContainer *jc, uint32 flags, + JsonbValue *key); +extern JsonbValue *getIthJsonValueFromContainer(JsonContainer *array, + uint32 index); + #endif /* JSONAPI_H */ diff --git a/src/include/utils/jsonb.h b/src/include/utils/jsonb.h index 9698c46..a5d1df4 100644 --- a/src/include/utils/jsonb.h +++ b/src/include/utils/jsonb.h @@ -221,10 +221,10 @@ typedef struct } Jsonb; /* convenience macros for accessing the root container in a Jsonb datum */ -#define JB_ROOT_COUNT(jbp_) (*(uint32 *) VARDATA(jbp_) & JB_CMASK) -#define JB_ROOT_IS_SCALAR(jbp_) ((*(uint32 *) VARDATA(jbp_) & JB_FSCALAR) != 0) -#define JB_ROOT_IS_OBJECT(jbp_) ((*(uint32 *) VARDATA(jbp_) & JB_FOBJECT) != 0) -#define JB_ROOT_IS_ARRAY(jbp_) ((*(uint32 *) VARDATA(jbp_) & JB_FARRAY) != 0) +#define JB_ROOT_COUNT(jbp_) JsonContainerSize(&(jbp_)->root) +#define JB_ROOT_IS_SCALAR(jbp_) JsonContainerIsScalar(&(jbp_)->root) +#define JB_ROOT_IS_OBJECT(jbp_) JsonContainerIsObject(&(jbp_)->root) +#define JB_ROOT_IS_ARRAY(jbp_) JsonContainerIsArray(&(jbp_)->root) enum jbvType @@ -239,14 +239,6 @@ enum jbvType jbvObject, /* Binary (i.e. struct Jsonb) jbvArray/jbvObject */ jbvBinary, - - /* - * Virtual types. - * - * These types are used only for in-memory JSON processing and serialized - * into JSON strings when outputted to json/jsonb. - */ - jbvDatetime = 0x20, }; /* @@ -280,6 +272,8 @@ struct JsonbValue { int nPairs; /* 1 pair, 2 elements */ JsonbPair *pairs; + bool uniquify; /* Should we sort pairs by key name and + * remove duplicate keys? */ } object; /* Associative container type */ struct @@ -287,20 +281,11 @@ 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) || \ - (jsonbval)->type == jbvDatetime) + (jsonbval)->type <= jbvBool)) /* * Key/value pair within an Object. @@ -374,6 +359,8 @@ typedef struct JsonbIterator /* Support functions */ extern uint32 getJsonbOffset(const JsonbContainer *jc, int index); extern uint32 getJsonbLength(const JsonbContainer *jc, int index); +extern int lengthCompareJsonbStringValue(const void *a, const void *b); +extern bool equalsJsonbScalarValue(JsonbValue *a, JsonbValue *b); extern int compareJsonbContainers(JsonbContainer *a, JsonbContainer *b); extern JsonbValue *findJsonbValueFromContainer(JsonbContainer *sheader, uint32 flags, @@ -382,6 +369,8 @@ extern JsonbValue *getIthJsonbValueFromContainer(JsonbContainer *sheader, uint32 i); extern JsonbValue *pushJsonbValue(JsonbParseState **pstate, JsonbIteratorToken seq, JsonbValue *jbVal); +extern JsonbValue *pushJsonbValueScalar(JsonbParseState **pstate, + JsonbIteratorToken seq,JsonbValue *scalarVal); extern JsonbIterator *JsonbIteratorInit(JsonbContainer *container); extern JsonbIteratorToken JsonbIteratorNext(JsonbIterator **it, JsonbValue *val, bool skipNested); diff --git a/src/test/regress/expected/json_jsonpath.out b/src/test/regress/expected/json_jsonpath.out new file mode 100644 index 0000000..220025d --- /dev/null +++ b/src/test/regress/expected/json_jsonpath.out @@ -0,0 +1,2120 @@ +select json '{"a": 12}' @? '$'; + ?column? +---------- + t +(1 row) + +select json '{"a": 12}' @? '1'; + ?column? +---------- + t +(1 row) + +select json '{"a": 12}' @? '$.a.b'; + ?column? +---------- + f +(1 row) + +select json '{"a": 12}' @? '$.b'; + ?column? +---------- + f +(1 row) + +select json '{"a": 12}' @? '$.a + 2'; + ?column? +---------- + t +(1 row) + +select json '{"a": 12}' @? '$.b + 2'; + ?column? +---------- + +(1 row) + +select json '{"a": {"a": 12}}' @? '$.a.a'; + ?column? +---------- + t +(1 row) + +select json '{"a": {"a": 12}}' @? '$.*.a'; + ?column? +---------- + t +(1 row) + +select json '{"b": {"a": 12}}' @? '$.*.a'; + ?column? +---------- + t +(1 row) + +select json '{"b": {"a": 12}}' @? '$.*.b'; + ?column? +---------- + f +(1 row) + +select json '{"b": {"a": 12}}' @? 'strict $.*.b'; + ?column? +---------- + +(1 row) + +select json '{}' @? '$.*'; + ?column? +---------- + f +(1 row) + +select json '{"a": 1}' @? '$.*'; + ?column? +---------- + t +(1 row) + +select json '{"a": {"b": 1}}' @? 'lax $.**{1}'; + ?column? +---------- + t +(1 row) + +select json '{"a": {"b": 1}}' @? 'lax $.**{2}'; + ?column? +---------- + t +(1 row) + +select json '{"a": {"b": 1}}' @? 'lax $.**{3}'; + ?column? +---------- + f +(1 row) + +select json '[]' @? '$[*]'; + ?column? +---------- + f +(1 row) + +select json '[1]' @? '$[*]'; + ?column? +---------- + t +(1 row) + +select json '[1]' @? '$[1]'; + ?column? +---------- + f +(1 row) + +select json '[1]' @? 'strict $[1]'; + ?column? +---------- + +(1 row) + +select json_path_query('[1]', 'strict $[1]'); +ERROR: invalid SQL/JSON subscript +DETAIL: jsonpath array subscript is out of bounds +select json '[1]' @? 'lax $[10000000000000000]'; + ?column? +---------- + +(1 row) + +select json '[1]' @? 'strict $[10000000000000000]'; + ?column? +---------- + +(1 row) + +select json_path_query('[1]', 'lax $[10000000000000000]'); +ERROR: invalid SQL/JSON subscript +DETAIL: jsonpath array subscript is out of integer range +select json_path_query('[1]', 'strict $[10000000000000000]'); +ERROR: invalid SQL/JSON subscript +DETAIL: jsonpath array subscript is out of integer range +select json '[1]' @? '$[0]'; + ?column? +---------- + t +(1 row) + +select json '[1]' @? '$[0.3]'; + ?column? +---------- + t +(1 row) + +select json '[1]' @? '$[0.5]'; + ?column? +---------- + t +(1 row) + +select json '[1]' @? '$[0.9]'; + ?column? +---------- + t +(1 row) + +select json '[1]' @? '$[1.2]'; + ?column? +---------- + f +(1 row) + +select json '[1]' @? 'strict $[1.2]'; + ?column? +---------- + +(1 row) + +select json '{"a": [1,2,3], "b": [3,4,5]}' @? '$ ? (@.a[*] > @.b[*])'; + ?column? +---------- + f +(1 row) + +select json '{"a": [1,2,3], "b": [3,4,5]}' @? '$ ? (@.a[*] >= @.b[*])'; + ?column? +---------- + t +(1 row) + +select json '{"a": [1,2,3], "b": [3,4,"5"]}' @? '$ ? (@.a[*] >= @.b[*])'; + ?column? +---------- + t +(1 row) + +select json '{"a": [1,2,3], "b": [3,4,"5"]}' @? 'strict $ ? (@.a[*] >= @.b[*])'; + ?column? +---------- + f +(1 row) + +select json '{"a": [1,2,3], "b": [3,4,null]}' @? '$ ? (@.a[*] >= @.b[*])'; + ?column? +---------- + t +(1 row) + +select json '1' @? '$ ? ((@ == "1") is unknown)'; + ?column? +---------- + t +(1 row) + +select json '1' @? '$ ? ((@ == 1) is unknown)'; + ?column? +---------- + f +(1 row) + +select json '[{"a": 1}, {"a": 2}]' @? '$[0 to 1] ? (@.a > 1)'; + ?column? +---------- + t +(1 row) + +select json_path_query('1', 'lax $.a'); + json_path_query +----------------- +(0 rows) + +select json_path_query('1', 'strict $.a'); +ERROR: SQL/JSON member not found +DETAIL: jsonpath member accessor is applied to not an object +select json_path_query('1', 'strict $.*'); +ERROR: SQL/JSON object not found +DETAIL: jsonpath wildcard member accessor is applied to not an object +select json_path_query('[]', 'lax $.a'); + json_path_query +----------------- +(0 rows) + +select json_path_query('[]', 'strict $.a'); +ERROR: SQL/JSON member not found +DETAIL: jsonpath member accessor is applied to not an object +select json_path_query('{}', 'lax $.a'); + json_path_query +----------------- +(0 rows) + +select json_path_query('{}', 'strict $.a'); +ERROR: SQL/JSON member not found +DETAIL: JSON object does not contain key "a" +select json_path_query('1', 'strict $[1]'); +ERROR: SQL/JSON array not found +DETAIL: jsonpath array accessor is applied to not an array +select json_path_query('1', 'strict $[*]'); +ERROR: SQL/JSON array not found +DETAIL: jsonpath wildcard array accessor is applied to not an array +select json_path_query('[]', 'strict $[1]'); +ERROR: invalid SQL/JSON subscript +DETAIL: jsonpath array subscript is out of bounds +select json_path_query('[]', 'strict $["a"]'); +ERROR: invalid SQL/JSON subscript +DETAIL: jsonpath array subscript is not a singleton numeric value +select json_path_query('{"a": 12, "b": {"a": 13}}', '$.a'); + json_path_query +----------------- + 12 +(1 row) + +select json_path_query('{"a": 12, "b": {"a": 13}}', '$.b'); + json_path_query +----------------- + {"a": 13} +(1 row) + +select json_path_query('{"a": 12, "b": {"a": 13}}', '$.*'); + json_path_query +----------------- + 12 + {"a": 13} +(2 rows) + +select json_path_query('{"a": 12, "b": {"a": 13}}', 'lax $.*.a'); + json_path_query +----------------- + 13 +(1 row) + +select json_path_query('[12, {"a": 13}, {"b": 14}]', 'lax $[*].a'); + json_path_query +----------------- + 13 +(1 row) + +select json_path_query('[12, {"a": 13}, {"b": 14}]', 'lax $[*].*'); + json_path_query +----------------- + 13 + 14 +(2 rows) + +select json_path_query('[12, {"a": 13}, {"b": 14}]', 'lax $[0].a'); + json_path_query +----------------- +(0 rows) + +select json_path_query('[12, {"a": 13}, {"b": 14}]', 'lax $[1].a'); + json_path_query +----------------- + 13 +(1 row) + +select json_path_query('[12, {"a": 13}, {"b": 14}]', 'lax $[2].a'); + json_path_query +----------------- +(0 rows) + +select json_path_query('[12, {"a": 13}, {"b": 14}]', 'lax $[0,1].a'); + json_path_query +----------------- + 13 +(1 row) + +select json_path_query('[12, {"a": 13}, {"b": 14}]', 'lax $[0 to 10].a'); + json_path_query +----------------- + 13 +(1 row) + +select json_path_query('[12, {"a": 13}, {"b": 14}]', 'lax $[0 to 10 / 0].a'); +ERROR: division by zero +select json_path_query('[12, {"a": 13}, {"b": 14}, "ccc", true]', '$[2.5 - 1 to $.size() - 2]'); + json_path_query +----------------- + {"a": 13} + {"b": 14} + "ccc" +(3 rows) + +select json_path_query('1', 'lax $[0]'); + json_path_query +----------------- + 1 +(1 row) + +select json_path_query('1', 'lax $[*]'); + json_path_query +----------------- + 1 +(1 row) + +select json_path_query('[1]', 'lax $[0]'); + json_path_query +----------------- + 1 +(1 row) + +select json_path_query('[1]', 'lax $[*]'); + json_path_query +----------------- + 1 +(1 row) + +select json_path_query('[1,2,3]', 'lax $[*]'); + json_path_query +----------------- + 1 + 2 + 3 +(3 rows) + +select json_path_query('[1,2,3]', 'strict $[*].a'); +ERROR: SQL/JSON member not found +DETAIL: jsonpath member accessor is applied to not an object +select json_path_query('[]', '$[last]'); + json_path_query +----------------- +(0 rows) + +select json_path_query('[]', '$[last ? (exists(last))]'); + json_path_query +----------------- +(0 rows) + +select json_path_query('[]', 'strict $[last]'); +ERROR: invalid SQL/JSON subscript +DETAIL: jsonpath array subscript is out of bounds +select json_path_query('[1]', '$[last]'); + json_path_query +----------------- + 1 +(1 row) + +select json_path_query('[1,2,3]', '$[last]'); + json_path_query +----------------- + 3 +(1 row) + +select json_path_query('[1,2,3]', '$[last - 1]'); + json_path_query +----------------- + 2 +(1 row) + +select json_path_query('[1,2,3]', '$[last ? (@.type() == "number")]'); + json_path_query +----------------- + 3 +(1 row) + +select json_path_query('[1,2,3]', '$[last ? (@.type() == "string")]'); +ERROR: invalid SQL/JSON subscript +DETAIL: jsonpath array subscript is not a singleton numeric value +select * from json_path_query('{"a": 10}', '$'); + json_path_query +----------------- + {"a": 10} +(1 row) + +select * from json_path_query('{"a": 10}', '$ ? (@.a < $value)'); +ERROR: cannot find jsonpath variable 'value' +select * from json_path_query('{"a": 10}', '$ ? (@.a < $value)', '1'); +ERROR: json containing jsonpath variables is not an object +select * from json_path_query('{"a": 10}', '$ ? (@.a < $value)', '[{"value" : 13}]'); +ERROR: json containing jsonpath variables is not an object +select * from json_path_query('{"a": 10}', '$ ? (@.a < $value)', '{"value" : 13}'); + json_path_query +----------------- + {"a": 10} +(1 row) + +select * from json_path_query('{"a": 10}', '$ ? (@.a < $value)', '{"value" : 8}'); + json_path_query +----------------- +(0 rows) + +select * from json_path_query('{"a": 10}', '$.a ? (@ < $value)', '{"value" : 13}'); + json_path_query +----------------- + 10 +(1 row) + +select * from json_path_query('[10,11,12,13,14,15]', '$[*] ? (@ < $value)', '{"value" : 13}'); + json_path_query +----------------- + 10 + 11 + 12 +(3 rows) + +select * from json_path_query('[10,11,12,13,14,15]', '$[0,1] ? (@ < $x.value)', '{"x": {"value" : 13}}'); + json_path_query +----------------- + 10 + 11 +(2 rows) + +select * from json_path_query('[10,11,12,13,14,15]', '$[0 to 2] ? (@ < $value)', '{"value" : 15}'); + json_path_query +----------------- + 10 + 11 + 12 +(3 rows) + +select * from json_path_query('[1,"1",2,"2",null]', '$[*] ? (@ == "1")'); + json_path_query +----------------- + "1" +(1 row) + +select * from json_path_query('[1,"1",2,"2",null]', '$[*] ? (@ == $value)', '{"value" : "1"}'); + json_path_query +----------------- + "1" +(1 row) + +select * from json_path_query('[1,"1",2,"2",null]', '$[*] ? (@ == $value)', '{"value" : null}'); + json_path_query +----------------- + null +(1 row) + +select * from json_path_query('[1, "2", null]', '$[*] ? (@ != null)'); + json_path_query +----------------- + 1 + "2" +(2 rows) + +select * from json_path_query('[1, "2", null]', '$[*] ? (@ == null)'); + json_path_query +----------------- + null +(1 row) + +select * from json_path_query('{}', '$ ? (@ == @)'); + json_path_query +----------------- +(0 rows) + +select * from json_path_query('[]', 'strict $ ? (@ == @)'); + json_path_query +----------------- +(0 rows) + +select json_path_query('{"a": {"b": 1}}', 'lax $.**'); + json_path_query +----------------- + {"a": {"b": 1}} + {"b": 1} + 1 +(3 rows) + +select json_path_query('{"a": {"b": 1}}', 'lax $.**{0}'); + json_path_query +----------------- + {"a": {"b": 1}} +(1 row) + +select json_path_query('{"a": {"b": 1}}', 'lax $.**{0 to last}'); + json_path_query +----------------- + {"a": {"b": 1}} + {"b": 1} + 1 +(3 rows) + +select json_path_query('{"a": {"b": 1}}', 'lax $.**{1}'); + json_path_query +----------------- + {"b": 1} +(1 row) + +select json_path_query('{"a": {"b": 1}}', 'lax $.**{1 to last}'); + json_path_query +----------------- + {"b": 1} + 1 +(2 rows) + +select json_path_query('{"a": {"b": 1}}', 'lax $.**{2}'); + json_path_query +----------------- + 1 +(1 row) + +select json_path_query('{"a": {"b": 1}}', 'lax $.**{2 to last}'); + json_path_query +----------------- + 1 +(1 row) + +select json_path_query('{"a": {"b": 1}}', 'lax $.**{3 to last}'); + json_path_query +----------------- +(0 rows) + +select json_path_query('{"a": {"b": 1}}', 'lax $.**{last}'); + json_path_query +----------------- + 1 +(1 row) + +select json_path_query('{"a": {"b": 1}}', 'lax $.**.b ? (@ > 0)'); + json_path_query +----------------- + 1 +(1 row) + +select json_path_query('{"a": {"b": 1}}', 'lax $.**{0}.b ? (@ > 0)'); + json_path_query +----------------- +(0 rows) + +select json_path_query('{"a": {"b": 1}}', 'lax $.**{1}.b ? (@ > 0)'); + json_path_query +----------------- + 1 +(1 row) + +select json_path_query('{"a": {"b": 1}}', 'lax $.**{0 to last}.b ? (@ > 0)'); + json_path_query +----------------- + 1 +(1 row) + +select json_path_query('{"a": {"b": 1}}', 'lax $.**{1 to last}.b ? (@ > 0)'); + json_path_query +----------------- + 1 +(1 row) + +select json_path_query('{"a": {"b": 1}}', 'lax $.**{1 to 2}.b ? (@ > 0)'); + json_path_query +----------------- + 1 +(1 row) + +select json_path_query('{"a": {"c": {"b": 1}}}', 'lax $.**.b ? (@ > 0)'); + json_path_query +----------------- + 1 +(1 row) + +select json_path_query('{"a": {"c": {"b": 1}}}', 'lax $.**{0}.b ? (@ > 0)'); + json_path_query +----------------- +(0 rows) + +select json_path_query('{"a": {"c": {"b": 1}}}', 'lax $.**{1}.b ? (@ > 0)'); + json_path_query +----------------- +(0 rows) + +select json_path_query('{"a": {"c": {"b": 1}}}', 'lax $.**{0 to last}.b ? (@ > 0)'); + json_path_query +----------------- + 1 +(1 row) + +select json_path_query('{"a": {"c": {"b": 1}}}', 'lax $.**{1 to last}.b ? (@ > 0)'); + json_path_query +----------------- + 1 +(1 row) + +select json_path_query('{"a": {"c": {"b": 1}}}', 'lax $.**{1 to 2}.b ? (@ > 0)'); + json_path_query +----------------- + 1 +(1 row) + +select json_path_query('{"a": {"c": {"b": 1}}}', 'lax $.**{2 to 3}.b ? (@ > 0)'); + json_path_query +----------------- + 1 +(1 row) + +select json '{"a": {"b": 1}}' @? '$.**.b ? ( @ > 0)'; + ?column? +---------- + t +(1 row) + +select json '{"a": {"b": 1}}' @? '$.**{0}.b ? ( @ > 0)'; + ?column? +---------- + f +(1 row) + +select json '{"a": {"b": 1}}' @? '$.**{1}.b ? ( @ > 0)'; + ?column? +---------- + t +(1 row) + +select json '{"a": {"b": 1}}' @? '$.**{0 to last}.b ? ( @ > 0)'; + ?column? +---------- + t +(1 row) + +select json '{"a": {"b": 1}}' @? '$.**{1 to last}.b ? ( @ > 0)'; + ?column? +---------- + t +(1 row) + +select json '{"a": {"b": 1}}' @? '$.**{1 to 2}.b ? ( @ > 0)'; + ?column? +---------- + t +(1 row) + +select json '{"a": {"c": {"b": 1}}}' @? '$.**.b ? ( @ > 0)'; + ?column? +---------- + t +(1 row) + +select json '{"a": {"c": {"b": 1}}}' @? '$.**{0}.b ? ( @ > 0)'; + ?column? +---------- + f +(1 row) + +select json '{"a": {"c": {"b": 1}}}' @? '$.**{1}.b ? ( @ > 0)'; + ?column? +---------- + f +(1 row) + +select json '{"a": {"c": {"b": 1}}}' @? '$.**{0 to last}.b ? ( @ > 0)'; + ?column? +---------- + t +(1 row) + +select json '{"a": {"c": {"b": 1}}}' @? '$.**{1 to last}.b ? ( @ > 0)'; + ?column? +---------- + t +(1 row) + +select json '{"a": {"c": {"b": 1}}}' @? '$.**{1 to 2}.b ? ( @ > 0)'; + ?column? +---------- + t +(1 row) + +select json '{"a": {"c": {"b": 1}}}' @? '$.**{2 to 3}.b ? ( @ > 0)'; + ?column? +---------- + t +(1 row) + +select json_path_query('{"g": {"x": 2}}', '$.g ? (exists (@.x))'); + json_path_query +----------------- + {"x": 2} +(1 row) + +select json_path_query('{"g": {"x": 2}}', '$.g ? (exists (@.y))'); + json_path_query +----------------- +(0 rows) + +select json_path_query('{"g": {"x": 2}}', '$.g ? (exists (@.x ? (@ >= 2) ))'); + json_path_query +----------------- + {"x": 2} +(1 row) + +select json_path_query('{"g": [{"x": 2}, {"y": 3}]}', 'lax $.g ? (exists (@.x))'); + json_path_query +----------------- + {"x": 2} +(1 row) + +select json_path_query('{"g": [{"x": 2}, {"y": 3}]}', 'lax $.g ? (exists (@.x + "3"))'); + json_path_query +----------------- +(0 rows) + +select json_path_query('{"g": [{"x": 2}, {"y": 3}]}', 'lax $.g ? ((exists (@.x + "3")) is unknown)'); + json_path_query +----------------- + {"x": 2} + {"y": 3} +(2 rows) + +select json_path_query('{"g": [{"x": 2}, {"y": 3}]}', 'strict $.g[*] ? (exists (@.x))'); + json_path_query +----------------- + {"x": 2} +(1 row) + +select json_path_query('{"g": [{"x": 2}, {"y": 3}]}', 'strict $.g[*] ? ((exists (@.x)) is unknown)'); + json_path_query +----------------- + {"y": 3} +(1 row) + +select json_path_query('{"g": [{"x": 2}, {"y": 3}]}', 'strict $.g ? (exists (@[*].x))'); + json_path_query +----------------- +(0 rows) + +select json_path_query('{"g": [{"x": 2}, {"y": 3}]}', 'strict $.g ? ((exists (@[*].x)) is unknown)'); + json_path_query +---------------------- + [{"x": 2}, {"y": 3}] +(1 row) + +--test ternary logic +select + x, y, + json_path_query( + '[true, false, null]', + '$[*] ? (@ == true && ($x == true && $y == true) || + @ == false && !($x == true && $y == true) || + @ == null && ($x == true && $y == true) is unknown)', + json_build_object('x', x, 'y', y) + ) as "x && y" +from + (values (json 'true'), ('false'), ('"null"')) x(x), + (values (json '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, + json_path_query( + '[true, false, null]', + '$[*] ? (@ == true && ($x == true || $y == true) || + @ == false && !($x == true || $y == true) || + @ == null && ($x == true || $y == true) is unknown)', + json_build_object('x', x, 'y', y) + ) as "x || y" +from + (values (json 'true'), ('false'), ('"null"')) x(x), + (values (json '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 json '{"a": 1, "b":1}' @? '$ ? (@.a == @.b)'; + ?column? +---------- + t +(1 row) + +select json '{"c": {"a": 1, "b":1}}' @? '$ ? (@.a == @.b)'; + ?column? +---------- + f +(1 row) + +select json '{"c": {"a": 1, "b":1}}' @? '$.c ? (@.a == @.b)'; + ?column? +---------- + t +(1 row) + +select json '{"c": {"a": 1, "b":1}}' @? '$.c ? ($.c.a == @.b)'; + ?column? +---------- + t +(1 row) + +select json '{"c": {"a": 1, "b":1}}' @? '$.* ? (@.a == @.b)'; + ?column? +---------- + t +(1 row) + +select json '{"a": 1, "b":1}' @? '$.** ? (@.a == @.b)'; + ?column? +---------- + t +(1 row) + +select json '{"c": {"a": 1, "b":1}}' @? '$.** ? (@.a == @.b)'; + ?column? +---------- + t +(1 row) + +select json_path_query('{"c": {"a": 2, "b":1}}', '$.** ? (@.a == 1 + 1)'); + json_path_query +----------------- + {"a": 2, "b":1} +(1 row) + +select json_path_query('{"c": {"a": 2, "b":1}}', '$.** ? (@.a == (1 + 1))'); + json_path_query +----------------- + {"a": 2, "b":1} +(1 row) + +select json_path_query('{"c": {"a": 2, "b":1}}', '$.** ? (@.a == @.b + 1)'); + json_path_query +----------------- + {"a": 2, "b":1} +(1 row) + +select json_path_query('{"c": {"a": 2, "b":1}}', '$.** ? (@.a == (@.b + 1))'); + json_path_query +----------------- + {"a": 2, "b":1} +(1 row) + +select json '{"c": {"a": -1, "b":1}}' @? '$.** ? (@.a == - 1)'; + ?column? +---------- + t +(1 row) + +select json '{"c": {"a": -1, "b":1}}' @? '$.** ? (@.a == -1)'; + ?column? +---------- + t +(1 row) + +select json '{"c": {"a": -1, "b":1}}' @? '$.** ? (@.a == -@.b)'; + ?column? +---------- + t +(1 row) + +select json '{"c": {"a": -1, "b":1}}' @? '$.** ? (@.a == - @.b)'; + ?column? +---------- + t +(1 row) + +select json '{"c": {"a": 0, "b":1}}' @? '$.** ? (@.a == 1 - @.b)'; + ?column? +---------- + t +(1 row) + +select json '{"c": {"a": 2, "b":1}}' @? '$.** ? (@.a == 1 - - @.b)'; + ?column? +---------- + t +(1 row) + +select json '{"c": {"a": 0, "b":1}}' @? '$.** ? (@.a == 1 - +@.b)'; + ?column? +---------- + t +(1 row) + +select json '[1,2,3]' @? '$ ? (+@[*] > +2)'; + ?column? +---------- + t +(1 row) + +select json '[1,2,3]' @? '$ ? (+@[*] > +3)'; + ?column? +---------- + f +(1 row) + +select json '[1,2,3]' @? '$ ? (-@[*] < -2)'; + ?column? +---------- + t +(1 row) + +select json '[1,2,3]' @? '$ ? (-@[*] < -3)'; + ?column? +---------- + f +(1 row) + +select json '1' @? '$ ? ($ > 0)'; + ?column? +---------- + t +(1 row) + +-- arithmetic errors +select json_path_query('[1,2,0,3]', '$[*] ? (2 / @ > 0)'); + json_path_query +----------------- + 1 + 2 + 3 +(3 rows) + +select json_path_query('[1,2,0,3]', '$[*] ? ((2 / @ > 0) is unknown)'); + json_path_query +----------------- + 0 +(1 row) + +select json_path_query('0', '1 / $'); +ERROR: division by zero +select json_path_query('0', '1 / $ + 2'); +ERROR: division by zero +select json_path_query('0', '-(3 + 1 % $)'); +ERROR: division by zero +select json_path_query('1', '$ + "2"'); +ERROR: singleton SQL/JSON item required +DETAIL: right operand of binary jsonpath operator + is not a singleton numeric value +select json_path_query('[1, 2]', '3 * $'); +ERROR: singleton SQL/JSON item required +DETAIL: right operand of binary jsonpath operator * is not a singleton numeric value +select json_path_query('"a"', '-$'); +ERROR: SQL/JSON number not found +DETAIL: operand of unary jsonpath operator - is not a numeric value +select json_path_query('[1,"2",3]', '+$'); +ERROR: SQL/JSON number not found +DETAIL: operand of unary jsonpath operator + is not a numeric value +select json '["1",2,0,3]' @? '-$[*]'; + ?column? +---------- + t +(1 row) + +select json '[1,"2",0,3]' @? '-$[*]'; + ?column? +---------- + t +(1 row) + +select json '["1",2,0,3]' @? 'strict -$[*]'; + ?column? +---------- + +(1 row) + +select json '[1,"2",0,3]' @? 'strict -$[*]'; + ?column? +---------- + +(1 row) + +-- unwrapping of operator arguments in lax mode +select json_path_query('{"a": [2]}', 'lax $.a * 3'); + json_path_query +----------------- + 6 +(1 row) + +select json_path_query('{"a": [2]}', 'lax $.a + 3'); + json_path_query +----------------- + 5 +(1 row) + +select json_path_query('{"a": [2, 3, 4]}', 'lax -$.a'); + json_path_query +----------------- + -2 + -3 + -4 +(3 rows) + +-- should fail +select json_path_query('{"a": [1, 2]}', 'lax $.a * 3'); +ERROR: singleton SQL/JSON item required +DETAIL: left operand of binary jsonpath operator * is not a singleton numeric value +-- extension: boolean expressions +select json_path_query('2', '$ > 1'); + json_path_query +----------------- + true +(1 row) + +select json_path_query('2', '$ <= 1'); + json_path_query +----------------- + false +(1 row) + +select json_path_query('2', '$ == "2"'); + json_path_query +----------------- + null +(1 row) + +select json '2' @? '$ == "2"'; + ?column? +---------- + t +(1 row) + +select json '2' @@ '$ > 1'; + ?column? +---------- + t +(1 row) + +select json '2' @@ '$ <= 1'; + ?column? +---------- + f +(1 row) + +select json '2' @@ '$ == "2"'; + ?column? +---------- + +(1 row) + +select json '2' @@ '1'; + ?column? +---------- + +(1 row) + +select json '{}' @@ '$'; + ?column? +---------- + +(1 row) + +select json '[]' @@ '$'; + ?column? +---------- + +(1 row) + +select json '[1,2,3]' @@ '$[*]'; + ?column? +---------- + +(1 row) + +select json '[]' @@ '$[*]'; + ?column? +---------- + +(1 row) + +select json_path_match('[[1, true], [2, false]]', 'strict $[*] ? (@[0] > $x) [1]', '{"x": 1}'); + json_path_match +----------------- + f +(1 row) + +select json_path_match('[[1, true], [2, false]]', 'strict $[*] ? (@[0] < $x) [1]', '{"x": 2}'); + json_path_match +----------------- + t +(1 row) + +select json_path_query('[null,1,true,"a",[],{}]', '$.type()'); + json_path_query +----------------- + "array" +(1 row) + +select json_path_query('[null,1,true,"a",[],{}]', 'lax $.type()'); + json_path_query +----------------- + "array" +(1 row) + +select json_path_query('[null,1,true,"a",[],{}]', '$[*].type()'); + json_path_query +----------------- + "null" + "number" + "boolean" + "string" + "array" + "object" +(6 rows) + +select json_path_query('null', 'null.type()'); + json_path_query +----------------- + "null" +(1 row) + +select json_path_query('null', 'true.type()'); + json_path_query +----------------- + "boolean" +(1 row) + +select json_path_query('null', '123.type()'); + json_path_query +----------------- + "number" +(1 row) + +select json_path_query('null', '"123".type()'); + json_path_query +----------------- + "string" +(1 row) + +select json_path_query('{"a": 2}', '($.a - 5).abs() + 10'); + json_path_query +----------------- + 13 +(1 row) + +select json_path_query('{"a": 2.5}', '-($.a * $.a).floor() % 4.3'); + json_path_query +----------------- + -1.7 +(1 row) + +select json_path_query('[1, 2, 3]', '($[*] > 2) ? (@ == true)'); + json_path_query +----------------- + true +(1 row) + +select json_path_query('[1, 2, 3]', '($[*] > 3).type()'); + json_path_query +----------------- + "boolean" +(1 row) + +select json_path_query('[1, 2, 3]', '($[*].a > 3).type()'); + json_path_query +----------------- + "boolean" +(1 row) + +select json_path_query('[1, 2, 3]', 'strict ($[*].a > 3).type()'); + json_path_query +----------------- + "null" +(1 row) + +select json_path_query('[1,null,true,"11",[],[1],[1,2,3],{},{"a":1,"b":2}]', 'strict $[*].size()'); +ERROR: SQL/JSON array not found +DETAIL: jsonpath item method .size() is applied to not an array +select json_path_query('[1,null,true,"11",[],[1],[1,2,3],{},{"a":1,"b":2}]', 'lax $[*].size()'); + json_path_query +----------------- + 1 + 1 + 1 + 1 + 0 + 1 + 3 + 1 + 1 +(9 rows) + +select json_path_query('[0, 1, -2, -3.4, 5.6]', '$[*].abs()'); + json_path_query +----------------- + 0 + 1 + 2 + 3.4 + 5.6 +(5 rows) + +select json_path_query('[0, 1, -2, -3.4, 5.6]', '$[*].floor()'); + json_path_query +----------------- + 0 + 1 + -2 + -4 + 5 +(5 rows) + +select json_path_query('[0, 1, -2, -3.4, 5.6]', '$[*].ceiling()'); + json_path_query +----------------- + 0 + 1 + -2 + -3 + 6 +(5 rows) + +select json_path_query('[0, 1, -2, -3.4, 5.6]', '$[*].ceiling().abs()'); + json_path_query +----------------- + 0 + 1 + 2 + 3 + 6 +(5 rows) + +select json_path_query('[0, 1, -2, -3.4, 5.6]', '$[*].ceiling().abs().type()'); + json_path_query +----------------- + "number" + "number" + "number" + "number" + "number" +(5 rows) + +select json_path_query('[{},1]', '$[*].keyvalue()'); +ERROR: SQL/JSON object not found +DETAIL: jsonpath item method .keyvalue() is applied to not an object +select json_path_query('{}', '$.keyvalue()'); + json_path_query +----------------- +(0 rows) + +select json_path_query('{"a": 1, "b": [1, 2], "c": {"a": "bbb"}}', '$.keyvalue()'); + json_path_query +---------------------------------------------- + {"key": "a", "value": 1, "id": 0} + {"key": "b", "value": [1, 2], "id": 0} + {"key": "c", "value": {"a": "bbb"}, "id": 0} +(3 rows) + +select json_path_query('[{"a": 1, "b": [1, 2]}, {"c": {"a": "bbb"}}]', '$[*].keyvalue()'); + json_path_query +----------------------------------------------- + {"key": "a", "value": 1, "id": 1} + {"key": "b", "value": [1, 2], "id": 1} + {"key": "c", "value": {"a": "bbb"}, "id": 24} +(3 rows) + +select json_path_query('[{"a": 1, "b": [1, 2]}, {"c": {"a": "bbb"}}]', 'strict $.keyvalue()'); +ERROR: SQL/JSON object not found +DETAIL: jsonpath item method .keyvalue() is applied to not an object +select json_path_query('[{"a": 1, "b": [1, 2]}, {"c": {"a": "bbb"}}]', 'lax $.keyvalue()'); + json_path_query +----------------------------------------------- + {"key": "a", "value": 1, "id": 1} + {"key": "b", "value": [1, 2], "id": 1} + {"key": "c", "value": {"a": "bbb"}, "id": 24} +(3 rows) + +select json_path_query('[{"a": 1, "b": [1, 2]}, {"c": {"a": "bbb"}}]', 'strict $.keyvalue().a'); +ERROR: SQL/JSON object not found +DETAIL: jsonpath item method .keyvalue() is applied to not an object +select json '{"a": 1, "b": [1, 2]}' @? 'lax $.keyvalue()'; + ?column? +---------- + t +(1 row) + +select json '{"a": 1, "b": [1, 2]}' @? 'lax $.keyvalue().key'; + ?column? +---------- + t +(1 row) + +select json_path_query('null', '$.double()'); +ERROR: non-numeric SQL/JSON item +DETAIL: jsonpath item method .double() is applied to neither a string nor numeric value +select json_path_query('true', '$.double()'); +ERROR: non-numeric SQL/JSON item +DETAIL: jsonpath item method .double() is applied to neither a string nor numeric value +select json_path_query('[]', '$.double()'); + json_path_query +----------------- +(0 rows) + +select json_path_query('[]', 'strict $.double()'); +ERROR: non-numeric SQL/JSON item +DETAIL: jsonpath item method .double() is applied to neither a string nor numeric value +select json_path_query('{}', '$.double()'); +ERROR: non-numeric SQL/JSON item +DETAIL: jsonpath item method .double() is applied to neither a string nor numeric value +select json_path_query('1.23', '$.double()'); + json_path_query +----------------- + 1.23 +(1 row) + +select json_path_query('"1.23"', '$.double()'); + json_path_query +----------------- + 1.23 +(1 row) + +select json_path_query('"1.23aaa"', '$.double()'); +ERROR: non-numeric SQL/JSON item +DETAIL: jsonpath item method .double() is applied to not a numeric value +select json_path_query('"nan"', '$.double()'); + json_path_query +----------------- + "NaN" +(1 row) + +select json_path_query('"NaN"', '$.double()'); + json_path_query +----------------- + "NaN" +(1 row) + +select json_path_query('"inf"', '$.double()'); +ERROR: non-numeric SQL/JSON item +DETAIL: jsonpath item method .double() is applied to not a numeric value +select json_path_query('"-inf"', '$.double()'); +ERROR: non-numeric SQL/JSON item +DETAIL: jsonpath item method .double() is applied to not a numeric value +select json_path_query('{}', '$.abs()'); +ERROR: non-numeric SQL/JSON item +DETAIL: jsonpath item method .abs() is applied to not a numeric value +select json_path_query('true', '$.floor()'); +ERROR: non-numeric SQL/JSON item +DETAIL: jsonpath item method .floor() is applied to not a numeric value +select json_path_query('"1.2"', '$.ceiling()'); +ERROR: non-numeric SQL/JSON item +DETAIL: jsonpath item method .ceiling() is applied to not a numeric value +select json_path_query('["", "a", "abc", "abcabc"]', '$[*] ? (@ starts with "abc")'); + json_path_query +----------------- + "abc" + "abcabc" +(2 rows) + +select json_path_query('["", "a", "abc", "abcabc"]', 'strict $ ? (@[*] starts with "abc")'); + json_path_query +---------------------------- + ["", "a", "abc", "abcabc"] +(1 row) + +select json_path_query('["", "a", "abd", "abdabc"]', 'strict $ ? (@[*] starts with "abc")'); + json_path_query +----------------- +(0 rows) + +select json_path_query('["abc", "abcabc", null, 1]', 'strict $ ? (@[*] starts with "abc")'); + json_path_query +----------------- +(0 rows) + +select json_path_query('["abc", "abcabc", null, 1]', 'strict $ ? ((@[*] starts with "abc") is unknown)'); + json_path_query +---------------------------- + ["abc", "abcabc", null, 1] +(1 row) + +select json_path_query('[[null, 1, "abc", "abcabc"]]', 'lax $ ? (@[*] starts with "abc")'); + json_path_query +---------------------------- + [null, 1, "abc", "abcabc"] +(1 row) + +select json_path_query('[[null, 1, "abd", "abdabc"]]', 'lax $ ? ((@[*] starts with "abc") is unknown)'); + json_path_query +---------------------------- + [null, 1, "abd", "abdabc"] +(1 row) + +select json_path_query('[null, 1, "abd", "abdabc"]', 'lax $[*] ? ((@ starts with "abc") is unknown)'); + json_path_query +----------------- + null + 1 +(2 rows) + +select json_path_query('[null, 1, "abc", "abd", "aBdC", "abdacb", "adc\nabc", "babc"]', 'lax $[*] ? (@ like_regex "^ab.*c")'); + json_path_query +----------------- + "abc" + "abdacb" +(2 rows) + +select json_path_query('[null, 1, "abc", "abd", "aBdC", "abdacb", "adc\nabc", "babc"]', 'lax $[*] ? (@ like_regex "^a b.* c " flag "ix")'); + json_path_query +----------------- + "abc" + "aBdC" + "abdacb" +(3 rows) + +select json_path_query('[null, 1, "abc", "abd", "aBdC", "abdacb", "adc\nabc", "babc"]', 'lax $[*] ? (@ like_regex "^ab.*c" flag "m")'); + json_path_query +----------------- + "abc" + "abdacb" + "adc\nabc" +(3 rows) + +select json_path_query('[null, 1, "abc", "abd", "aBdC", "abdacb", "adc\nabc", "babc"]', 'lax $[*] ? (@ like_regex "^ab.*c" flag "s")'); + json_path_query +----------------- + "abc" + "abdacb" +(2 rows) + +select json_path_query('null', '$.datetime()'); +ERROR: invalid argument for SQL/JSON datetime function +DETAIL: jsonpath item method .datetime() is applied to not a string +select json_path_query('true', '$.datetime()'); +ERROR: invalid argument for SQL/JSON datetime function +DETAIL: jsonpath item method .datetime() is applied to not a string +select json_path_query('1', '$.datetime()'); +ERROR: invalid argument for SQL/JSON datetime function +DETAIL: jsonpath item method .datetime() is applied to not a string +select json_path_query('[]', '$.datetime()'); + json_path_query +----------------- +(0 rows) + +select json_path_query('[]', 'strict $.datetime()'); +ERROR: invalid argument for SQL/JSON datetime function +DETAIL: jsonpath item method .datetime() is applied to not a string +select json_path_query('{}', '$.datetime()'); +ERROR: invalid argument for SQL/JSON datetime function +DETAIL: jsonpath item method .datetime() is applied to not a string +select json_path_query('""', '$.datetime()'); +ERROR: invalid argument for SQL/JSON datetime function +DETAIL: unrecognized datetime format +HINT: use datetime template argument for explicit format specification +select json_path_query('"12:34"', '$.datetime("aaa")'); +ERROR: datetime format is not dated and not timed +select json_path_query('"12:34"', '$.datetime("aaa", 1)'); +ERROR: datetime format is not dated and not timed +select json_path_query('"12:34"', '$.datetime("HH24:MI TZH", 1)'); + json_path_query +--------------------- + "12:34:00+00:00:01" +(1 row) + +select json_path_query('"12:34"', '$.datetime("HH24:MI TZH", 10000000000000)'); +ERROR: invalid argument for SQL/JSON datetime function +DETAIL: timezone argument of jsonpath item method .datetime() is out of integer range +select json_path_query('"12:34"', '$.datetime("HH24:MI TZH", 10000000000000)'); +ERROR: invalid argument for SQL/JSON datetime function +DETAIL: timezone argument of jsonpath item method .datetime() is out of integer range +select json_path_query('"12:34"', '$.datetime("HH24:MI TZH", 2147483647)'); + json_path_query +------------------------- + "12:34:00+596523:14:07" +(1 row) + +select json_path_query('"12:34"', '$.datetime("HH24:MI TZH", 2147483648)'); +ERROR: invalid argument for SQL/JSON datetime function +DETAIL: timezone argument of jsonpath item method .datetime() is out of integer range +select json_path_query('"12:34"', '$.datetime("HH24:MI TZH", -2147483647)'); + json_path_query +------------------------- + "12:34:00-596523:14:07" +(1 row) + +select json_path_query('"12:34"', '$.datetime("HH24:MI TZH", -2147483648)'); +ERROR: invalid argument for SQL/JSON datetime function +DETAIL: timezone argument of jsonpath item method .datetime() is out of integer range +select json_path_query('"aaaa"', '$.datetime("HH24")'); +ERROR: invalid value "aa" for "HH24" +DETAIL: Value must be an integer. +select json '"10-03-2017"' @? '$.datetime("dd-mm-yyyy")'; + ?column? +---------- + t +(1 row) + +select json_path_query('"10-03-2017"', '$.datetime("dd-mm-yyyy")'); + json_path_query +----------------- + "2017-03-10" +(1 row) + +select json_path_query('"10-03-2017"', '$.datetime("dd-mm-yyyy").type()'); + json_path_query +----------------- + "date" +(1 row) + +select json_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy")'); + json_path_query +----------------- + "2017-03-10" +(1 row) + +select json_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy").type()'); + json_path_query +----------------- + "date" +(1 row) + +select json_path_query('"10-03-2017 12:34"', ' $.datetime("dd-mm-yyyy HH24:MI").type()'); + json_path_query +------------------------------- + "timestamp without time zone" +(1 row) + +select json_path_query('"10-03-2017 12:34 +05:20"', '$.datetime("dd-mm-yyyy HH24:MI TZH:TZM").type()'); + json_path_query +---------------------------- + "timestamp with time zone" +(1 row) + +select json_path_query('"12:34:56"', '$.datetime("HH24:MI:SS").type()'); + json_path_query +-------------------------- + "time without time zone" +(1 row) + +select json_path_query('"12:34:56 +05:20"', '$.datetime("HH24:MI:SS TZH:TZM").type()'); + json_path_query +----------------------- + "time with time zone" +(1 row) + +set time zone '+00'; +select json_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy HH24:MI")'); + json_path_query +----------------------- + "2017-03-10T12:34:00" +(1 row) + +select json_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy HH24:MI TZH")'); +ERROR: missing time-zone in input string for type timestamptz +select json_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy HH24:MI TZH", "+00")'); + json_path_query +----------------------------- + "2017-03-10T12:34:00+00:00" +(1 row) + +select json_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy HH24:MI TZH", "+00:12")'); + json_path_query +----------------------------- + "2017-03-10T12:34:00+00:12" +(1 row) + +select json_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy HH24:MI TZH", "-00:12:34")'); + json_path_query +-------------------------------- + "2017-03-10T12:34:00-00:12:34" +(1 row) + +select json_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy HH24:MI TZH", "UTC")'); +ERROR: invalid input syntax for type timestamptz: "UTC" +select json_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy HH24:MI TZH", 12 * 60)'); + json_path_query +----------------------------- + "2017-03-10T12:34:00+00:12" +(1 row) + +select json_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy HH24:MI TZH", -(5 * 3600 + 12 * 60 + 34))'); + json_path_query +-------------------------------- + "2017-03-10T12:34:00-05:12:34" +(1 row) + +select json_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy HH24:MI TZH", $tz)', + json_build_object('tz', extract(timezone from now()))); + json_path_query +----------------------------- + "2017-03-10T12:34:00+00:00" +(1 row) + +select json_path_query('"10-03-2017 12:34 +05"', '$.datetime("dd-mm-yyyy HH24:MI TZH")'); + json_path_query +----------------------------- + "2017-03-10T12:34:00+05:00" +(1 row) + +select json_path_query('"10-03-2017 12:34 -05"', '$.datetime("dd-mm-yyyy HH24:MI TZH")'); + json_path_query +----------------------------- + "2017-03-10T12:34:00-05:00" +(1 row) + +select json_path_query('"10-03-2017 12:34 +05:20"', '$.datetime("dd-mm-yyyy HH24:MI TZH:TZM")'); + json_path_query +----------------------------- + "2017-03-10T12:34:00+05:20" +(1 row) + +select json_path_query('"10-03-2017 12:34 -05:20"', '$.datetime("dd-mm-yyyy HH24:MI TZH:TZM")'); + json_path_query +----------------------------- + "2017-03-10T12:34:00-05:20" +(1 row) + +select json_path_query('"12:34"', '$.datetime("HH24:MI")'); + json_path_query +----------------- + "12:34:00" +(1 row) + +select json_path_query('"12:34"', '$.datetime("HH24:MI TZH")'); +ERROR: missing time-zone in input string for type timetz +select json_path_query('"12:34"', '$.datetime("HH24:MI TZH", "+00")'); + json_path_query +------------------ + "12:34:00+00:00" +(1 row) + +select json_path_query('"12:34 +05"', '$.datetime("HH24:MI TZH")'); + json_path_query +------------------ + "12:34:00+05:00" +(1 row) + +select json_path_query('"12:34 -05"', '$.datetime("HH24:MI TZH")'); + json_path_query +------------------ + "12:34:00-05:00" +(1 row) + +select json_path_query('"12:34 +05:20"', '$.datetime("HH24:MI TZH:TZM")'); + json_path_query +------------------ + "12:34:00+05:20" +(1 row) + +select json_path_query('"12:34 -05:20"', '$.datetime("HH24:MI TZH:TZM")'); + json_path_query +------------------ + "12:34:00-05:20" +(1 row) + +set time zone '+10'; +select json_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy HH24:MI")'); + json_path_query +----------------------- + "2017-03-10T12:34:00" +(1 row) + +select json_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy HH24:MI TZH")'); +ERROR: missing time-zone in input string for type timestamptz +select json_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy HH24:MI TZH", "+10")'); + json_path_query +----------------------------- + "2017-03-10T12:34:00+10:00" +(1 row) + +select json_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy HH24:MI TZH", $tz)', + json_build_object('tz', extract(timezone from now()))); + json_path_query +----------------------------- + "2017-03-10T12:34:00+10:00" +(1 row) + +select json_path_query('"10-03-2017 12:34 +05"', '$.datetime("dd-mm-yyyy HH24:MI TZH")'); + json_path_query +----------------------------- + "2017-03-10T12:34:00+05:00" +(1 row) + +select json_path_query('"10-03-2017 12:34 -05"', '$.datetime("dd-mm-yyyy HH24:MI TZH")'); + json_path_query +----------------------------- + "2017-03-10T12:34:00-05:00" +(1 row) + +select json_path_query('"10-03-2017 12:34 +05:20"', '$.datetime("dd-mm-yyyy HH24:MI TZH:TZM")'); + json_path_query +----------------------------- + "2017-03-10T12:34:00+05:20" +(1 row) + +select json_path_query('"10-03-2017 12:34 -05:20"', '$.datetime("dd-mm-yyyy HH24:MI TZH:TZM")'); + json_path_query +----------------------------- + "2017-03-10T12:34:00-05:20" +(1 row) + +select json_path_query('"12:34"', '$.datetime("HH24:MI")'); + json_path_query +----------------- + "12:34:00" +(1 row) + +select json_path_query('"12:34"', '$.datetime("HH24:MI TZH")'); +ERROR: missing time-zone in input string for type timetz +select json_path_query('"12:34"', '$.datetime("HH24:MI TZH", "+10")'); + json_path_query +------------------ + "12:34:00+10:00" +(1 row) + +select json_path_query('"12:34 +05"', '$.datetime("HH24:MI TZH")'); + json_path_query +------------------ + "12:34:00+05:00" +(1 row) + +select json_path_query('"12:34 -05"', '$.datetime("HH24:MI TZH")'); + json_path_query +------------------ + "12:34:00-05:00" +(1 row) + +select json_path_query('"12:34 +05:20"', '$.datetime("HH24:MI TZH:TZM")'); + json_path_query +------------------ + "12:34:00+05:20" +(1 row) + +select json_path_query('"12:34 -05:20"', '$.datetime("HH24:MI TZH:TZM")'); + json_path_query +------------------ + "12:34:00-05:20" +(1 row) + +set time zone default; +select json_path_query('"2017-03-10"', '$.datetime().type()'); + json_path_query +----------------- + "date" +(1 row) + +select json_path_query('"2017-03-10"', '$.datetime()'); + json_path_query +----------------- + "2017-03-10" +(1 row) + +select json_path_query('"2017-03-10 12:34:56"', '$.datetime().type()'); + json_path_query +------------------------------- + "timestamp without time zone" +(1 row) + +select json_path_query('"2017-03-10 12:34:56"', '$.datetime()'); + json_path_query +----------------------- + "2017-03-10T12:34:56" +(1 row) + +select json_path_query('"2017-03-10 12:34:56 +3"', '$.datetime().type()'); + json_path_query +---------------------------- + "timestamp with time zone" +(1 row) + +select json_path_query('"2017-03-10 12:34:56 +3"', '$.datetime()'); + json_path_query +----------------------------- + "2017-03-10T12:34:56+03:00" +(1 row) + +select json_path_query('"2017-03-10 12:34:56 +3:10"', '$.datetime().type()'); + json_path_query +---------------------------- + "timestamp with time zone" +(1 row) + +select json_path_query('"2017-03-10 12:34:56 +3:10"', '$.datetime()'); + json_path_query +----------------------------- + "2017-03-10T12:34:56+03:10" +(1 row) + +select json_path_query('"12:34:56"', '$.datetime().type()'); + json_path_query +-------------------------- + "time without time zone" +(1 row) + +select json_path_query('"12:34:56"', '$.datetime()'); + json_path_query +----------------- + "12:34:56" +(1 row) + +select json_path_query('"12:34:56 +3"', '$.datetime().type()'); + json_path_query +----------------------- + "time with time zone" +(1 row) + +select json_path_query('"12:34:56 +3"', '$.datetime()'); + json_path_query +------------------ + "12:34:56+03:00" +(1 row) + +select json_path_query('"12:34:56 +3:10"', '$.datetime().type()'); + json_path_query +----------------------- + "time with time zone" +(1 row) + +select json_path_query('"12:34:56 +3:10"', '$.datetime()'); + json_path_query +------------------ + "12:34:56+03:10" +(1 row) + +set time zone '+00'; +-- date comparison +select json_path_query( + '["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"))'); + json_path_query +----------------------- + "2017-03-10" + "2017-03-10T00:00:00" +(2 rows) + +select json_path_query( + '["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"))'); + json_path_query +----------------------- + "2017-03-10" + "2017-03-11" + "2017-03-10T00:00:00" + "2017-03-10T12:34:56" +(4 rows) + +select json_path_query( + '["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"))'); + json_path_query +----------------- + "2017-03-09" +(1 row) + +-- time comparison +select json_path_query( + '["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"))'); + json_path_query +----------------- + "12:35:00" +(1 row) + +select json_path_query( + '["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"))'); + json_path_query +----------------- + "12:35:00" + "12:36:00" +(2 rows) + +select json_path_query( + '["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"))'); + json_path_query +----------------- + "12:34:00" +(1 row) + +-- timetz comparison +select json_path_query( + '["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"))'); + json_path_query +------------------ + "12:35:00+01:00" +(1 row) + +select json_path_query( + '["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"))'); + json_path_query +------------------ + "12:35:00+01:00" + "12:36:00+01:00" + "12:35:00-02:00" +(3 rows) + +select json_path_query( + '["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"))'); + json_path_query +------------------ + "12:34:00+01:00" + "12:35:00+02:00" +(2 rows) + +-- timestamp comparison +select json_path_query( + '["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"))'); + json_path_query +----------------------- + "2017-03-10T12:35:00" +(1 row) + +select json_path_query( + '["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"))'); + json_path_query +----------------------- + "2017-03-10T12:35:00" + "2017-03-10T12:36:00" + "2017-03-11" +(3 rows) + +select json_path_query( + '["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"))'); + json_path_query +----------------------- + "2017-03-10T12:34:00" + "2017-03-10" +(2 rows) + +-- timestamptz comparison +select json_path_query( + '["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"))'); + json_path_query +----------------------------- + "2017-03-10T12:35:00+01:00" +(1 row) + +select json_path_query( + '["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"))'); + json_path_query +----------------------------- + "2017-03-10T12:35:00+01:00" + "2017-03-10T12:36:00+01:00" + "2017-03-10T12:35:00-02:00" +(3 rows) + +select json_path_query( + '["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"))'); + json_path_query +----------------------------- + "2017-03-10T12:34:00+01:00" + "2017-03-10T12:35:00+02:00" +(2 rows) + +set time zone default; +-- jsonpath operators +SELECT json_path_query('[{"a": 1}, {"a": 2}]', '$[*]'); + json_path_query +----------------- + {"a": 1} + {"a": 2} +(2 rows) + +SELECT json_path_query('[{"a": 1}, {"a": 2}]', '$[*] ? (@.a > 10)'); + json_path_query +----------------- +(0 rows) + +SELECT json_path_query_array('[{"a": 1}, {"a": 2}, {}]', 'strict $[*].a'); +ERROR: SQL/JSON member not found +DETAIL: JSON object does not contain key "a" +SELECT json_path_query_array('[{"a": 1}, {"a": 2}]', '$[*].a'); + json_path_query_array +----------------------- + [1, 2] +(1 row) + +SELECT json_path_query_array('[{"a": 1}, {"a": 2}]', '$[*].a ? (@ == 1)'); + json_path_query_array +----------------------- + [1] +(1 row) + +SELECT json_path_query_array('[{"a": 1}, {"a": 2}]', '$[*].a ? (@ > 10)'); + json_path_query_array +----------------------- + [] +(1 row) + +SELECT json_path_query_array('[{"a": 1}, {"a": 2}, {"a": 3}, {"a": 5}]', '$[*].a ? (@ > $min && @ < $max)', '{"min": 1, "max": 4}'); + json_path_query_array +----------------------- + [2, 3] +(1 row) + +SELECT json_path_query_array('[{"a": 1}, {"a": 2}, {"a": 3}, {"a": 5}]', '$[*].a ? (@ > $min && @ < $max)', '{"min": 3, "max": 4}'); + json_path_query_array +----------------------- + [] +(1 row) + +SELECT json_path_query_first('[{"a": 1}, {"a": 2}, {}]', 'strict $[*].a'); +ERROR: SQL/JSON member not found +DETAIL: JSON object does not contain key "a" +SELECT json_path_query_first('[{"a": 1}, {"a": 2}]', '$[*].a'); + json_path_query_first +----------------------- + 1 +(1 row) + +SELECT json_path_query_first('[{"a": 1}, {"a": 2}]', '$[*].a ? (@ == 1)'); + json_path_query_first +----------------------- + 1 +(1 row) + +SELECT json_path_query_first('[{"a": 1}, {"a": 2}]', '$[*].a ? (@ > 10)'); + json_path_query_first +----------------------- + +(1 row) + +SELECT json_path_query_first('[{"a": 1}, {"a": 2}, {"a": 3}, {"a": 5}]', '$[*].a ? (@ > $min && @ < $max)', '{"min": 1, "max": 4}'); + json_path_query_first +----------------------- + 2 +(1 row) + +SELECT json_path_query_first('[{"a": 1}, {"a": 2}, {"a": 3}, {"a": 5}]', '$[*].a ? (@ > $min && @ < $max)', '{"min": 3, "max": 4}'); + json_path_query_first +----------------------- + +(1 row) + +SELECT json '[{"a": 1}, {"a": 2}]' @? '$[*].a ? (@ > 1)'; + ?column? +---------- + t +(1 row) + +SELECT json '[{"a": 1}, {"a": 2}]' @? '$[*] ? (@.a > 2)'; + ?column? +---------- + f +(1 row) + +SELECT json_path_exists('[{"a": 1}, {"a": 2}, {"a": 3}, {"a": 5}]', '$[*] ? (@.a > $min && @.a < $max)', '{"min": 1, "max": 4}'); + json_path_exists +------------------ + t +(1 row) + +SELECT json_path_exists('[{"a": 1}, {"a": 2}, {"a": 3}, {"a": 5}]', '$[*] ? (@.a > $min && @.a < $max)', '{"min": 3, "max": 4}'); + json_path_exists +------------------ + f +(1 row) + +SELECT json '[{"a": 1}, {"a": 2}]' @@ '$[*].a > 1'; + ?column? +---------- + t +(1 row) + +SELECT json '[{"a": 1}, {"a": 2}]' @@ '$[*].a > 2'; + ?column? +---------- + f +(1 row) + diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index c10d909..2f1dd8d 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -115,7 +115,7 @@ test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combo # ---------- # Another group of parallel tests (JSON related) # ---------- -test: json jsonb json_encoding jsonpath jsonb_jsonpath +test: json jsonb json_encoding jsonpath json_jsonpath jsonb_jsonpath # ---------- # Another group of parallel tests diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index d2bf88b..f1fb336 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -158,6 +158,7 @@ test: json test: jsonb test: json_encoding test: jsonpath +test: json_jsonpath test: jsonb_jsonpath test: indirect_toast test: equivclass diff --git a/src/test/regress/sql/json_jsonpath.sql b/src/test/regress/sql/json_jsonpath.sql new file mode 100644 index 0000000..73781b0 --- /dev/null +++ b/src/test/regress/sql/json_jsonpath.sql @@ -0,0 +1,477 @@ +select json '{"a": 12}' @? '$'; +select json '{"a": 12}' @? '1'; +select json '{"a": 12}' @? '$.a.b'; +select json '{"a": 12}' @? '$.b'; +select json '{"a": 12}' @? '$.a + 2'; +select json '{"a": 12}' @? '$.b + 2'; +select json '{"a": {"a": 12}}' @? '$.a.a'; +select json '{"a": {"a": 12}}' @? '$.*.a'; +select json '{"b": {"a": 12}}' @? '$.*.a'; +select json '{"b": {"a": 12}}' @? '$.*.b'; +select json '{"b": {"a": 12}}' @? 'strict $.*.b'; +select json '{}' @? '$.*'; +select json '{"a": 1}' @? '$.*'; +select json '{"a": {"b": 1}}' @? 'lax $.**{1}'; +select json '{"a": {"b": 1}}' @? 'lax $.**{2}'; +select json '{"a": {"b": 1}}' @? 'lax $.**{3}'; +select json '[]' @? '$[*]'; +select json '[1]' @? '$[*]'; +select json '[1]' @? '$[1]'; +select json '[1]' @? 'strict $[1]'; +select json_path_query('[1]', 'strict $[1]'); +select json '[1]' @? 'lax $[10000000000000000]'; +select json '[1]' @? 'strict $[10000000000000000]'; +select json_path_query('[1]', 'lax $[10000000000000000]'); +select json_path_query('[1]', 'strict $[10000000000000000]'); +select json '[1]' @? '$[0]'; +select json '[1]' @? '$[0.3]'; +select json '[1]' @? '$[0.5]'; +select json '[1]' @? '$[0.9]'; +select json '[1]' @? '$[1.2]'; +select json '[1]' @? 'strict $[1.2]'; +select json '{"a": [1,2,3], "b": [3,4,5]}' @? '$ ? (@.a[*] > @.b[*])'; +select json '{"a": [1,2,3], "b": [3,4,5]}' @? '$ ? (@.a[*] >= @.b[*])'; +select json '{"a": [1,2,3], "b": [3,4,"5"]}' @? '$ ? (@.a[*] >= @.b[*])'; +select json '{"a": [1,2,3], "b": [3,4,"5"]}' @? 'strict $ ? (@.a[*] >= @.b[*])'; +select json '{"a": [1,2,3], "b": [3,4,null]}' @? '$ ? (@.a[*] >= @.b[*])'; +select json '1' @? '$ ? ((@ == "1") is unknown)'; +select json '1' @? '$ ? ((@ == 1) is unknown)'; +select json '[{"a": 1}, {"a": 2}]' @? '$[0 to 1] ? (@.a > 1)'; + +select json_path_query('1', 'lax $.a'); +select json_path_query('1', 'strict $.a'); +select json_path_query('1', 'strict $.*'); +select json_path_query('[]', 'lax $.a'); +select json_path_query('[]', 'strict $.a'); +select json_path_query('{}', 'lax $.a'); +select json_path_query('{}', 'strict $.a'); + +select json_path_query('1', 'strict $[1]'); +select json_path_query('1', 'strict $[*]'); +select json_path_query('[]', 'strict $[1]'); +select json_path_query('[]', 'strict $["a"]'); + +select json_path_query('{"a": 12, "b": {"a": 13}}', '$.a'); +select json_path_query('{"a": 12, "b": {"a": 13}}', '$.b'); +select json_path_query('{"a": 12, "b": {"a": 13}}', '$.*'); +select json_path_query('{"a": 12, "b": {"a": 13}}', 'lax $.*.a'); +select json_path_query('[12, {"a": 13}, {"b": 14}]', 'lax $[*].a'); +select json_path_query('[12, {"a": 13}, {"b": 14}]', 'lax $[*].*'); +select json_path_query('[12, {"a": 13}, {"b": 14}]', 'lax $[0].a'); +select json_path_query('[12, {"a": 13}, {"b": 14}]', 'lax $[1].a'); +select json_path_query('[12, {"a": 13}, {"b": 14}]', 'lax $[2].a'); +select json_path_query('[12, {"a": 13}, {"b": 14}]', 'lax $[0,1].a'); +select json_path_query('[12, {"a": 13}, {"b": 14}]', 'lax $[0 to 10].a'); +select json_path_query('[12, {"a": 13}, {"b": 14}]', 'lax $[0 to 10 / 0].a'); +select json_path_query('[12, {"a": 13}, {"b": 14}, "ccc", true]', '$[2.5 - 1 to $.size() - 2]'); +select json_path_query('1', 'lax $[0]'); +select json_path_query('1', 'lax $[*]'); +select json_path_query('[1]', 'lax $[0]'); +select json_path_query('[1]', 'lax $[*]'); +select json_path_query('[1,2,3]', 'lax $[*]'); +select json_path_query('[1,2,3]', 'strict $[*].a'); +select json_path_query('[]', '$[last]'); +select json_path_query('[]', '$[last ? (exists(last))]'); +select json_path_query('[]', 'strict $[last]'); +select json_path_query('[1]', '$[last]'); +select json_path_query('[1,2,3]', '$[last]'); +select json_path_query('[1,2,3]', '$[last - 1]'); +select json_path_query('[1,2,3]', '$[last ? (@.type() == "number")]'); +select json_path_query('[1,2,3]', '$[last ? (@.type() == "string")]'); + +select * from json_path_query('{"a": 10}', '$'); +select * from json_path_query('{"a": 10}', '$ ? (@.a < $value)'); +select * from json_path_query('{"a": 10}', '$ ? (@.a < $value)', '1'); +select * from json_path_query('{"a": 10}', '$ ? (@.a < $value)', '[{"value" : 13}]'); +select * from json_path_query('{"a": 10}', '$ ? (@.a < $value)', '{"value" : 13}'); +select * from json_path_query('{"a": 10}', '$ ? (@.a < $value)', '{"value" : 8}'); +select * from json_path_query('{"a": 10}', '$.a ? (@ < $value)', '{"value" : 13}'); +select * from json_path_query('[10,11,12,13,14,15]', '$[*] ? (@ < $value)', '{"value" : 13}'); +select * from json_path_query('[10,11,12,13,14,15]', '$[0,1] ? (@ < $x.value)', '{"x": {"value" : 13}}'); +select * from json_path_query('[10,11,12,13,14,15]', '$[0 to 2] ? (@ < $value)', '{"value" : 15}'); +select * from json_path_query('[1,"1",2,"2",null]', '$[*] ? (@ == "1")'); +select * from json_path_query('[1,"1",2,"2",null]', '$[*] ? (@ == $value)', '{"value" : "1"}'); +select * from json_path_query('[1,"1",2,"2",null]', '$[*] ? (@ == $value)', '{"value" : null}'); +select * from json_path_query('[1, "2", null]', '$[*] ? (@ != null)'); +select * from json_path_query('[1, "2", null]', '$[*] ? (@ == null)'); +select * from json_path_query('{}', '$ ? (@ == @)'); +select * from json_path_query('[]', 'strict $ ? (@ == @)'); + +select json_path_query('{"a": {"b": 1}}', 'lax $.**'); +select json_path_query('{"a": {"b": 1}}', 'lax $.**{0}'); +select json_path_query('{"a": {"b": 1}}', 'lax $.**{0 to last}'); +select json_path_query('{"a": {"b": 1}}', 'lax $.**{1}'); +select json_path_query('{"a": {"b": 1}}', 'lax $.**{1 to last}'); +select json_path_query('{"a": {"b": 1}}', 'lax $.**{2}'); +select json_path_query('{"a": {"b": 1}}', 'lax $.**{2 to last}'); +select json_path_query('{"a": {"b": 1}}', 'lax $.**{3 to last}'); +select json_path_query('{"a": {"b": 1}}', 'lax $.**{last}'); +select json_path_query('{"a": {"b": 1}}', 'lax $.**.b ? (@ > 0)'); +select json_path_query('{"a": {"b": 1}}', 'lax $.**{0}.b ? (@ > 0)'); +select json_path_query('{"a": {"b": 1}}', 'lax $.**{1}.b ? (@ > 0)'); +select json_path_query('{"a": {"b": 1}}', 'lax $.**{0 to last}.b ? (@ > 0)'); +select json_path_query('{"a": {"b": 1}}', 'lax $.**{1 to last}.b ? (@ > 0)'); +select json_path_query('{"a": {"b": 1}}', 'lax $.**{1 to 2}.b ? (@ > 0)'); +select json_path_query('{"a": {"c": {"b": 1}}}', 'lax $.**.b ? (@ > 0)'); +select json_path_query('{"a": {"c": {"b": 1}}}', 'lax $.**{0}.b ? (@ > 0)'); +select json_path_query('{"a": {"c": {"b": 1}}}', 'lax $.**{1}.b ? (@ > 0)'); +select json_path_query('{"a": {"c": {"b": 1}}}', 'lax $.**{0 to last}.b ? (@ > 0)'); +select json_path_query('{"a": {"c": {"b": 1}}}', 'lax $.**{1 to last}.b ? (@ > 0)'); +select json_path_query('{"a": {"c": {"b": 1}}}', 'lax $.**{1 to 2}.b ? (@ > 0)'); +select json_path_query('{"a": {"c": {"b": 1}}}', 'lax $.**{2 to 3}.b ? (@ > 0)'); + +select json '{"a": {"b": 1}}' @? '$.**.b ? ( @ > 0)'; +select json '{"a": {"b": 1}}' @? '$.**{0}.b ? ( @ > 0)'; +select json '{"a": {"b": 1}}' @? '$.**{1}.b ? ( @ > 0)'; +select json '{"a": {"b": 1}}' @? '$.**{0 to last}.b ? ( @ > 0)'; +select json '{"a": {"b": 1}}' @? '$.**{1 to last}.b ? ( @ > 0)'; +select json '{"a": {"b": 1}}' @? '$.**{1 to 2}.b ? ( @ > 0)'; +select json '{"a": {"c": {"b": 1}}}' @? '$.**.b ? ( @ > 0)'; +select json '{"a": {"c": {"b": 1}}}' @? '$.**{0}.b ? ( @ > 0)'; +select json '{"a": {"c": {"b": 1}}}' @? '$.**{1}.b ? ( @ > 0)'; +select json '{"a": {"c": {"b": 1}}}' @? '$.**{0 to last}.b ? ( @ > 0)'; +select json '{"a": {"c": {"b": 1}}}' @? '$.**{1 to last}.b ? ( @ > 0)'; +select json '{"a": {"c": {"b": 1}}}' @? '$.**{1 to 2}.b ? ( @ > 0)'; +select json '{"a": {"c": {"b": 1}}}' @? '$.**{2 to 3}.b ? ( @ > 0)'; + +select json_path_query('{"g": {"x": 2}}', '$.g ? (exists (@.x))'); +select json_path_query('{"g": {"x": 2}}', '$.g ? (exists (@.y))'); +select json_path_query('{"g": {"x": 2}}', '$.g ? (exists (@.x ? (@ >= 2) ))'); +select json_path_query('{"g": [{"x": 2}, {"y": 3}]}', 'lax $.g ? (exists (@.x))'); +select json_path_query('{"g": [{"x": 2}, {"y": 3}]}', 'lax $.g ? (exists (@.x + "3"))'); +select json_path_query('{"g": [{"x": 2}, {"y": 3}]}', 'lax $.g ? ((exists (@.x + "3")) is unknown)'); +select json_path_query('{"g": [{"x": 2}, {"y": 3}]}', 'strict $.g[*] ? (exists (@.x))'); +select json_path_query('{"g": [{"x": 2}, {"y": 3}]}', 'strict $.g[*] ? ((exists (@.x)) is unknown)'); +select json_path_query('{"g": [{"x": 2}, {"y": 3}]}', 'strict $.g ? (exists (@[*].x))'); +select json_path_query('{"g": [{"x": 2}, {"y": 3}]}', 'strict $.g ? ((exists (@[*].x)) is unknown)'); + +--test ternary logic +select + x, y, + json_path_query( + '[true, false, null]', + '$[*] ? (@ == true && ($x == true && $y == true) || + @ == false && !($x == true && $y == true) || + @ == null && ($x == true && $y == true) is unknown)', + json_build_object('x', x, 'y', y) + ) as "x && y" +from + (values (json 'true'), ('false'), ('"null"')) x(x), + (values (json 'true'), ('false'), ('"null"')) y(y); + +select + x, y, + json_path_query( + '[true, false, null]', + '$[*] ? (@ == true && ($x == true || $y == true) || + @ == false && !($x == true || $y == true) || + @ == null && ($x == true || $y == true) is unknown)', + json_build_object('x', x, 'y', y) + ) as "x || y" +from + (values (json 'true'), ('false'), ('"null"')) x(x), + (values (json 'true'), ('false'), ('"null"')) y(y); + +select json '{"a": 1, "b":1}' @? '$ ? (@.a == @.b)'; +select json '{"c": {"a": 1, "b":1}}' @? '$ ? (@.a == @.b)'; +select json '{"c": {"a": 1, "b":1}}' @? '$.c ? (@.a == @.b)'; +select json '{"c": {"a": 1, "b":1}}' @? '$.c ? ($.c.a == @.b)'; +select json '{"c": {"a": 1, "b":1}}' @? '$.* ? (@.a == @.b)'; +select json '{"a": 1, "b":1}' @? '$.** ? (@.a == @.b)'; +select json '{"c": {"a": 1, "b":1}}' @? '$.** ? (@.a == @.b)'; + +select json_path_query('{"c": {"a": 2, "b":1}}', '$.** ? (@.a == 1 + 1)'); +select json_path_query('{"c": {"a": 2, "b":1}}', '$.** ? (@.a == (1 + 1))'); +select json_path_query('{"c": {"a": 2, "b":1}}', '$.** ? (@.a == @.b + 1)'); +select json_path_query('{"c": {"a": 2, "b":1}}', '$.** ? (@.a == (@.b + 1))'); +select json '{"c": {"a": -1, "b":1}}' @? '$.** ? (@.a == - 1)'; +select json '{"c": {"a": -1, "b":1}}' @? '$.** ? (@.a == -1)'; +select json '{"c": {"a": -1, "b":1}}' @? '$.** ? (@.a == -@.b)'; +select json '{"c": {"a": -1, "b":1}}' @? '$.** ? (@.a == - @.b)'; +select json '{"c": {"a": 0, "b":1}}' @? '$.** ? (@.a == 1 - @.b)'; +select json '{"c": {"a": 2, "b":1}}' @? '$.** ? (@.a == 1 - - @.b)'; +select json '{"c": {"a": 0, "b":1}}' @? '$.** ? (@.a == 1 - +@.b)'; +select json '[1,2,3]' @? '$ ? (+@[*] > +2)'; +select json '[1,2,3]' @? '$ ? (+@[*] > +3)'; +select json '[1,2,3]' @? '$ ? (-@[*] < -2)'; +select json '[1,2,3]' @? '$ ? (-@[*] < -3)'; +select json '1' @? '$ ? ($ > 0)'; + +-- arithmetic errors +select json_path_query('[1,2,0,3]', '$[*] ? (2 / @ > 0)'); +select json_path_query('[1,2,0,3]', '$[*] ? ((2 / @ > 0) is unknown)'); +select json_path_query('0', '1 / $'); +select json_path_query('0', '1 / $ + 2'); +select json_path_query('0', '-(3 + 1 % $)'); +select json_path_query('1', '$ + "2"'); +select json_path_query('[1, 2]', '3 * $'); +select json_path_query('"a"', '-$'); +select json_path_query('[1,"2",3]', '+$'); +select json '["1",2,0,3]' @? '-$[*]'; +select json '[1,"2",0,3]' @? '-$[*]'; +select json '["1",2,0,3]' @? 'strict -$[*]'; +select json '[1,"2",0,3]' @? 'strict -$[*]'; + +-- unwrapping of operator arguments in lax mode +select json_path_query('{"a": [2]}', 'lax $.a * 3'); +select json_path_query('{"a": [2]}', 'lax $.a + 3'); +select json_path_query('{"a": [2, 3, 4]}', 'lax -$.a'); +-- should fail +select json_path_query('{"a": [1, 2]}', 'lax $.a * 3'); + +-- extension: boolean expressions +select json_path_query('2', '$ > 1'); +select json_path_query('2', '$ <= 1'); +select json_path_query('2', '$ == "2"'); +select json '2' @? '$ == "2"'; + +select json '2' @@ '$ > 1'; +select json '2' @@ '$ <= 1'; +select json '2' @@ '$ == "2"'; +select json '2' @@ '1'; +select json '{}' @@ '$'; +select json '[]' @@ '$'; +select json '[1,2,3]' @@ '$[*]'; +select json '[]' @@ '$[*]'; +select json_path_match('[[1, true], [2, false]]', 'strict $[*] ? (@[0] > $x) [1]', '{"x": 1}'); +select json_path_match('[[1, true], [2, false]]', 'strict $[*] ? (@[0] < $x) [1]', '{"x": 2}'); + +select json_path_query('[null,1,true,"a",[],{}]', '$.type()'); +select json_path_query('[null,1,true,"a",[],{}]', 'lax $.type()'); +select json_path_query('[null,1,true,"a",[],{}]', '$[*].type()'); +select json_path_query('null', 'null.type()'); +select json_path_query('null', 'true.type()'); +select json_path_query('null', '123.type()'); +select json_path_query('null', '"123".type()'); + +select json_path_query('{"a": 2}', '($.a - 5).abs() + 10'); +select json_path_query('{"a": 2.5}', '-($.a * $.a).floor() % 4.3'); +select json_path_query('[1, 2, 3]', '($[*] > 2) ? (@ == true)'); +select json_path_query('[1, 2, 3]', '($[*] > 3).type()'); +select json_path_query('[1, 2, 3]', '($[*].a > 3).type()'); +select json_path_query('[1, 2, 3]', 'strict ($[*].a > 3).type()'); + +select json_path_query('[1,null,true,"11",[],[1],[1,2,3],{},{"a":1,"b":2}]', 'strict $[*].size()'); +select json_path_query('[1,null,true,"11",[],[1],[1,2,3],{},{"a":1,"b":2}]', 'lax $[*].size()'); + +select json_path_query('[0, 1, -2, -3.4, 5.6]', '$[*].abs()'); +select json_path_query('[0, 1, -2, -3.4, 5.6]', '$[*].floor()'); +select json_path_query('[0, 1, -2, -3.4, 5.6]', '$[*].ceiling()'); +select json_path_query('[0, 1, -2, -3.4, 5.6]', '$[*].ceiling().abs()'); +select json_path_query('[0, 1, -2, -3.4, 5.6]', '$[*].ceiling().abs().type()'); + +select json_path_query('[{},1]', '$[*].keyvalue()'); +select json_path_query('{}', '$.keyvalue()'); +select json_path_query('{"a": 1, "b": [1, 2], "c": {"a": "bbb"}}', '$.keyvalue()'); +select json_path_query('[{"a": 1, "b": [1, 2]}, {"c": {"a": "bbb"}}]', '$[*].keyvalue()'); +select json_path_query('[{"a": 1, "b": [1, 2]}, {"c": {"a": "bbb"}}]', 'strict $.keyvalue()'); +select json_path_query('[{"a": 1, "b": [1, 2]}, {"c": {"a": "bbb"}}]', 'lax $.keyvalue()'); +select json_path_query('[{"a": 1, "b": [1, 2]}, {"c": {"a": "bbb"}}]', 'strict $.keyvalue().a'); +select json '{"a": 1, "b": [1, 2]}' @? 'lax $.keyvalue()'; +select json '{"a": 1, "b": [1, 2]}' @? 'lax $.keyvalue().key'; + +select json_path_query('null', '$.double()'); +select json_path_query('true', '$.double()'); +select json_path_query('[]', '$.double()'); +select json_path_query('[]', 'strict $.double()'); +select json_path_query('{}', '$.double()'); +select json_path_query('1.23', '$.double()'); +select json_path_query('"1.23"', '$.double()'); +select json_path_query('"1.23aaa"', '$.double()'); +select json_path_query('"nan"', '$.double()'); +select json_path_query('"NaN"', '$.double()'); +select json_path_query('"inf"', '$.double()'); +select json_path_query('"-inf"', '$.double()'); + +select json_path_query('{}', '$.abs()'); +select json_path_query('true', '$.floor()'); +select json_path_query('"1.2"', '$.ceiling()'); + +select json_path_query('["", "a", "abc", "abcabc"]', '$[*] ? (@ starts with "abc")'); +select json_path_query('["", "a", "abc", "abcabc"]', 'strict $ ? (@[*] starts with "abc")'); +select json_path_query('["", "a", "abd", "abdabc"]', 'strict $ ? (@[*] starts with "abc")'); +select json_path_query('["abc", "abcabc", null, 1]', 'strict $ ? (@[*] starts with "abc")'); +select json_path_query('["abc", "abcabc", null, 1]', 'strict $ ? ((@[*] starts with "abc") is unknown)'); +select json_path_query('[[null, 1, "abc", "abcabc"]]', 'lax $ ? (@[*] starts with "abc")'); +select json_path_query('[[null, 1, "abd", "abdabc"]]', 'lax $ ? ((@[*] starts with "abc") is unknown)'); +select json_path_query('[null, 1, "abd", "abdabc"]', 'lax $[*] ? ((@ starts with "abc") is unknown)'); + +select json_path_query('[null, 1, "abc", "abd", "aBdC", "abdacb", "adc\nabc", "babc"]', 'lax $[*] ? (@ like_regex "^ab.*c")'); +select json_path_query('[null, 1, "abc", "abd", "aBdC", "abdacb", "adc\nabc", "babc"]', 'lax $[*] ? (@ like_regex "^a b.* c " flag "ix")'); +select json_path_query('[null, 1, "abc", "abd", "aBdC", "abdacb", "adc\nabc", "babc"]', 'lax $[*] ? (@ like_regex "^ab.*c" flag "m")'); +select json_path_query('[null, 1, "abc", "abd", "aBdC", "abdacb", "adc\nabc", "babc"]', 'lax $[*] ? (@ like_regex "^ab.*c" flag "s")'); + +select json_path_query('null', '$.datetime()'); +select json_path_query('true', '$.datetime()'); +select json_path_query('1', '$.datetime()'); +select json_path_query('[]', '$.datetime()'); +select json_path_query('[]', 'strict $.datetime()'); +select json_path_query('{}', '$.datetime()'); +select json_path_query('""', '$.datetime()'); +select json_path_query('"12:34"', '$.datetime("aaa")'); +select json_path_query('"12:34"', '$.datetime("aaa", 1)'); +select json_path_query('"12:34"', '$.datetime("HH24:MI TZH", 1)'); +select json_path_query('"12:34"', '$.datetime("HH24:MI TZH", 10000000000000)'); +select json_path_query('"12:34"', '$.datetime("HH24:MI TZH", 10000000000000)'); +select json_path_query('"12:34"', '$.datetime("HH24:MI TZH", 2147483647)'); +select json_path_query('"12:34"', '$.datetime("HH24:MI TZH", 2147483648)'); +select json_path_query('"12:34"', '$.datetime("HH24:MI TZH", -2147483647)'); +select json_path_query('"12:34"', '$.datetime("HH24:MI TZH", -2147483648)'); +select json_path_query('"aaaa"', '$.datetime("HH24")'); + +select json '"10-03-2017"' @? '$.datetime("dd-mm-yyyy")'; +select json_path_query('"10-03-2017"', '$.datetime("dd-mm-yyyy")'); +select json_path_query('"10-03-2017"', '$.datetime("dd-mm-yyyy").type()'); +select json_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy")'); +select json_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy").type()'); + +select json_path_query('"10-03-2017 12:34"', ' $.datetime("dd-mm-yyyy HH24:MI").type()'); +select json_path_query('"10-03-2017 12:34 +05:20"', '$.datetime("dd-mm-yyyy HH24:MI TZH:TZM").type()'); +select json_path_query('"12:34:56"', '$.datetime("HH24:MI:SS").type()'); +select json_path_query('"12:34:56 +05:20"', '$.datetime("HH24:MI:SS TZH:TZM").type()'); + +set time zone '+00'; + +select json_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy HH24:MI")'); +select json_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy HH24:MI TZH")'); +select json_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy HH24:MI TZH", "+00")'); +select json_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy HH24:MI TZH", "+00:12")'); +select json_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy HH24:MI TZH", "-00:12:34")'); +select json_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy HH24:MI TZH", "UTC")'); +select json_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy HH24:MI TZH", 12 * 60)'); +select json_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy HH24:MI TZH", -(5 * 3600 + 12 * 60 + 34))'); +select json_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy HH24:MI TZH", $tz)', + json_build_object('tz', extract(timezone from now()))); +select json_path_query('"10-03-2017 12:34 +05"', '$.datetime("dd-mm-yyyy HH24:MI TZH")'); +select json_path_query('"10-03-2017 12:34 -05"', '$.datetime("dd-mm-yyyy HH24:MI TZH")'); +select json_path_query('"10-03-2017 12:34 +05:20"', '$.datetime("dd-mm-yyyy HH24:MI TZH:TZM")'); +select json_path_query('"10-03-2017 12:34 -05:20"', '$.datetime("dd-mm-yyyy HH24:MI TZH:TZM")'); +select json_path_query('"12:34"', '$.datetime("HH24:MI")'); +select json_path_query('"12:34"', '$.datetime("HH24:MI TZH")'); +select json_path_query('"12:34"', '$.datetime("HH24:MI TZH", "+00")'); +select json_path_query('"12:34 +05"', '$.datetime("HH24:MI TZH")'); +select json_path_query('"12:34 -05"', '$.datetime("HH24:MI TZH")'); +select json_path_query('"12:34 +05:20"', '$.datetime("HH24:MI TZH:TZM")'); +select json_path_query('"12:34 -05:20"', '$.datetime("HH24:MI TZH:TZM")'); + +set time zone '+10'; + +select json_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy HH24:MI")'); +select json_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy HH24:MI TZH")'); +select json_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy HH24:MI TZH", "+10")'); +select json_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy HH24:MI TZH", $tz)', + json_build_object('tz', extract(timezone from now()))); +select json_path_query('"10-03-2017 12:34 +05"', '$.datetime("dd-mm-yyyy HH24:MI TZH")'); +select json_path_query('"10-03-2017 12:34 -05"', '$.datetime("dd-mm-yyyy HH24:MI TZH")'); +select json_path_query('"10-03-2017 12:34 +05:20"', '$.datetime("dd-mm-yyyy HH24:MI TZH:TZM")'); +select json_path_query('"10-03-2017 12:34 -05:20"', '$.datetime("dd-mm-yyyy HH24:MI TZH:TZM")'); +select json_path_query('"12:34"', '$.datetime("HH24:MI")'); +select json_path_query('"12:34"', '$.datetime("HH24:MI TZH")'); +select json_path_query('"12:34"', '$.datetime("HH24:MI TZH", "+10")'); +select json_path_query('"12:34 +05"', '$.datetime("HH24:MI TZH")'); +select json_path_query('"12:34 -05"', '$.datetime("HH24:MI TZH")'); +select json_path_query('"12:34 +05:20"', '$.datetime("HH24:MI TZH:TZM")'); +select json_path_query('"12:34 -05:20"', '$.datetime("HH24:MI TZH:TZM")'); + +set time zone default; + +select json_path_query('"2017-03-10"', '$.datetime().type()'); +select json_path_query('"2017-03-10"', '$.datetime()'); +select json_path_query('"2017-03-10 12:34:56"', '$.datetime().type()'); +select json_path_query('"2017-03-10 12:34:56"', '$.datetime()'); +select json_path_query('"2017-03-10 12:34:56 +3"', '$.datetime().type()'); +select json_path_query('"2017-03-10 12:34:56 +3"', '$.datetime()'); +select json_path_query('"2017-03-10 12:34:56 +3:10"', '$.datetime().type()'); +select json_path_query('"2017-03-10 12:34:56 +3:10"', '$.datetime()'); +select json_path_query('"12:34:56"', '$.datetime().type()'); +select json_path_query('"12:34:56"', '$.datetime()'); +select json_path_query('"12:34:56 +3"', '$.datetime().type()'); +select json_path_query('"12:34:56 +3"', '$.datetime()'); +select json_path_query('"12:34:56 +3:10"', '$.datetime().type()'); +select json_path_query('"12:34:56 +3:10"', '$.datetime()'); + +set time zone '+00'; + +-- date comparison +select json_path_query( + '["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 json_path_query( + '["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 json_path_query( + '["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 json_path_query( + '["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 json_path_query( + '["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 json_path_query( + '["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 json_path_query( + '["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 json_path_query( + '["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 json_path_query( + '["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 json_path_query( + '["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 json_path_query( + '["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 json_path_query( + '["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 json_path_query( + '["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 json_path_query( + '["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 json_path_query( + '["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 json_path_query('[{"a": 1}, {"a": 2}]', '$[*]'); +SELECT json_path_query('[{"a": 1}, {"a": 2}]', '$[*] ? (@.a > 10)'); + +SELECT json_path_query_array('[{"a": 1}, {"a": 2}, {}]', 'strict $[*].a'); +SELECT json_path_query_array('[{"a": 1}, {"a": 2}]', '$[*].a'); +SELECT json_path_query_array('[{"a": 1}, {"a": 2}]', '$[*].a ? (@ == 1)'); +SELECT json_path_query_array('[{"a": 1}, {"a": 2}]', '$[*].a ? (@ > 10)'); +SELECT json_path_query_array('[{"a": 1}, {"a": 2}, {"a": 3}, {"a": 5}]', '$[*].a ? (@ > $min && @ < $max)', '{"min": 1, "max": 4}'); +SELECT json_path_query_array('[{"a": 1}, {"a": 2}, {"a": 3}, {"a": 5}]', '$[*].a ? (@ > $min && @ < $max)', '{"min": 3, "max": 4}'); + +SELECT json_path_query_first('[{"a": 1}, {"a": 2}, {}]', 'strict $[*].a'); +SELECT json_path_query_first('[{"a": 1}, {"a": 2}]', '$[*].a'); +SELECT json_path_query_first('[{"a": 1}, {"a": 2}]', '$[*].a ? (@ == 1)'); +SELECT json_path_query_first('[{"a": 1}, {"a": 2}]', '$[*].a ? (@ > 10)'); +SELECT json_path_query_first('[{"a": 1}, {"a": 2}, {"a": 3}, {"a": 5}]', '$[*].a ? (@ > $min && @ < $max)', '{"min": 1, "max": 4}'); +SELECT json_path_query_first('[{"a": 1}, {"a": 2}, {"a": 3}, {"a": 5}]', '$[*].a ? (@ > $min && @ < $max)', '{"min": 3, "max": 4}'); + +SELECT json '[{"a": 1}, {"a": 2}]' @? '$[*].a ? (@ > 1)'; +SELECT json '[{"a": 1}, {"a": 2}]' @? '$[*] ? (@.a > 2)'; +SELECT json_path_exists('[{"a": 1}, {"a": 2}, {"a": 3}, {"a": 5}]', '$[*] ? (@.a > $min && @.a < $max)', '{"min": 1, "max": 4}'); +SELECT json_path_exists('[{"a": 1}, {"a": 2}, {"a": 3}, {"a": 5}]', '$[*] ? (@.a > $min && @.a < $max)', '{"min": 3, "max": 4}'); + +SELECT json '[{"a": 1}, {"a": 2}]' @@ '$[*].a > 1'; +SELECT json '[{"a": 1}, {"a": 2}]' @@ '$[*].a > 2'; diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index daaafa3..5a1518e 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -1097,6 +1097,7 @@ JsValue JsonAggState JsonBaseObjectInfo JsonHashEntry +JsonItem JsonItemStack JsonItemStackEntry JsonIterateStringValuesAction @@ -1112,6 +1113,7 @@ JsonPathItemType JsonPathParseItem JsonPathParseResult JsonPathPredicateCallback +JsonPathUserFuncContext JsonPathVariable JsonPathVariable_cb JsonSemAction -- 2.7.4 --------------FD6F0D8EDD0CFB174B99F31A Content-Type: text/x-patch; name="0005-Jsonpath-GIN-support-v34.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="0005-Jsonpath-GIN-support-v34.patch"