agora inbox for [email protected]
help / color / mirror / Atom feedFrom: Nikita Glukhov <[email protected]>
Subject: [PATCH 3/4] Jsonpath GIN support
Date: Tue, 6 Nov 2018 16:33:13 +0300
---
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"
view thread (7+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: [PATCH 3/4] Jsonpath GIN support
In-Reply-To: <no-message-id-238398@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox