On 02/14/2015 10:06 PM, Andrew Dunstan wrote:
Attached is a patch to provide a number of very useful facilities to
jsonb that people have asked for. These are based on work by Dmitry
Dolgov in his jsonbx extension, but I take responsibility for any bugs.
The facilities are:
new operations:
concatenation: jsonb || jsonb -> jsonb
deletion: jsonb - text -> jsonb
deletion: jsonb - int -> text
new functions:
produce indented text: jsonb_indent(jsonb) -> text
change an element at a path: jsonb_replace(jsonb, text[], jsonb) ->
jsonb.
It would be relatively trivial to add:
delete an element at a path: jsonb_delete(jsonb, text[]) -> json
and I think we should do that for the sake of completeness.
The docs might need a little extra work, and the indent code
definitely needs work, which I hope to complete in the next day or
two, but I wanted to put a stake in the ground.
In this version the indent code now works correctly, and there is an
additional delete operator:
jsonb - text[] -> jsonb
Which deletes data at the designated path.
cheers
andrew
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index d57243a..9936bff 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -10256,6 +10256,30 @@ table2-mapping
<entry>Do all of these key/element <emphasis>strings</emphasis> exist?</entry>
<entry><literal>'["a", "b"]'::jsonb ?& array['a', 'b']</literal></entry>
</row>
+ <row>
+ <entry><literal>||</literal></entry>
+ <entry><type>jsonb</type></entry>
+ <entry>Concatentate these two values to make a new value</entry>
+ <entry><literal>'["a", "b"]'::jsonb || '["c", "d"]'::jsonb</literal></entry>
+ </row>
+ <row>
+ <entry><literal>-</literal></entry>
+ <entry><type>text</type></entry>
+ <entry>Delete the field with this key, or element with this value</entry>
+ <entry><literal>'{"a": "b"}'::jsonb - 'a' </literal></entry>
+ </row>
+ <row>
+ <entry><literal>-</literal></entry>
+ <entry><type>integer</type></entry>
+ <entry>Delete the field or element with this index (Negative integers count from the end)</entry>
+ <entry><literal>'["a", "b"]'::jsonb - 1 </literal></entry>
+ </row>
+ <row>
+ <entry><literal>-</literal></entry>
+ <entry><type>text[]</type></entry>
+ <entry>Delete the field or element with this path</entry>
+ <entry><literal>'["a", {"b":1}]'::jsonb - '{1,b}'::text[] </literal></entry>
+ </row>
</tbody>
</tgroup>
</table>
@@ -10760,6 +10784,42 @@ table2-mapping
<entry><literal>json_strip_nulls('[{"f1":1,"f2":null},2,null,3]')</literal></entry>
<entry><literal>[{"f1":1},2,null,3]</literal></entry>
</row>
+ <row>
+ <entry><para><literal>jsonb_replace(target jsonb, path text[], replacement jsonb)</literal>
+ </para></entry>
+ <entry><para><type>jsonb</type></para></entry>
+ <entry>
+ Returns <replaceable>target</replaceable>
+ with the section designated by <replaceable>path</replaceable>
+ replaced by <replaceable>replacement</replaceable>.
+ </entry>
+ <entry><literal>jsonb_replace('[{"f1":1,"f2":null},2,null,3]', '{0,f1},'[2,3,4]')</literal></entry>
+ <entry><literal>[{"f1":[2,3,4],"f2":null},2,null,3]</literal>
+ </entry>
+ </row>
+ <row>
+ <entry><para><literal>jsonb_indent(from_json jsonb)</literal>
+ </para></entry>
+ <entry><para><type>text</type></para></entry>
+ <entry>
+ Returns <replaceable>from_json</replaceable>
+ as indented json text.
+ </entry>
+ <entry><literal>jsonb_indent('[{"f1":1,"f2":null},2,null,3]')</literal></entry>
+ <entry>
+<programlisting>
+ [
+ {
+ "f1": 1,
+ "f2": null
+ },
+ 2,
+ null,
+ 3
+ ]
+</programlisting>
+ </entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index 644ea6d..133b9ba 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -77,6 +77,8 @@ static void datum_to_jsonb(Datum val, bool is_null, JsonbInState *result,
static void add_jsonb(Datum val, bool is_null, JsonbInState *result,
Oid val_type, bool key_scalar);
static JsonbParseState * clone_parse_state(JsonbParseState * state);
+static char *JsonbToCStringWorker(StringInfo out, JsonbContainer *in, int estimated_len, bool indent);
+static void add_indent(StringInfo out, bool indent, int level);
/*
* jsonb type input function
@@ -414,12 +416,40 @@ jsonb_in_scalar(void *pstate, char *token, JsonTokenType tokentype)
char *
JsonbToCString(StringInfo out, JsonbContainer *in, int estimated_len)
{
+ return JsonbToCStringWorker(out, in, estimated_len, false);
+}
+
+/*
+ * same thing but with indentation turned on
+ */
+
+char *
+JsonbToCStringIndent(StringInfo out, JsonbContainer *in, int estimated_len)
+{
+ return JsonbToCStringWorker(out, in, estimated_len, true);
+}
+
+
+/*
+ * common worker for above two functions
+ */
+static char *
+JsonbToCStringWorker(StringInfo out, JsonbContainer *in, int estimated_len, bool indent)
+{
bool first = true;
JsonbIterator *it;
int type = 0;
JsonbValue v;
int level = 0;
bool redo_switch = false;
+ /* If we are indenting, don't add a space after a comma */
+ int ispaces = indent ? 1 : 2;
+ /*
+ * Don't indent the very first item. This gets set to the indent flag
+ * at the bottom of the loop.
+ */
+ bool use_indent = false;
+ bool raw_scalar = false;
if (out == NULL)
out = makeStringInfo();
@@ -436,26 +466,37 @@ JsonbToCString(StringInfo out, JsonbContainer *in, int estimated_len)
{
case WJB_BEGIN_ARRAY:
if (!first)
- appendBinaryStringInfo(out, ", ", 2);
+ appendBinaryStringInfo(out, ", ", ispaces);
first = true;
if (!v.val.array.rawScalar)
- appendStringInfoChar(out, '[');
+ {
+ add_indent(out, use_indent, level);
+ appendStringInfoCharMacro(out, '[');
+ }
+ else
+ {
+ raw_scalar = true;
+ }
level++;
break;
case WJB_BEGIN_OBJECT:
if (!first)
- appendBinaryStringInfo(out, ", ", 2);
+ appendBinaryStringInfo(out, ", ", ispaces);
first = true;
+
+ add_indent(out, use_indent, level);
appendStringInfoCharMacro(out, '{');
level++;
break;
case WJB_KEY:
if (!first)
- appendBinaryStringInfo(out, ", ", 2);
+ appendBinaryStringInfo(out, ", ", ispaces);
first = true;
+ add_indent(out, use_indent, level);
+
/* json rules guarantee this is a string */
jsonb_put_escaped_value(out, &v);
appendBinaryStringInfo(out, ": ", 2);
@@ -480,26 +521,32 @@ JsonbToCString(StringInfo out, JsonbContainer *in, int estimated_len)
break;
case WJB_ELEM:
if (!first)
- appendBinaryStringInfo(out, ", ", 2);
- else
- first = false;
+ appendBinaryStringInfo(out, ", ", ispaces);
+ first = false;
+ if (! raw_scalar)
+ add_indent(out, use_indent, level);
jsonb_put_escaped_value(out, &v);
break;
case WJB_END_ARRAY:
level--;
- if (!v.val.array.rawScalar)
- appendStringInfoChar(out, ']');
+ if (! raw_scalar)
+ {
+ add_indent(out, use_indent, level);
+ appendStringInfoCharMacro(out, ']');
+ }
first = false;
break;
case WJB_END_OBJECT:
level--;
+ add_indent(out, use_indent, level);
appendStringInfoCharMacro(out, '}');
first = false;
break;
default:
elog(ERROR, "unknown flag of jsonb iterator");
}
+ use_indent = indent;
}
Assert(level == 0);
@@ -508,6 +555,22 @@ JsonbToCString(StringInfo out, JsonbContainer *in, int estimated_len)
}
+
+static void
+add_indent(StringInfo out, bool indent, int level)
+{
+ if (indent)
+ {
+ int i;
+
+ appendStringInfoCharMacro(out, '\n');
+ for (i = 0; i < level; i++)
+ {
+ appendBinaryStringInfo(out, " ", 4);
+ }
+ }
+}
+
/*
* Determine how we want to render values of a given type in datum_to_jsonb.
*
diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c
index 3688163..089b9ca 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -124,6 +124,16 @@ static JsonbValue *findJsonbValueFromContainerLen(JsonbContainer *container,
char *key,
uint32 keylen);
+/* functions supporting jsonb_delete, jsonb_replace and jsonb_concat */
+static bool h_atoi(char *c, int l, int *acc);
+static JsonbValue *IteratorConcat(JsonbIterator **it1, JsonbIterator **it2,
+ JsonbParseState **state);
+static JsonbValue *walkJsonb(JsonbIterator **it, JsonbParseState **state, bool stop_at_level_zero);
+static JsonbValue *replacePath(JsonbIterator **it, Datum *path_elems,
+ bool *path_nulls, int path_len,
+ JsonbParseState **st, int level, Jsonb *newval);
+static void addJsonbToParseState(JsonbParseState **jbps, Jsonb * jb);
+
/* state for json_object_keys */
typedef struct OkeysState
{
@@ -3195,3 +3205,761 @@ jsonb_strip_nulls(PG_FUNCTION_ARGS)
PG_RETURN_POINTER(JsonbValueToJsonb(res));
}
+
+/*
+ * Add values from the jsonb to the parse state.
+ *
+ * If the parse state container is an object, the jsonb is pushed as
+ * a value, not a key.
+ *
+ * This needs to be done using an iterator because pushJsonbValue doesn't
+ * like getting jbvBinary values, so we can't just push jb as a whole.
+ */
+static void
+addJsonbToParseState(JsonbParseState **jbps, Jsonb * jb)
+{
+
+ JsonbIterator *it;
+ JsonbValue *o = &(*jbps)->contVal;
+ int type;
+ JsonbValue v;
+
+ it = JsonbIteratorInit(&jb->root);
+
+ Assert(o->type == jbvArray || o->type == jbvObject);
+
+ if (JB_ROOT_IS_SCALAR(jb))
+ {
+ (void) JsonbIteratorNext(&it, &v, false); /* skip array header */
+ (void) JsonbIteratorNext(&it, &v, false); /* fetch scalar value */
+
+ switch (o->type)
+ {
+ case jbvArray:
+ (void) pushJsonbValue(jbps, WJB_ELEM, &v);
+ break;
+ case jbvObject:
+ (void) pushJsonbValue(jbps, WJB_VALUE, &v);
+ break;
+ default:
+ elog(ERROR, "unexpected parent of nested structure");
+ }
+ }
+ else
+ {
+ while ((type = JsonbIteratorNext(&it, &v, false)) != WJB_DONE)
+ {
+ if (type == WJB_KEY || type == WJB_VALUE || type == WJB_ELEM)
+ (void) pushJsonbValue(jbps, type, &v);
+ else
+ (void) pushJsonbValue(jbps, type, NULL);
+ }
+ }
+
+}
+
+/*
+ * SQL function jsonb_indent (jsonb)
+ *
+ * Pretty-printed text for the jsonb
+ */
+Datum
+jsonb_indent(PG_FUNCTION_ARGS)
+{
+ Jsonb *jb = PG_GETARG_JSONB(0);
+ StringInfo str = makeStringInfo();
+
+ JsonbToCStringIndent(str, &jb->root, VARSIZE(jb));
+
+ PG_RETURN_TEXT_P(cstring_to_text_with_len(str->data, str->len));
+}
+
+
+/*
+ * SQL function jsonb_concat (jsonb, jsonb)
+ *
+ * function for || operator
+ */
+Datum
+jsonb_concat(PG_FUNCTION_ARGS)
+{
+ Jsonb *jb1 = PG_GETARG_JSONB(0);
+ Jsonb *jb2 = PG_GETARG_JSONB(1);
+ Jsonb *out = palloc(VARSIZE(jb1) + VARSIZE(jb2));
+ JsonbParseState *state = NULL;
+ JsonbValue *res;
+ JsonbIterator *it1,
+ *it2;
+
+ /*
+ * If one of the jsonb is empty, just return other.
+ */
+ if (JB_ROOT_COUNT(jb1) == 0)
+ {
+ memcpy(out, jb2, VARSIZE(jb2));
+ PG_RETURN_POINTER(out);
+ }
+ else if (JB_ROOT_COUNT(jb2) == 0)
+ {
+ memcpy(out, jb1, VARSIZE(jb1));
+ PG_RETURN_POINTER(out);
+ }
+
+ it1 = JsonbIteratorInit(&jb1->root);
+ it2 = JsonbIteratorInit(&jb2->root);
+
+ res = IteratorConcat(&it1, &it2, &state);
+
+ if (res == NULL || (res->type == jbvArray && res->val.array.nElems == 0) ||
+ (res->type == jbvObject && res->val.object.nPairs == 0))
+ {
+ SET_VARSIZE(out, VARHDRSZ);
+ }
+ else
+ {
+ if (res->type == jbvArray && res->val.array.nElems > 1)
+ res->val.array.rawScalar = false;
+
+ out = JsonbValueToJsonb(res);
+ }
+
+ PG_RETURN_POINTER(out);
+}
+
+
+/*
+ * SQL function jsonb_delete (jsonb, text)
+ *
+ * return a copy of the jsonb with the indicated item
+ * removed.
+ */
+Datum
+jsonb_delete(PG_FUNCTION_ARGS)
+{
+ Jsonb *in = PG_GETARG_JSONB(0);
+ text *key = PG_GETARG_TEXT_PP(1);
+ char *keyptr = VARDATA_ANY(key);
+ int keylen = VARSIZE_ANY_EXHDR(key);
+ Jsonb *out = palloc(VARSIZE(in));
+ JsonbParseState *state = NULL;
+ JsonbIterator *it;
+ uint32 r;
+ JsonbValue v,
+ *res = NULL;
+ bool skipNested = false;
+
+ SET_VARSIZE(out, VARSIZE(in));
+
+ if (JB_ROOT_COUNT(in) == 0)
+ {
+ PG_RETURN_POINTER(out);
+ }
+
+ it = JsonbIteratorInit(&in->root);
+
+ while ((r = JsonbIteratorNext(&it, &v, skipNested)) != 0)
+ {
+ skipNested = true;
+
+ if ((r == WJB_ELEM || r == WJB_KEY) &&
+ (v.type == jbvString && keylen == v.val.string.len &&
+ memcmp(keyptr, v.val.string.val, keylen) == 0))
+ {
+ if (r == WJB_KEY)
+ {
+ /* skip corresponding value */
+ JsonbIteratorNext(&it, &v, true);
+ }
+
+ continue;
+ }
+
+ res = pushJsonbValue(&state, r, r < WJB_BEGIN_ARRAY ? &v : NULL);
+ }
+
+ if (res == NULL || (res->type == jbvArray && res->val.array.nElems == 0) ||
+ (res->type == jbvObject && res->val.object.nPairs == 0))
+ {
+ SET_VARSIZE(out, VARHDRSZ);
+ }
+ else
+ {
+ out = JsonbValueToJsonb(res);
+ }
+
+ PG_RETURN_POINTER(out);
+}
+
+/*
+ * SQL function jsonb_delete (jsonb, int)
+ *
+ * return a copy of the jsonb with the indicated item
+ * removed. Negative int means count back from the
+ * end of the items.
+ */
+Datum
+jsonb_delete_idx(PG_FUNCTION_ARGS)
+{
+ Jsonb *in = PG_GETARG_JSONB(0);
+ int idx = PG_GETARG_INT32(1);
+ Jsonb *out = palloc(VARSIZE(in));
+ JsonbParseState *state = NULL;
+ JsonbIterator *it;
+ uint32 r,
+ i = 0,
+ n;
+ JsonbValue v,
+ *res = NULL;
+
+ if (JB_ROOT_COUNT(in) == 0)
+ {
+ memcpy(out, in, VARSIZE(in));
+ PG_RETURN_POINTER(out);
+ }
+
+ it = JsonbIteratorInit(&in->root);
+
+ r = JsonbIteratorNext(&it, &v, false);
+ if (r == WJB_BEGIN_ARRAY)
+ n = v.val.array.nElems;
+ else
+ n = v.val.object.nPairs;
+
+ if (idx < 0)
+ {
+ if (-idx > n)
+ idx = n;
+ else
+ idx = n + idx;
+ }
+
+ if (idx >= n)
+ {
+ memcpy(out, in, VARSIZE(in));
+ PG_RETURN_POINTER(out);
+ }
+
+ pushJsonbValue(&state, r, r < WJB_BEGIN_ARRAY ? &v : NULL);
+
+ while ((r = JsonbIteratorNext(&it, &v, true)) != 0)
+ {
+ if (r == WJB_ELEM || r == WJB_KEY)
+ {
+ if (i++ == idx)
+ {
+ if (r == WJB_KEY)
+ JsonbIteratorNext(&it, &v, true); /* skip value */
+ continue;
+ }
+ }
+
+ res = pushJsonbValue(&state, r, r < WJB_BEGIN_ARRAY ? &v : NULL);
+ }
+
+ if (res == NULL || (res->type == jbvArray && res->val.array.nElems == 0) ||
+ (res->type == jbvObject && res->val.object.nPairs == 0))
+ {
+ SET_VARSIZE(out, VARHDRSZ);
+ }
+ else
+ {
+ out = JsonbValueToJsonb(res);
+ }
+
+ PG_RETURN_POINTER(out);
+}
+
+/*
+ * SQL function jsonb_replace(jsonb, text[], jsonb)
+ */
+Datum
+jsonb_replace(PG_FUNCTION_ARGS)
+{
+ Jsonb *in = PG_GETARG_JSONB(0);
+ ArrayType *path = PG_GETARG_ARRAYTYPE_P(1);
+ Jsonb *newval = PG_GETARG_JSONB(2);
+ Jsonb *out = palloc(VARSIZE(in) + VARSIZE(newval));
+ JsonbValue *res = NULL;
+ Datum *path_elems;
+ bool *path_nulls;
+ int path_len;
+ JsonbIterator *it;
+ JsonbParseState *st = NULL;
+
+ /* XXX : why do we need this assertion? The functions is declared to take text[] */
+ Assert(ARR_ELEMTYPE(path) == TEXTOID);
+
+ if (ARR_NDIM(path) > 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR),
+ errmsg("wrong number of array subscripts")));
+
+ if (JB_ROOT_COUNT(in) == 0)
+ {
+ memcpy(out, in, VARSIZE(in));
+ PG_RETURN_POINTER(out);
+ }
+
+ deconstruct_array(path, TEXTOID, -1, false, 'i',
+ &path_elems, &path_nulls, &path_len);
+
+ if (path_len == 0)
+ {
+ memcpy(out, in, VARSIZE(in));
+ PG_RETURN_POINTER(out);
+ }
+
+ it = JsonbIteratorInit(&in->root);
+
+ res = replacePath(&it, path_elems, path_nulls, path_len, &st, 0, newval);
+
+ if (res == NULL)
+ {
+ SET_VARSIZE(out, VARHDRSZ);
+ }
+ else
+ {
+ out = JsonbValueToJsonb(res);
+ }
+
+ PG_RETURN_POINTER(out);
+}
+
+
+/*
+ * SQL function jsonb_delete(jsonb, text[])
+ */
+Datum
+jsonb_delete_path(PG_FUNCTION_ARGS)
+{
+ Jsonb *in = PG_GETARG_JSONB(0);
+ ArrayType *path = PG_GETARG_ARRAYTYPE_P(1);
+ Jsonb *out = palloc(VARSIZE(in));
+ JsonbValue *res = NULL;
+ Datum *path_elems;
+ bool *path_nulls;
+ int path_len;
+ JsonbIterator *it;
+ JsonbParseState *st = NULL;
+
+ /* XXX : why do we need this assertion? The functions is declared to take text[] */
+ Assert(ARR_ELEMTYPE(path) == TEXTOID);
+
+ if (ARR_NDIM(path) > 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR),
+ errmsg("wrong number of array subscripts")));
+
+ if (JB_ROOT_COUNT(in) == 0)
+ {
+ memcpy(out, in, VARSIZE(in));
+ PG_RETURN_POINTER(out);
+ }
+
+ deconstruct_array(path, TEXTOID, -1, false, 'i',
+ &path_elems, &path_nulls, &path_len);
+
+ if (path_len == 0)
+ {
+ memcpy(out, in, VARSIZE(in));
+ PG_RETURN_POINTER(out);
+ }
+
+ it = JsonbIteratorInit(&in->root);
+
+ res = replacePath(&it, path_elems, path_nulls, path_len, &st, 0, NULL);
+
+ if (res == NULL)
+ {
+ SET_VARSIZE(out, VARHDRSZ);
+ }
+ else
+ {
+ out = JsonbValueToJsonb(res);
+ }
+
+ PG_RETURN_POINTER(out);
+}
+
+
+/*
+ * Iterate over all jsonb objects and merge them into one.
+ * The logic of this function copied from the same hstore function,
+ * except the case, when it1 & it2 represents jbvObject.
+ * In that case we just append the content of it2 to it1 without any
+ * verifications.
+ */
+static JsonbValue *
+IteratorConcat(JsonbIterator **it1, JsonbIterator **it2,
+ JsonbParseState **state)
+{
+ uint32 r1,
+ r2,
+ rk1,
+ rk2;
+ JsonbValue v1,
+ v2,
+ *res = NULL;
+
+ r1 = rk1 = JsonbIteratorNext(it1, &v1, false);
+ r2 = rk2 = JsonbIteratorNext(it2, &v2, false);
+
+ /*
+ * Both elements are objects.
+ */
+ if (rk1 == WJB_BEGIN_OBJECT && rk2 == WJB_BEGIN_OBJECT)
+ {
+ int level = 1;
+
+ /*
+ * Append the all tokens from v1 to res, exept last WJB_END_OBJECT
+ * (because res will not be finished yet).
+ */
+ (void) pushJsonbValue(state, r1, NULL);
+ while ((r1 = JsonbIteratorNext(it1, &v1, false)) != 0)
+ {
+ if (r1 == WJB_BEGIN_OBJECT)
+ {
+ ++level;
+ }
+ else if (r1 == WJB_END_OBJECT)
+ {
+ --level;
+ }
+
+ if (level != 0)
+ {
+ res = pushJsonbValue(state, r1, r1 < WJB_BEGIN_ARRAY ? &v1 : NULL);
+ }
+ }
+
+ /*
+ * Append the all tokens from v2 to res, include last WJB_END_OBJECT
+ * (the concatenation will be completed).
+ */
+ while ((r2 = JsonbIteratorNext(it2, &v2, false)) != 0)
+ {
+ res = pushJsonbValue(state, r2, r2 < WJB_BEGIN_ARRAY ? &v2 : NULL);
+ }
+ }
+
+ /*
+ * Both elements are arrays (either can be scalar).
+ */
+ else if (rk1 == WJB_BEGIN_ARRAY && rk2 == WJB_BEGIN_ARRAY)
+ {
+ res = pushJsonbValue(state, r1, NULL);
+ for (;;)
+ {
+ r1 = JsonbIteratorNext(it1, &v1, true);
+ if (r1 == WJB_END_OBJECT || r1 == WJB_END_ARRAY)
+ break;
+ Assert(r1 == WJB_KEY || r1 == WJB_VALUE || r1 == WJB_ELEM);
+ pushJsonbValue(state, r1, &v1);
+ }
+
+ while ((r2 = JsonbIteratorNext(it2, &v2, true)) != 0)
+ {
+ if (!(r2 == WJB_END_OBJECT || r2 == WJB_END_ARRAY))
+ {
+ if (rk1 == WJB_BEGIN_OBJECT)
+ {
+ pushJsonbValue(state, WJB_KEY, NULL);
+ r2 = JsonbIteratorNext(it2, &v2, true);
+ Assert(r2 == WJB_ELEM);
+ pushJsonbValue(state, WJB_VALUE, &v2);
+ }
+ else
+ {
+ pushJsonbValue(state, WJB_ELEM, &v2);
+ }
+ }
+ }
+
+ res = pushJsonbValue(state,
+ (rk1 == WJB_BEGIN_OBJECT) ? WJB_END_OBJECT : WJB_END_ARRAY,
+ NULL /* signal to sort */ );
+ }
+ /* have we got array || object or object || array? */
+ else if (((rk1 == WJB_BEGIN_ARRAY && !(*it1)->isScalar) && rk2 == WJB_BEGIN_OBJECT) ||
+ (rk1 == WJB_BEGIN_OBJECT && (rk2 == WJB_BEGIN_ARRAY && !(*it2)->isScalar)))
+ {
+
+ JsonbIterator **it_array = rk1 == WJB_BEGIN_ARRAY ? it1 : it2;
+ JsonbIterator **it_object = rk1 == WJB_BEGIN_OBJECT ? it1 : it2;
+
+ bool prepend = (rk1 == WJB_BEGIN_OBJECT) ? true : false;
+
+ pushJsonbValue(state, WJB_BEGIN_ARRAY, NULL);
+ if (prepend)
+ {
+ pushJsonbValue(state, WJB_BEGIN_OBJECT, NULL);
+ walkJsonb(it_object, state, false);
+
+ res = walkJsonb(it_array, state, false);
+ }
+ else
+ {
+ walkJsonb(it_array, state, true);
+
+ pushJsonbValue(state, WJB_BEGIN_OBJECT, NULL);
+ walkJsonb(it_object, state, false);
+
+ res = pushJsonbValue(state, WJB_END_ARRAY, NULL);
+ }
+ }
+ else
+ {
+ elog(ERROR, "invalid concatenation of jsonb objects");
+ }
+
+ return res;
+}
+
+/*
+ * copy elements from the iterator to the parse state
+ * stopping at level zero if required.
+ */
+static JsonbValue *
+walkJsonb(JsonbIterator **it, JsonbParseState **state, bool stop_at_level_zero)
+{
+ uint32 r,
+ level = 1;
+ JsonbValue v;
+ JsonbValue *res = NULL;
+
+ while ((r = JsonbIteratorNext(it, &v, false)) != WJB_DONE)
+ {
+ if (r == WJB_BEGIN_OBJECT || r == WJB_BEGIN_ARRAY)
+ {
+ ++level;
+ }
+ else if (r == WJB_END_OBJECT || r == WJB_END_ARRAY)
+ {
+ --level;
+ }
+
+ if (stop_at_level_zero && level == 0)
+ break;
+
+ res = pushJsonbValue(state, r, r < WJB_BEGIN_ARRAY ? &v : NULL);
+ }
+
+ return res;
+}
+
+/*
+ * do most of the heavy work for jsonb_replace
+ */
+static JsonbValue *
+replacePath(JsonbIterator **it, Datum *path_elems,
+ bool *path_nulls, int path_len,
+ JsonbParseState **st, int level, Jsonb *newval)
+{
+ JsonbValue v;
+ JsonbValue *res = NULL;
+ int r;
+
+ r = JsonbIteratorNext(it, &v, false);
+
+ if (r == WJB_BEGIN_ARRAY)
+ {
+ int idx,
+ i;
+ uint32 n = v.val.array.nElems;
+
+ idx = n;
+ if (level >= path_len || path_nulls[level] ||
+ h_atoi(VARDATA_ANY(path_elems[level]),
+ VARSIZE_ANY_EXHDR(path_elems[level]), &idx) == false)
+ {
+ idx = n;
+ }
+ else if (idx < 0)
+ {
+ if (-idx > n)
+ idx = n;
+ else
+ idx = n + idx;
+ }
+
+ if (idx > n)
+ idx = n;
+
+ (void) pushJsonbValue(st, r, NULL);
+
+ for (i = 0; i < n; i++)
+ {
+ if (i == idx && level < path_len)
+ {
+ if (level == path_len - 1)
+ {
+ r = JsonbIteratorNext(it, &v, true); /* skip */
+ if (newval != NULL)
+ addJsonbToParseState(st, newval);
+ }
+ else
+ {
+ replacePath(it, path_elems, path_nulls, path_len,
+ st, level + 1, newval);
+ }
+ }
+ else
+ {
+ r = JsonbIteratorNext(it, &v, false);
+
+ (void) pushJsonbValue(st, r, r < WJB_BEGIN_ARRAY ? &v : NULL);
+
+ if (r == WJB_BEGIN_ARRAY || r == WJB_BEGIN_OBJECT)
+ {
+ int walking_level = 1;
+
+ while (walking_level != 0)
+ {
+ r = JsonbIteratorNext(it, &v, false);
+ if (r == WJB_BEGIN_ARRAY || r == WJB_BEGIN_OBJECT)
+ {
+ ++walking_level;
+ }
+ if (r == WJB_END_ARRAY || r == WJB_END_OBJECT)
+ {
+ --walking_level;
+ }
+ (void) pushJsonbValue(st, r, r < WJB_BEGIN_ARRAY ? &v : NULL);
+ }
+ }
+
+ }
+ }
+
+ r = JsonbIteratorNext(it, &v, false);
+ Assert(r == WJB_END_ARRAY);
+ res = pushJsonbValue(st, r, NULL);
+ }
+ else if (r == WJB_BEGIN_OBJECT)
+ {
+ int i;
+ uint32 n = v.val.object.nPairs;
+ JsonbValue k;
+ bool done = false;
+
+ (void) pushJsonbValue(st, WJB_BEGIN_OBJECT, NULL);
+
+ if (level >= path_len || path_nulls[level])
+ done = true;
+
+ for (i = 0; i < n; i++)
+ {
+ r = JsonbIteratorNext(it, &k, true);
+ Assert(r == WJB_KEY);
+
+ if ( !done &&
+ k.val.string.len == VARSIZE_ANY_EXHDR(path_elems[level]) &&
+ memcmp(k.val.string.val, VARDATA_ANY(path_elems[level]),
+ k.val.string.len) == 0)
+ {
+ if (level == path_len - 1)
+ {
+ r = JsonbIteratorNext(it, &v, true); /* skip */
+ if (newval != NULL)
+ {
+ (void) pushJsonbValue(st, WJB_KEY, &k);
+ addJsonbToParseState(st, newval);
+ }
+ }
+ else
+ {
+ (void) pushJsonbValue(st, r, &k);
+ replacePath(it, path_elems, path_nulls, path_len,
+ st, level + 1, newval);
+ }
+ }
+ else
+ {
+ (void) pushJsonbValue(st, r, &k);
+ r = JsonbIteratorNext(it, &v, false);
+ (void) pushJsonbValue(st, r, r < WJB_BEGIN_ARRAY ? &v : NULL);
+ if (r == WJB_BEGIN_ARRAY || r == WJB_BEGIN_OBJECT)
+ {
+ int walking_level = 1;
+
+ while (walking_level != 0)
+ {
+ r = JsonbIteratorNext(it, &v, false);
+ if (r == WJB_BEGIN_ARRAY || r == WJB_BEGIN_OBJECT)
+ {
+ ++walking_level;
+ }
+ if (r == WJB_END_ARRAY || r == WJB_END_OBJECT)
+ {
+ --walking_level;
+ }
+ (void) pushJsonbValue(st, r, r < WJB_BEGIN_ARRAY ? &v : NULL);
+ }
+ }
+ }
+ }
+
+ r = JsonbIteratorNext(it, &v, true);
+ Assert(r == WJB_END_OBJECT);
+ res = pushJsonbValue(st, r, NULL);
+ }
+ else if (r == WJB_ELEM || r == WJB_VALUE)
+ {
+ res = pushJsonbValue(st, r, &v);
+ }
+ else
+ {
+ elog(PANIC, "impossible state");
+ }
+
+ return res;
+}
+
+/*
+ * get the integer in the argument, if any,
+ * returning a success flag.
+ */
+static bool
+h_atoi(char *c, int l, int *acc)
+{
+ bool negative = false;
+ char *p = c;
+
+ *acc = 0;
+
+ while (isspace(*p) && p - c < l)
+ p++;
+
+ if (p - c >= l)
+ return false;
+
+ if (*p == '-')
+ {
+ negative = true;
+ p++;
+ }
+ else if (*p == '+')
+ {
+ p++;
+ }
+
+ if (p - c >= l)
+ return false;
+
+
+ while (p - c < l)
+ {
+ if (!isdigit(*p))
+ return false;
+
+ *acc *= 10;
+ *acc += (*p - '0');
+ p++;
+ }
+
+ if (negative)
+ *acc = -*acc;
+
+ return true;
+}
diff --git a/src/include/catalog/pg_operator.h b/src/include/catalog/pg_operator.h
index af991d3..57e521b 100644
--- a/src/include/catalog/pg_operator.h
+++ b/src/include/catalog/pg_operator.h
@@ -1808,6 +1808,14 @@ DATA(insert OID = 3249 ( "?&" PGNSP PGUID b f f 3802 1009 16 0 0 jsonb_exist
DESCR("exists all");
DATA(insert OID = 3250 ( "<@" PGNSP PGUID b f f 3802 3802 16 3246 0 jsonb_contained contsel contjoinsel ));
DESCR("is contained by");
+DATA(insert OID = 3277 ( "||" PGNSP PGUID b f f 3802 3802 3802 0 0 jsonb_concat - - ));
+DESCR("concatenate");
+DATA(insert OID = 3278 ( "-" PGNSP PGUID b f f 3802 25 3802 0 0 3388 - - ));
+DESCR("delete");
+DATA(insert OID = 3279 ( "-" PGNSP PGUID b f f 3802 23 3802 0 0 3389 - - ));
+DESCR("delete");
+DATA(insert OID = 3280 ( "-" PGNSP PGUID b f f 3802 1009 3802 0 0 3390 - - ));
+DESCR("delete");
/*
* function prototypes
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 9edfdb8..eaa8362 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -4760,7 +4760,14 @@ DATA(insert OID = 3487 ( gin_consistent_jsonb_path PGNSP PGUID 12 1 0 0 0 f f
DESCR("GIN support");
DATA(insert OID = 3489 ( gin_triconsistent_jsonb_path PGNSP PGUID 12 1 0 0 0 f f f f t f i 7 0 18 "2281 21 2277 23 2281 2281 2281" _null_ _null_ _null_ _null_ gin_triconsistent_jsonb_path _null_ _null_ _null_ ));
DESCR("GIN support");
-
+DATA(insert OID = 3387 ( jsonb_concat PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 3802 "3802 3802" _null_ _null_ _null_ _null_ jsonb_concat _null_ _null_ _null_ ));
+DATA(insert OID = 3388 ( jsonb_delete PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 3802 "3802 25" _null_ _null_ _null_ _null_ jsonb_delete _null_ _null_ _null_ ));
+DATA(insert OID = 3389 ( jsonb_delete PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 3802 "3802 23" _null_ _null_ _null_ _null_ jsonb_delete_idx _null_ _null_ _null_ ));
+DATA(insert OID = 3390 ( jsonb_delete PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 3802 "3802 1009" _null_ _null_ _null_ _null_ jsonb_delete_path _null_ _null_ _null_ ));
+DATA(insert OID = 3391 ( jsonb_replace PGNSP PGUID 12 1 0 0 0 f f f f t f i 3 0 3802 "3802 1009 3802" _null_ _null_ _null_ _null_ jsonb_replace _null_ _null_ _null_ ));
+DESCR("Replace part of a jsonb");
+DATA(insert OID = 3392 ( jsonb_indent PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 25 "3802" _null_ _null_ _null_ _null_ jsonb_indent _null_ _null_ _null_ ));
+DESCR("Indented text from jsonb");
/* txid */
DATA(insert OID = 2939 ( txid_snapshot_in PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 2970 "2275" _null_ _null_ _null_ _null_ txid_snapshot_in _null_ _null_ _null_ ));
DESCR("I/O");
diff --git a/src/include/utils/jsonb.h b/src/include/utils/jsonb.h
index 887eb9b..656668b 100644
--- a/src/include/utils/jsonb.h
+++ b/src/include/utils/jsonb.h
@@ -394,6 +394,19 @@ extern Datum gin_extract_jsonb_query_path(PG_FUNCTION_ARGS);
extern Datum gin_consistent_jsonb_path(PG_FUNCTION_ARGS);
extern Datum gin_triconsistent_jsonb_path(PG_FUNCTION_ARGS);
+/* pretty printer, returns text */
+extern Datum jsonb_indent(PG_FUNCTION_ARGS);
+
+/* concatenation */
+extern Datum jsonb_concat(PG_FUNCTION_ARGS);
+
+/* deletion */
+Datum jsonb_delete(PG_FUNCTION_ARGS);
+Datum jsonb_delete_idx(PG_FUNCTION_ARGS);
+
+/* replacement */
+extern Datum jsonb_replace(PG_FUNCTION_ARGS);
+
/* Support functions */
extern uint32 getJsonbOffset(const JsonbContainer *jc, int index);
extern uint32 getJsonbLength(const JsonbContainer *jc, int index);
@@ -413,8 +426,11 @@ extern bool JsonbDeepContains(JsonbIterator **val,
JsonbIterator **mContained);
extern void JsonbHashScalarValue(const JsonbValue *scalarVal, uint32 *hash);
-/* jsonb.c support function */
+/* jsonb.c support functions */
extern char *JsonbToCString(StringInfo out, JsonbContainer *in,
int estimated_len);
+extern char *JsonbToCStringIndent(StringInfo out, JsonbContainer *in,
+ int estimated_len);
+
#endif /* __JSONB_H__ */
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index 6c6ed95..e8bd5d2 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -2252,7 +2252,7 @@ SELECT count(*) FROM (SELECT j FROM (SELECT * FROM testjsonb UNION ALL SELECT *
894
(1 row)
-SELECT distinct * FROM (values (jsonb '{}' || ''),('{}')) v(j);
+SELECT distinct * FROM (values (jsonb '{}' || ''::text),('{}')) v(j);
j
----
{}
@@ -2729,3 +2729,390 @@ select jsonb_strip_nulls('{"a": {"b": null, "c": null}, "d": {} }');
{"a": {}, "d": {}}
(1 row)
+select jsonb_indent('{"a": "test", "b": [1, 2, 3], "c": "test3", "d":{"dd": "test4", "dd2":{"ddd": "test5"}}}');
+ jsonb_indent
+----------------------------
+ { +
+ "a": "test", +
+ "b": +
+ [ +
+ 1, +
+ 2, +
+ 3 +
+ ], +
+ "c": "test3", +
+ "d": +
+ { +
+ "dd": "test4", +
+ "dd2": +
+ { +
+ "ddd": "test5"+
+ } +
+ } +
+ }
+(1 row)
+
+select jsonb_concat('{"d": "test", "a": [1, 2]}', '{"g": "test2", "c": {"c1":1, "c2":2}}');
+ jsonb_concat
+-------------------------------------------------------------------
+ {"a": [1, 2], "c": {"c1": 1, "c2": 2}, "d": "test", "g": "test2"}
+(1 row)
+
+select '{"aa":1 , "b":2, "cq":3}'::jsonb || '{"cq":"l", "b":"g", "fg":false}';
+ ?column?
+---------------------------------------------
+ {"b": "g", "aa": 1, "cq": "l", "fg": false}
+(1 row)
+
+select '{"aa":1 , "b":2, "cq":3}'::jsonb || '{"aq":"l"}';
+ ?column?
+---------------------------------------
+ {"b": 2, "aa": 1, "aq": "l", "cq": 3}
+(1 row)
+
+select '{"aa":1 , "b":2, "cq":3}'::jsonb || '{"aa":"l"}';
+ ?column?
+------------------------------
+ {"b": 2, "aa": "l", "cq": 3}
+(1 row)
+
+select '{"aa":1 , "b":2, "cq":3}'::jsonb || '{}';
+ ?column?
+----------------------------
+ {"b": 2, "aa": 1, "cq": 3}
+(1 row)
+
+select '["a", "b"]'::jsonb || '["c"]';
+ ?column?
+-----------------
+ ["a", "b", "c"]
+(1 row)
+
+select '["a", "b"]'::jsonb || '["c", "d"]';
+ ?column?
+----------------------
+ ["a", "b", "c", "d"]
+(1 row)
+
+select '["c"]' || '["a", "b"]'::jsonb;
+ ?column?
+-----------------
+ ["c", "a", "b"]
+(1 row)
+
+select '["a", "b"]'::jsonb || '"c"';
+ ?column?
+-----------------
+ ["a", "b", "c"]
+(1 row)
+
+select '"c"' || '["a", "b"]'::jsonb;
+ ?column?
+-----------------
+ ["c", "a", "b"]
+(1 row)
+
+select '"a"'::jsonb || '{"a":1}';
+ERROR: invalid concatenation of jsonb objects
+select '{"a":1}' || '"a"'::jsonb;
+ERROR: invalid concatenation of jsonb objects
+select '["a", "b"]'::jsonb || '{"c":1}';
+ ?column?
+----------------------
+ ["a", "b", {"c": 1}]
+(1 row)
+
+select '{"c": 1}'::jsonb || '["a", "b"]';
+ ?column?
+----------------------
+ [{"c": 1}, "a", "b"]
+(1 row)
+
+select '{}'::jsonb || '{"cq":"l", "b":"g", "fg":false}';
+ ?column?
+------------------------------------
+ {"b": "g", "cq": "l", "fg": false}
+(1 row)
+
+select pg_column_size('{}'::jsonb || '{}'::jsonb) = pg_column_size('{}'::jsonb);
+ ?column?
+----------
+ t
+(1 row)
+
+select pg_column_size('{"aa":1}'::jsonb || '{"b":2}'::jsonb) = pg_column_size('{"aa":1, "b":2}'::jsonb);
+ ?column?
+----------
+ t
+(1 row)
+
+select pg_column_size('{"aa":1, "b":2}'::jsonb || '{}'::jsonb) = pg_column_size('{"aa":1, "b":2}'::jsonb);
+ ?column?
+----------
+ t
+(1 row)
+
+select pg_column_size('{}'::jsonb || '{"aa":1, "b":2}'::jsonb) = pg_column_size('{"aa":1, "b":2}'::jsonb);
+ ?column?
+----------
+ t
+(1 row)
+
+select jsonb_delete('{"a":1 , "b":2, "c":3}'::jsonb, 'a');
+ jsonb_delete
+------------------
+ {"b": 2, "c": 3}
+(1 row)
+
+select jsonb_delete('{"a":null , "b":2, "c":3}'::jsonb, 'a');
+ jsonb_delete
+------------------
+ {"b": 2, "c": 3}
+(1 row)
+
+select jsonb_delete('{"a":1 , "b":2, "c":3}'::jsonb, 'b');
+ jsonb_delete
+------------------
+ {"a": 1, "c": 3}
+(1 row)
+
+select jsonb_delete('{"a":1 , "b":2, "c":3}'::jsonb, 'c');
+ jsonb_delete
+------------------
+ {"a": 1, "b": 2}
+(1 row)
+
+select jsonb_delete('{"a":1 , "b":2, "c":3}'::jsonb, 'd');
+ jsonb_delete
+--------------------------
+ {"a": 1, "b": 2, "c": 3}
+(1 row)
+
+select '{"a":1 , "b":2, "c":3}'::jsonb - 'a'::text;
+ ?column?
+------------------
+ {"b": 2, "c": 3}
+(1 row)
+
+select '{"a":null , "b":2, "c":3}'::jsonb - 'a'::text;
+ ?column?
+------------------
+ {"b": 2, "c": 3}
+(1 row)
+
+select '{"a":1 , "b":2, "c":3}'::jsonb - 'b'::text;
+ ?column?
+------------------
+ {"a": 1, "c": 3}
+(1 row)
+
+select '{"a":1 , "b":2, "c":3}'::jsonb - 'c'::text;
+ ?column?
+------------------
+ {"a": 1, "b": 2}
+(1 row)
+
+select '{"a":1 , "b":2, "c":3}'::jsonb - 'd'::text;
+ ?column?
+--------------------------
+ {"a": 1, "b": 2, "c": 3}
+(1 row)
+
+select pg_column_size('{"a":1 , "b":2, "c":3}'::jsonb - 'b'::text) = pg_column_size('{"a":1, "b":2}'::jsonb);
+ ?column?
+----------
+ t
+(1 row)
+
+select '["a","b","c"]'::jsonb - 3;
+ ?column?
+-----------------
+ ["a", "b", "c"]
+(1 row)
+
+select '["a","b","c"]'::jsonb - 2;
+ ?column?
+------------
+ ["a", "b"]
+(1 row)
+
+select '["a","b","c"]'::jsonb - 1;
+ ?column?
+------------
+ ["a", "c"]
+(1 row)
+
+select '["a","b","c"]'::jsonb - 0;
+ ?column?
+------------
+ ["b", "c"]
+(1 row)
+
+select '["a","b","c"]'::jsonb - -1;
+ ?column?
+------------
+ ["a", "b"]
+(1 row)
+
+select '["a","b","c"]'::jsonb - -2;
+ ?column?
+------------
+ ["a", "c"]
+(1 row)
+
+select '["a","b","c"]'::jsonb - -3;
+ ?column?
+------------
+ ["b", "c"]
+(1 row)
+
+select '["a","b","c"]'::jsonb - -4;
+ ?column?
+-----------------
+ ["a", "b", "c"]
+(1 row)
+
+select '{"a":1, "b":2, "c":3}'::jsonb - 3;
+ ?column?
+--------------------------
+ {"a": 1, "b": 2, "c": 3}
+(1 row)
+
+select '{"a":1, "b":2, "c":3}'::jsonb - 2;
+ ?column?
+------------------
+ {"a": 1, "b": 2}
+(1 row)
+
+select '{"a":1, "b":2, "c":3}'::jsonb - 1;
+ ?column?
+------------------
+ {"a": 1, "c": 3}
+(1 row)
+
+select '{"a":1, "b":2, "c":3}'::jsonb - 0;
+ ?column?
+------------------
+ {"b": 2, "c": 3}
+(1 row)
+
+select '{"a":1, "b":2, "c":3}'::jsonb - -1;
+ ?column?
+------------------
+ {"a": 1, "b": 2}
+(1 row)
+
+select '{"a":1, "b":2, "c":3}'::jsonb - -2;
+ ?column?
+------------------
+ {"a": 1, "c": 3}
+(1 row)
+
+select '{"a":1, "b":2, "c":3}'::jsonb - -3;
+ ?column?
+------------------
+ {"b": 2, "c": 3}
+(1 row)
+
+select '{"a":1, "b":2, "c":3}'::jsonb - -4;
+ ?column?
+--------------------------
+ {"a": 1, "b": 2, "c": 3}
+(1 row)
+
+select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{n}', '[1,2,3]');
+ jsonb_replace
+--------------------------------------------------------------------------
+ {"a": 1, "b": [1, 2], "c": {"1": 2}, "d": {"1": [2, 3]}, "n": [1, 2, 3]}
+(1 row)
+
+select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{b,-1}', '[1,2,3]');
+ jsonb_replace
+-----------------------------------------------------------------------------
+ {"a": 1, "b": [1, [1, 2, 3]], "c": {"1": 2}, "d": {"1": [2, 3]}, "n": null}
+(1 row)
+
+select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{d,1,0}', '[1,2,3]');
+ jsonb_replace
+-----------------------------------------------------------------------------
+ {"a": 1, "b": [1, 2], "c": {"1": 2}, "d": {"1": [[1, 2, 3], 3]}, "n": null}
+(1 row)
+
+select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{d,NULL,0}', '[1,2,3]');
+ jsonb_replace
+---------------------------------------------------------------------
+ {"a": 1, "b": [1, 2], "c": {"1": 2}, "d": {"1": [2, 3]}, "n": null}
+(1 row)
+
+select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{n}', '{"1": 2}');
+ jsonb_replace
+-------------------------------------------------------------------------
+ {"a": 1, "b": [1, 2], "c": {"1": 2}, "d": {"1": [2, 3]}, "n": {"1": 2}}
+(1 row)
+
+select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{b,-1}', '{"1": 2}');
+ jsonb_replace
+----------------------------------------------------------------------------
+ {"a": 1, "b": [1, {"1": 2}], "c": {"1": 2}, "d": {"1": [2, 3]}, "n": null}
+(1 row)
+
+select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{d,1,0}', '{"1": 2}');
+ jsonb_replace
+----------------------------------------------------------------------------
+ {"a": 1, "b": [1, 2], "c": {"1": 2}, "d": {"1": [{"1": 2}, 3]}, "n": null}
+(1 row)
+
+select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{d,NULL,0}', '{"1": 2}');
+ jsonb_replace
+---------------------------------------------------------------------
+ {"a": 1, "b": [1, 2], "c": {"1": 2}, "d": {"1": [2, 3]}, "n": null}
+(1 row)
+
+select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{b,-1}', '"test"');
+ jsonb_replace
+--------------------------------------------------------------------------
+ {"a": 1, "b": [1, "test"], "c": {"1": 2}, "d": {"1": [2, 3]}, "n": null}
+(1 row)
+
+select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{b,-1}', '{"f": "test"}');
+ jsonb_replace
+---------------------------------------------------------------------------------
+ {"a": 1, "b": [1, {"f": "test"}], "c": {"1": 2}, "d": {"1": [2, 3]}, "n": null}
+(1 row)
+
+select jsonb_delete('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}', '{n}'::text[]);
+ jsonb_delete
+----------------------------------------------------------
+ {"a": 1, "b": [1, 2], "c": {"1": 2}, "d": {"1": [2, 3]}}
+(1 row)
+
+select jsonb_delete('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}', '{b,-1}'::text[]);
+ jsonb_delete
+------------------------------------------------------------------
+ {"a": 1, "b": [1], "c": {"1": 2}, "d": {"1": [2, 3]}, "n": null}
+(1 row)
+
+select jsonb_delete('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}', '{d,1,0}'::text[]);
+ jsonb_delete
+------------------------------------------------------------------
+ {"a": 1, "b": [1, 2], "c": {"1": 2}, "d": {"1": [3]}, "n": null}
+(1 row)
+
+select '{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb - '{n}'::text[];
+ ?column?
+----------------------------------------------------------
+ {"a": 1, "b": [1, 2], "c": {"1": 2}, "d": {"1": [2, 3]}}
+(1 row)
+
+select '{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb - '{b,-1}'::text[];
+ ?column?
+------------------------------------------------------------------
+ {"a": 1, "b": [1], "c": {"1": 2}, "d": {"1": [2, 3]}, "n": null}
+(1 row)
+
+select '{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb - '{d,1,0}'::text[];
+ ?column?
+------------------------------------------------------------------
+ {"a": 1, "b": [1, 2], "c": {"1": 2}, "d": {"1": [3]}, "n": null}
+(1 row)
+
diff --git a/src/test/regress/expected/jsonb_1.out b/src/test/regress/expected/jsonb_1.out
index f30148d..3eba934 100644
--- a/src/test/regress/expected/jsonb_1.out
+++ b/src/test/regress/expected/jsonb_1.out
@@ -2252,7 +2252,7 @@ SELECT count(*) FROM (SELECT j FROM (SELECT * FROM testjsonb UNION ALL SELECT *
894
(1 row)
-SELECT distinct * FROM (values (jsonb '{}' || ''),('{}')) v(j);
+SELECT distinct * FROM (values (jsonb '{}' || ''::text),('{}')) v(j);
j
----
{}
@@ -2729,3 +2729,390 @@ select jsonb_strip_nulls('{"a": {"b": null, "c": null}, "d": {} }');
{"a": {}, "d": {}}
(1 row)
+select jsonb_indent('{"a": "test", "b": [1, 2, 3], "c": "test3", "d":{"dd": "test4", "dd2":{"ddd": "test5"}}}');
+ jsonb_indent
+----------------------------
+ { +
+ "a": "test", +
+ "b": +
+ [ +
+ 1, +
+ 2, +
+ 3 +
+ ], +
+ "c": "test3", +
+ "d": +
+ { +
+ "dd": "test4", +
+ "dd2": +
+ { +
+ "ddd": "test5"+
+ } +
+ } +
+ }
+(1 row)
+
+select jsonb_concat('{"d": "test", "a": [1, 2]}', '{"g": "test2", "c": {"c1":1, "c2":2}}');
+ jsonb_concat
+-------------------------------------------------------------------
+ {"a": [1, 2], "c": {"c1": 1, "c2": 2}, "d": "test", "g": "test2"}
+(1 row)
+
+select '{"aa":1 , "b":2, "cq":3}'::jsonb || '{"cq":"l", "b":"g", "fg":false}';
+ ?column?
+---------------------------------------------
+ {"b": "g", "aa": 1, "cq": "l", "fg": false}
+(1 row)
+
+select '{"aa":1 , "b":2, "cq":3}'::jsonb || '{"aq":"l"}';
+ ?column?
+---------------------------------------
+ {"b": 2, "aa": 1, "aq": "l", "cq": 3}
+(1 row)
+
+select '{"aa":1 , "b":2, "cq":3}'::jsonb || '{"aa":"l"}';
+ ?column?
+------------------------------
+ {"b": 2, "aa": "l", "cq": 3}
+(1 row)
+
+select '{"aa":1 , "b":2, "cq":3}'::jsonb || '{}';
+ ?column?
+----------------------------
+ {"b": 2, "aa": 1, "cq": 3}
+(1 row)
+
+select '["a", "b"]'::jsonb || '["c"]';
+ ?column?
+-----------------
+ ["a", "b", "c"]
+(1 row)
+
+select '["a", "b"]'::jsonb || '["c", "d"]';
+ ?column?
+----------------------
+ ["a", "b", "c", "d"]
+(1 row)
+
+select '["c"]' || '["a", "b"]'::jsonb;
+ ?column?
+-----------------
+ ["c", "a", "b"]
+(1 row)
+
+select '["a", "b"]'::jsonb || '"c"';
+ ?column?
+-----------------
+ ["a", "b", "c"]
+(1 row)
+
+select '"c"' || '["a", "b"]'::jsonb;
+ ?column?
+-----------------
+ ["c", "a", "b"]
+(1 row)
+
+select '"a"'::jsonb || '{"a":1}';
+ERROR: invalid concatenation of jsonb objects
+select '{"a":1}' || '"a"'::jsonb;
+ERROR: invalid concatenation of jsonb objects
+select '["a", "b"]'::jsonb || '{"c":1}';
+ ?column?
+----------------------
+ ["a", "b", {"c": 1}]
+(1 row)
+
+select '{"c": 1}'::jsonb || '["a", "b"]';
+ ?column?
+----------------------
+ [{"c": 1}, "a", "b"]
+(1 row)
+
+select '{}'::jsonb || '{"cq":"l", "b":"g", "fg":false}';
+ ?column?
+------------------------------------
+ {"b": "g", "cq": "l", "fg": false}
+(1 row)
+
+select pg_column_size('{}'::jsonb || '{}'::jsonb) = pg_column_size('{}'::jsonb);
+ ?column?
+----------
+ t
+(1 row)
+
+select pg_column_size('{"aa":1}'::jsonb || '{"b":2}'::jsonb) = pg_column_size('{"aa":1, "b":2}'::jsonb);
+ ?column?
+----------
+ t
+(1 row)
+
+select pg_column_size('{"aa":1, "b":2}'::jsonb || '{}'::jsonb) = pg_column_size('{"aa":1, "b":2}'::jsonb);
+ ?column?
+----------
+ t
+(1 row)
+
+select pg_column_size('{}'::jsonb || '{"aa":1, "b":2}'::jsonb) = pg_column_size('{"aa":1, "b":2}'::jsonb);
+ ?column?
+----------
+ t
+(1 row)
+
+select jsonb_delete('{"a":1 , "b":2, "c":3}'::jsonb, 'a');
+ jsonb_delete
+------------------
+ {"b": 2, "c": 3}
+(1 row)
+
+select jsonb_delete('{"a":null , "b":2, "c":3}'::jsonb, 'a');
+ jsonb_delete
+------------------
+ {"b": 2, "c": 3}
+(1 row)
+
+select jsonb_delete('{"a":1 , "b":2, "c":3}'::jsonb, 'b');
+ jsonb_delete
+------------------
+ {"a": 1, "c": 3}
+(1 row)
+
+select jsonb_delete('{"a":1 , "b":2, "c":3}'::jsonb, 'c');
+ jsonb_delete
+------------------
+ {"a": 1, "b": 2}
+(1 row)
+
+select jsonb_delete('{"a":1 , "b":2, "c":3}'::jsonb, 'd');
+ jsonb_delete
+--------------------------
+ {"a": 1, "b": 2, "c": 3}
+(1 row)
+
+select '{"a":1 , "b":2, "c":3}'::jsonb - 'a'::text;
+ ?column?
+------------------
+ {"b": 2, "c": 3}
+(1 row)
+
+select '{"a":null , "b":2, "c":3}'::jsonb - 'a'::text;
+ ?column?
+------------------
+ {"b": 2, "c": 3}
+(1 row)
+
+select '{"a":1 , "b":2, "c":3}'::jsonb - 'b'::text;
+ ?column?
+------------------
+ {"a": 1, "c": 3}
+(1 row)
+
+select '{"a":1 , "b":2, "c":3}'::jsonb - 'c'::text;
+ ?column?
+------------------
+ {"a": 1, "b": 2}
+(1 row)
+
+select '{"a":1 , "b":2, "c":3}'::jsonb - 'd'::text;
+ ?column?
+--------------------------
+ {"a": 1, "b": 2, "c": 3}
+(1 row)
+
+select pg_column_size('{"a":1 , "b":2, "c":3}'::jsonb - 'b'::text) = pg_column_size('{"a":1, "b":2}'::jsonb);
+ ?column?
+----------
+ t
+(1 row)
+
+select '["a","b","c"]'::jsonb - 3;
+ ?column?
+-----------------
+ ["a", "b", "c"]
+(1 row)
+
+select '["a","b","c"]'::jsonb - 2;
+ ?column?
+------------
+ ["a", "b"]
+(1 row)
+
+select '["a","b","c"]'::jsonb - 1;
+ ?column?
+------------
+ ["a", "c"]
+(1 row)
+
+select '["a","b","c"]'::jsonb - 0;
+ ?column?
+------------
+ ["b", "c"]
+(1 row)
+
+select '["a","b","c"]'::jsonb - -1;
+ ?column?
+------------
+ ["a", "b"]
+(1 row)
+
+select '["a","b","c"]'::jsonb - -2;
+ ?column?
+------------
+ ["a", "c"]
+(1 row)
+
+select '["a","b","c"]'::jsonb - -3;
+ ?column?
+------------
+ ["b", "c"]
+(1 row)
+
+select '["a","b","c"]'::jsonb - -4;
+ ?column?
+-----------------
+ ["a", "b", "c"]
+(1 row)
+
+select '{"a":1, "b":2, "c":3}'::jsonb - 3;
+ ?column?
+--------------------------
+ {"a": 1, "b": 2, "c": 3}
+(1 row)
+
+select '{"a":1, "b":2, "c":3}'::jsonb - 2;
+ ?column?
+------------------
+ {"a": 1, "b": 2}
+(1 row)
+
+select '{"a":1, "b":2, "c":3}'::jsonb - 1;
+ ?column?
+------------------
+ {"a": 1, "c": 3}
+(1 row)
+
+select '{"a":1, "b":2, "c":3}'::jsonb - 0;
+ ?column?
+------------------
+ {"b": 2, "c": 3}
+(1 row)
+
+select '{"a":1, "b":2, "c":3}'::jsonb - -1;
+ ?column?
+------------------
+ {"a": 1, "b": 2}
+(1 row)
+
+select '{"a":1, "b":2, "c":3}'::jsonb - -2;
+ ?column?
+------------------
+ {"a": 1, "c": 3}
+(1 row)
+
+select '{"a":1, "b":2, "c":3}'::jsonb - -3;
+ ?column?
+------------------
+ {"b": 2, "c": 3}
+(1 row)
+
+select '{"a":1, "b":2, "c":3}'::jsonb - -4;
+ ?column?
+--------------------------
+ {"a": 1, "b": 2, "c": 3}
+(1 row)
+
+select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{n}', '[1,2,3]');
+ jsonb_replace
+--------------------------------------------------------------------------
+ {"a": 1, "b": [1, 2], "c": {"1": 2}, "d": {"1": [2, 3]}, "n": [1, 2, 3]}
+(1 row)
+
+select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{b,-1}', '[1,2,3]');
+ jsonb_replace
+-----------------------------------------------------------------------------
+ {"a": 1, "b": [1, [1, 2, 3]], "c": {"1": 2}, "d": {"1": [2, 3]}, "n": null}
+(1 row)
+
+select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{d,1,0}', '[1,2,3]');
+ jsonb_replace
+-----------------------------------------------------------------------------
+ {"a": 1, "b": [1, 2], "c": {"1": 2}, "d": {"1": [[1, 2, 3], 3]}, "n": null}
+(1 row)
+
+select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{d,NULL,0}', '[1,2,3]');
+ jsonb_replace
+---------------------------------------------------------------------
+ {"a": 1, "b": [1, 2], "c": {"1": 2}, "d": {"1": [2, 3]}, "n": null}
+(1 row)
+
+select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{n}', '{"1": 2}');
+ jsonb_replace
+-------------------------------------------------------------------------
+ {"a": 1, "b": [1, 2], "c": {"1": 2}, "d": {"1": [2, 3]}, "n": {"1": 2}}
+(1 row)
+
+select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{b,-1}', '{"1": 2}');
+ jsonb_replace
+----------------------------------------------------------------------------
+ {"a": 1, "b": [1, {"1": 2}], "c": {"1": 2}, "d": {"1": [2, 3]}, "n": null}
+(1 row)
+
+select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{d,1,0}', '{"1": 2}');
+ jsonb_replace
+----------------------------------------------------------------------------
+ {"a": 1, "b": [1, 2], "c": {"1": 2}, "d": {"1": [{"1": 2}, 3]}, "n": null}
+(1 row)
+
+select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{d,NULL,0}', '{"1": 2}');
+ jsonb_replace
+---------------------------------------------------------------------
+ {"a": 1, "b": [1, 2], "c": {"1": 2}, "d": {"1": [2, 3]}, "n": null}
+(1 row)
+
+select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{b,-1}', '"test"');
+ jsonb_replace
+--------------------------------------------------------------------------
+ {"a": 1, "b": [1, "test"], "c": {"1": 2}, "d": {"1": [2, 3]}, "n": null}
+(1 row)
+
+select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{b,-1}', '{"f": "test"}');
+ jsonb_replace
+---------------------------------------------------------------------------------
+ {"a": 1, "b": [1, {"f": "test"}], "c": {"1": 2}, "d": {"1": [2, 3]}, "n": null}
+(1 row)
+
+select jsonb_delete('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}', '{n}'::text[]);
+ jsonb_delete
+----------------------------------------------------------
+ {"a": 1, "b": [1, 2], "c": {"1": 2}, "d": {"1": [2, 3]}}
+(1 row)
+
+select jsonb_delete('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}', '{b,-1}'::text[]);
+ jsonb_delete
+------------------------------------------------------------------
+ {"a": 1, "b": [1], "c": {"1": 2}, "d": {"1": [2, 3]}, "n": null}
+(1 row)
+
+select jsonb_delete('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}', '{d,1,0}'::text[]);
+ jsonb_delete
+------------------------------------------------------------------
+ {"a": 1, "b": [1, 2], "c": {"1": 2}, "d": {"1": [3]}, "n": null}
+(1 row)
+
+select '{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb - '{n}'::text[];
+ ?column?
+----------------------------------------------------------
+ {"a": 1, "b": [1, 2], "c": {"1": 2}, "d": {"1": [2, 3]}}
+(1 row)
+
+select '{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb - '{b,-1}'::text[];
+ ?column?
+------------------------------------------------------------------
+ {"a": 1, "b": [1], "c": {"1": 2}, "d": {"1": [2, 3]}, "n": null}
+(1 row)
+
+select '{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb - '{d,1,0}'::text[];
+ ?column?
+------------------------------------------------------------------
+ {"a": 1, "b": [1, 2], "c": {"1": 2}, "d": {"1": [3]}, "n": null}
+(1 row)
+
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index 53cc239..d642e09 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -551,7 +551,7 @@ SELECT count(*) FROM (SELECT j FROM (SELECT * FROM testjsonb UNION ALL SELECT *
SET enable_hashagg = on;
SET enable_sort = off;
SELECT count(*) FROM (SELECT j FROM (SELECT * FROM testjsonb UNION ALL SELECT * FROM testjsonb) js GROUP BY j) js2;
-SELECT distinct * FROM (values (jsonb '{}' || ''),('{}')) v(j);
+SELECT distinct * FROM (values (jsonb '{}' || ''::text),('{}')) v(j);
SET enable_sort = on;
RESET enable_hashagg;
@@ -678,3 +678,84 @@ select jsonb_strip_nulls('[1,{"a":1,"b":null,"c":2},3]');
-- an empty object is not null and should not be stripped
select jsonb_strip_nulls('{"a": {"b": null, "c": null}, "d": {} }');
+
+
+select jsonb_indent('{"a": "test", "b": [1, 2, 3], "c": "test3", "d":{"dd": "test4", "dd2":{"ddd": "test5"}}}');
+
+select jsonb_concat('{"d": "test", "a": [1, 2]}', '{"g": "test2", "c": {"c1":1, "c2":2}}');
+
+select '{"aa":1 , "b":2, "cq":3}'::jsonb || '{"cq":"l", "b":"g", "fg":false}';
+select '{"aa":1 , "b":2, "cq":3}'::jsonb || '{"aq":"l"}';
+select '{"aa":1 , "b":2, "cq":3}'::jsonb || '{"aa":"l"}';
+select '{"aa":1 , "b":2, "cq":3}'::jsonb || '{}';
+
+select '["a", "b"]'::jsonb || '["c"]';
+select '["a", "b"]'::jsonb || '["c", "d"]';
+select '["c"]' || '["a", "b"]'::jsonb;
+
+select '["a", "b"]'::jsonb || '"c"';
+select '"c"' || '["a", "b"]'::jsonb;
+
+select '"a"'::jsonb || '{"a":1}';
+select '{"a":1}' || '"a"'::jsonb;
+
+select '["a", "b"]'::jsonb || '{"c":1}';
+select '{"c": 1}'::jsonb || '["a", "b"]';
+
+select '{}'::jsonb || '{"cq":"l", "b":"g", "fg":false}';
+
+select pg_column_size('{}'::jsonb || '{}'::jsonb) = pg_column_size('{}'::jsonb);
+select pg_column_size('{"aa":1}'::jsonb || '{"b":2}'::jsonb) = pg_column_size('{"aa":1, "b":2}'::jsonb);
+select pg_column_size('{"aa":1, "b":2}'::jsonb || '{}'::jsonb) = pg_column_size('{"aa":1, "b":2}'::jsonb);
+select pg_column_size('{}'::jsonb || '{"aa":1, "b":2}'::jsonb) = pg_column_size('{"aa":1, "b":2}'::jsonb);
+
+select jsonb_delete('{"a":1 , "b":2, "c":3}'::jsonb, 'a');
+select jsonb_delete('{"a":null , "b":2, "c":3}'::jsonb, 'a');
+select jsonb_delete('{"a":1 , "b":2, "c":3}'::jsonb, 'b');
+select jsonb_delete('{"a":1 , "b":2, "c":3}'::jsonb, 'c');
+select jsonb_delete('{"a":1 , "b":2, "c":3}'::jsonb, 'd');
+select '{"a":1 , "b":2, "c":3}'::jsonb - 'a'::text;
+select '{"a":null , "b":2, "c":3}'::jsonb - 'a'::text;
+select '{"a":1 , "b":2, "c":3}'::jsonb - 'b'::text;
+select '{"a":1 , "b":2, "c":3}'::jsonb - 'c'::text;
+select '{"a":1 , "b":2, "c":3}'::jsonb - 'd'::text;
+select pg_column_size('{"a":1 , "b":2, "c":3}'::jsonb - 'b'::text) = pg_column_size('{"a":1, "b":2}'::jsonb);
+
+select '["a","b","c"]'::jsonb - 3;
+select '["a","b","c"]'::jsonb - 2;
+select '["a","b","c"]'::jsonb - 1;
+select '["a","b","c"]'::jsonb - 0;
+select '["a","b","c"]'::jsonb - -1;
+select '["a","b","c"]'::jsonb - -2;
+select '["a","b","c"]'::jsonb - -3;
+select '["a","b","c"]'::jsonb - -4;
+
+select '{"a":1, "b":2, "c":3}'::jsonb - 3;
+select '{"a":1, "b":2, "c":3}'::jsonb - 2;
+select '{"a":1, "b":2, "c":3}'::jsonb - 1;
+select '{"a":1, "b":2, "c":3}'::jsonb - 0;
+select '{"a":1, "b":2, "c":3}'::jsonb - -1;
+select '{"a":1, "b":2, "c":3}'::jsonb - -2;
+select '{"a":1, "b":2, "c":3}'::jsonb - -3;
+select '{"a":1, "b":2, "c":3}'::jsonb - -4;
+
+select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{n}', '[1,2,3]');
+select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{b,-1}', '[1,2,3]');
+select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{d,1,0}', '[1,2,3]');
+select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{d,NULL,0}', '[1,2,3]');
+
+select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{n}', '{"1": 2}');
+select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{b,-1}', '{"1": 2}');
+select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{d,1,0}', '{"1": 2}');
+select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{d,NULL,0}', '{"1": 2}');
+
+select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{b,-1}', '"test"');
+select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{b,-1}', '{"f": "test"}');
+
+select jsonb_delete('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}', '{n}'::text[]);
+select jsonb_delete('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}', '{b,-1}'::text[]);
+select jsonb_delete('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}', '{d,1,0}'::text[]);
+
+select '{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb - '{n}'::text[];
+select '{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb - '{b,-1}'::text[];
+select '{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb - '{d,1,0}'::text[];
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers