summaryrefslogtreecommitdiff
path: root/src/test
diff options
context:
space:
mode:
Diffstat (limited to 'src/test')
-rw-r--r--src/test/regress/expected/json.out466
-rw-r--r--src/test/regress/expected/jsonb.out478
-rw-r--r--src/test/regress/sql/json.sql156
-rw-r--r--src/test/regress/sql/jsonb.sql156
4 files changed, 1242 insertions, 14 deletions
diff --git a/src/test/regress/expected/json.out b/src/test/regress/expected/json.out
index a229472d54f..b25e20ca20a 100644
--- a/src/test/regress/expected/json.out
+++ b/src/test/regress/expected/json.out
@@ -1313,6 +1313,31 @@ select * from json_array_elements_text('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8
-- populate_record
create type jpop as (a text, b int, c timestamp);
+CREATE DOMAIN js_int_not_null AS int NOT NULL;
+CREATE DOMAIN js_int_array_1d AS int[] CHECK(array_length(VALUE, 1) = 3);
+CREATE DOMAIN js_int_array_2d AS int[][] CHECK(array_length(VALUE, 2) = 3);
+CREATE TYPE jsrec AS (
+ i int,
+ ia _int4,
+ ia1 int[],
+ ia2 int[][],
+ ia3 int[][][],
+ ia1d js_int_array_1d,
+ ia2d js_int_array_2d,
+ t text,
+ ta text[],
+ c char(10),
+ ca char(10)[],
+ ts timestamp,
+ js json,
+ jsb jsonb,
+ jsa json[],
+ rec jpop,
+ reca jpop[]
+);
+CREATE TYPE jsrec_i_not_null AS (
+ i js_int_not_null
+);
select * from json_populate_record(null::jpop,'{"a":"blurfl","x":43.2}') q;
a | b | c
--------+---+---
@@ -1351,6 +1376,370 @@ select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"a":
select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"c":[100,200,false],"x":43.2}') q;
ERROR: invalid input syntax for type timestamp: "[100,200,false]"
+select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{}') q;
+ a | b | c
+---+---+--------------------------
+ x | 3 | Mon Dec 31 15:30:56 2012
+(1 row)
+
+SELECT i FROM json_populate_record(NULL::jsrec_i_not_null, '{"x": 43.2}') q;
+ERROR: domain js_int_not_null does not allow null values
+SELECT i FROM json_populate_record(NULL::jsrec_i_not_null, '{"i": null}') q;
+ERROR: domain js_int_not_null does not allow null values
+SELECT i FROM json_populate_record(NULL::jsrec_i_not_null, '{"i": 12345}') q;
+ i
+-------
+ 12345
+(1 row)
+
+SELECT ia FROM json_populate_record(NULL::jsrec, '{"ia": null}') q;
+ ia
+----
+
+(1 row)
+
+SELECT ia FROM json_populate_record(NULL::jsrec, '{"ia": 123}') q;
+ERROR: expected json array
+HINT: see the value of key "ia"
+SELECT ia FROM json_populate_record(NULL::jsrec, '{"ia": [1, "2", null, 4]}') q;
+ ia
+--------------
+ {1,2,NULL,4}
+(1 row)
+
+SELECT ia FROM json_populate_record(NULL::jsrec, '{"ia": [[1, 2], [3, 4]]}') q;
+ ia
+---------------
+ {{1,2},{3,4}}
+(1 row)
+
+SELECT ia FROM json_populate_record(NULL::jsrec, '{"ia": [[1], 2]}') q;
+ERROR: expected json array
+HINT: see the array element [1] of key "ia"
+SELECT ia FROM json_populate_record(NULL::jsrec, '{"ia": [[1], [2, 3]]}') q;
+ERROR: malformed json array
+DETAIL: Multidimensional arrays must have sub-arrays with matching dimensions.
+SELECT ia FROM json_populate_record(NULL::jsrec, '{"ia": "{1,2,3}"}') q;
+ ia
+---------
+ {1,2,3}
+(1 row)
+
+SELECT ia1 FROM json_populate_record(NULL::jsrec, '{"ia1": null}') q;
+ ia1
+-----
+
+(1 row)
+
+SELECT ia1 FROM json_populate_record(NULL::jsrec, '{"ia1": 123}') q;
+ERROR: expected json array
+HINT: see the value of key "ia1"
+SELECT ia1 FROM json_populate_record(NULL::jsrec, '{"ia1": [1, "2", null, 4]}') q;
+ ia1
+--------------
+ {1,2,NULL,4}
+(1 row)
+
+SELECT ia1 FROM json_populate_record(NULL::jsrec, '{"ia1": [[1, 2, 3]]}') q;
+ ia1
+-----------
+ {{1,2,3}}
+(1 row)
+
+SELECT ia1d FROM json_populate_record(NULL::jsrec, '{"ia1d": null}') q;
+ ia1d
+------
+
+(1 row)
+
+SELECT ia1d FROM json_populate_record(NULL::jsrec, '{"ia1d": 123}') q;
+ERROR: expected json array
+HINT: see the value of key "ia1d"
+SELECT ia1d FROM json_populate_record(NULL::jsrec, '{"ia1d": [1, "2", null, 4]}') q;
+ERROR: value for domain js_int_array_1d violates check constraint "js_int_array_1d_check"
+SELECT ia1d FROM json_populate_record(NULL::jsrec, '{"ia1d": [1, "2", null]}') q;
+ ia1d
+------------
+ {1,2,NULL}
+(1 row)
+
+SELECT ia2 FROM json_populate_record(NULL::jsrec, '{"ia2": [1, "2", null, 4]}') q;
+ ia2
+--------------
+ {1,2,NULL,4}
+(1 row)
+
+SELECT ia2 FROM json_populate_record(NULL::jsrec, '{"ia2": [[1, 2], [null, 4]]}') q;
+ ia2
+------------------
+ {{1,2},{NULL,4}}
+(1 row)
+
+SELECT ia2 FROM json_populate_record(NULL::jsrec, '{"ia2": [[], []]}') q;
+ ia2
+-----
+ {}
+(1 row)
+
+SELECT ia2 FROM json_populate_record(NULL::jsrec, '{"ia2": [[1, 2], [3]]}') q;
+ERROR: malformed json array
+DETAIL: Multidimensional arrays must have sub-arrays with matching dimensions.
+SELECT ia2 FROM json_populate_record(NULL::jsrec, '{"ia2": [[1, 2], 3, 4]}') q;
+ERROR: expected json array
+HINT: see the array element [1] of key "ia2"
+SELECT ia2d FROM json_populate_record(NULL::jsrec, '{"ia2d": [[1, "2"], [null, 4]]}') q;
+ERROR: value for domain js_int_array_2d violates check constraint "js_int_array_2d_check"
+SELECT ia2d FROM json_populate_record(NULL::jsrec, '{"ia2d": [[1, "2", 3], [null, 5, 6]]}') q;
+ ia2d
+----------------------
+ {{1,2,3},{NULL,5,6}}
+(1 row)
+
+SELECT ia3 FROM json_populate_record(NULL::jsrec, '{"ia3": [1, "2", null, 4]}') q;
+ ia3
+--------------
+ {1,2,NULL,4}
+(1 row)
+
+SELECT ia3 FROM json_populate_record(NULL::jsrec, '{"ia3": [[1, 2], [null, 4]]}') q;
+ ia3
+------------------
+ {{1,2},{NULL,4}}
+(1 row)
+
+SELECT ia3 FROM json_populate_record(NULL::jsrec, '{"ia3": [ [[], []], [[], []], [[], []] ]}') q;
+ ia3
+-----
+ {}
+(1 row)
+
+SELECT ia3 FROM json_populate_record(NULL::jsrec, '{"ia3": [ [[1, 2]], [[3, 4]] ]}') q;
+ ia3
+-------------------
+ {{{1,2}},{{3,4}}}
+(1 row)
+
+SELECT ia3 FROM json_populate_record(NULL::jsrec, '{"ia3": [ [[1, 2], [3, 4]], [[5, 6], [7, 8]] ]}') q;
+ ia3
+-------------------------------
+ {{{1,2},{3,4}},{{5,6},{7,8}}}
+(1 row)
+
+SELECT ia3 FROM json_populate_record(NULL::jsrec, '{"ia3": [ [[1, 2], [3, 4]], [[5, 6], [7, 8], [9, 10]] ]}') q;
+ERROR: malformed json array
+DETAIL: Multidimensional arrays must have sub-arrays with matching dimensions.
+SELECT ta FROM json_populate_record(NULL::jsrec, '{"ta": null}') q;
+ ta
+----
+
+(1 row)
+
+SELECT ta FROM json_populate_record(NULL::jsrec, '{"ta": 123}') q;
+ERROR: expected json array
+HINT: see the value of key "ta"
+SELECT ta FROM json_populate_record(NULL::jsrec, '{"ta": [1, "2", null, 4]}') q;
+ ta
+--------------
+ {1,2,NULL,4}
+(1 row)
+
+SELECT ta FROM json_populate_record(NULL::jsrec, '{"ta": [[1, 2, 3], {"k": "v"}]}') q;
+ERROR: expected json array
+HINT: see the array element [1] of key "ta"
+SELECT c FROM json_populate_record(NULL::jsrec, '{"c": null}') q;
+ c
+---
+
+(1 row)
+
+SELECT c FROM json_populate_record(NULL::jsrec, '{"c": "aaa"}') q;
+ c
+------------
+ aaa
+(1 row)
+
+SELECT c FROM json_populate_record(NULL::jsrec, '{"c": "aaaaaaaaaa"}') q;
+ c
+------------
+ aaaaaaaaaa
+(1 row)
+
+SELECT c FROM json_populate_record(NULL::jsrec, '{"c": "aaaaaaaaaaaaa"}') q;
+ERROR: value too long for type character(10)
+SELECT ca FROM json_populate_record(NULL::jsrec, '{"ca": null}') q;
+ ca
+----
+
+(1 row)
+
+SELECT ca FROM json_populate_record(NULL::jsrec, '{"ca": 123}') q;
+ERROR: expected json array
+HINT: see the value of key "ca"
+SELECT ca FROM json_populate_record(NULL::jsrec, '{"ca": [1, "2", null, 4]}') q;
+ ca
+-----------------------------------------------
+ {"1 ","2 ",NULL,"4 "}
+(1 row)
+
+SELECT ca FROM json_populate_record(NULL::jsrec, '{"ca": ["aaaaaaaaaaaaaaaa"]}') q;
+ERROR: value too long for type character(10)
+SELECT ca FROM json_populate_record(NULL::jsrec, '{"ca": [[1, 2, 3], {"k": "v"}]}') q;
+ERROR: expected json array
+HINT: see the array element [1] of key "ca"
+SELECT js FROM json_populate_record(NULL::jsrec, '{"js": null}') q;
+ js
+----
+
+(1 row)
+
+SELECT js FROM json_populate_record(NULL::jsrec, '{"js": true}') q;
+ js
+------
+ true
+(1 row)
+
+SELECT js FROM json_populate_record(NULL::jsrec, '{"js": 123.45}') q;
+ js
+--------
+ 123.45
+(1 row)
+
+SELECT js FROM json_populate_record(NULL::jsrec, '{"js": "123.45"}') q;
+ js
+----------
+ "123.45"
+(1 row)
+
+SELECT js FROM json_populate_record(NULL::jsrec, '{"js": "abc"}') q;
+ js
+-------
+ "abc"
+(1 row)
+
+SELECT js FROM json_populate_record(NULL::jsrec, '{"js": [123, "123", null, {"key": "value"}]}') q;
+ js
+--------------------------------------
+ [123, "123", null, {"key": "value"}]
+(1 row)
+
+SELECT js FROM json_populate_record(NULL::jsrec, '{"js": {"a": "bbb", "b": null, "c": 123.45}}') q;
+ js
+--------------------------------------
+ {"a": "bbb", "b": null, "c": 123.45}
+(1 row)
+
+SELECT jsb FROM json_populate_record(NULL::jsrec, '{"jsb": null}') q;
+ jsb
+-----
+
+(1 row)
+
+SELECT jsb FROM json_populate_record(NULL::jsrec, '{"jsb": true}') q;
+ jsb
+------
+ true
+(1 row)
+
+SELECT jsb FROM json_populate_record(NULL::jsrec, '{"jsb": 123.45}') q;
+ jsb
+--------
+ 123.45
+(1 row)
+
+SELECT jsb FROM json_populate_record(NULL::jsrec, '{"jsb": "123.45"}') q;
+ jsb
+----------
+ "123.45"
+(1 row)
+
+SELECT jsb FROM json_populate_record(NULL::jsrec, '{"jsb": "abc"}') q;
+ jsb
+-------
+ "abc"
+(1 row)
+
+SELECT jsb FROM json_populate_record(NULL::jsrec, '{"jsb": [123, "123", null, {"key": "value"}]}') q;
+ jsb
+--------------------------------------
+ [123, "123", null, {"key": "value"}]
+(1 row)
+
+SELECT jsb FROM json_populate_record(NULL::jsrec, '{"jsb": {"a": "bbb", "b": null, "c": 123.45}}') q;
+ jsb
+--------------------------------------
+ {"a": "bbb", "b": null, "c": 123.45}
+(1 row)
+
+SELECT jsa FROM json_populate_record(NULL::jsrec, '{"jsa": null}') q;
+ jsa
+-----
+
+(1 row)
+
+SELECT jsa FROM json_populate_record(NULL::jsrec, '{"jsa": 123}') q;
+ERROR: expected json array
+HINT: see the value of key "jsa"
+SELECT jsa FROM json_populate_record(NULL::jsrec, '{"jsa": [1, "2", null, 4]}') q;
+ jsa
+--------------------
+ {1,"\"2\"",NULL,4}
+(1 row)
+
+SELECT jsa FROM json_populate_record(NULL::jsrec, '{"jsa": ["aaa", null, [1, 2, "3", {}], { "k" : "v" }]}') q;
+ jsa
+----------------------------------------------------------
+ {"\"aaa\"",NULL,"[1, 2, \"3\", {}]","{ \"k\" : \"v\" }"}
+(1 row)
+
+SELECT rec FROM json_populate_record(NULL::jsrec, '{"rec": 123}') q;
+ERROR: cannot call populate_composite on a scalar
+SELECT rec FROM json_populate_record(NULL::jsrec, '{"rec": [1, 2]}') q;
+ERROR: cannot call populate_composite on an array
+SELECT rec FROM json_populate_record(NULL::jsrec, '{"rec": {"a": "abc", "c": "01.02.2003", "x": 43.2}}') q;
+ rec
+-----------------------------------
+ (abc,,"Thu Jan 02 00:00:00 2003")
+(1 row)
+
+SELECT rec FROM json_populate_record(NULL::jsrec, '{"rec": "(abc,42,01.02.2003)"}') q;
+ rec
+-------------------------------------
+ (abc,42,"Thu Jan 02 00:00:00 2003")
+(1 row)
+
+SELECT reca FROM json_populate_record(NULL::jsrec, '{"reca": 123}') q;
+ERROR: expected json array
+HINT: see the value of key "reca"
+SELECT reca FROM json_populate_record(NULL::jsrec, '{"reca": [1, 2]}') q;
+ERROR: cannot call populate_composite on a scalar
+SELECT reca FROM json_populate_record(NULL::jsrec, '{"reca": [{"a": "abc", "b": 456}, null, {"c": "01.02.2003", "x": 43.2}]}') q;
+ reca
+--------------------------------------------------------
+ {"(abc,456,)",NULL,"(,,\"Thu Jan 02 00:00:00 2003\")"}
+(1 row)
+
+SELECT reca FROM json_populate_record(NULL::jsrec, '{"reca": ["(abc,42,01.02.2003)"]}') q;
+ reca
+-------------------------------------------
+ {"(abc,42,\"Thu Jan 02 00:00:00 2003\")"}
+(1 row)
+
+SELECT reca FROM json_populate_record(NULL::jsrec, '{"reca": "{\"(abc,42,01.02.2003)\"}"}') q;
+ reca
+-------------------------------------------
+ {"(abc,42,\"Thu Jan 02 00:00:00 2003\")"}
+(1 row)
+
+SELECT rec FROM json_populate_record(
+ row(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
+ row('x',3,'2012-12-31 15:30:56')::jpop,NULL)::jsrec,
+ '{"rec": {"a": "abc", "c": "01.02.2003", "x": 43.2}}'
+) q;
+ rec
+------------------------------------
+ (abc,3,"Thu Jan 02 00:00:00 2003")
+(1 row)
+
-- populate_recordset
select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q;
a | b | c
@@ -1417,6 +1806,28 @@ select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":[100,200,3
{"z":true} | 3 | Fri Jan 20 10:42:53 2012
(2 rows)
+-- test type info caching in json_populate_record()
+CREATE TEMP TABLE jspoptest (js json);
+INSERT INTO jspoptest
+SELECT '{
+ "jsa": [1, "2", null, 4],
+ "rec": {"a": "abc", "c": "01.02.2003", "x": 43.2},
+ "reca": [{"a": "abc", "b": 456}, null, {"c": "01.02.2003", "x": 43.2}]
+}'::json
+FROM generate_series(1, 3);
+SELECT (json_populate_record(NULL::jsrec, js)).* FROM jspoptest;
+ i | ia | ia1 | ia2 | ia3 | ia1d | ia2d | t | ta | c | ca | ts | js | jsb | jsa | rec | reca
+---+----+-----+-----+-----+------+------+---+----+---+----+----+----+-----+--------------------+-----------------------------------+--------------------------------------------------------
+ | | | | | | | | | | | | | | {1,"\"2\"",NULL,4} | (abc,,"Thu Jan 02 00:00:00 2003") | {"(abc,456,)",NULL,"(,,\"Thu Jan 02 00:00:00 2003\")"}
+ | | | | | | | | | | | | | | {1,"\"2\"",NULL,4} | (abc,,"Thu Jan 02 00:00:00 2003") | {"(abc,456,)",NULL,"(,,\"Thu Jan 02 00:00:00 2003\")"}
+ | | | | | | | | | | | | | | {1,"\"2\"",NULL,4} | (abc,,"Thu Jan 02 00:00:00 2003") | {"(abc,456,)",NULL,"(,,\"Thu Jan 02 00:00:00 2003\")"}
+(3 rows)
+
+DROP TYPE jsrec;
+DROP TYPE jsrec_i_not_null;
+DROP DOMAIN js_int_not_null;
+DROP DOMAIN js_int_array_1d;
+DROP DOMAIN js_int_array_2d;
--json_typeof() function
select value, json_typeof(value)
from (values (json '123.4'),
@@ -1609,11 +2020,11 @@ select * from json_to_recordset('[{"a":1,"b":{"d":"foo"},"c":true},{"a":2,"c":fa
(2 rows)
select *, c is null as c_is_null
-from json_to_record('{"a":1, "b":{"c":16, "d":2}, "x":8}'::json)
- as t(a int, b json, c text, x int);
- a | b | c | x | c_is_null
----+-----------------+---+---+-----------
- 1 | {"c":16, "d":2} | | 8 | t
+from json_to_record('{"a":1, "b":{"c":16, "d":2}, "x":8, "ca": ["1 2", 3], "ia": [[1,2],[3,4]], "r": {"a": "aaa", "b": 123}}'::json)
+ as t(a int, b json, c text, x int, ca char(5)[], ia int[][], r jpop);
+ a | b | c | x | ca | ia | r | c_is_null
+---+-----------------+---+---+-------------------+---------------+------------+-----------
+ 1 | {"c":16, "d":2} | | 8 | {"1 2 ","3 "} | {{1,2},{3,4}} | (aaa,123,) | t
(1 row)
select *, c is null as c_is_null
@@ -1624,6 +2035,51 @@ from json_to_recordset('[{"a":1, "b":{"c":16, "d":2}, "x":8}]'::json)
1 | {"c":16, "d":2} | | 8 | t
(1 row)
+select * from json_to_record('{"ia": null}') as x(ia _int4);
+ ia
+----
+
+(1 row)
+
+select * from json_to_record('{"ia": 123}') as x(ia _int4);
+ERROR: expected json array
+HINT: see the value of key "ia"
+select * from json_to_record('{"ia": [1, "2", null, 4]}') as x(ia _int4);
+ ia
+--------------
+ {1,2,NULL,4}
+(1 row)
+
+select * from json_to_record('{"ia": [[1, 2], [3, 4]]}') as x(ia _int4);
+ ia
+---------------
+ {{1,2},{3,4}}
+(1 row)
+
+select * from json_to_record('{"ia": [[1], 2]}') as x(ia _int4);
+ERROR: expected json array
+HINT: see the array element [1] of key "ia"
+select * from json_to_record('{"ia": [[1], [2, 3]]}') as x(ia _int4);
+ERROR: malformed json array
+DETAIL: Multidimensional arrays must have sub-arrays with matching dimensions.
+select * from json_to_record('{"ia2": [1, 2, 3]}') as x(ia2 int[][]);
+ ia2
+---------
+ {1,2,3}
+(1 row)
+
+select * from json_to_record('{"ia2": [[1, 2], [3, 4]]}') as x(ia2 int4[][]);
+ ia2
+---------------
+ {{1,2},{3,4}}
+(1 row)
+
+select * from json_to_record('{"ia2": [[[1], [2], [3]]]}') as x(ia2 int4[][]);
+ ia2
+-----------------
+ {{{1},{2},{3}}}
+(1 row)
+
-- json_strip_nulls
select json_strip_nulls(null);
json_strip_nulls
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index e93a15f98a8..f199eb4f709 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -1897,6 +1897,31 @@ SELECT * FROM jsonb_array_elements_text('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,
-- populate_record
CREATE TYPE jbpop AS (a text, b int, c timestamp);
+CREATE DOMAIN jsb_int_not_null AS int NOT NULL;
+CREATE DOMAIN jsb_int_array_1d AS int[] CHECK(array_length(VALUE, 1) = 3);
+CREATE DOMAIN jsb_int_array_2d AS int[][] CHECK(array_length(VALUE, 2) = 3);
+CREATE TYPE jsbrec AS (
+ i int,
+ ia _int4,
+ ia1 int[],
+ ia2 int[][],
+ ia3 int[][][],
+ ia1d jsb_int_array_1d,
+ ia2d jsb_int_array_2d,
+ t text,
+ ta text[],
+ c char(10),
+ ca char(10)[],
+ ts timestamp,
+ js json,
+ jsb jsonb,
+ jsa json[],
+ rec jbpop,
+ reca jbpop[]
+);
+CREATE TYPE jsbrec_i_not_null AS (
+ i jsb_int_not_null
+);
SELECT * FROM jsonb_populate_record(NULL::jbpop,'{"a":"blurfl","x":43.2}') q;
a | b | c
--------+---+---
@@ -1935,6 +1960,382 @@ SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"a
SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"c":[100,200,false],"x":43.2}') q;
ERROR: invalid input syntax for type timestamp: "[100, 200, false]"
+SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop, '{}') q;
+ a | b | c
+---+---+--------------------------
+ x | 3 | Mon Dec 31 15:30:56 2012
+(1 row)
+
+SELECT i FROM jsonb_populate_record(NULL::jsbrec_i_not_null, '{"x": 43.2}') q;
+ERROR: domain jsb_int_not_null does not allow null values
+SELECT i FROM jsonb_populate_record(NULL::jsbrec_i_not_null, '{"i": null}') q;
+ERROR: domain jsb_int_not_null does not allow null values
+SELECT i FROM jsonb_populate_record(NULL::jsbrec_i_not_null, '{"i": 12345}') q;
+ i
+-------
+ 12345
+(1 row)
+
+SELECT ia FROM jsonb_populate_record(NULL::jsbrec, '{"ia": null}') q;
+ ia
+----
+
+(1 row)
+
+SELECT ia FROM jsonb_populate_record(NULL::jsbrec, '{"ia": 123}') q;
+ERROR: expected json array
+HINT: see the value of key "ia"
+SELECT ia FROM jsonb_populate_record(NULL::jsbrec, '{"ia": [1, "2", null, 4]}') q;
+ ia
+--------------
+ {1,2,NULL,4}
+(1 row)
+
+SELECT ia FROM jsonb_populate_record(NULL::jsbrec, '{"ia": [[1, 2], [3, 4]]}') q;
+ ia
+---------------
+ {{1,2},{3,4}}
+(1 row)
+
+SELECT ia FROM jsonb_populate_record(NULL::jsbrec, '{"ia": [[1], 2]}') q;
+ERROR: expected json array
+HINT: see the array element [1] of key "ia"
+SELECT ia FROM jsonb_populate_record(NULL::jsbrec, '{"ia": [[1], [2, 3]]}') q;
+ERROR: malformed json array
+DETAIL: Multidimensional arrays must have sub-arrays with matching dimensions.
+SELECT ia FROM jsonb_populate_record(NULL::jsbrec, '{"ia": "{1,2,3}"}') q;
+ ia
+---------
+ {1,2,3}
+(1 row)
+
+SELECT ia1 FROM jsonb_populate_record(NULL::jsbrec, '{"ia1": null}') q;
+ ia1
+-----
+
+(1 row)
+
+SELECT ia1 FROM jsonb_populate_record(NULL::jsbrec, '{"ia1": 123}') q;
+ERROR: expected json array
+HINT: see the value of key "ia1"
+SELECT ia1 FROM jsonb_populate_record(NULL::jsbrec, '{"ia1": [1, "2", null, 4]}') q;
+ ia1
+--------------
+ {1,2,NULL,4}
+(1 row)
+
+SELECT ia1 FROM jsonb_populate_record(NULL::jsbrec, '{"ia1": [[1, 2, 3]]}') q;
+ ia1
+-----------
+ {{1,2,3}}
+(1 row)
+
+SELECT ia1d FROM jsonb_populate_record(NULL::jsbrec, '{"ia1d": null}') q;
+ ia1d
+------
+
+(1 row)
+
+SELECT ia1d FROM jsonb_populate_record(NULL::jsbrec, '{"ia1d": 123}') q;
+ERROR: expected json array
+HINT: see the value of key "ia1d"
+SELECT ia1d FROM jsonb_populate_record(NULL::jsbrec, '{"ia1d": [1, "2", null, 4]}') q;
+ERROR: value for domain jsb_int_array_1d violates check constraint "jsb_int_array_1d_check"
+SELECT ia1d FROM jsonb_populate_record(NULL::jsbrec, '{"ia1d": [1, "2", null]}') q;
+ ia1d
+------------
+ {1,2,NULL}
+(1 row)
+
+SELECT ia2 FROM jsonb_populate_record(NULL::jsbrec, '{"ia2": [1, "2", null, 4]}') q;
+ ia2
+--------------
+ {1,2,NULL,4}
+(1 row)
+
+SELECT ia2 FROM jsonb_populate_record(NULL::jsbrec, '{"ia2": [[1, 2], [null, 4]]}') q;
+ ia2
+------------------
+ {{1,2},{NULL,4}}
+(1 row)
+
+SELECT ia2 FROM jsonb_populate_record(NULL::jsbrec, '{"ia2": [[], []]}') q;
+ ia2
+-----
+ {}
+(1 row)
+
+SELECT ia2 FROM jsonb_populate_record(NULL::jsbrec, '{"ia2": [[1, 2], [3]]}') q;
+ERROR: malformed json array
+DETAIL: Multidimensional arrays must have sub-arrays with matching dimensions.
+SELECT ia2 FROM jsonb_populate_record(NULL::jsbrec, '{"ia2": [[1, 2], 3, 4]}') q;
+ERROR: expected json array
+HINT: see the array element [1] of key "ia2"
+SELECT ia2d FROM jsonb_populate_record(NULL::jsbrec, '{"ia2d": [[1, "2"], [null, 4]]}') q;
+ERROR: value for domain jsb_int_array_2d violates check constraint "jsb_int_array_2d_check"
+SELECT ia2d FROM jsonb_populate_record(NULL::jsbrec, '{"ia2d": [[1, "2", 3], [null, 5, 6]]}') q;
+ ia2d
+----------------------
+ {{1,2,3},{NULL,5,6}}
+(1 row)
+
+SELECT ia3 FROM jsonb_populate_record(NULL::jsbrec, '{"ia3": [1, "2", null, 4]}') q;
+ ia3
+--------------
+ {1,2,NULL,4}
+(1 row)
+
+SELECT ia3 FROM jsonb_populate_record(NULL::jsbrec, '{"ia3": [[1, 2], [null, 4]]}') q;
+ ia3
+------------------
+ {{1,2},{NULL,4}}
+(1 row)
+
+SELECT ia3 FROM jsonb_populate_record(NULL::jsbrec, '{"ia3": [ [[], []], [[], []], [[], []] ]}') q;
+ ia3
+-----
+ {}
+(1 row)
+
+SELECT ia3 FROM jsonb_populate_record(NULL::jsbrec, '{"ia3": [ [[1, 2]], [[3, 4]] ]}') q;
+ ia3
+-------------------
+ {{{1,2}},{{3,4}}}
+(1 row)
+
+SELECT ia3 FROM jsonb_populate_record(NULL::jsbrec, '{"ia3": [ [[1, 2], [3, 4]], [[5, 6], [7, 8]] ]}') q;
+ ia3
+-------------------------------
+ {{{1,2},{3,4}},{{5,6},{7,8}}}
+(1 row)
+
+SELECT ia3 FROM jsonb_populate_record(NULL::jsbrec, '{"ia3": [ [[1, 2], [3, 4]], [[5, 6], [7, 8], [9, 10]] ]}') q;
+ERROR: malformed json array
+DETAIL: Multidimensional arrays must have sub-arrays with matching dimensions.
+SELECT ta FROM jsonb_populate_record(NULL::jsbrec, '{"ta": null}') q;
+ ta
+----
+
+(1 row)
+
+SELECT ta FROM jsonb_populate_record(NULL::jsbrec, '{"ta": 123}') q;
+ERROR: expected json array
+HINT: see the value of key "ta"
+SELECT ta FROM jsonb_populate_record(NULL::jsbrec, '{"ta": [1, "2", null, 4]}') q;
+ ta
+--------------
+ {1,2,NULL,4}
+(1 row)
+
+SELECT ta FROM jsonb_populate_record(NULL::jsbrec, '{"ta": [[1, 2, 3], {"k": "v"}]}') q;
+ERROR: expected json array
+HINT: see the array element [1] of key "ta"
+SELECT c FROM jsonb_populate_record(NULL::jsbrec, '{"c": null}') q;
+ c
+---
+
+(1 row)
+
+SELECT c FROM jsonb_populate_record(NULL::jsbrec, '{"c": "aaa"}') q;
+ c
+------------
+ aaa
+(1 row)
+
+SELECT c FROM jsonb_populate_record(NULL::jsbrec, '{"c": "aaaaaaaaaa"}') q;
+ c
+------------
+ aaaaaaaaaa
+(1 row)
+
+SELECT c FROM jsonb_populate_record(NULL::jsbrec, '{"c": "aaaaaaaaaaaaa"}') q;
+ERROR: value too long for type character(10)
+SELECT ca FROM jsonb_populate_record(NULL::jsbrec, '{"ca": null}') q;
+ ca
+----
+
+(1 row)
+
+SELECT ca FROM jsonb_populate_record(NULL::jsbrec, '{"ca": 123}') q;
+ERROR: expected json array
+HINT: see the value of key "ca"
+SELECT ca FROM jsonb_populate_record(NULL::jsbrec, '{"ca": [1, "2", null, 4]}') q;
+ ca
+-----------------------------------------------
+ {"1 ","2 ",NULL,"4 "}
+(1 row)
+
+SELECT ca FROM jsonb_populate_record(NULL::jsbrec, '{"ca": ["aaaaaaaaaaaaaaaa"]}') q;
+ERROR: value too long for type character(10)
+SELECT ca FROM jsonb_populate_record(NULL::jsbrec, '{"ca": [[1, 2, 3], {"k": "v"}]}') q;
+ERROR: expected json array
+HINT: see the array element [1] of key "ca"
+SELECT js FROM jsonb_populate_record(NULL::jsbrec, '{"js": null}') q;
+ js
+----
+
+(1 row)
+
+SELECT js FROM jsonb_populate_record(NULL::jsbrec, '{"js": true}') q;
+ js
+------
+ true
+(1 row)
+
+SELECT js FROM jsonb_populate_record(NULL::jsbrec, '{"js": 123.45}') q;
+ js
+--------
+ 123.45
+(1 row)
+
+SELECT js FROM jsonb_populate_record(NULL::jsbrec, '{"js": "123.45"}') q;
+ js
+----------
+ "123.45"
+(1 row)
+
+SELECT js FROM jsonb_populate_record(NULL::jsbrec, '{"js": "abc"}') q;
+ js
+-------
+ "abc"
+(1 row)
+
+SELECT js FROM jsonb_populate_record(NULL::jsbrec, '{"js": [123, "123", null, {"key": "value"}]}') q;
+ js
+--------------------------------------
+ [123, "123", null, {"key": "value"}]
+(1 row)
+
+SELECT js FROM jsonb_populate_record(NULL::jsbrec, '{"js": {"a": "bbb", "b": null, "c": 123.45}}') q;
+ js
+--------------------------------------
+ {"a": "bbb", "b": null, "c": 123.45}
+(1 row)
+
+SELECT jsb FROM jsonb_populate_record(NULL::jsbrec, '{"jsb": null}') q;
+ jsb
+-----
+
+(1 row)
+
+SELECT jsb FROM jsonb_populate_record(NULL::jsbrec, '{"jsb": true}') q;
+ jsb
+------
+ true
+(1 row)
+
+SELECT jsb FROM jsonb_populate_record(NULL::jsbrec, '{"jsb": 123.45}') q;
+ jsb
+--------
+ 123.45
+(1 row)
+
+SELECT jsb FROM jsonb_populate_record(NULL::jsbrec, '{"jsb": "123.45"}') q;
+ jsb
+----------
+ "123.45"
+(1 row)
+
+SELECT jsb FROM jsonb_populate_record(NULL::jsbrec, '{"jsb": "abc"}') q;
+ jsb
+-------
+ "abc"
+(1 row)
+
+SELECT jsb FROM jsonb_populate_record(NULL::jsbrec, '{"jsb": [123, "123", null, {"key": "value"}]}') q;
+ jsb
+--------------------------------------
+ [123, "123", null, {"key": "value"}]
+(1 row)
+
+SELECT jsb FROM jsonb_populate_record(NULL::jsbrec, '{"jsb": {"a": "bbb", "b": null, "c": 123.45}}') q;
+ jsb
+--------------------------------------
+ {"a": "bbb", "b": null, "c": 123.45}
+(1 row)
+
+SELECT jsa FROM jsonb_populate_record(NULL::jsbrec, '{"jsa": null}') q;
+ jsa
+-----
+
+(1 row)
+
+SELECT jsa FROM jsonb_populate_record(NULL::jsbrec, '{"jsa": 123}') q;
+ERROR: expected json array
+HINT: see the value of key "jsa"
+SELECT jsa FROM jsonb_populate_record(NULL::jsbrec, '{"jsa": [1, "2", null, 4]}') q;
+ jsa
+--------------------
+ {1,"\"2\"",NULL,4}
+(1 row)
+
+SELECT jsa FROM jsonb_populate_record(NULL::jsbrec, '{"jsa": ["aaa", null, [1, 2, "3", {}], { "k" : "v" }]}') q;
+ jsa
+-------------------------------------------------------
+ {"\"aaa\"",NULL,"[1, 2, \"3\", {}]","{\"k\": \"v\"}"}
+(1 row)
+
+SELECT rec FROM jsonb_populate_record(NULL::jsbrec, '{"rec": 123}') q;
+ rec
+------
+ (,,)
+(1 row)
+
+SELECT rec FROM jsonb_populate_record(NULL::jsbrec, '{"rec": [1, 2]}') q;
+ rec
+------
+ (,,)
+(1 row)
+
+SELECT rec FROM jsonb_populate_record(NULL::jsbrec, '{"rec": {"a": "abc", "c": "01.02.2003", "x": 43.2}}') q;
+ rec
+-----------------------------------
+ (abc,,"Thu Jan 02 00:00:00 2003")
+(1 row)
+
+SELECT rec FROM jsonb_populate_record(NULL::jsbrec, '{"rec": "(abc,42,01.02.2003)"}') q;
+ rec
+-------------------------------------
+ (abc,42,"Thu Jan 02 00:00:00 2003")
+(1 row)
+
+SELECT reca FROM jsonb_populate_record(NULL::jsbrec, '{"reca": 123}') q;
+ERROR: expected json array
+HINT: see the value of key "reca"
+SELECT reca FROM jsonb_populate_record(NULL::jsbrec, '{"reca": [1, 2]}') q;
+ reca
+-----------------
+ {"(,,)","(,,)"}
+(1 row)
+
+SELECT reca FROM jsonb_populate_record(NULL::jsbrec, '{"reca": [{"a": "abc", "b": 456}, null, {"c": "01.02.2003", "x": 43.2}]}') q;
+ reca
+--------------------------------------------------------
+ {"(abc,456,)",NULL,"(,,\"Thu Jan 02 00:00:00 2003\")"}
+(1 row)
+
+SELECT reca FROM jsonb_populate_record(NULL::jsbrec, '{"reca": ["(abc,42,01.02.2003)"]}') q;
+ reca
+-------------------------------------------
+ {"(abc,42,\"Thu Jan 02 00:00:00 2003\")"}
+(1 row)
+
+SELECT reca FROM jsonb_populate_record(NULL::jsbrec, '{"reca": "{\"(abc,42,01.02.2003)\"}"}') q;
+ reca
+-------------------------------------------
+ {"(abc,42,\"Thu Jan 02 00:00:00 2003\")"}
+(1 row)
+
+SELECT rec FROM jsonb_populate_record(
+ row(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
+ row('x',3,'2012-12-31 15:30:56')::jbpop,NULL)::jsbrec,
+ '{"rec": {"a": "abc", "c": "01.02.2003", "x": 43.2}}'
+) q;
+ rec
+------------------------------------
+ (abc,3,"Thu Jan 02 00:00:00 2003")
+(1 row)
+
-- populate_recordset
SELECT * FROM jsonb_populate_recordset(NULL::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q;
a | b | c
@@ -2011,11 +2412,11 @@ select * from jsonb_to_recordset('[{"a":1,"b":"foo","d":false},{"a":2,"b":"bar",
(2 rows)
select *, c is null as c_is_null
-from jsonb_to_record('{"a":1, "b":{"c":16, "d":2}, "x":8}'::jsonb)
- as t(a int, b jsonb, c text, x int);
- a | b | c | x | c_is_null
----+-------------------+---+---+-----------
- 1 | {"c": 16, "d": 2} | | 8 | t
+from jsonb_to_record('{"a":1, "b":{"c":16, "d":2}, "x":8, "ca": ["1 2", 3], "ia": [[1,2],[3,4]], "r": {"a": "aaa", "b": 123}}'::jsonb)
+ as t(a int, b jsonb, c text, x int, ca char(5)[], ia int[][], r jbpop);
+ a | b | c | x | ca | ia | r | c_is_null
+---+-------------------+---+---+-------------------+---------------+------------+-----------
+ 1 | {"c": 16, "d": 2} | | 8 | {"1 2 ","3 "} | {{1,2},{3,4}} | (aaa,123,) | t
(1 row)
select *, c is null as c_is_null
@@ -2026,6 +2427,73 @@ from jsonb_to_recordset('[{"a":1, "b":{"c":16, "d":2}, "x":8}]'::jsonb)
1 | {"c": 16, "d": 2} | | 8 | t
(1 row)
+select * from jsonb_to_record('{"ia": null}') as x(ia _int4);
+ ia
+----
+
+(1 row)
+
+select * from jsonb_to_record('{"ia": 123}') as x(ia _int4);
+ERROR: expected json array
+HINT: see the value of key "ia"
+select * from jsonb_to_record('{"ia": [1, "2", null, 4]}') as x(ia _int4);
+ ia
+--------------
+ {1,2,NULL,4}
+(1 row)
+
+select * from jsonb_to_record('{"ia": [[1, 2], [3, 4]]}') as x(ia _int4);
+ ia
+---------------
+ {{1,2},{3,4}}
+(1 row)
+
+select * from jsonb_to_record('{"ia": [[1], 2]}') as x(ia _int4);
+ERROR: expected json array
+HINT: see the array element [1] of key "ia"
+select * from jsonb_to_record('{"ia": [[1], [2, 3]]}') as x(ia _int4);
+ERROR: malformed json array
+DETAIL: Multidimensional arrays must have sub-arrays with matching dimensions.
+select * from jsonb_to_record('{"ia2": [1, 2, 3]}') as x(ia2 int[][]);
+ ia2
+---------
+ {1,2,3}
+(1 row)
+
+select * from jsonb_to_record('{"ia2": [[1, 2], [3, 4]]}') as x(ia2 int4[][]);
+ ia2
+---------------
+ {{1,2},{3,4}}
+(1 row)
+
+select * from jsonb_to_record('{"ia2": [[[1], [2], [3]]]}') as x(ia2 int4[][]);
+ ia2
+-----------------
+ {{{1},{2},{3}}}
+(1 row)
+
+-- test type info caching in jsonb_populate_record()
+CREATE TEMP TABLE jsbpoptest (js jsonb);
+INSERT INTO jsbpoptest
+SELECT '{
+ "jsa": [1, "2", null, 4],
+ "rec": {"a": "abc", "c": "01.02.2003", "x": 43.2},
+ "reca": [{"a": "abc", "b": 456}, null, {"c": "01.02.2003", "x": 43.2}]
+}'::jsonb
+FROM generate_series(1, 3);
+SELECT (jsonb_populate_record(NULL::jsbrec, js)).* FROM jsbpoptest;
+ i | ia | ia1 | ia2 | ia3 | ia1d | ia2d | t | ta | c | ca | ts | js | jsb | jsa | rec | reca
+---+----+-----+-----+-----+------+------+---+----+---+----+----+----+-----+--------------------+-----------------------------------+--------------------------------------------------------
+ | | | | | | | | | | | | | | {1,"\"2\"",NULL,4} | (abc,,"Thu Jan 02 00:00:00 2003") | {"(abc,456,)",NULL,"(,,\"Thu Jan 02 00:00:00 2003\")"}
+ | | | | | | | | | | | | | | {1,"\"2\"",NULL,4} | (abc,,"Thu Jan 02 00:00:00 2003") | {"(abc,456,)",NULL,"(,,\"Thu Jan 02 00:00:00 2003\")"}
+ | | | | | | | | | | | | | | {1,"\"2\"",NULL,4} | (abc,,"Thu Jan 02 00:00:00 2003") | {"(abc,456,)",NULL,"(,,\"Thu Jan 02 00:00:00 2003\")"}
+(3 rows)
+
+DROP TYPE jsbrec;
+DROP TYPE jsbrec_i_not_null;
+DROP DOMAIN jsb_int_not_null;
+DROP DOMAIN jsb_int_array_1d;
+DROP DOMAIN jsb_int_array_2d;
-- indexing
SELECT count(*) FROM testjsonb WHERE j @> '{"wait":null}';
count
diff --git a/src/test/regress/sql/json.sql b/src/test/regress/sql/json.sql
index 0973737289b..506e3a8fc54 100644
--- a/src/test/regress/sql/json.sql
+++ b/src/test/regress/sql/json.sql
@@ -384,6 +384,34 @@ select * from json_array_elements_text('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8
-- populate_record
create type jpop as (a text, b int, c timestamp);
+CREATE DOMAIN js_int_not_null AS int NOT NULL;
+CREATE DOMAIN js_int_array_1d AS int[] CHECK(array_length(VALUE, 1) = 3);
+CREATE DOMAIN js_int_array_2d AS int[][] CHECK(array_length(VALUE, 2) = 3);
+
+CREATE TYPE jsrec AS (
+ i int,
+ ia _int4,
+ ia1 int[],
+ ia2 int[][],
+ ia3 int[][][],
+ ia1d js_int_array_1d,
+ ia2d js_int_array_2d,
+ t text,
+ ta text[],
+ c char(10),
+ ca char(10)[],
+ ts timestamp,
+ js json,
+ jsb jsonb,
+ jsa json[],
+ rec jpop,
+ reca jpop[]
+);
+
+CREATE TYPE jsrec_i_not_null AS (
+ i js_int_not_null
+);
+
select * from json_populate_record(null::jpop,'{"a":"blurfl","x":43.2}') q;
select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"a":"blurfl","x":43.2}') q;
@@ -394,6 +422,100 @@ select * from json_populate_record(null::jpop,'{"a":[100,200,false],"x":43.2}')
select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"a":[100,200,false],"x":43.2}') q;
select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"c":[100,200,false],"x":43.2}') q;
+select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{}') q;
+
+SELECT i FROM json_populate_record(NULL::jsrec_i_not_null, '{"x": 43.2}') q;
+SELECT i FROM json_populate_record(NULL::jsrec_i_not_null, '{"i": null}') q;
+SELECT i FROM json_populate_record(NULL::jsrec_i_not_null, '{"i": 12345}') q;
+
+SELECT ia FROM json_populate_record(NULL::jsrec, '{"ia": null}') q;
+SELECT ia FROM json_populate_record(NULL::jsrec, '{"ia": 123}') q;
+SELECT ia FROM json_populate_record(NULL::jsrec, '{"ia": [1, "2", null, 4]}') q;
+SELECT ia FROM json_populate_record(NULL::jsrec, '{"ia": [[1, 2], [3, 4]]}') q;
+SELECT ia FROM json_populate_record(NULL::jsrec, '{"ia": [[1], 2]}') q;
+SELECT ia FROM json_populate_record(NULL::jsrec, '{"ia": [[1], [2, 3]]}') q;
+SELECT ia FROM json_populate_record(NULL::jsrec, '{"ia": "{1,2,3}"}') q;
+
+SELECT ia1 FROM json_populate_record(NULL::jsrec, '{"ia1": null}') q;
+SELECT ia1 FROM json_populate_record(NULL::jsrec, '{"ia1": 123}') q;
+SELECT ia1 FROM json_populate_record(NULL::jsrec, '{"ia1": [1, "2", null, 4]}') q;
+SELECT ia1 FROM json_populate_record(NULL::jsrec, '{"ia1": [[1, 2, 3]]}') q;
+
+SELECT ia1d FROM json_populate_record(NULL::jsrec, '{"ia1d": null}') q;
+SELECT ia1d FROM json_populate_record(NULL::jsrec, '{"ia1d": 123}') q;
+SELECT ia1d FROM json_populate_record(NULL::jsrec, '{"ia1d": [1, "2", null, 4]}') q;
+SELECT ia1d FROM json_populate_record(NULL::jsrec, '{"ia1d": [1, "2", null]}') q;
+
+SELECT ia2 FROM json_populate_record(NULL::jsrec, '{"ia2": [1, "2", null, 4]}') q;
+SELECT ia2 FROM json_populate_record(NULL::jsrec, '{"ia2": [[1, 2], [null, 4]]}') q;
+SELECT ia2 FROM json_populate_record(NULL::jsrec, '{"ia2": [[], []]}') q;
+SELECT ia2 FROM json_populate_record(NULL::jsrec, '{"ia2": [[1, 2], [3]]}') q;
+SELECT ia2 FROM json_populate_record(NULL::jsrec, '{"ia2": [[1, 2], 3, 4]}') q;
+
+SELECT ia2d FROM json_populate_record(NULL::jsrec, '{"ia2d": [[1, "2"], [null, 4]]}') q;
+SELECT ia2d FROM json_populate_record(NULL::jsrec, '{"ia2d": [[1, "2", 3], [null, 5, 6]]}') q;
+
+SELECT ia3 FROM json_populate_record(NULL::jsrec, '{"ia3": [1, "2", null, 4]}') q;
+SELECT ia3 FROM json_populate_record(NULL::jsrec, '{"ia3": [[1, 2], [null, 4]]}') q;
+SELECT ia3 FROM json_populate_record(NULL::jsrec, '{"ia3": [ [[], []], [[], []], [[], []] ]}') q;
+SELECT ia3 FROM json_populate_record(NULL::jsrec, '{"ia3": [ [[1, 2]], [[3, 4]] ]}') q;
+SELECT ia3 FROM json_populate_record(NULL::jsrec, '{"ia3": [ [[1, 2], [3, 4]], [[5, 6], [7, 8]] ]}') q;
+SELECT ia3 FROM json_populate_record(NULL::jsrec, '{"ia3": [ [[1, 2], [3, 4]], [[5, 6], [7, 8], [9, 10]] ]}') q;
+
+SELECT ta FROM json_populate_record(NULL::jsrec, '{"ta": null}') q;
+SELECT ta FROM json_populate_record(NULL::jsrec, '{"ta": 123}') q;
+SELECT ta FROM json_populate_record(NULL::jsrec, '{"ta": [1, "2", null, 4]}') q;
+SELECT ta FROM json_populate_record(NULL::jsrec, '{"ta": [[1, 2, 3], {"k": "v"}]}') q;
+
+SELECT c FROM json_populate_record(NULL::jsrec, '{"c": null}') q;
+SELECT c FROM json_populate_record(NULL::jsrec, '{"c": "aaa"}') q;
+SELECT c FROM json_populate_record(NULL::jsrec, '{"c": "aaaaaaaaaa"}') q;
+SELECT c FROM json_populate_record(NULL::jsrec, '{"c": "aaaaaaaaaaaaa"}') q;
+
+SELECT ca FROM json_populate_record(NULL::jsrec, '{"ca": null}') q;
+SELECT ca FROM json_populate_record(NULL::jsrec, '{"ca": 123}') q;
+SELECT ca FROM json_populate_record(NULL::jsrec, '{"ca": [1, "2", null, 4]}') q;
+SELECT ca FROM json_populate_record(NULL::jsrec, '{"ca": ["aaaaaaaaaaaaaaaa"]}') q;
+SELECT ca FROM json_populate_record(NULL::jsrec, '{"ca": [[1, 2, 3], {"k": "v"}]}') q;
+
+SELECT js FROM json_populate_record(NULL::jsrec, '{"js": null}') q;
+SELECT js FROM json_populate_record(NULL::jsrec, '{"js": true}') q;
+SELECT js FROM json_populate_record(NULL::jsrec, '{"js": 123.45}') q;
+SELECT js FROM json_populate_record(NULL::jsrec, '{"js": "123.45"}') q;
+SELECT js FROM json_populate_record(NULL::jsrec, '{"js": "abc"}') q;
+SELECT js FROM json_populate_record(NULL::jsrec, '{"js": [123, "123", null, {"key": "value"}]}') q;
+SELECT js FROM json_populate_record(NULL::jsrec, '{"js": {"a": "bbb", "b": null, "c": 123.45}}') q;
+
+SELECT jsb FROM json_populate_record(NULL::jsrec, '{"jsb": null}') q;
+SELECT jsb FROM json_populate_record(NULL::jsrec, '{"jsb": true}') q;
+SELECT jsb FROM json_populate_record(NULL::jsrec, '{"jsb": 123.45}') q;
+SELECT jsb FROM json_populate_record(NULL::jsrec, '{"jsb": "123.45"}') q;
+SELECT jsb FROM json_populate_record(NULL::jsrec, '{"jsb": "abc"}') q;
+SELECT jsb FROM json_populate_record(NULL::jsrec, '{"jsb": [123, "123", null, {"key": "value"}]}') q;
+SELECT jsb FROM json_populate_record(NULL::jsrec, '{"jsb": {"a": "bbb", "b": null, "c": 123.45}}') q;
+
+SELECT jsa FROM json_populate_record(NULL::jsrec, '{"jsa": null}') q;
+SELECT jsa FROM json_populate_record(NULL::jsrec, '{"jsa": 123}') q;
+SELECT jsa FROM json_populate_record(NULL::jsrec, '{"jsa": [1, "2", null, 4]}') q;
+SELECT jsa FROM json_populate_record(NULL::jsrec, '{"jsa": ["aaa", null, [1, 2, "3", {}], { "k" : "v" }]}') q;
+
+SELECT rec FROM json_populate_record(NULL::jsrec, '{"rec": 123}') q;
+SELECT rec FROM json_populate_record(NULL::jsrec, '{"rec": [1, 2]}') q;
+SELECT rec FROM json_populate_record(NULL::jsrec, '{"rec": {"a": "abc", "c": "01.02.2003", "x": 43.2}}') q;
+SELECT rec FROM json_populate_record(NULL::jsrec, '{"rec": "(abc,42,01.02.2003)"}') q;
+
+SELECT reca FROM json_populate_record(NULL::jsrec, '{"reca": 123}') q;
+SELECT reca FROM json_populate_record(NULL::jsrec, '{"reca": [1, 2]}') q;
+SELECT reca FROM json_populate_record(NULL::jsrec, '{"reca": [{"a": "abc", "b": 456}, null, {"c": "01.02.2003", "x": 43.2}]}') q;
+SELECT reca FROM json_populate_record(NULL::jsrec, '{"reca": ["(abc,42,01.02.2003)"]}') q;
+SELECT reca FROM json_populate_record(NULL::jsrec, '{"reca": "{\"(abc,42,01.02.2003)\"}"}') q;
+
+SELECT rec FROM json_populate_record(
+ row(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
+ row('x',3,'2012-12-31 15:30:56')::jpop,NULL)::jsrec,
+ '{"rec": {"a": "abc", "c": "01.02.2003", "x": 43.2}}'
+) q;
+
-- populate_recordset
select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q;
@@ -410,6 +532,25 @@ select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":
select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q;
select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]') q;
+-- test type info caching in json_populate_record()
+CREATE TEMP TABLE jspoptest (js json);
+
+INSERT INTO jspoptest
+SELECT '{
+ "jsa": [1, "2", null, 4],
+ "rec": {"a": "abc", "c": "01.02.2003", "x": 43.2},
+ "reca": [{"a": "abc", "b": 456}, null, {"c": "01.02.2003", "x": 43.2}]
+}'::json
+FROM generate_series(1, 3);
+
+SELECT (json_populate_record(NULL::jsrec, js)).* FROM jspoptest;
+
+DROP TYPE jsrec;
+DROP TYPE jsrec_i_not_null;
+DROP DOMAIN js_int_not_null;
+DROP DOMAIN js_int_array_1d;
+DROP DOMAIN js_int_array_2d;
+
--json_typeof() function
select value, json_typeof(value)
from (values (json '123.4'),
@@ -526,13 +667,24 @@ select * from json_to_recordset('[{"a":1,"b":{"d":"foo"},"c":true},{"a":2,"c":fa
as x(a int, b json, c boolean);
select *, c is null as c_is_null
-from json_to_record('{"a":1, "b":{"c":16, "d":2}, "x":8}'::json)
- as t(a int, b json, c text, x int);
+from json_to_record('{"a":1, "b":{"c":16, "d":2}, "x":8, "ca": ["1 2", 3], "ia": [[1,2],[3,4]], "r": {"a": "aaa", "b": 123}}'::json)
+ as t(a int, b json, c text, x int, ca char(5)[], ia int[][], r jpop);
select *, c is null as c_is_null
from json_to_recordset('[{"a":1, "b":{"c":16, "d":2}, "x":8}]'::json)
as t(a int, b json, c text, x int);
+select * from json_to_record('{"ia": null}') as x(ia _int4);
+select * from json_to_record('{"ia": 123}') as x(ia _int4);
+select * from json_to_record('{"ia": [1, "2", null, 4]}') as x(ia _int4);
+select * from json_to_record('{"ia": [[1, 2], [3, 4]]}') as x(ia _int4);
+select * from json_to_record('{"ia": [[1], 2]}') as x(ia _int4);
+select * from json_to_record('{"ia": [[1], [2, 3]]}') as x(ia _int4);
+
+select * from json_to_record('{"ia2": [1, 2, 3]}') as x(ia2 int[][]);
+select * from json_to_record('{"ia2": [[1, 2], [3, 4]]}') as x(ia2 int4[][]);
+select * from json_to_record('{"ia2": [[[1], [2], [3]]]}') as x(ia2 int4[][]);
+
-- json_strip_nulls
select json_strip_nulls(null);
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index 576e1448fd4..57fff3bfb3e 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -484,6 +484,34 @@ SELECT * FROM jsonb_array_elements_text('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,
-- populate_record
CREATE TYPE jbpop AS (a text, b int, c timestamp);
+CREATE DOMAIN jsb_int_not_null AS int NOT NULL;
+CREATE DOMAIN jsb_int_array_1d AS int[] CHECK(array_length(VALUE, 1) = 3);
+CREATE DOMAIN jsb_int_array_2d AS int[][] CHECK(array_length(VALUE, 2) = 3);
+
+CREATE TYPE jsbrec AS (
+ i int,
+ ia _int4,
+ ia1 int[],
+ ia2 int[][],
+ ia3 int[][][],
+ ia1d jsb_int_array_1d,
+ ia2d jsb_int_array_2d,
+ t text,
+ ta text[],
+ c char(10),
+ ca char(10)[],
+ ts timestamp,
+ js json,
+ jsb jsonb,
+ jsa json[],
+ rec jbpop,
+ reca jbpop[]
+);
+
+CREATE TYPE jsbrec_i_not_null AS (
+ i jsb_int_not_null
+);
+
SELECT * FROM jsonb_populate_record(NULL::jbpop,'{"a":"blurfl","x":43.2}') q;
SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"a":"blurfl","x":43.2}') q;
@@ -494,6 +522,100 @@ SELECT * FROM jsonb_populate_record(NULL::jbpop,'{"a":[100,200,false],"x":43.2}'
SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"a":[100,200,false],"x":43.2}') q;
SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"c":[100,200,false],"x":43.2}') q;
+SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop, '{}') q;
+
+SELECT i FROM jsonb_populate_record(NULL::jsbrec_i_not_null, '{"x": 43.2}') q;
+SELECT i FROM jsonb_populate_record(NULL::jsbrec_i_not_null, '{"i": null}') q;
+SELECT i FROM jsonb_populate_record(NULL::jsbrec_i_not_null, '{"i": 12345}') q;
+
+SELECT ia FROM jsonb_populate_record(NULL::jsbrec, '{"ia": null}') q;
+SELECT ia FROM jsonb_populate_record(NULL::jsbrec, '{"ia": 123}') q;
+SELECT ia FROM jsonb_populate_record(NULL::jsbrec, '{"ia": [1, "2", null, 4]}') q;
+SELECT ia FROM jsonb_populate_record(NULL::jsbrec, '{"ia": [[1, 2], [3, 4]]}') q;
+SELECT ia FROM jsonb_populate_record(NULL::jsbrec, '{"ia": [[1], 2]}') q;
+SELECT ia FROM jsonb_populate_record(NULL::jsbrec, '{"ia": [[1], [2, 3]]}') q;
+SELECT ia FROM jsonb_populate_record(NULL::jsbrec, '{"ia": "{1,2,3}"}') q;
+
+SELECT ia1 FROM jsonb_populate_record(NULL::jsbrec, '{"ia1": null}') q;
+SELECT ia1 FROM jsonb_populate_record(NULL::jsbrec, '{"ia1": 123}') q;
+SELECT ia1 FROM jsonb_populate_record(NULL::jsbrec, '{"ia1": [1, "2", null, 4]}') q;
+SELECT ia1 FROM jsonb_populate_record(NULL::jsbrec, '{"ia1": [[1, 2, 3]]}') q;
+
+SELECT ia1d FROM jsonb_populate_record(NULL::jsbrec, '{"ia1d": null}') q;
+SELECT ia1d FROM jsonb_populate_record(NULL::jsbrec, '{"ia1d": 123}') q;
+SELECT ia1d FROM jsonb_populate_record(NULL::jsbrec, '{"ia1d": [1, "2", null, 4]}') q;
+SELECT ia1d FROM jsonb_populate_record(NULL::jsbrec, '{"ia1d": [1, "2", null]}') q;
+
+SELECT ia2 FROM jsonb_populate_record(NULL::jsbrec, '{"ia2": [1, "2", null, 4]}') q;
+SELECT ia2 FROM jsonb_populate_record(NULL::jsbrec, '{"ia2": [[1, 2], [null, 4]]}') q;
+SELECT ia2 FROM jsonb_populate_record(NULL::jsbrec, '{"ia2": [[], []]}') q;
+SELECT ia2 FROM jsonb_populate_record(NULL::jsbrec, '{"ia2": [[1, 2], [3]]}') q;
+SELECT ia2 FROM jsonb_populate_record(NULL::jsbrec, '{"ia2": [[1, 2], 3, 4]}') q;
+
+SELECT ia2d FROM jsonb_populate_record(NULL::jsbrec, '{"ia2d": [[1, "2"], [null, 4]]}') q;
+SELECT ia2d FROM jsonb_populate_record(NULL::jsbrec, '{"ia2d": [[1, "2", 3], [null, 5, 6]]}') q;
+
+SELECT ia3 FROM jsonb_populate_record(NULL::jsbrec, '{"ia3": [1, "2", null, 4]}') q;
+SELECT ia3 FROM jsonb_populate_record(NULL::jsbrec, '{"ia3": [[1, 2], [null, 4]]}') q;
+SELECT ia3 FROM jsonb_populate_record(NULL::jsbrec, '{"ia3": [ [[], []], [[], []], [[], []] ]}') q;
+SELECT ia3 FROM jsonb_populate_record(NULL::jsbrec, '{"ia3": [ [[1, 2]], [[3, 4]] ]}') q;
+SELECT ia3 FROM jsonb_populate_record(NULL::jsbrec, '{"ia3": [ [[1, 2], [3, 4]], [[5, 6], [7, 8]] ]}') q;
+SELECT ia3 FROM jsonb_populate_record(NULL::jsbrec, '{"ia3": [ [[1, 2], [3, 4]], [[5, 6], [7, 8], [9, 10]] ]}') q;
+
+SELECT ta FROM jsonb_populate_record(NULL::jsbrec, '{"ta": null}') q;
+SELECT ta FROM jsonb_populate_record(NULL::jsbrec, '{"ta": 123}') q;
+SELECT ta FROM jsonb_populate_record(NULL::jsbrec, '{"ta": [1, "2", null, 4]}') q;
+SELECT ta FROM jsonb_populate_record(NULL::jsbrec, '{"ta": [[1, 2, 3], {"k": "v"}]}') q;
+
+SELECT c FROM jsonb_populate_record(NULL::jsbrec, '{"c": null}') q;
+SELECT c FROM jsonb_populate_record(NULL::jsbrec, '{"c": "aaa"}') q;
+SELECT c FROM jsonb_populate_record(NULL::jsbrec, '{"c": "aaaaaaaaaa"}') q;
+SELECT c FROM jsonb_populate_record(NULL::jsbrec, '{"c": "aaaaaaaaaaaaa"}') q;
+
+SELECT ca FROM jsonb_populate_record(NULL::jsbrec, '{"ca": null}') q;
+SELECT ca FROM jsonb_populate_record(NULL::jsbrec, '{"ca": 123}') q;
+SELECT ca FROM jsonb_populate_record(NULL::jsbrec, '{"ca": [1, "2", null, 4]}') q;
+SELECT ca FROM jsonb_populate_record(NULL::jsbrec, '{"ca": ["aaaaaaaaaaaaaaaa"]}') q;
+SELECT ca FROM jsonb_populate_record(NULL::jsbrec, '{"ca": [[1, 2, 3], {"k": "v"}]}') q;
+
+SELECT js FROM jsonb_populate_record(NULL::jsbrec, '{"js": null}') q;
+SELECT js FROM jsonb_populate_record(NULL::jsbrec, '{"js": true}') q;
+SELECT js FROM jsonb_populate_record(NULL::jsbrec, '{"js": 123.45}') q;
+SELECT js FROM jsonb_populate_record(NULL::jsbrec, '{"js": "123.45"}') q;
+SELECT js FROM jsonb_populate_record(NULL::jsbrec, '{"js": "abc"}') q;
+SELECT js FROM jsonb_populate_record(NULL::jsbrec, '{"js": [123, "123", null, {"key": "value"}]}') q;
+SELECT js FROM jsonb_populate_record(NULL::jsbrec, '{"js": {"a": "bbb", "b": null, "c": 123.45}}') q;
+
+SELECT jsb FROM jsonb_populate_record(NULL::jsbrec, '{"jsb": null}') q;
+SELECT jsb FROM jsonb_populate_record(NULL::jsbrec, '{"jsb": true}') q;
+SELECT jsb FROM jsonb_populate_record(NULL::jsbrec, '{"jsb": 123.45}') q;
+SELECT jsb FROM jsonb_populate_record(NULL::jsbrec, '{"jsb": "123.45"}') q;
+SELECT jsb FROM jsonb_populate_record(NULL::jsbrec, '{"jsb": "abc"}') q;
+SELECT jsb FROM jsonb_populate_record(NULL::jsbrec, '{"jsb": [123, "123", null, {"key": "value"}]}') q;
+SELECT jsb FROM jsonb_populate_record(NULL::jsbrec, '{"jsb": {"a": "bbb", "b": null, "c": 123.45}}') q;
+
+SELECT jsa FROM jsonb_populate_record(NULL::jsbrec, '{"jsa": null}') q;
+SELECT jsa FROM jsonb_populate_record(NULL::jsbrec, '{"jsa": 123}') q;
+SELECT jsa FROM jsonb_populate_record(NULL::jsbrec, '{"jsa": [1, "2", null, 4]}') q;
+SELECT jsa FROM jsonb_populate_record(NULL::jsbrec, '{"jsa": ["aaa", null, [1, 2, "3", {}], { "k" : "v" }]}') q;
+
+SELECT rec FROM jsonb_populate_record(NULL::jsbrec, '{"rec": 123}') q;
+SELECT rec FROM jsonb_populate_record(NULL::jsbrec, '{"rec": [1, 2]}') q;
+SELECT rec FROM jsonb_populate_record(NULL::jsbrec, '{"rec": {"a": "abc", "c": "01.02.2003", "x": 43.2}}') q;
+SELECT rec FROM jsonb_populate_record(NULL::jsbrec, '{"rec": "(abc,42,01.02.2003)"}') q;
+
+SELECT reca FROM jsonb_populate_record(NULL::jsbrec, '{"reca": 123}') q;
+SELECT reca FROM jsonb_populate_record(NULL::jsbrec, '{"reca": [1, 2]}') q;
+SELECT reca FROM jsonb_populate_record(NULL::jsbrec, '{"reca": [{"a": "abc", "b": 456}, null, {"c": "01.02.2003", "x": 43.2}]}') q;
+SELECT reca FROM jsonb_populate_record(NULL::jsbrec, '{"reca": ["(abc,42,01.02.2003)"]}') q;
+SELECT reca FROM jsonb_populate_record(NULL::jsbrec, '{"reca": "{\"(abc,42,01.02.2003)\"}"}') q;
+
+SELECT rec FROM jsonb_populate_record(
+ row(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
+ row('x',3,'2012-12-31 15:30:56')::jbpop,NULL)::jsbrec,
+ '{"rec": {"a": "abc", "c": "01.02.2003", "x": 43.2}}'
+) q;
+
-- populate_recordset
SELECT * FROM jsonb_populate_recordset(NULL::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q;
SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q;
@@ -515,13 +637,43 @@ select * from jsonb_to_recordset('[{"a":1,"b":"foo","d":false},{"a":2,"b":"bar",
as x(a int, b text, c boolean);
select *, c is null as c_is_null
-from jsonb_to_record('{"a":1, "b":{"c":16, "d":2}, "x":8}'::jsonb)
- as t(a int, b jsonb, c text, x int);
+from jsonb_to_record('{"a":1, "b":{"c":16, "d":2}, "x":8, "ca": ["1 2", 3], "ia": [[1,2],[3,4]], "r": {"a": "aaa", "b": 123}}'::jsonb)
+ as t(a int, b jsonb, c text, x int, ca char(5)[], ia int[][], r jbpop);
select *, c is null as c_is_null
from jsonb_to_recordset('[{"a":1, "b":{"c":16, "d":2}, "x":8}]'::jsonb)
as t(a int, b jsonb, c text, x int);
+select * from jsonb_to_record('{"ia": null}') as x(ia _int4);
+select * from jsonb_to_record('{"ia": 123}') as x(ia _int4);
+select * from jsonb_to_record('{"ia": [1, "2", null, 4]}') as x(ia _int4);
+select * from jsonb_to_record('{"ia": [[1, 2], [3, 4]]}') as x(ia _int4);
+select * from jsonb_to_record('{"ia": [[1], 2]}') as x(ia _int4);
+select * from jsonb_to_record('{"ia": [[1], [2, 3]]}') as x(ia _int4);
+
+select * from jsonb_to_record('{"ia2": [1, 2, 3]}') as x(ia2 int[][]);
+select * from jsonb_to_record('{"ia2": [[1, 2], [3, 4]]}') as x(ia2 int4[][]);
+select * from jsonb_to_record('{"ia2": [[[1], [2], [3]]]}') as x(ia2 int4[][]);
+
+-- test type info caching in jsonb_populate_record()
+CREATE TEMP TABLE jsbpoptest (js jsonb);
+
+INSERT INTO jsbpoptest
+SELECT '{
+ "jsa": [1, "2", null, 4],
+ "rec": {"a": "abc", "c": "01.02.2003", "x": 43.2},
+ "reca": [{"a": "abc", "b": 456}, null, {"c": "01.02.2003", "x": 43.2}]
+}'::jsonb
+FROM generate_series(1, 3);
+
+SELECT (jsonb_populate_record(NULL::jsbrec, js)).* FROM jsbpoptest;
+
+DROP TYPE jsbrec;
+DROP TYPE jsbrec_i_not_null;
+DROP DOMAIN jsb_int_not_null;
+DROP DOMAIN jsb_int_array_1d;
+DROP DOMAIN jsb_int_array_2d;
+
-- indexing
SELECT count(*) FROM testjsonb WHERE j @> '{"wait":null}';
SELECT count(*) FROM testjsonb WHERE j @> '{"wait":"CC"}';
generated by cgit v1.2.3 (git 2.39.1) at 2025-12-21 00:27:29 +0000