diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 9408a25..edcc96d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -11525,12 +11525,12 @@ table2-mapping whose columns match the record type defined by base (see note below). - select * from json_populate_record(null::myrowtype, '{"a":1,"b":2}') + select * from json_populate_record(null::myrowtype, '{"a": 1, "b": ["2", "a b"], "c": {"d": 4, "e": "a b c"}}') - a | b ----+--- - 1 | 2 + a | b | c +---+-----------+------------- + 1 | {2,"a b"} | (4,"a b c") @@ -11619,12 +11619,12 @@ table2-mapping explicitly define the structure of the record with an AS clause. - select * from json_to_record('{"a":1,"b":[1,2,3],"c":"bar"}') as x(a int, b text, d text) + select * from json_to_record('{"a":1,"b":[1,2,3],"c":[1,2,3],"e":"bar","r": {"a": 123, "b": "a b c"}}') as x(a int, b text, c int[], d text, r myrowtype) - a | b | d ----+---------+--- - 1 | [1,2,3] | + a | b | c | d | r +---+---------+---------+---+--------------- + 1 | [1,2,3] | {1,2,3} | | (123,"a b c") diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c index da29dab..507b47f 100644 --- a/src/backend/utils/adt/jsonfuncs.c +++ b/src/backend/utils/adt/jsonfuncs.c @@ -31,6 +31,7 @@ #include "utils/jsonb.h" #include "utils/lsyscache.h" #include "utils/memutils.h" +#include "utils/syscache.h" #include "utils/typcache.h" /* Operations available for setPath */ @@ -130,6 +131,14 @@ typedef struct ScalarIOData typedef struct ColumnIOData ColumnIOData; typedef struct RecordIOData RecordIOData; +/* structure to cache metadata needed for populate_array() */ +typedef struct ArrayIOData +{ + ColumnIOData *element_info; /* metadata cache */ + Oid element_type; /* array element type id */ + int32 element_typmod; /* array element type modifier */ +} ArrayIOData; + /* structure to cache metadata needed for populate_composite() */ typedef struct CompositeIOData { @@ -141,6 +150,24 @@ typedef struct CompositeIOData TupleDesc tupdesc; /* cached tuple descriptor */ } CompositeIOData; +/* structure to cache metadata needed for populate_domain() */ +typedef struct DomainIOData +{ + ColumnIOData *base_io; /* metadata cache */ + Oid base_typid; /* base type id */ + int32 base_typmod; /* base type modifier */ + void *domain_info; /* opaque cache for domain checks */ +} DomainIOData; + +/* enumeration type categories */ +typedef enum TypeCat +{ + TYPECAT_SCALAR = 's', + TYPECAT_ARRAY = 'a', + TYPECAT_COMPOSITE = 'c', + TYPECAT_DOMAIN = 'd', +} TypeCat; + /* these two are stolen from hstore / record_out, used in populate_record* */ /* structure to cache record metadata needed for populate_record_field() */ @@ -148,8 +175,15 @@ struct ColumnIOData { Oid typid; /* column type id */ int32 typmod; /* column type modifier */ + TypeCat typcat; /* column type category */ ScalarIOData scalar_io; /* metadata cache for directi conversion * through input function */ + union + { + ArrayIOData array; + CompositeIOData composite; + DomainIOData domain; + } io; /* metadata cache for various column type categories */ }; /* structure to cache record metadata needed for populate_record() */ @@ -177,6 +211,29 @@ typedef struct PopulateRecordsetState MemoryContext fn_mcxt; /* used to stash IO funcs */ } PopulateRecordsetState; +/* common data for populate_array_json() and populate_array_dim_jsonb() */ +typedef struct PopulateArrayContext +{ + ArrayBuildState *astate; /* array build state */ + ArrayIOData *aio; /* metadata cache */ + MemoryContext acxt; /* array build memory context */ + MemoryContext mcxt; /* cache memory context */ + const char *colname; /* for diagnostics only */ + int *dims; /* dimensions */ + int *sizes; /* current dimension counters */ + int ndims; /* number of dimensions */ +} PopulateArrayContext; + +/* state for populate_array_json() */ +typedef struct PopulateArrayState +{ + JsonLexContext *lex; /* json lexer */ + PopulateArrayContext *ctx; /* context */ + char *element_start; /* start of the current array element */ + char *element_scalar; /* current array element token if it is a scalar */ + JsonTokenType element_type; /* current array element type */ +} PopulateArrayState; + /* state for json_strip_nulls */ typedef struct StripnullState { @@ -2179,6 +2236,358 @@ json_to_record(PG_FUNCTION_ARGS) return populate_record_worker(fcinfo, "json_to_record", false); } +/* helper function for diagnostics */ +static void +populate_array_report_expected_array(PopulateArrayContext *ctx, int ndim) +{ + if (ndim <= 0) + { + if (ctx->colname) + ereport(ERROR, + (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION), + errmsg("expected json array"), + errhint("see the value of key \"%s\"", ctx->colname))); + else + ereport(ERROR, + (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION), + errmsg("expected json array"))); + } + else + { + StringInfoData indices; + int i; + + initStringInfo(&indices); + + Assert(ctx->ndims > 0 && ndim < ctx->ndims); + + for (i = 0; i < ndim; i++) + appendStringInfo(&indices, "[%d]", ctx->sizes[i]); + + ereport(ERROR, + (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION), + errmsg("expected json array"), + errhint(ctx->colname ? "see the array element %s of key \"%s\"" + : "see the array element %s", + indices.data, ctx->colname))); + } +} + +/* assign number of dimensions of the populated array when it became known */ +static void +populate_array_assign_ndims(PopulateArrayContext *ctx, int ndims) +{ + int i; + + Assert(ctx->ndims <= 0); + + if (ndims <= 0) + populate_array_report_expected_array(ctx, ndims); + + ctx->ndims = ndims; + ctx->dims = palloc(sizeof(int) * ndims); + ctx->sizes = palloc0(sizeof(int) * ndims); + + for (i = 0; i < ndims; i++) + ctx->dims[i] = -1; /* dimensions are unknown yet */ +} + +/* check populated subarray dimension */ +static void +populate_array_check_dimension(PopulateArrayContext *ctx, int ndim) +{ + int dim = ctx->sizes[ndim]; /* current dimension counter */ + + if (ctx->dims[ndim] == -1) + ctx->dims[ndim] = dim; /* assign dimension if unknown yet */ + else if (ctx->dims[ndim] != dim) + ereport(ERROR, + (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION), + errmsg("malformed json array"), + errdetail("Multidimensional arrays must have " + "sub-arrays with matching dimensions."))); + + /* reset current array dimension size counter */ + ctx->sizes[ndim] = 0; + + /* increment parent dimension counter if it is a nested sub-array */ + if (ndim > 0) + ctx->sizes[ndim - 1]++; +} + +static void +populate_array_element(PopulateArrayContext *ctx, int ndim, JsValue *jsv) +{ + Datum element; + bool element_isnull; + + /* populate array element */ + element = populate_record_field(ctx->aio->element_info, + ctx->aio->element_type, + ctx->aio->element_typmod, + NULL, ctx->mcxt, PointerGetDatum(NULL), + jsv, &element_isnull); + + accumArrayResult(ctx->astate, element, element_isnull, + ctx->aio->element_type, ctx->acxt); + + Assert(ndim > 0); + ctx->sizes[ndim - 1]++; /* increment current dimension counter */ +} + +/* json object start handler for populate_array_json() */ +static void +populate_array_object_start(void *_state) +{ + PopulateArrayState *state = (PopulateArrayState *) _state; + int ndim = state->lex->lex_level; + + if (state->ctx->ndims <= 0) + populate_array_assign_ndims(state->ctx, ndim); + else if (ndim < state->ctx->ndims) + populate_array_report_expected_array(state->ctx, ndim); +} + +/* json array end handler for populate_array_json() */ +static void +populate_array_array_end(void *_state) +{ + PopulateArrayState *state = (PopulateArrayState *) _state; + PopulateArrayContext *ctx = state->ctx; + int ndim = state->lex->lex_level; + + if (ctx->ndims <= 0) + populate_array_assign_ndims(ctx, ndim + 1); + + if (ndim < ctx->ndims) + populate_array_check_dimension(ctx, ndim); +} + +/* json array element start handler for populate_array_json() */ +static void +populate_array_element_start(void *_state, bool isnull) +{ + PopulateArrayState *state = (PopulateArrayState *) _state; + int ndim = state->lex->lex_level; + + if (state->ctx->ndims <= 0 || ndim == state->ctx->ndims) + { + /* remember current array element start */ + state->element_start = state->lex->token_start; + state->element_type = state->lex->token_type; + state->element_scalar = NULL; + } +} + +/* json array element end handler for populate_array_json() */ +static void +populate_array_element_end(void *_state, bool isnull) +{ + PopulateArrayState *state = (PopulateArrayState *) _state; + PopulateArrayContext *ctx = state->ctx; + int ndim = state->lex->lex_level; + + Assert(ctx->ndims > 0); + + if (ndim == ctx->ndims) + { + JsValue jsv; + + jsv.is_json = true; + jsv.val.json.type = state->element_type; + + if (isnull) + { + Assert(jsv.val.json.type == JSON_TOKEN_NULL); + jsv.val.json.str = NULL; + jsv.val.json.len = 0; + } + else if (state->element_scalar) + { + jsv.val.json.str = state->element_scalar; + jsv.val.json.len = -1; /* null-terminated */ + } + else + { + jsv.val.json.str = state->element_start; + jsv.val.json.len = (state->lex->prev_token_terminator - + state->element_start) * sizeof(char); + } + + populate_array_element(ctx, ndim, &jsv); + } +} + +/* json scalar handler for populate_array_json() */ +static void +populate_array_scalar(void *_state, char *token, JsonTokenType tokentype) +{ + PopulateArrayState *state = (PopulateArrayState *) _state; + PopulateArrayContext *ctx = state->ctx; + int ndim = state->lex->lex_level; + + if (ctx->ndims <= 0) + populate_array_assign_ndims(ctx, ndim); + else if (ndim < ctx->ndims) + populate_array_report_expected_array(ctx, ndim); + + if (ndim == ctx->ndims) + { + /* remeber scalar element token */ + state->element_scalar = token; + /* element_type must already be set in populate_array_element_start() */ + Assert(state->element_type == tokentype); + } +} + +/* parse json array and populate array */ +static void +populate_array_json(PopulateArrayContext *ctx, char *json, int len) +{ + PopulateArrayState state; + JsonSemAction sem; + + state.lex = makeJsonLexContextCstringLen(json, len, true); + state.ctx = ctx; + + memset(&sem, 0, sizeof(sem)); + sem.semstate = (void *) &state; + sem.object_start = populate_array_object_start; + sem.array_end = populate_array_array_end; + sem.array_element_start = populate_array_element_start; + sem.array_element_end = populate_array_element_end; + sem.scalar = populate_array_scalar; + + pg_parse_json(state.lex, &sem); + + /* number of dimensions should be already known */ + Assert(ctx->ndims > 0 && ctx->dims); + + pfree(state.lex); +} + +/* + * populate_array_dim_jsonb() -- Iterate recursively through jsonb sub-array + * elements and accumulate result using given ArrayBuildState. + */ +static void +populate_array_dim_jsonb(PopulateArrayContext *ctx, /* context */ + JsonbValue *jbv, /* jsonb sub-array */ + int ndim) /* current dimension */ +{ + JsonbContainer *jbc = jbv->val.binary.data; + JsonbIterator *it; + JsonbIteratorToken tok; + JsonbValue val; + JsValue jsv; + + check_stack_depth(); + + if (jbv->type != jbvBinary || !JsonContainerIsArray(jbc)) + populate_array_report_expected_array(ctx, ndim - 1); + + Assert(!JsonContainerIsScalar(jbc)); + + it = JsonbIteratorInit(jbc); + + tok = JsonbIteratorNext(&it, &val, true); + Assert(tok == WJB_BEGIN_ARRAY); + + tok = JsonbIteratorNext(&it, &val, true); + + /* + * If the number of dimensions is yet unknown and + * we have found end of array or the first child element is not an array + * then assign number of dimensions now. + */ + if (ctx->ndims <= 0 && + (tok == WJB_END_ARRAY || + (tok == WJB_ELEM && + (val.type != jbvBinary || + !JsonContainerIsArray(val.val.binary.data))))) + populate_array_assign_ndims(ctx, ndim); + + jsv.is_json = false; + jsv.val.jsonb = &val; + + /* process all array elements */ + while (tok == WJB_ELEM) + { + /* + * Recurse only if the dimensions of dimensions is yet unknown or + * if it is not the innermost dimension. + */ + if (ctx->ndims > 0 && ndim >= ctx->ndims) + populate_array_element(ctx, ndim, &jsv); + else + { + /* populate child sub-array */ + populate_array_dim_jsonb(ctx, &val, ndim + 1); + + /* number of dimensions should be already known */ + Assert(ctx->ndims > 0 && ctx->dims); + + populate_array_check_dimension(ctx, ndim); + } + + tok = JsonbIteratorNext(&it, &val, true); + } + + Assert(tok == WJB_END_ARRAY); + + /* free iterator, iterating until WJB_DONE */ + tok = JsonbIteratorNext(&it, &val, true); + Assert(tok == WJB_DONE && !it); +} + +/* populate recursively json/jsonb array */ +static Datum +populate_array(ArrayIOData *aio, /* metadata cache */ + const char *colname, /* for diagnostics only */ + MemoryContext mcxt, /* cache memory context */ + JsValue *jsv) /* json/jsonb array */ +{ + PopulateArrayContext ctx; + Datum result; + int *lbs; + int i; + + ctx.aio = aio; + ctx.mcxt = mcxt; + ctx.acxt = CurrentMemoryContext; + ctx.astate = initArrayResult(aio->element_type, ctx.acxt, true); + ctx.colname = colname; + ctx.ndims = 0; /* unknown yet */ + ctx.dims = NULL; + ctx.sizes = NULL; + + if (jsv->is_json) + populate_array_json(&ctx, jsv->val.json.str, + jsv->val.json.len >= 0 ? jsv->val.json.len + : strlen(jsv->val.json.str)); + else + { + populate_array_dim_jsonb(&ctx, jsv->val.jsonb, 1); + ctx.dims[0] = ctx.sizes[0]; + } + + Assert(ctx.ndims > 0); + + lbs = palloc(sizeof(int) * ctx.ndims); + + for (i = 0; i < ctx.ndims; i++) + lbs[i] = 1; + + result = makeMdArrayResult(ctx.astate, ctx.ndims, ctx.dims, lbs, + ctx.acxt, true); + + pfree(ctx.dims); + pfree(ctx.sizes); + pfree(lbs); + + return result; +} + static void JsValueToJsObject(JsValue *jsv, JsObject *jso) { @@ -2258,26 +2667,35 @@ populate_scalar(ScalarIOData *io, /* metadata cache */ JsValue *jsv) /* json/jsonb value to convert */ { Datum res; - char *str; + char *str = NULL; char *json = NULL; - if (!jsv) - { - /* - * Need InputFunctionCall to happen even for NULLs, so that domain - * checks are done. - */ - str = NULL; - } - else if (jsv->is_json) + if (jsv->is_json) { - /* already done the hard work in the json case */ int len = jsv->val.json.len; json = jsv->val.json.str; Assert(json); - if (len >= 0) + /* already done the hard work in the json case */ + if ((typid == JSONOID || typid == JSONBOID) && + jsv->val.json.type == JSON_TOKEN_STRING) + { + /* + * Add quotes around string value (should be already escaped) + * if converting to json/jsonb. + */ + + if (len < 0) + len = strlen(json); + + str = palloc(len + sizeof(char) * 3); + str[0] = '"'; + memcpy(&str[1], json, len); + str[len + 1] = '"'; + str[len + 2] = '\0'; + } + else if (len >= 0) { /* Need to copy non-null-terminated string */ str = palloc(len + 1 * sizeof(char)); @@ -2291,10 +2709,25 @@ populate_scalar(ScalarIOData *io, /* metadata cache */ { JsonbValue *jbv = jsv->val.jsonb; - if (jbv->type == jbvString) /* quotes are stripped */ + if (typid == JSONBOID) + { + Jsonb *jsonb = JsonbValueToJsonb(jbv); /* directly use jsonb */ + return JsonbGetDatum(jsonb); + } + /* convert jsonb to string for typio call */ + else if (typid == JSONOID && jbv->type != jbvBinary) + { + /* + * Convert scalar jsonb (non-scalars are passed here as jbvBinary) + * to json string, preserving quotes around top-level strings. + */ + Jsonb *jsonb = JsonbValueToJsonb(jbv); + str = JsonbToCString(NULL, &jsonb->root, VARSIZE(jsonb)); + } + else if (jbv->type == jbvString) /* quotes are stripped */ str = pnstrdup(jbv->val.string.val, jbv->val.string.len); else if (jbv->type == jbvBool) - str = pnstrdup(jbv->val.boolean ? "t" : "f", 1); + str = pstrdup(jbv->val.boolean ? "true" : "false"); else if (jbv->type == jbvNumeric) str = DatumGetCString(DirectFunctionCall1(numeric_out, PointerGetDatum(jbv->val.numeric))); @@ -2302,10 +2735,7 @@ populate_scalar(ScalarIOData *io, /* metadata cache */ str = JsonbToCString(NULL, jbv->val.binary.data, jbv->val.binary.len); else - { elog(ERROR, "unrecognized jsonb type: %d", (int) jbv->type); - str = NULL; - } } res = InputFunctionCall(&io->typiofunc, str, io->typioparam, typmod); @@ -2317,6 +2747,32 @@ populate_scalar(ScalarIOData *io, /* metadata cache */ return res; } +static Datum +populate_domain(DomainIOData *io, /* metadata cache */ + Oid typid, /* domain type id */ + const char *colname, /* for diagnostics only */ + MemoryContext mcxt, /* cache memory context */ + JsValue *jsv, /* json/jsonb value to convert */ + bool isnull) /* value is NULL */ +{ + Datum res; + + if (isnull) + res = (Datum) 0; + else + { + res = populate_record_field(io->base_io, + io->base_typid, io->base_typmod, + colname, mcxt, PointerGetDatum(NULL), + jsv, &isnull); + Assert(!isnull); + } + + domain_check(res, isnull, typid, &io->domain_info, mcxt); + + return res; +} + /* prepare column metadata cache for the given type */ static void prepare_column_cache(ColumnIOData *column, /* metadata cache */ @@ -2325,13 +2781,55 @@ prepare_column_cache(ColumnIOData *column, /* metadata cache */ MemoryContext mcxt, /* cache memory context */ bool json) /* json/jsonb */ { - Oid typioproc; + HeapTuple tup; + Form_pg_type type; column->typid = typid; column->typmod = typmod; - getTypeInputInfo(typid, &typioproc, &column->scalar_io.typioparam); - fmgr_info_cxt(typioproc, &column->scalar_io.typiofunc, mcxt); + tup = SearchSysCache1(TYPEOID, ObjectIdGetDatum(typid)); + if (!HeapTupleIsValid(tup)) + elog(ERROR, "cache lookup failed for type %u", typid); + + type = (Form_pg_type) GETSTRUCT(tup); + + if (type->typtype == TYPTYPE_DOMAIN) + { + column->typcat = TYPECAT_DOMAIN; + column->io.domain.base_typid = type->typbasetype; + column->io.domain.base_typmod = type->typtypmod; + column->io.domain.base_io = MemoryContextAllocZero(mcxt, + sizeof(ColumnIOData)); + column->io.domain.domain_info = NULL; + } + else if (type->typtype == TYPTYPE_COMPOSITE || typid == RECORDOID) + { + column->typcat = TYPECAT_COMPOSITE; + column->io.composite.record_io = NULL; + column->io.composite.tupdesc = NULL; + } + else if (type->typlen == -1 && OidIsValid(type->typelem)) + { + column->typcat = TYPECAT_ARRAY; + column->io.array.element_info = MemoryContextAllocZero(mcxt, + sizeof(ColumnIOData)); + column->io.array.element_type = type->typelem; + /* array element typemod stored in attribute's typmod */ + column->io.array.element_typmod = typmod; + } + else + column->typcat = TYPECAT_SCALAR; + + /* don't need input function when converting from jsonb to jsonb */ + if (json || typid != JSONBOID) + { + Oid typioproc; + + getTypeInputInfo(typid, &typioproc, &column->scalar_io.typioparam); + fmgr_info_cxt(typioproc, &column->scalar_io.typiofunc, mcxt); + } + + ReleaseSysCache(tup); } /* populate recursively a record field or an array element from json/jsonb value */ @@ -2345,6 +2843,8 @@ populate_record_field(ColumnIOData *col, /* metadata cache */ JsValue *jsv, /* json/jsonb value to convert */ bool *isnull) /* value is NULL */ { + TypeCat typcat; + check_stack_depth(); /* prepare column metadata cache for the given type */ @@ -2353,8 +2853,41 @@ populate_record_field(ColumnIOData *col, /* metadata cache */ *isnull = JsValueIsNull(jsv); - return populate_scalar(&col->scalar_io, typid, typmod, - *isnull ? NULL : jsv); + typcat = col->typcat; + + /* try to convert json string to a non-scalar type through input function */ + if (JsValueIsString(jsv) && + (typcat == TYPECAT_ARRAY || typcat == TYPECAT_COMPOSITE)) + typcat = TYPECAT_SCALAR; + + /* we must perform domain checks for NULLs */ + if (*isnull && typcat != TYPECAT_DOMAIN) + return (Datum) 0; + + switch (typcat) + { + case TYPECAT_SCALAR: + return populate_scalar(&col->scalar_io, typid, typmod, jsv); + + case TYPECAT_ARRAY: + return populate_array(&col->io.array, colname, mcxt, jsv); + + case TYPECAT_COMPOSITE: + return populate_composite(&col->io.composite, typid, typmod, + colname, mcxt, + DatumGetPointer(defaultval) + ? DatumGetHeapTupleHeader(defaultval) + : NULL, + jsv); + + case TYPECAT_DOMAIN: + return populate_domain(&col->io.domain, typid, colname, mcxt, + jsv, *isnull); + + default: + elog(ERROR, "unrecognized type category '%c'", typcat); + return (Datum) 0; + } } static RecordIOData * diff --git a/src/test/regress/expected/json.out b/src/test/regress/expected/json.out index 1bb8768..c7182c8 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 8ec4150..82a1d1c 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 5e61922..ce5fb77 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 e2eaca0..1f4575d 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"}';