From: Nikita Glukhov Date: Tue, 6 Nov 2018 16:33:13 +0300 Subject: [PATCH 3/4] Jsonpath GIN support --- src/backend/utils/adt/jsonb_gin.c | 765 ++++++++++++++++++++++++++++--- src/include/catalog/pg_amop.dat | 12 + src/include/utils/jsonb.h | 3 + src/include/utils/jsonpath.h | 2 + src/test/regress/expected/jsonb.out | 453 ++++++++++++++++++ src/test/regress/expected/opr_sanity.out | 4 +- src/test/regress/sql/jsonb.sql | 79 ++++ 7 files changed, 1242 insertions(+), 76 deletions(-) diff --git a/src/backend/utils/adt/jsonb_gin.c b/src/backend/utils/adt/jsonb_gin.c index c8a2745..c11960c 100644 --- a/src/backend/utils/adt/jsonb_gin.c +++ b/src/backend/utils/adt/jsonb_gin.c @@ -13,6 +13,7 @@ */ #include "postgres.h" +#include "miscadmin.h" #include "access/gin.h" #include "access/hash.h" #include "access/stratnum.h" @@ -20,6 +21,7 @@ #include "catalog/pg_type.h" #include "utils/builtins.h" #include "utils/jsonb.h" +#include "utils/jsonpath.h" #include "utils/varlena.h" typedef struct PathHashStack @@ -28,9 +30,140 @@ typedef struct PathHashStack struct PathHashStack *parent; } PathHashStack; +typedef enum { eOr, eAnd, eEntry } JsonPathNodeType; + +typedef struct JsonPathNode +{ + JsonPathNodeType type; + union + { + int nargs; + int entryIndex; + Datum entryDatum; + } val; + struct JsonPathNode *args[FLEXIBLE_ARRAY_MEMBER]; +} JsonPathNode; + +typedef struct GinEntries +{ + Datum *buf; + int count; + int allocated; +} GinEntries; + +typedef struct ExtractedPathEntry +{ + struct ExtractedPathEntry *parent; + Datum entry; + JsonPathItemType type; +} ExtractedPathEntry; + +typedef union ExtractedJsonPath +{ + ExtractedPathEntry *entries; + uint32 hash; +} ExtractedJsonPath; + +typedef struct JsonPathExtractionContext +{ + ExtractedJsonPath (*addKey)(ExtractedJsonPath path, char *key, int len); + JsonPath *indexedPaths; + bool pathOps; + bool lax; +} JsonPathExtractionContext; + + static Datum make_text_key(char flag, const char *str, int len); static Datum make_scalar_key(const JsonbValue *scalarVal, bool is_key); +static JsonPathNode *gin_extract_jsonpath_expr(JsonPathExtractionContext *cxt, + JsonPathItem *jsp, ExtractedJsonPath path, bool not); + + +static void +gin_entries_init(GinEntries *list, int preallocated) +{ + list->allocated = preallocated; + list->buf = (Datum *) palloc(sizeof(Datum) * list->allocated); + list->count = 0; +} + +static int +gin_entries_add(GinEntries *list, Datum entry) +{ + int id = list->count; + + if (list->count >= list->allocated) + { + + if (list->allocated) + { + list->allocated *= 2; + list->buf = (Datum *) repalloc(list->buf, + sizeof(Datum) * list->allocated); + } + else + { + list->allocated = 8; + list->buf = (Datum *) palloc(sizeof(Datum) * list->allocated); + } + } + + list->buf[list->count++] = entry; + + return id; +} + +/* Append key name to a path. */ +static ExtractedJsonPath +gin_jsonb_ops_add_key(ExtractedJsonPath path, char *key, int len) +{ + ExtractedPathEntry *pentry = palloc(sizeof(*pentry)); + + pentry->parent = path.entries; + + if (key) + { + pentry->entry = make_text_key(JGINFLAG_KEY, key, len); + pentry->type = jpiKey; + } + else + { + pentry->entry = PointerGetDatum(NULL); + pentry->type = len; + } + + path.entries = pentry; + + return path; +} + +/* Combine existing path hash with next key hash. */ +static ExtractedJsonPath +gin_jsonb_path_ops_add_key(ExtractedJsonPath path, char *key, int len) +{ + if (key) + { + JsonbValue jbv; + + jbv.type = jbvString; + jbv.val.string.val = key; + jbv.val.string.len = len; + + JsonbHashScalarValue(&jbv, &path.hash); + } + + return path; +} + +static void +gin_jsonpath_init_context(JsonPathExtractionContext *cxt, bool pathOps, bool lax) +{ + cxt->addKey = pathOps ? gin_jsonb_path_ops_add_key : gin_jsonb_ops_add_key; + cxt->pathOps = pathOps; + cxt->lax = lax; +} + /* * * jsonb_ops GIN opclass support functions @@ -68,12 +201,11 @@ gin_extract_jsonb(PG_FUNCTION_ARGS) { Jsonb *jb = (Jsonb *) PG_GETARG_JSONB_P(0); int32 *nentries = (int32 *) PG_GETARG_POINTER(1); - int total = 2 * JB_ROOT_COUNT(jb); + int total = JB_ROOT_COUNT(jb); JsonbIterator *it; JsonbValue v; JsonbIteratorToken r; - int i = 0; - Datum *entries; + GinEntries entries; /* If the root level is empty, we certainly have no keys */ if (total == 0) @@ -83,30 +215,23 @@ gin_extract_jsonb(PG_FUNCTION_ARGS) } /* Otherwise, use 2 * root count as initial estimate of result size */ - entries = (Datum *) palloc(sizeof(Datum) * total); + gin_entries_init(&entries, 2 * total); it = JsonbIteratorInit(&jb->root); while ((r = JsonbIteratorNext(&it, &v, false)) != WJB_DONE) { - /* Since we recurse into the object, we might need more space */ - if (i >= total) - { - total *= 2; - entries = (Datum *) repalloc(entries, sizeof(Datum) * total); - } - switch (r) { case WJB_KEY: - entries[i++] = make_scalar_key(&v, true); + gin_entries_add(&entries, make_scalar_key(&v, true)); break; case WJB_ELEM: /* Pretend string array elements are keys, see jsonb.h */ - entries[i++] = make_scalar_key(&v, (v.type == jbvString)); + gin_entries_add(&entries, make_scalar_key(&v, v.type == jbvString)); break; case WJB_VALUE: - entries[i++] = make_scalar_key(&v, false); + gin_entries_add(&entries, make_scalar_key(&v, false)); break; default: /* we can ignore structural items */ @@ -114,9 +239,447 @@ gin_extract_jsonb(PG_FUNCTION_ARGS) } } - *nentries = i; + *nentries = entries.count; - PG_RETURN_POINTER(entries); + PG_RETURN_POINTER(entries.buf); +} + + +/* + * Extract JSON path into the 'path' with filters. + * Returns true iff this path is supported by the index opclass. + */ +static bool +gin_extract_jsonpath_path(JsonPathExtractionContext *cxt, JsonPathItem *jsp, + ExtractedJsonPath *path, List **filters) +{ + JsonPathItem next; + + for (;;) + { + switch (jsp->type) + { + case jpiRoot: + path->entries = NULL; /* reset path */ + break; + + case jpiCurrent: + break; + + case jpiKey: + { + int keylen; + char *key = jspGetString(jsp, &keylen); + + *path = cxt->addKey(*path, key, keylen); + break; + } + + case jpiIndexArray: + case jpiAnyArray: + *path = cxt->addKey(*path, NULL, jsp->type); + break; + + case jpiAny: + case jpiAnyKey: + if (cxt->pathOps) + /* jsonb_path_ops doesn't support wildcard paths */ + return false; + + *path = cxt->addKey(*path, NULL, jsp->type); + break; + + case jpiFilter: + { + JsonPathItem arg; + JsonPathNode *filter; + + jspGetArg(jsp, &arg); + + filter = gin_extract_jsonpath_expr(cxt, &arg, *path, false); + + if (filter) + *filters = lappend(*filters, filter); + + break; + } + + default: + /* other path items (like item methods) are not supported */ + return false; + } + + if (!jspGetNext(jsp, &next)) + break; + + jsp = &next; + } + + return true; +} + +/* Append an entry node to the global entry list. */ +static inline JsonPathNode * +gin_jsonpath_make_entry_node(Datum entry) +{ + JsonPathNode *node = palloc(offsetof(JsonPathNode, args)); + + node->type = eEntry; + node->val.entryDatum = entry; + + return node; +} + +static inline JsonPathNode * +gin_jsonpath_make_entry_node_scalar(JsonbValue *scalar, bool iskey) +{ + return gin_jsonpath_make_entry_node(make_scalar_key(scalar, iskey)); +} + +static inline JsonPathNode * +gin_jsonpath_make_expr_node(JsonPathNodeType type, int nargs) +{ + JsonPathNode *node = palloc(offsetof(JsonPathNode, args) + + sizeof(node->args[0]) * nargs); + + node->type = type; + node->val.nargs = nargs; + + return node; +} + +static inline JsonPathNode * +gin_jsonpath_make_expr_node_args(JsonPathNodeType type, List *args) +{ + JsonPathNode *node = gin_jsonpath_make_expr_node(type, list_length(args)); + ListCell *lc; + int i = 0; + + foreach(lc, args) + node->args[i++] = lfirst(lc); + + return node; +} + +static inline JsonPathNode * +gin_jsonpath_make_expr_node_binary(JsonPathNodeType type, + JsonPathNode *arg1, JsonPathNode *arg2) +{ + JsonPathNode *node = gin_jsonpath_make_expr_node(type, 2); + + node->args[0] = arg1; + node->args[1] = arg2; + + return node; +} + +/* + * Extract node from the EXISTS/equality-comparison jsonpath expression. If + * 'scalar' is not NULL this is equality-comparsion, otherwise this is + * EXISTS-predicate. The current path is passed in 'pathcxt'. + */ +static JsonPathNode * +gin_extract_jsonpath_node(JsonPathExtractionContext *cxt, JsonPathItem *jsp, + ExtractedJsonPath path, JsonbValue *scalar) +{ + List *nodes = NIL; /* nodes to be AND-ed */ + + /* filters extracted into 'nodes' */ + if (!gin_extract_jsonpath_path(cxt, jsp, &path, &nodes)) + return NULL; + + if (cxt->pathOps) + { + if (scalar) + { + /* append path hash node for equality queries */ + uint32 hash = path.hash; + JsonPathNode *node; + + JsonbHashScalarValue(scalar, &hash); + + node = gin_jsonpath_make_entry_node(UInt32GetDatum(hash)); + nodes = lappend(nodes, node); + } + /* else: jsonb_path_ops doesn't support EXISTS queries */ + } + else + { + ExtractedPathEntry *pentry; + + /* append path entry nodes */ + for (pentry = path.entries; pentry; pentry = pentry->parent) + { + if (pentry->type == jpiKey) /* only keys are indexed */ + nodes = lappend(nodes, + gin_jsonpath_make_entry_node(pentry->entry)); + } + + if (scalar) + { + /* append scalar node for equality queries */ + JsonPathNode *node; + ExtractedPathEntry *last = path.entries; + GinTernaryValue lastIsArrayAccessor = !last ? GIN_FALSE : + last->type == jpiIndexArray || + last->type == jpiAnyArray ? GIN_TRUE : + last->type == jpiAny ? GIN_MAYBE : GIN_FALSE; + + /* + * Create OR-node when the string scalar can be matched as a key + * and a non-key. It is possible in lax mode where arrays are + * automatically unwrapped, or in strict mode for jpiAny items. + */ + if (scalar->type == jbvString && + (cxt->lax || lastIsArrayAccessor == GIN_MAYBE)) + node = gin_jsonpath_make_expr_node_binary(eOr, + gin_jsonpath_make_entry_node_scalar(scalar, true), + gin_jsonpath_make_entry_node_scalar(scalar, false)); + else + node = gin_jsonpath_make_entry_node_scalar(scalar, + scalar->type == jbvString && + lastIsArrayAccessor == GIN_TRUE); + + nodes = lappend(nodes, node); + } + } + + if (list_length(nodes) <= 0) + return NULL; /* need full scan for EXISTS($) queries without filters */ + + if (list_length(nodes) == 1) + return linitial(nodes); /* avoid extra AND-node */ + + /* construct AND-node for path with filters */ + return gin_jsonpath_make_expr_node_args(eAnd, nodes); +} + +/* Recursively extract nodes from the boolean jsonpath expression. */ +static JsonPathNode * +gin_extract_jsonpath_expr(JsonPathExtractionContext *cxt, JsonPathItem *jsp, + ExtractedJsonPath path, bool not) +{ + check_stack_depth(); + + switch (jsp->type) + { + case jpiAnd: + case jpiOr: + { + JsonPathItem arg; + JsonPathNode *larg; + JsonPathNode *rarg; + JsonPathNodeType type; + + jspGetLeftArg(jsp, &arg); + larg = gin_extract_jsonpath_expr(cxt, &arg, path, not); + + jspGetRightArg(jsp, &arg); + rarg = gin_extract_jsonpath_expr(cxt, &arg, path, not); + + if (!larg || !rarg) + { + if (jsp->type == jpiOr) + return NULL; + return larg ? larg : rarg; + } + + type = not ^ (jsp->type == jpiAnd) ? eAnd : eOr; + + return gin_jsonpath_make_expr_node_binary(type, larg, rarg); + } + + case jpiNot: + { + JsonPathItem arg; + + jspGetArg(jsp, &arg); + + return gin_extract_jsonpath_expr(cxt, &arg, path, !not); + } + + case jpiExists: + { + JsonPathItem arg; + + if (not) + return NULL; + + jspGetArg(jsp, &arg); + + return gin_extract_jsonpath_node(cxt, &arg, path, NULL); + } + + case jpiEqual: + { + JsonPathItem leftItem; + JsonPathItem rightItem; + JsonPathItem *pathItem; + JsonPathItem *scalarItem; + JsonbValue scalar; + + if (not) + return NULL; + + jspGetLeftArg(jsp, &leftItem); + jspGetRightArg(jsp, &rightItem); + + if (jspIsScalar(leftItem.type)) + { + scalarItem = &leftItem; + pathItem = &rightItem; + } + else if (jspIsScalar(rightItem.type)) + { + scalarItem = &rightItem; + pathItem = &leftItem; + } + else + return NULL; /* at least one operand should be a scalar */ + + switch (scalarItem->type) + { + case jpiNull: + scalar.type = jbvNull; + break; + case jpiBool: + scalar.type = jbvBool; + scalar.val.boolean = !!*scalarItem->content.value.data; + break; + case jpiNumeric: + scalar.type = jbvNumeric; + scalar.val.numeric = + (Numeric) scalarItem->content.value.data; + break; + case jpiString: + scalar.type = jbvString; + scalar.val.string.val = scalarItem->content.value.data; + scalar.val.string.len = scalarItem->content.value.datalen; + break; + default: + elog(ERROR, "invalid scalar jsonpath item type: %d", + scalarItem->type); + return NULL; + } + + return gin_extract_jsonpath_node(cxt, pathItem, path, &scalar); + } + + default: + return NULL; + } +} + +/* Recursively emit all GIN entries found in the node tree */ +static void +gin_jsonpath_emit_entries(JsonPathNode *node, GinEntries *entries) +{ + check_stack_depth(); + + switch (node->type) + { + case eEntry: + /* replace datum with its index in the array */ + node->val.entryIndex = + gin_entries_add(entries, node->val.entryDatum); + break; + + case eOr: + case eAnd: + { + int i; + + for (i = 0; i < node->val.nargs; i++) + gin_jsonpath_emit_entries(node->args[i], entries); + + break; + } + } +} + +static Datum * +gin_extract_jsonpath_query(JsonPath *jp, StrategyNumber strat, bool pathOps, + int32 *nentries, Pointer **extra_data) +{ + JsonPathExtractionContext cxt; + JsonPathItem root; + JsonPathNode *node; + ExtractedJsonPath path = { 0 }; + GinEntries entries = { 0 }; + + gin_jsonpath_init_context(&cxt, pathOps, (jp->header & JSONPATH_LAX) != 0); + + jspInit(&root, jp); + + node = strat == JsonbJsonpathExistsStrategyNumber + ? gin_extract_jsonpath_node(&cxt, &root, path, NULL) + : gin_extract_jsonpath_expr(&cxt, &root, path, false); + + if (!node) + { + *nentries = 0; + return NULL; + } + + gin_jsonpath_emit_entries(node, &entries); + + *nentries = entries.count; + if (!*nentries) + return NULL; + + *extra_data = palloc0(sizeof(**extra_data) * entries.count); + **extra_data = (Pointer) node; + + return entries.buf; +} + +static GinTernaryValue +gin_execute_jsonpath(JsonPathNode *node, void *check, bool ternary) +{ + GinTernaryValue res; + GinTernaryValue v; + int i; + + switch (node->type) + { + case eAnd: + res = GIN_TRUE; + for (i = 0; i < node->val.nargs; i++) + { + v = gin_execute_jsonpath(node->args[i], check, ternary); + if (v == GIN_FALSE) + return GIN_FALSE; + else if (v == GIN_MAYBE) + res = GIN_MAYBE; + } + return res; + + case eOr: + res = GIN_FALSE; + for (i = 0; i < node->val.nargs; i++) + { + v = gin_execute_jsonpath(node->args[i], check, ternary); + if (v == GIN_TRUE) + return GIN_TRUE; + else if (v == GIN_MAYBE) + res = GIN_MAYBE; + } + return res; + + case eEntry: + { + int index = node->val.entryIndex; + bool maybe = ternary + ? ((GinTernaryValue *) check)[index] != GIN_FALSE + : ((bool *) check)[index]; + + return maybe ? GIN_MAYBE : GIN_FALSE; + } + + default: + elog(ERROR, "invalid jsonpath gin node type: %d", node->type); + return GIN_FALSE; + } } Datum @@ -181,6 +744,18 @@ gin_extract_jsonb_query(PG_FUNCTION_ARGS) if (j == 0 && strategy == JsonbExistsAllStrategyNumber) *searchMode = GIN_SEARCH_MODE_ALL; } + else if (strategy == JsonbJsonpathPredicateStrategyNumber || + strategy == JsonbJsonpathExistsStrategyNumber) + { + JsonPath *jp = PG_GETARG_JSONPATH_P(0); + Pointer **extra_data = (Pointer **) PG_GETARG_POINTER(4); + + entries = gin_extract_jsonpath_query(jp, strategy, false, nentries, + extra_data); + + if (!entries) + *searchMode = GIN_SEARCH_MODE_ALL; + } else { elog(ERROR, "unrecognized strategy number: %d", strategy); @@ -199,7 +774,7 @@ gin_consistent_jsonb(PG_FUNCTION_ARGS) /* Jsonb *query = PG_GETARG_JSONB_P(2); */ int32 nkeys = PG_GETARG_INT32(3); - /* Pointer *extra_data = (Pointer *) PG_GETARG_POINTER(4); */ + Pointer *extra_data = (Pointer *) PG_GETARG_POINTER(4); bool *recheck = (bool *) PG_GETARG_POINTER(5); bool res = true; int32 i; @@ -256,6 +831,15 @@ gin_consistent_jsonb(PG_FUNCTION_ARGS) } } } + else if (strategy == JsonbJsonpathPredicateStrategyNumber || + strategy == JsonbJsonpathExistsStrategyNumber) + { + JsonPathNode *node = (JsonPathNode *) extra_data[0]; + + *recheck = true; + res = nkeys <= 0 || + gin_execute_jsonpath(node, check, false) != GIN_FALSE; + } else elog(ERROR, "unrecognized strategy number: %d", strategy); @@ -270,8 +854,7 @@ gin_triconsistent_jsonb(PG_FUNCTION_ARGS) /* Jsonb *query = PG_GETARG_JSONB_P(2); */ int32 nkeys = PG_GETARG_INT32(3); - - /* Pointer *extra_data = (Pointer *) PG_GETARG_POINTER(4); */ + Pointer *extra_data = (Pointer *) PG_GETARG_POINTER(4); GinTernaryValue res = GIN_MAYBE; int32 i; @@ -308,6 +891,12 @@ gin_triconsistent_jsonb(PG_FUNCTION_ARGS) } } } + else if (strategy == JsonbJsonpathPredicateStrategyNumber || + strategy == JsonbJsonpathExistsStrategyNumber) + { + res = nkeys <= 0 ? GIN_MAYBE : + gin_execute_jsonpath((JsonPathNode *) extra_data[0], check, true); + } else elog(ERROR, "unrecognized strategy number: %d", strategy); @@ -331,14 +920,13 @@ gin_extract_jsonb_path(PG_FUNCTION_ARGS) { Jsonb *jb = PG_GETARG_JSONB_P(0); int32 *nentries = (int32 *) PG_GETARG_POINTER(1); - int total = 2 * JB_ROOT_COUNT(jb); + int total = JB_ROOT_COUNT(jb); JsonbIterator *it; JsonbValue v; JsonbIteratorToken r; PathHashStack tail; PathHashStack *stack; - int i = 0; - Datum *entries; + GinEntries entries; /* If the root level is empty, we certainly have no keys */ if (total == 0) @@ -348,7 +936,7 @@ gin_extract_jsonb_path(PG_FUNCTION_ARGS) } /* Otherwise, use 2 * root count as initial estimate of result size */ - entries = (Datum *) palloc(sizeof(Datum) * total); + gin_entries_init(&entries, 2 * total); /* We keep a stack of partial hashes corresponding to parent key levels */ tail.parent = NULL; @@ -361,13 +949,6 @@ gin_extract_jsonb_path(PG_FUNCTION_ARGS) { PathHashStack *parent; - /* Since we recurse into the object, we might need more space */ - if (i >= total) - { - total *= 2; - entries = (Datum *) repalloc(entries, sizeof(Datum) * total); - } - switch (r) { case WJB_BEGIN_ARRAY: @@ -398,7 +979,7 @@ gin_extract_jsonb_path(PG_FUNCTION_ARGS) /* mix the element or value's hash into the prepared hash */ JsonbHashScalarValue(&v, &stack->hash); /* and emit an index entry */ - entries[i++] = UInt32GetDatum(stack->hash); + gin_entries_add(&entries, UInt32GetDatum(stack->hash)); /* reset hash for next key, value, or sub-object */ stack->hash = stack->parent->hash; break; @@ -419,9 +1000,9 @@ gin_extract_jsonb_path(PG_FUNCTION_ARGS) } } - *nentries = i; + *nentries = entries.count; - PG_RETURN_POINTER(entries); + PG_RETURN_POINTER(entries.buf); } Datum @@ -432,18 +1013,35 @@ gin_extract_jsonb_query_path(PG_FUNCTION_ARGS) int32 *searchMode = (int32 *) PG_GETARG_POINTER(6); Datum *entries; - if (strategy != JsonbContainsStrategyNumber) - elog(ERROR, "unrecognized strategy number: %d", strategy); + if (strategy == JsonbContainsStrategyNumber) + { + /* Query is a jsonb, so just apply gin_extract_jsonb_path ... */ + entries = (Datum *) + DatumGetPointer(DirectFunctionCall2(gin_extract_jsonb_path, + PG_GETARG_DATUM(0), + PointerGetDatum(nentries))); - /* Query is a jsonb, so just apply gin_extract_jsonb_path ... */ - entries = (Datum *) - DatumGetPointer(DirectFunctionCall2(gin_extract_jsonb_path, - PG_GETARG_DATUM(0), - PointerGetDatum(nentries))); + /* ... although "contains {}" requires a full index scan */ + if (*nentries == 0) + *searchMode = GIN_SEARCH_MODE_ALL; + } + else if (strategy == JsonbJsonpathPredicateStrategyNumber || + strategy == JsonbJsonpathExistsStrategyNumber) + { + JsonPath *jp = PG_GETARG_JSONPATH_P(0); + Pointer **extra_data = (Pointer **) PG_GETARG_POINTER(4); - /* ... although "contains {}" requires a full index scan */ - if (*nentries == 0) - *searchMode = GIN_SEARCH_MODE_ALL; + entries = gin_extract_jsonpath_query(jp, strategy, true, nentries, + extra_data); + + if (!entries) + *searchMode = GIN_SEARCH_MODE_ALL; + } + else + { + elog(ERROR, "unrecognized strategy number: %d", strategy); + entries = NULL; + } PG_RETURN_POINTER(entries); } @@ -456,32 +1054,42 @@ gin_consistent_jsonb_path(PG_FUNCTION_ARGS) /* Jsonb *query = PG_GETARG_JSONB_P(2); */ int32 nkeys = PG_GETARG_INT32(3); - - /* Pointer *extra_data = (Pointer *) PG_GETARG_POINTER(4); */ + Pointer *extra_data = (Pointer *) PG_GETARG_POINTER(4); bool *recheck = (bool *) PG_GETARG_POINTER(5); bool res = true; int32 i; - if (strategy != JsonbContainsStrategyNumber) - elog(ERROR, "unrecognized strategy number: %d", strategy); - - /* - * jsonb_path_ops is necessarily lossy, not only because of hash - * collisions but also because it doesn't preserve complete information - * about the structure of the JSON object. Besides, there are some - * special rules around the containment of raw scalars in arrays that are - * not handled here. So we must always recheck a match. However, if not - * all of the keys are present, the tuple certainly doesn't match. - */ - *recheck = true; - for (i = 0; i < nkeys; i++) + if (strategy == JsonbContainsStrategyNumber) { - if (!check[i]) + /* + * jsonb_path_ops is necessarily lossy, not only because of hash + * collisions but also because it doesn't preserve complete information + * about the structure of the JSON object. Besides, there are some + * special rules around the containment of raw scalars in arrays that are + * not handled here. So we must always recheck a match. However, if not + * all of the keys are present, the tuple certainly doesn't match. + */ + *recheck = true; + for (i = 0; i < nkeys; i++) { - res = false; - break; + if (!check[i]) + { + res = false; + break; + } } } + else if (strategy == JsonbJsonpathPredicateStrategyNumber || + strategy == JsonbJsonpathExistsStrategyNumber) + { + JsonPathNode *node = (JsonPathNode *) extra_data[0]; + + *recheck = true; + res = nkeys <= 0 || + gin_execute_jsonpath(node, check, false) != GIN_FALSE; + } + else + elog(ERROR, "unrecognized strategy number: %d", strategy); PG_RETURN_BOOL(res); } @@ -494,27 +1102,34 @@ gin_triconsistent_jsonb_path(PG_FUNCTION_ARGS) /* Jsonb *query = PG_GETARG_JSONB_P(2); */ int32 nkeys = PG_GETARG_INT32(3); - - /* Pointer *extra_data = (Pointer *) PG_GETARG_POINTER(4); */ + Pointer *extra_data = (Pointer *) PG_GETARG_POINTER(4); GinTernaryValue res = GIN_MAYBE; int32 i; - if (strategy != JsonbContainsStrategyNumber) - elog(ERROR, "unrecognized strategy number: %d", strategy); - - /* - * Note that we never return GIN_TRUE, only GIN_MAYBE or GIN_FALSE; this - * corresponds to always forcing recheck in the regular consistent - * function, for the reasons listed there. - */ - for (i = 0; i < nkeys; i++) + if (strategy == JsonbContainsStrategyNumber) { - if (check[i] == GIN_FALSE) + /* + * Note that we never return GIN_TRUE, only GIN_MAYBE or GIN_FALSE; this + * corresponds to always forcing recheck in the regular consistent + * function, for the reasons listed there. + */ + for (i = 0; i < nkeys; i++) { - res = GIN_FALSE; - break; + if (check[i] == GIN_FALSE) + { + res = GIN_FALSE; + break; + } } } + else if (strategy == JsonbJsonpathPredicateStrategyNumber || + strategy == JsonbJsonpathExistsStrategyNumber) + { + res = nkeys <= 0 ? GIN_MAYBE : + gin_execute_jsonpath((JsonPathNode *) extra_data[0], check, true); + } + else + elog(ERROR, "unrecognized strategy number: %d", strategy); PG_RETURN_GIN_TERNARY_VALUE(res); } diff --git a/src/include/catalog/pg_amop.dat b/src/include/catalog/pg_amop.dat index 075a54c..b2d226f 100644 --- a/src/include/catalog/pg_amop.dat +++ b/src/include/catalog/pg_amop.dat @@ -1433,11 +1433,23 @@ { amopfamily => 'gin/jsonb_ops', amoplefttype => 'jsonb', amoprighttype => '_text', amopstrategy => '11', amopopr => '?&(jsonb,_text)', amopmethod => 'gin' }, +{ amopfamily => 'gin/jsonb_ops', amoplefttype => 'jsonb', + amoprighttype => 'jsonpath', amopstrategy => '15', + amopopr => '@?(jsonb,jsonpath)', amopmethod => 'gin' }, +{ amopfamily => 'gin/jsonb_ops', amoplefttype => 'jsonb', + amoprighttype => 'jsonpath', amopstrategy => '16', + amopopr => '@~(jsonb,jsonpath)', amopmethod => 'gin' }, # GIN jsonb_path_ops { amopfamily => 'gin/jsonb_path_ops', amoplefttype => 'jsonb', amoprighttype => 'jsonb', amopstrategy => '7', amopopr => '@>(jsonb,jsonb)', amopmethod => 'gin' }, +{ amopfamily => 'gin/jsonb_path_ops', amoplefttype => 'jsonb', + amoprighttype => 'jsonpath', amopstrategy => '15', + amopopr => '@?(jsonb,jsonpath)', amopmethod => 'gin' }, +{ amopfamily => 'gin/jsonb_path_ops', amoplefttype => 'jsonb', + amoprighttype => 'jsonpath', amopstrategy => '16', + amopopr => '@~(jsonb,jsonpath)', amopmethod => 'gin' }, # SP-GiST range_ops { amopfamily => 'spgist/range_ops', amoplefttype => 'anyrange', diff --git a/src/include/utils/jsonb.h b/src/include/utils/jsonb.h index 22643de..404ed70 100644 --- a/src/include/utils/jsonb.h +++ b/src/include/utils/jsonb.h @@ -34,6 +34,9 @@ typedef enum #define JsonbExistsStrategyNumber 9 #define JsonbExistsAnyStrategyNumber 10 #define JsonbExistsAllStrategyNumber 11 +#define JsonbJsonpathExistsStrategyNumber 15 +#define JsonbJsonpathPredicateStrategyNumber 16 + /* * In the standard jsonb_ops GIN opclass for jsonb, we choose to index both diff --git a/src/include/utils/jsonpath.h b/src/include/utils/jsonpath.h index 5d16131..b3cf4c2 100644 --- a/src/include/utils/jsonpath.h +++ b/src/include/utils/jsonpath.h @@ -35,6 +35,8 @@ typedef struct #define PG_GETARG_JSONPATH_P_COPY(x) DatumGetJsonPathPCopy(PG_GETARG_DATUM(x)) #define PG_RETURN_JSONPATH_P(p) PG_RETURN_POINTER(p) +#define jspIsScalar(type) ((type) >= jpiNull && (type) <= jpiBool) + /* * All node's type of jsonpath expression */ diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out index f045e08..92cf4c7 100644 --- a/src/test/regress/expected/jsonb.out +++ b/src/test/regress/expected/jsonb.out @@ -2718,6 +2718,114 @@ SELECT count(*) FROM testjsonb WHERE j ?& ARRAY['public','disabled']; 42 (1 row) +SELECT count(*) FROM testjsonb WHERE j @~ '$.wait == null'; + count +------- + 1 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @~ '"CC" == $.wait'; + count +------- + 15 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @~ '$.wait == "CC" && true == $.public'; + count +------- + 2 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @~ '$.age == 25'; + count +------- + 2 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @~ '$.age == 25.0'; + count +------- + 2 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @~ 'exists($)'; + count +------- + 1012 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @~ 'exists($.public)'; + count +------- + 194 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @~ 'exists($.bar)'; + count +------- + 0 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @~ 'exists($.public) || exists($.disabled)'; + count +------- + 337 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @~ 'exists($.public) && exists($.disabled)'; + count +------- + 42 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @? '$.wait ? (@ == null)'; + count +------- + 1 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @? '$.wait ? ("CC" == @)'; + count +------- + 15 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @? '$ ? (@.wait == "CC" && true == @.public)'; + count +------- + 2 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @? '$.age ? (@ == 25)'; + count +------- + 2 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @? '$ ? (@.age == 25.0)'; + count +------- + 2 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @? '$'; + count +------- + 1012 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @? '$.public'; + count +------- + 194 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @? '$.bar'; + count +------- + 0 +(1 row) + CREATE INDEX jidx ON testjsonb USING gin (j); SET enable_seqscan = off; SELECT count(*) FROM testjsonb WHERE j @> '{"wait":null}'; @@ -2793,6 +2901,196 @@ SELECT count(*) FROM testjsonb WHERE j ?& ARRAY['public','disabled']; 42 (1 row) +EXPLAIN (COSTS OFF) +SELECT count(*) FROM testjsonb WHERE j @~ '$.wait == null'; + QUERY PLAN +----------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on testjsonb + Recheck Cond: (j @~ '($."wait" == null)'::jsonpath) + -> Bitmap Index Scan on jidx + Index Cond: (j @~ '($."wait" == null)'::jsonpath) +(5 rows) + +SELECT count(*) FROM testjsonb WHERE j @~ '$.wait == null'; + count +------- + 1 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @~ 'exists($ ? (@.wait == null))'; + count +------- + 1 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @~ 'exists($.wait ? (@ == null))'; + count +------- + 1 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @~ '"CC" == $.wait'; + count +------- + 15 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @~ '$.wait == "CC" && true == $.public'; + count +------- + 2 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @~ '$.age == 25'; + count +------- + 2 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @~ '$.age == 25.0'; + count +------- + 2 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @~ '$.array[*] == "foo"'; + count +------- + 3 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @~ '$.array[*] == "bar"'; + count +------- + 3 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @~ 'exists($ ? (@.array[*] == "bar"))'; + count +------- + 3 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @~ 'exists($.array ? (@[*] == "bar"))'; + count +------- + 3 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @~ 'exists($.array[*] ? (@ == "bar"))'; + count +------- + 3 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @~ 'exists($)'; + count +------- + 1012 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @~ 'exists($.public)'; + count +------- + 194 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @~ 'exists($.bar)'; + count +------- + 0 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @~ 'exists($.public) || exists($.disabled)'; + count +------- + 337 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @~ 'exists($.public) && exists($.disabled)'; + count +------- + 42 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM testjsonb WHERE j @? '$.wait ? (@ == null)'; + QUERY PLAN +------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on testjsonb + Recheck Cond: (j @? '$."wait"?(@ == null)'::jsonpath) + -> Bitmap Index Scan on jidx + Index Cond: (j @? '$."wait"?(@ == null)'::jsonpath) +(5 rows) + +SELECT count(*) FROM testjsonb WHERE j @? '$.wait ? (@ == null)'; + count +------- + 1 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @? '$.wait ? ("CC" == @)'; + count +------- + 15 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @? '$ ? (@.wait == "CC" && true == @.public)'; + count +------- + 2 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @? '$.age ? (@ == 25)'; + count +------- + 2 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @? '$ ? (@.age == 25.0)'; + count +------- + 2 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @? '$ ? (@.array[*] == "bar")'; + count +------- + 3 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @? '$.array ? (@[*] == "bar")'; + count +------- + 3 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @? '$.array[*] ? (@ == "bar")'; + count +------- + 3 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @? '$'; + count +------- + 1012 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @? '$.public'; + count +------- + 194 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @? '$.bar'; + count +------- + 0 +(1 row) + -- array exists - array elements should behave as keys (for GIN index scans too) CREATE INDEX jidx_array ON testjsonb USING gin((j->'array')); SELECT count(*) from testjsonb WHERE j->'array' ? 'bar'; @@ -2943,6 +3241,161 @@ SELECT count(*) FROM testjsonb WHERE j @> '{}'; 1012 (1 row) +SELECT count(*) FROM testjsonb WHERE j @~ '$.wait == null'; + count +------- + 1 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @~ 'exists($ ? (@.wait == null))'; + count +------- + 1 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @~ 'exists($.wait ? (@ == null))'; + count +------- + 1 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @~ '"CC" == $.wait'; + count +------- + 15 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @~ '$.wait == "CC" && true == $.public'; + count +------- + 2 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @~ '$.age == 25'; + count +------- + 2 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @~ '$.age == 25.0'; + count +------- + 2 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @~ '$.array[*] == "foo"'; + count +------- + 3 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @~ '$.array[*] == "bar"'; + count +------- + 3 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @~ 'exists($ ? (@.array[*] == "bar"))'; + count +------- + 3 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @~ 'exists($.array ? (@[*] == "bar"))'; + count +------- + 3 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @~ 'exists($.array[*] ? (@ == "bar"))'; + count +------- + 3 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @~ 'exists($)'; + count +------- + 1012 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM testjsonb WHERE j @? '$.wait ? (@ == null)'; + QUERY PLAN +------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on testjsonb + Recheck Cond: (j @? '$."wait"?(@ == null)'::jsonpath) + -> Bitmap Index Scan on jidx + Index Cond: (j @? '$."wait"?(@ == null)'::jsonpath) +(5 rows) + +SELECT count(*) FROM testjsonb WHERE j @? '$.wait ? (@ == null)'; + count +------- + 1 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @? '$.wait ? ("CC" == @)'; + count +------- + 15 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @? '$ ? (@.wait == "CC" && true == @.public)'; + count +------- + 2 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @? '$.age ? (@ == 25)'; + count +------- + 2 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @? '$ ? (@.age == 25.0)'; + count +------- + 2 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @? '$ ? (@.array[*] == "bar")'; + count +------- + 3 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @? '$.array ? (@[*] == "bar")'; + count +------- + 3 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @? '$.array[*] ? (@ == "bar")'; + count +------- + 3 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @? '$'; + count +------- + 1012 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @? '$.public'; + count +------- + 194 +(1 row) + +SELECT count(*) FROM testjsonb WHERE j @? '$.bar'; + count +------- + 0 +(1 row) + RESET enable_seqscan; DROP INDEX jidx; -- nested tests diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out index c073a5a..dd7b142 100644 --- a/src/test/regress/expected/opr_sanity.out +++ b/src/test/regress/expected/opr_sanity.out @@ -1845,6 +1845,8 @@ ORDER BY 1, 2, 3; 2742 | 9 | ? 2742 | 10 | ?| 2742 | 11 | ?& + 2742 | 15 | @? + 2742 | 16 | @~ 3580 | 1 | < 3580 | 1 | << 3580 | 2 | &< @@ -1910,7 +1912,7 @@ ORDER BY 1, 2, 3; 4000 | 26 | >> 4000 | 27 | >>= 4000 | 28 | ^@ -(123 rows) +(125 rows) -- Check that all opclass search operators have selectivity estimators. -- This is not absolutely required, but it seems a reasonable thing diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql index bd82fd1..1430a98 100644 --- a/src/test/regress/sql/jsonb.sql +++ b/src/test/regress/sql/jsonb.sql @@ -735,6 +735,24 @@ SELECT count(*) FROM testjsonb WHERE j ? 'public'; SELECT count(*) FROM testjsonb WHERE j ? 'bar'; SELECT count(*) FROM testjsonb WHERE j ?| ARRAY['public','disabled']; SELECT count(*) FROM testjsonb WHERE j ?& ARRAY['public','disabled']; +SELECT count(*) FROM testjsonb WHERE j @~ '$.wait == null'; +SELECT count(*) FROM testjsonb WHERE j @~ '"CC" == $.wait'; +SELECT count(*) FROM testjsonb WHERE j @~ '$.wait == "CC" && true == $.public'; +SELECT count(*) FROM testjsonb WHERE j @~ '$.age == 25'; +SELECT count(*) FROM testjsonb WHERE j @~ '$.age == 25.0'; +SELECT count(*) FROM testjsonb WHERE j @~ 'exists($)'; +SELECT count(*) FROM testjsonb WHERE j @~ 'exists($.public)'; +SELECT count(*) FROM testjsonb WHERE j @~ 'exists($.bar)'; +SELECT count(*) FROM testjsonb WHERE j @~ 'exists($.public) || exists($.disabled)'; +SELECT count(*) FROM testjsonb WHERE j @~ 'exists($.public) && exists($.disabled)'; +SELECT count(*) FROM testjsonb WHERE j @? '$.wait ? (@ == null)'; +SELECT count(*) FROM testjsonb WHERE j @? '$.wait ? ("CC" == @)'; +SELECT count(*) FROM testjsonb WHERE j @? '$ ? (@.wait == "CC" && true == @.public)'; +SELECT count(*) FROM testjsonb WHERE j @? '$.age ? (@ == 25)'; +SELECT count(*) FROM testjsonb WHERE j @? '$ ? (@.age == 25.0)'; +SELECT count(*) FROM testjsonb WHERE j @? '$'; +SELECT count(*) FROM testjsonb WHERE j @? '$.public'; +SELECT count(*) FROM testjsonb WHERE j @? '$.bar'; CREATE INDEX jidx ON testjsonb USING gin (j); SET enable_seqscan = off; @@ -753,6 +771,39 @@ SELECT count(*) FROM testjsonb WHERE j ? 'bar'; SELECT count(*) FROM testjsonb WHERE j ?| ARRAY['public','disabled']; SELECT count(*) FROM testjsonb WHERE j ?& ARRAY['public','disabled']; +EXPLAIN (COSTS OFF) +SELECT count(*) FROM testjsonb WHERE j @~ '$.wait == null'; +SELECT count(*) FROM testjsonb WHERE j @~ '$.wait == null'; +SELECT count(*) FROM testjsonb WHERE j @~ 'exists($ ? (@.wait == null))'; +SELECT count(*) FROM testjsonb WHERE j @~ 'exists($.wait ? (@ == null))'; +SELECT count(*) FROM testjsonb WHERE j @~ '"CC" == $.wait'; +SELECT count(*) FROM testjsonb WHERE j @~ '$.wait == "CC" && true == $.public'; +SELECT count(*) FROM testjsonb WHERE j @~ '$.age == 25'; +SELECT count(*) FROM testjsonb WHERE j @~ '$.age == 25.0'; +SELECT count(*) FROM testjsonb WHERE j @~ '$.array[*] == "foo"'; +SELECT count(*) FROM testjsonb WHERE j @~ '$.array[*] == "bar"'; +SELECT count(*) FROM testjsonb WHERE j @~ 'exists($ ? (@.array[*] == "bar"))'; +SELECT count(*) FROM testjsonb WHERE j @~ 'exists($.array ? (@[*] == "bar"))'; +SELECT count(*) FROM testjsonb WHERE j @~ 'exists($.array[*] ? (@ == "bar"))'; +SELECT count(*) FROM testjsonb WHERE j @~ 'exists($)'; +SELECT count(*) FROM testjsonb WHERE j @~ 'exists($.public)'; +SELECT count(*) FROM testjsonb WHERE j @~ 'exists($.bar)'; +SELECT count(*) FROM testjsonb WHERE j @~ 'exists($.public) || exists($.disabled)'; +SELECT count(*) FROM testjsonb WHERE j @~ 'exists($.public) && exists($.disabled)'; +EXPLAIN (COSTS OFF) +SELECT count(*) FROM testjsonb WHERE j @? '$.wait ? (@ == null)'; +SELECT count(*) FROM testjsonb WHERE j @? '$.wait ? (@ == null)'; +SELECT count(*) FROM testjsonb WHERE j @? '$.wait ? ("CC" == @)'; +SELECT count(*) FROM testjsonb WHERE j @? '$ ? (@.wait == "CC" && true == @.public)'; +SELECT count(*) FROM testjsonb WHERE j @? '$.age ? (@ == 25)'; +SELECT count(*) FROM testjsonb WHERE j @? '$ ? (@.age == 25.0)'; +SELECT count(*) FROM testjsonb WHERE j @? '$ ? (@.array[*] == "bar")'; +SELECT count(*) FROM testjsonb WHERE j @? '$.array ? (@[*] == "bar")'; +SELECT count(*) FROM testjsonb WHERE j @? '$.array[*] ? (@ == "bar")'; +SELECT count(*) FROM testjsonb WHERE j @? '$'; +SELECT count(*) FROM testjsonb WHERE j @? '$.public'; +SELECT count(*) FROM testjsonb WHERE j @? '$.bar'; + -- array exists - array elements should behave as keys (for GIN index scans too) CREATE INDEX jidx_array ON testjsonb USING gin((j->'array')); SELECT count(*) from testjsonb WHERE j->'array' ? 'bar'; @@ -802,6 +853,34 @@ SELECT count(*) FROM testjsonb WHERE j @> '{"age":25.0}'; -- exercise GIN_SEARCH_MODE_ALL SELECT count(*) FROM testjsonb WHERE j @> '{}'; +SELECT count(*) FROM testjsonb WHERE j @~ '$.wait == null'; +SELECT count(*) FROM testjsonb WHERE j @~ 'exists($ ? (@.wait == null))'; +SELECT count(*) FROM testjsonb WHERE j @~ 'exists($.wait ? (@ == null))'; +SELECT count(*) FROM testjsonb WHERE j @~ '"CC" == $.wait'; +SELECT count(*) FROM testjsonb WHERE j @~ '$.wait == "CC" && true == $.public'; +SELECT count(*) FROM testjsonb WHERE j @~ '$.age == 25'; +SELECT count(*) FROM testjsonb WHERE j @~ '$.age == 25.0'; +SELECT count(*) FROM testjsonb WHERE j @~ '$.array[*] == "foo"'; +SELECT count(*) FROM testjsonb WHERE j @~ '$.array[*] == "bar"'; +SELECT count(*) FROM testjsonb WHERE j @~ 'exists($ ? (@.array[*] == "bar"))'; +SELECT count(*) FROM testjsonb WHERE j @~ 'exists($.array ? (@[*] == "bar"))'; +SELECT count(*) FROM testjsonb WHERE j @~ 'exists($.array[*] ? (@ == "bar"))'; +SELECT count(*) FROM testjsonb WHERE j @~ 'exists($)'; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM testjsonb WHERE j @? '$.wait ? (@ == null)'; +SELECT count(*) FROM testjsonb WHERE j @? '$.wait ? (@ == null)'; +SELECT count(*) FROM testjsonb WHERE j @? '$.wait ? ("CC" == @)'; +SELECT count(*) FROM testjsonb WHERE j @? '$ ? (@.wait == "CC" && true == @.public)'; +SELECT count(*) FROM testjsonb WHERE j @? '$.age ? (@ == 25)'; +SELECT count(*) FROM testjsonb WHERE j @? '$ ? (@.age == 25.0)'; +SELECT count(*) FROM testjsonb WHERE j @? '$ ? (@.array[*] == "bar")'; +SELECT count(*) FROM testjsonb WHERE j @? '$.array ? (@[*] == "bar")'; +SELECT count(*) FROM testjsonb WHERE j @? '$.array[*] ? (@ == "bar")'; +SELECT count(*) FROM testjsonb WHERE j @? '$'; +SELECT count(*) FROM testjsonb WHERE j @? '$.public'; +SELECT count(*) FROM testjsonb WHERE j @? '$.bar'; + RESET enable_seqscan; DROP INDEX jidx; -- 2.7.4 --------------9B31BF271468F658D603E21E Content-Type: text/x-patch; name="0004-Jsonpath-syntax-extensions-v20.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="0004-Jsonpath-syntax-extensions-v20.patch"