There was a long thread about concatenating jsonb objects to each other,
but that discussion didn't touch concatenating other types. Currently
jsonb_concat always just returns the other argument if one of arguments
is considered empty. This causes surprising behavior when concatenating
scalar values to empty arrays:
os=# select '[]'::jsonb || '1'::jsonb;
1
os=# select '[]'::jsonb || '[1]'::jsonb;
[1]
os=# select '[]'::jsonb || '1'::jsonb || '2'::jsonb;
[1, 2]
os=# select '0'::jsonb || '1'::jsonb;
[0, 1]
os=# select '{"x": "y"}'::jsonb || '[1]'::jsonb;
[{"x": "y"}, 1]
os=# select '{"x": "y"}'::jsonb || '1'::jsonb;
ERROR: invalid concatenation of jsonb objects
Attached a patch to fix and test this. Also added a test case for
concatenating two scalar values which currently produces an array.. I'm
not sure that behavior makes sense, but didn't want to change that in
this patch as I guess someone could consider that feature useful.
/ Oskari
>From 299124e63bb26ab07fa8429b7d1c2035b81f15d5 Mon Sep 17 00:00:00 2001
From: Oskari Saarenmaa <o...@ohmu.fi>
Date: Sat, 5 Sep 2015 09:33:58 +0300
Subject: [PATCH] jsonb_concat: make sure we always return a non-scalar value
jsonb_concat used to always just return the other argument if one of
arguments was considered empty. This caused surprising behavior when
concatenating scalar values to empty arrays.
Fixed this and added a test case for it. Also added a test case for
concatenating two scalar values which currently produces an array.
---
src/backend/utils/adt/jsonfuncs.c | 8 +++++---
src/test/regress/expected/jsonb.out | 18 ++++++++++++++++++
src/test/regress/sql/jsonb.sql | 4 ++++
3 files changed, 27 insertions(+), 3 deletions(-)
diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c
index 3b8d42e..57edb63 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -3359,11 +3359,13 @@ jsonb_concat(PG_FUNCTION_ARGS)
*it2;
/*
- * If one of the jsonb is empty, just return other.
+ * If one of the jsonb is empty, just return other if it's not
+ * scalar. If it's a scalar we need to perform concatenation to
+ * make sure we return a non-scalar value.
*/
- if (JB_ROOT_COUNT(jb1) == 0)
+ if (JB_ROOT_COUNT(jb1) == 0 && !JB_ROOT_IS_SCALAR(jb2))
PG_RETURN_JSONB(jb2);
- else if (JB_ROOT_COUNT(jb2) == 0)
+ else if (JB_ROOT_COUNT(jb2) == 0 && !JB_ROOT_IS_SCALAR(jb1))
PG_RETURN_JSONB(jb1);
it1 = JsonbIteratorInit(&jb1->root);
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index 17656d4..98a318b 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -2912,6 +2912,24 @@ select '"c"' || '["a", "b"]'::jsonb;
["c", "a", "b"]
(1 row)
+select '[]'::jsonb || '["a"]'::jsonb;
+ ?column?
+----------
+ ["a"]
+(1 row)
+
+select '[]'::jsonb || '"a"'::jsonb;
+ ?column?
+----------
+ ["a"]
+(1 row)
+
+select '"b"'::jsonb || '"a"'::jsonb;
+ ?column?
+------------
+ ["b", "a"]
+(1 row)
+
select '"a"'::jsonb || '{"a":1}';
ERROR: invalid concatenation of jsonb objects
select '{"a":1}' || '"a"'::jsonb;
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index 83ed4eb..5a2b4a8 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -718,6 +718,10 @@ select '["c"]' || '["a", "b"]'::jsonb;
select '["a", "b"]'::jsonb || '"c"';
select '"c"' || '["a", "b"]'::jsonb;
+select '[]'::jsonb || '["a"]'::jsonb;
+select '[]'::jsonb || '"a"'::jsonb;
+select '"b"'::jsonb || '"a"'::jsonb;
+
select '"a"'::jsonb || '{"a":1}';
select '{"a":1}' || '"a"'::jsonb;
--
2.5.0
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
