894
(1 row)
-SELECT distinct * FROM (values (jsonb '{}' || ''),('{}')) v(j);
+SELECT distinct * FROM (values (jsonb '{}' || ''::text),('{}')) v(j);
j
----
{}
{"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)
+
projects / postgresql / blobdiff