On 27/04/15 18:46, Petr Jelinek wrote:
On 18/04/15 20:35, Dmitry Dolgov wrote:
Sorry for late reply. Here is a slightly improved version of the patch
with the new `h_atoi` function, I hope this implementation will be more
appropriate.
It's better, but a) I don't like the name of the function b) I don't see
why we need the function at all given that it's called from only one
place and is effectively couple lines of code.
In general I wonder if it wouldn't be better to split the replacePath
into 3 functions, one replacePath, one replacePathObject,
replacePathArray and call those Object/Array ones from the replacePath
and inlining the h_atoi code into the Array one. If this was done then
it would make also sense to change the main if/else in replacePath into
a switch.
Another thing I noticed now when reading the code again - you should not
be using braces when you only have one command in the code-block.
Hi,
I worked this over a bit (I hope Dmitry won't mind) and I am now more or
less happy with the patch. Here is list of changes I made:
- rebased to todays master (Oid conflicts, transforms patch conflicts)
- changed the main if/else if/else if/else to switch in replacePath
- split the replacePath into 3 functions (main one plus 2 helpers for
Object and Array)
- removed the h_atoi and use the strtol directly
- renamed jsonb_indent to jsonb_pretty because we use "pretty" for
similar use-case everywhere else
- fixed whitespace/brackets where needed
- added/reworded some comments and couple of lines in docs
I think it's ready for Andrew now.
--
Petr Jelinek http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
From b2da4958fc75e7c7009340e6b2e4ccb155632078 Mon Sep 17 00:00:00 2001
From: Petr Jelinek <pjmodos@pjmodos.net>
Date: Wed, 29 Apr 2015 22:54:01 +0200
Subject: [PATCH] jsonbxcore5
---
doc/src/sgml/func.sgml | 62 +++
src/backend/utils/adt/jsonb.c | 81 +++-
src/backend/utils/adt/jsonfuncs.c | 709 ++++++++++++++++++++++++++++++++++
src/include/catalog/pg_operator.h | 8 +
src/include/catalog/pg_proc.h | 9 +-
src/include/utils/jsonb.h | 19 +-
src/test/regress/expected/jsonb.out | 424 +++++++++++++++++++-
src/test/regress/expected/jsonb_1.out | 424 +++++++++++++++++++-
src/test/regress/sql/jsonb.sql | 85 +++-
9 files changed, 1805 insertions(+), 16 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index dcade93..f0d8d96 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -10257,6 +10257,32 @@ 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 two jsonb values into a new jsonb 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 a specified 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 specified 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 specified path</entry>
+ <entry><literal>'["a", {"b":1}]'::jsonb - '{1,b}'::text[] </literal></entry>
+ </row>
</tbody>
</tgroup>
</table>
@@ -10767,6 +10793,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_pretty(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_pretty('[{"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 7e2c359..bccc669 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -85,6 +85,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
@@ -422,12 +424,39 @@ 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;
JsonbIteratorToken type = WJB_DONE;
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;
+ bool last_was_key = false;
if (out == NULL)
out = makeStringInfo();
@@ -444,26 +473,36 @@ JsonbToCString(StringInfo out, JsonbContainer *in, int estimated_len)
{
case WJB_BEGIN_ARRAY:
if (!first)
- appendBinaryStringInfo(out, ", ", 2);
- first = true;
+ appendBinaryStringInfo(out, ", ", ispaces);
if (!v.val.array.rawScalar)
- appendStringInfoChar(out, '[');
+ {
+ add_indent(out, use_indent && !last_was_key, level);
+ appendStringInfoCharMacro(out, '[');
+ }
+ else
+ raw_scalar = true;
+
+ first = true;
level++;
break;
case WJB_BEGIN_OBJECT:
if (!first)
- appendBinaryStringInfo(out, ", ", 2);
- first = true;
+ appendBinaryStringInfo(out, ", ", ispaces);
+
+ add_indent(out, use_indent && !last_was_key, level);
appendStringInfoCharMacro(out, '{');
+ first = true;
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);
@@ -488,26 +527,33 @@ 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 jsonb iterator token type");
}
+ use_indent = indent;
+ last_was_key = redo_switch;
}
Assert(level == 0);
@@ -515,6 +561,19 @@ JsonbToCString(StringInfo out, JsonbContainer *in, int estimated_len)
return out->data;
}
+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 274f64c..bd71135 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -124,6 +124,21 @@ static JsonbValue *findJsonbValueFromContainerLen(JsonbContainer *container,
char *key,
uint32 keylen);
+/* functions supporting jsonb_delete, jsonb_replace and jsonb_concat */
+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 replacePathObject(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
+ int path_len, JsonbParseState **st, int level,
+ Jsonb *newval, uint32 nelems);
+static void replacePathArray(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
+ int path_len, JsonbParseState **st, int level,
+ Jsonb *newval, uint32 npairs);
+static void addJsonbToParseState(JsonbParseState **jbps, Jsonb * jb);
+
/* state for json_object_keys */
typedef struct OkeysState
{
@@ -3195,3 +3210,697 @@ 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_pretty (jsonb)
+ *
+ * Pretty-printed text for the jsonb
+ */
+Datum
+jsonb_pretty(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))
+ {
+ /* skip corresponding value as well */
+ if (r == WJB_KEY)
+ 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;
+
+ 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;
+
+ 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);
+
+ switch (r)
+ {
+ case WJB_BEGIN_ARRAY:
+ (void) pushJsonbValue(st, r, NULL);
+ replacePathArray(it, path_elems, path_nulls, path_len, st, level,
+ newval, v.val.array.nElems);
+ r = JsonbIteratorNext(it, &v, false);
+ Assert(r == WJB_END_ARRAY);
+ res = pushJsonbValue(st, r, NULL);
+
+ break;
+ case WJB_BEGIN_OBJECT:
+ (void) pushJsonbValue(st, r, NULL);
+ replacePathObject(it, path_elems, path_nulls, path_len, st, level,
+ newval, v.val.object.nPairs);
+ r = JsonbIteratorNext(it, &v, true);
+ Assert(r == WJB_END_OBJECT);
+ res = pushJsonbValue(st, r, NULL);
+
+ break;
+ case WJB_ELEM:
+ case WJB_VALUE:
+ res = pushJsonbValue(st, r, &v);
+ break;
+ default:
+ elog(PANIC, "impossible state");
+ }
+
+ return res;
+}
+
+/*
+ * Object walker for replacePath
+ */
+static void
+replacePathObject(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
+ int path_len, JsonbParseState **st, int level,
+ Jsonb *newval, uint32 nelems)
+{
+ JsonbValue v;
+ int i;
+ JsonbValue k;
+ bool done = false;
+
+ if (level >= path_len || path_nulls[level])
+ done = true;
+
+ for (i = 0; i < nelems; i++)
+ {
+ int 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);
+ }
+ }
+ }
+ }
+}
+
+/*
+ * Array walker for replacePath
+ */
+static void
+replacePathArray(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
+ int path_len, JsonbParseState **st, int level,
+ Jsonb *newval, uint32 npairs)
+{
+ JsonbValue v;
+ int idx,
+ i;
+ char *badp;
+
+ /* pick correct index */
+ if (level < path_len && !path_nulls[level])
+ {
+ char *c = VARDATA_ANY(path_elems[level]);
+
+ errno = 0;
+ idx = (int) strtol(c, &badp, 10);
+ if (errno != 0 || badp == c)
+ idx = npairs;
+ }
+ else
+ idx = npairs;
+
+ if (idx < 0)
+ {
+ if (-idx > npairs)
+ idx = npairs;
+ else
+ idx = npairs + idx;
+ }
+
+ if (idx > npairs)
+ idx = npairs;
+
+ /* iterate over the array elements */
+ for (i = 0; i < npairs; i++)
+ {
+ int r;
+
+ if (i == idx && level < path_len)
+ {
+ if (level == path_len - 1)
+ {
+ r = JsonbIteratorNext(it, &v, true); /* skip */
+ if (newval != NULL)
+ addJsonbToParseState(st, newval);
+ }
+ else
+ (void) 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);
+ }
+ }
+ }
+ }
+}
diff --git a/src/include/catalog/pg_operator.h b/src/include/catalog/pg_operator.h
index e22eb27..34ebb50 100644
--- a/src/include/catalog/pg_operator.h
+++ b/src/include/catalog/pg_operator.h
@@ -1809,6 +1809,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 = 3284 ( "||" PGNSP PGUID b f f 3802 3802 3802 0 0 jsonb_concat - - ));
+DESCR("concatenate");
+DATA(insert OID = 3285 ( "-" PGNSP PGUID b f f 3802 25 3802 0 0 3302 - - ));
+DESCR("delete");
+DATA(insert OID = 3286 ( "-" PGNSP PGUID b f f 3802 23 3802 0 0 3303 - - ));
+DESCR("delete");
+DATA(insert OID = 3287 ( "-" PGNSP PGUID b f f 3802 1009 3802 0 0 3304 - - ));
+DESCR("delete");
/*
* function prototypes
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 55c246e..6977036 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -4799,7 +4799,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_ _null_ gin_triconsistent_jsonb_path _null_ _null_ _null_ ));
DESCR("GIN support");
-
+DATA(insert OID = 3301 ( 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_ _null_ jsonb_concat _null_ _null_ _null_ ));
+DATA(insert OID = 3302 ( 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_ _null_ jsonb_delete _null_ _null_ _null_ ));
+DATA(insert OID = 3303 ( 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_ _null_ jsonb_delete_idx _null_ _null_ _null_ ));
+DATA(insert OID = 3304 ( 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_ _null_ jsonb_delete_path _null_ _null_ _null_ ));
+DATA(insert OID = 3305 ( 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_ _null_ jsonb_replace _null_ _null_ _null_ ));
+DESCR("Replace part of a jsonb");
+DATA(insert OID = 3306 ( jsonb_pretty PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 25 "3802" _null_ _null_ _null_ _null_ _null_ jsonb_pretty _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_ _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 9d1770e..7b56175 100644
--- a/src/include/utils/jsonb.h
+++ b/src/include/utils/jsonb.h
@@ -394,6 +394,20 @@ 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_pretty(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);
+Datum jsonb_delete_path(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 +427,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 0d55890..83201fb 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -2276,7 +2276,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
----
{}
@@ -2753,3 +2753,425 @@ select jsonb_strip_nulls('{"a": {"b": null, "c": null}, "d": {} }');
{"a": {}, "d": {}}
(1 row)
+select jsonb_pretty('{"a": "test", "b": [1, 2, 3], "c": "test3", "d":{"dd": "test4", "dd2":{"ddd": "test5"}}}');
+ jsonb_pretty
+----------------------------
+ { +
+ "a": "test", +
+ "b": [ +
+ 1, +
+ 2, +
+ 3 +
+ ], +
+ "c": "test3", +
+ "d": { +
+ "dd": "test4", +
+ "dd2": { +
+ "ddd": "test5"+
+ } +
+ } +
+ }
+(1 row)
+
+select jsonb_pretty('[{"f1":1,"f2":null},2,null,[[{"x":true},6,7],8],3]');
+ jsonb_pretty
+---------------------------
+ [ +
+ { +
+ "f1": 1, +
+ "f2": null +
+ }, +
+ 2, +
+ null, +
+ [ +
+ [ +
+ { +
+ "x": true+
+ }, +
+ 6, +
+ 7 +
+ ], +
+ 8 +
+ ], +
+ 3 +
+ ]
+(1 row)
+
+select jsonb_pretty('{"a":["b", "c"], "d": {"e":"f"}}');
+ jsonb_pretty
+------------------
+ { +
+ "a": [ +
+ "b", +
+ "c" +
+ ], +
+ "d": { +
+ "e": "f"+
+ } +
+ }
+(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 694b6ea..8c57594 100644
--- a/src/test/regress/expected/jsonb_1.out
+++ b/src/test/regress/expected/jsonb_1.out
@@ -2276,7 +2276,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
----
{}
@@ -2753,3 +2753,425 @@ 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_indent('[{"f1":1,"f2":null},2,null,[[{"x":true},6,7],8],3]');
+ jsonb_indent
+---------------------------
+ [ +
+ { +
+ "f1": 1, +
+ "f2": null +
+ }, +
+ 2, +
+ null, +
+ [ +
+ [ +
+ { +
+ "x": true+
+ }, +
+ 6, +
+ 7 +
+ ], +
+ 8 +
+ ], +
+ 3 +
+ ]
+(1 row)
+
+select jsonb_indent('{"a":["b", "c"], "d": {"e":"f"}}');
+ jsonb_indent
+------------------
+ { +
+ "a": [ +
+ "b", +
+ "c" +
+ ], +
+ "d": { +
+ "e": "f"+
+ } +
+ }
+(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 676e1a7..808da9c 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -557,7 +557,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;
@@ -684,3 +684,86 @@ 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_pretty('{"a": "test", "b": [1, 2, 3], "c": "test3", "d":{"dd": "test4", "dd2":{"ddd": "test5"}}}');
+select jsonb_pretty('[{"f1":1,"f2":null},2,null,[[{"x":true},6,7],8],3]');
+select jsonb_pretty('{"a":["b", "c"], "d": {"e":"f"}}');
+
+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[];
--
1.9.1
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
