diff --git a/doc/src/sgml/gin.sgml b/doc/src/sgml/gin.sgml index cc7cd1e..8c51e4e 100644 --- a/doc/src/sgml/gin.sgml +++ b/doc/src/sgml/gin.sgml @@ -102,6 +102,8 @@ ?& ?| @> + @? + @~ @@ -109,6 +111,8 @@ jsonb @> + @? + @~ diff --git a/doc/src/sgml/json.sgml b/doc/src/sgml/json.sgml index f179bc4..1be32b7 100644 --- a/doc/src/sgml/json.sgml +++ b/doc/src/sgml/json.sgml @@ -536,6 +536,19 @@ SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qu + jsonb_ops and jsonb_path_ops also + support queries with jsonpath operators @? + and @~. The previous example for @> + operator can be rewritten as follows: + +-- Find documents in which the key "tags" contains array element "qui" +SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @? '$.tags[*] ? (@ == "qui")'; +SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @~ '$.tags[*] == "qui"'; + + + + + jsonb also supports btree and hash indexes. These are usually useful only if it's important to check equality of complete JSON documents. diff --git a/src/backend/utils/adt/jsonb_gin.c b/src/backend/utils/adt/jsonb_gin.c index c8a2745..ecdcb2e 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,440 @@ 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 = palloc(sizeof(**extra_data) * entries.count); + **extra_data = (Pointer) node; + + return entries.buf; +} + +static GinTernaryValue +gin_execute_jsonpath(JsonPathNode *node, GinTernaryValue *check) +{ + 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); + 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); + if (v == GIN_TRUE) + return GIN_TRUE; + else if (v == GIN_MAYBE) + res = GIN_MAYBE; + } + return res; + + case eEntry: + return check[node->val.entryIndex] ? GIN_MAYBE : GIN_FALSE; + + default: + elog(ERROR, "invalid jsonpath gin node type: %d", node->type); + return GIN_FALSE; + } } Datum @@ -181,6 +737,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 +767,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 +824,13 @@ gin_consistent_jsonb(PG_FUNCTION_ARGS) } } } + else if (strategy == JsonbJsonpathPredicateStrategyNumber || + strategy == JsonbJsonpathExistsStrategyNumber) + { + *recheck = true; + res = nkeys <= 0 || + gin_execute_jsonpath((JsonPathNode *) extra_data[0], check) != GIN_FALSE; + } else elog(ERROR, "unrecognized strategy number: %d", strategy); @@ -270,8 +845,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 +882,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); + } else elog(ERROR, "unrecognized strategy number: %d", strategy); @@ -331,14 +911,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 +927,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 +940,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 +970,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 +991,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 +1004,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 +1045,40 @@ 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) + { + *recheck = true; + res = nkeys <= 0 || + gin_execute_jsonpath((JsonPathNode *) extra_data[0], check); + } + else + elog(ERROR, "unrecognized strategy number: %d", strategy); PG_RETURN_BOOL(res); } @@ -494,27 +1091,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); + } + else + elog(ERROR, "unrecognized strategy number: %d", strategy); PG_RETURN_GIN_TERNARY_VALUE(res); } diff --git a/src/include/catalog/pg_amop.h b/src/include/catalog/pg_amop.h index 03af581..e05ca03 100644 --- a/src/include/catalog/pg_amop.h +++ b/src/include/catalog/pg_amop.h @@ -821,11 +821,15 @@ DATA(insert ( 4036 3802 3802 7 s 3246 2742 0 )); DATA(insert ( 4036 3802 25 9 s 3247 2742 0 )); DATA(insert ( 4036 3802 1009 10 s 3248 2742 0 )); DATA(insert ( 4036 3802 1009 11 s 3249 2742 0 )); +DATA(insert ( 4036 3802 6050 15 s 6076 2742 0 )); +DATA(insert ( 4036 3802 6050 16 s 6107 2742 0 )); /* * GIN jsonb_path_ops */ DATA(insert ( 4037 3802 3802 7 s 3246 2742 0 )); +DATA(insert ( 4037 3802 6050 15 s 6076 2742 0 )); +DATA(insert ( 4037 3802 6050 16 s 6107 2742 0 )); /* * SP-GiST range_ops diff --git a/src/include/utils/jsonb.h b/src/include/utils/jsonb.h index b306b26..144b8b0 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 d06bb14..87dae0d 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 465195a..178e06f 100644 --- a/src/test/regress/expected/jsonb.out +++ b/src/test/regress/expected/jsonb.out @@ -2708,6 +2708,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}'; @@ -2783,6 +2891,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'; @@ -2933,6 +3231,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 6616cc1..67479c4 100644 --- a/src/test/regress/expected/opr_sanity.out +++ b/src/test/regress/expected/opr_sanity.out @@ -1823,6 +1823,8 @@ ORDER BY 1, 2, 3; 2742 | 9 | ? 2742 | 10 | ?| 2742 | 11 | ?& + 2742 | 15 | @? + 2742 | 16 | @~ 3580 | 1 | < 3580 | 1 | << 3580 | 2 | &< @@ -1886,7 +1888,7 @@ ORDER BY 1, 2, 3; 4000 | 25 | <<= 4000 | 26 | >> 4000 | 27 | >>= -(121 rows) +(123 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 903e5ef..b153e62 100644 --- a/src/test/regress/sql/jsonb.sql +++ b/src/test/regress/sql/jsonb.sql @@ -733,6 +733,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; @@ -751,6 +769,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'; @@ -800,6 +851,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;