diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c index 2fd87fc..82dae72 100644 --- a/src/backend/utils/adt/jsonb.c +++ b/src/backend/utils/adt/jsonb.c @@ -12,11 +12,20 @@ */ #include "postgres.h" +#include "miscadmin.h" +#include "access/htup_details.h" +#include "access/transam.h" +#include "catalog/pg_cast.h" +#include "catalog/pg_type.h" #include "libpq/pqformat.h" #include "utils/builtins.h" +#include "utils/datetime.h" +#include "utils/lsyscache.h" #include "utils/json.h" #include "utils/jsonapi.h" #include "utils/jsonb.h" +#include "utils/syscache.h" +#include "utils/typcache.h" typedef struct JsonbInState { @@ -24,6 +33,23 @@ typedef struct JsonbInState JsonbValue *res; } JsonbInState; +/* unlike with json categories, we need to treat json and jsonb differently */ +typedef enum /* type categories for datum_to_jsonb */ +{ + JSONBTYPE_NULL, /* null, so we didn't bother to identify */ + JSONBTYPE_BOOL, /* boolean (built-in types only) */ + JSONBTYPE_NUMERIC, /* numeric (ditto) */ + JSONBTYPE_TIMESTAMP, /* we use special formatting for timestamp */ + JSONBTYPE_TIMESTAMPTZ, /* ... and timestamptz */ + JSONBTYPE_JSON, /* JSON */ + JSONBTYPE_JSONB, /* JSONB */ + JSONBTYPE_ARRAY, /* array */ + JSONBTYPE_COMPOSITE, /* composite */ + JSONBTYPE_JSONCAST, /* something with an explicit cast to JSON */ + JSONBTYPE_JSONBCAST, /* something with an explicit cast to JSONB */ + JSONBTYPE_OTHER /* all else */ +} JsonbTypeCategory; + static inline Datum jsonb_from_cstring(char *json, int len); static size_t checkStringLen(size_t len); static void jsonb_in_object_start(void *pstate); @@ -33,6 +59,22 @@ static void jsonb_in_array_end(void *pstate); static void jsonb_in_object_field_start(void *pstate, char *fname, bool isnull); static void jsonb_put_escaped_value(StringInfo out, JsonbValue *scalarVal); static void jsonb_in_scalar(void *pstate, char *token, JsonTokenType tokentype); +static void jsonb_categorize_type(Oid typoid, + JsonbTypeCategory *tcategory, + Oid *outfuncoid); +static void composite_to_jsonb(Datum composite, JsonbInState *result); +static void array_dim_to_jsonb(JsonbInState *result, int dim, int ndims, int *dims, + Datum *vals, bool *nulls, int *valcount, + JsonbTypeCategory tcategory, Oid outfuncoid); +static void array_to_jsonb_internal(Datum array, JsonbInState *result); +static void jsonb_categorize_type(Oid typoid, + JsonbTypeCategory *tcategory, + Oid *outfuncoid); +static void datum_to_jsonb(Datum val, bool is_null, JsonbInState *result, + JsonbTypeCategory tcategory, Oid outfuncoid, + bool key_scalar); +static void add_jsonb(Datum val, bool is_null, JsonbInState *result, + Oid val_type, bool key_scalar); /* * jsonb type input function @@ -462,3 +504,1070 @@ JsonbToCString(StringInfo out, JsonbContainer *in, int estimated_len) return out->data; } + + +/* + * Determine how we want to render values of a given type in datum_to_jsonb. + * + * Given the datatype OID, return its JsonbTypeCategory, as well as the type's + * output function OID. If the returned category is JSONBTYPE_CAST, we + * return the OID of the type->JSON cast function instead. + */ +static void +jsonb_categorize_type(Oid typoid, + JsonbTypeCategory *tcategory, + Oid *outfuncoid) +{ + bool typisvarlena; + + /* Look through any domain */ + typoid = getBaseType(typoid); + + /* We'll usually need to return the type output function */ + getTypeOutputInfo(typoid, outfuncoid, &typisvarlena); + + /* Check for known types */ + switch (typoid) + { + case BOOLOID: + *tcategory = JSONBTYPE_BOOL; + break; + + case INT2OID: + case INT4OID: + case INT8OID: + case FLOAT4OID: + case FLOAT8OID: + case NUMERICOID: + *tcategory = JSONBTYPE_NUMERIC; + break; + + case TIMESTAMPOID: + *tcategory = JSONBTYPE_TIMESTAMP; + break; + + case TIMESTAMPTZOID: + *tcategory = JSONBTYPE_TIMESTAMPTZ; + break; + + case JSONBOID: + *tcategory = JSONBTYPE_JSONB; + break; + + case JSONOID: + *tcategory = JSONBTYPE_JSON; + break; + + default: + /* Check for arrays and composites */ + if (OidIsValid(get_element_type(typoid))) + *tcategory = JSONBTYPE_ARRAY; + else if (type_is_rowtype(typoid)) + *tcategory = JSONBTYPE_COMPOSITE; + else + { + /* It's probably the general case ... */ + *tcategory = JSONBTYPE_OTHER; + /* but let's look for a cast to json or jsonb, if it's not built-in */ + if (typoid >= FirstNormalObjectId) + { + HeapTuple tuple; + + tuple = SearchSysCache2(CASTSOURCETARGET, + ObjectIdGetDatum(typoid), + ObjectIdGetDatum(JSONBOID)); + if (HeapTupleIsValid(tuple)) + { + Form_pg_cast castForm = (Form_pg_cast) GETSTRUCT(tuple); + + if (castForm->castmethod == COERCION_METHOD_FUNCTION) + { + *tcategory = JSONBTYPE_JSONBCAST; + *outfuncoid = castForm->castfunc; + } + + ReleaseSysCache(tuple); + } + else + { + tuple = SearchSysCache2(CASTSOURCETARGET, + ObjectIdGetDatum(typoid), + ObjectIdGetDatum(JSONOID)); + if (HeapTupleIsValid(tuple)) + { + Form_pg_cast castForm = (Form_pg_cast) GETSTRUCT(tuple); + + if (castForm->castmethod == COERCION_METHOD_FUNCTION) + { + *tcategory = JSONBTYPE_JSONCAST; + *outfuncoid = castForm->castfunc; + } + + ReleaseSysCache(tuple); + } + } + } + break; + } + } +} + +/* + * Turn a Datum into JSON text, appending the string to "result". + * + * tcategory and outfuncoid are from a previous call to json_categorize_type, + * except that if is_null is true then they can be invalid. + * + * If key_scalar is true, the value is being printed as a key, so insist + * it's of an acceptable type, and force it to be quoted. + */ +static void +datum_to_jsonb(Datum val, bool is_null, JsonbInState *result, + JsonbTypeCategory tcategory, Oid outfuncoid, + bool key_scalar) +{ + char *outputstr; + bool numeric_error; + JsonbValue jb; + bool scalar_jsonb = false; + + if (is_null) + { + jb.type = jbvNull; + } + else if (key_scalar && + (tcategory == JSONBTYPE_ARRAY || + tcategory == JSONBTYPE_COMPOSITE || + tcategory == JSONBTYPE_JSON || + tcategory == JSONBTYPE_JSONB || + tcategory == JSONBTYPE_JSONCAST || + tcategory == JSONBTYPE_JSONBCAST)) + { + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("key value must be scalar, not array, composite or json"))); + } + else + { + if (tcategory == JSONBTYPE_JSONCAST || tcategory == JSONBTYPE_JSONBCAST) + val = OidFunctionCall1(outfuncoid, val); + + switch (tcategory) + { + case JSONBTYPE_ARRAY: + array_to_jsonb_internal(val, result); + break; + case JSONBTYPE_COMPOSITE: + composite_to_jsonb(val, result); + break; + case JSONBTYPE_BOOL: + if (key_scalar) + { + outputstr = DatumGetBool(val) ? "true" : "false"; + jb.type = jbvString; + jb.val.string.len = strlen(outputstr); + jb.val.string.val = outputstr; + } + else + { + jb.type = jbvBool; + jb.val.boolean = DatumGetBool(val); + } + break; + case JSONBTYPE_NUMERIC: + outputstr = OidOutputFunctionCall(outfuncoid, val); + if (key_scalar) + { + /* always quote keys */ + jb.type = jbvString; + jb.val.string.len = strlen(outputstr); + jb.val.string.val = outputstr; + } + else + { + /* + * Make it numeric if it's a valid JSON number, + * otherwise a string. Invalid numeric output will always + * have an 'N' or 'n' in it (I think). + */ + numeric_error = (strchr(outputstr,'N') != NULL || + strchr(outputstr,'n') != NULL); + if (!numeric_error) + { + jb.type = jbvNumeric; + jb.val.numeric = DatumGetNumeric(DirectFunctionCall3(numeric_in, CStringGetDatum(outputstr), 0, -1)); + pfree(outputstr); + } + else + { + jb.type = jbvString; + jb.val.string.len = strlen(outputstr); + jb.val.string.val = outputstr; + } + } + break; + case JSONBTYPE_TIMESTAMP: + { + Timestamp timestamp; + struct pg_tm tm; + fsec_t fsec; + char buf[MAXDATELEN + 1]; + + timestamp = DatumGetTimestamp(val); + + /* XSD doesn't support infinite values */ + if (TIMESTAMP_NOT_FINITE(timestamp)) + ereport(ERROR, + (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE), + errmsg("timestamp out of range"), + errdetail("JSON does not support infinite timestamp values."))); + else if (timestamp2tm(timestamp, NULL, &tm, &fsec, NULL, NULL) == 0) + EncodeDateTime(&tm, fsec, false, 0, NULL, USE_XSD_DATES, buf); + else + ereport(ERROR, + (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE), + errmsg("timestamp out of range"))); + + jb.type = jbvString; + jb.val.string.len = strlen(buf); + jb.val.string.val = pstrdup(buf); + } + break; + case JSONBTYPE_TIMESTAMPTZ: + { + TimestampTz timestamp; + struct pg_tm tm; + int tz; + fsec_t fsec; + const char *tzn = NULL; + char buf[MAXDATELEN + 1]; + + timestamp = DatumGetTimestamp(val); + + /* XSD doesn't support infinite values */ + if (TIMESTAMP_NOT_FINITE(timestamp)) + ereport(ERROR, + (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE), + errmsg("timestamp out of range"), + errdetail("JSON does not support infinite timestamp values."))); + else if (timestamp2tm(timestamp, &tz, &tm, &fsec, &tzn, NULL) == 0) + EncodeDateTime(&tm, fsec, true, tz, tzn, USE_XSD_DATES, buf); + else + ereport(ERROR, + (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE), + errmsg("timestamp out of range"))); + + jb.type = jbvString; + jb.val.string.len = strlen(buf); + jb.val.string.val = pstrdup(buf); + } + break; + case JSONBTYPE_JSONCAST: + case JSONBTYPE_JSON: + { + /* parse the json right into the existing result object */ + JsonLexContext *lex; + JsonSemAction sem; + text *json = DatumGetTextP(val); + + lex = makeJsonLexContext(json, true); + + sem.semstate = (void *) result; + + sem.object_start = jsonb_in_object_start; + sem.array_start = jsonb_in_array_start; + sem.object_end = jsonb_in_object_end; + sem.array_end = jsonb_in_array_end; + sem.scalar = jsonb_in_scalar; + sem.object_field_start = jsonb_in_object_field_start; + + pg_parse_json(lex, &sem); + + } + break; + case JSONBTYPE_JSONBCAST: + case JSONBTYPE_JSONB: + { + Jsonb *jsonb = DatumGetJsonb(val); + int type; + JsonbIterator *it; + + it = JsonbIteratorInit(&jsonb->root); + + if (JB_ROOT_IS_SCALAR(jsonb)) + { + (void) JsonbIteratorNext(&it, &jb, true); + Assert(jb.type == jbvArray); + (void) JsonbIteratorNext(&it, &jb, true); + scalar_jsonb = true; + } + else + { + while ((type = JsonbIteratorNext(&it, &jb, false)) + != WJB_DONE) + { + if (type == WJB_END_ARRAY || type == WJB_END_OBJECT || + type == WJB_BEGIN_ARRAY || type == WJB_BEGIN_OBJECT) + result->res = pushJsonbValue(&result->parseState, + type, NULL); + else + result->res = pushJsonbValue(&result->parseState, + type, &jb); + } + } + } + break; + default: + outputstr = OidOutputFunctionCall(outfuncoid, val); + if (key_scalar && *outputstr == '\0') + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("key value must not be empty"))); + jb.type = jbvString; + jb.val.string.len = checkStringLen(strlen(outputstr)); + jb.val.string.val = outputstr; + break; + } + } + if (tcategory >= JSONBTYPE_JSON && tcategory <= JSONBTYPE_JSONBCAST && + ! scalar_jsonb) + { + /* work has been done recursively */ + return; + } + else if (result->parseState == NULL) + { + /* single root scalar */ + JsonbValue va; + + va.type = jbvArray; + va.val.array.rawScalar = true; + va.val.array.nElems = 1; + + result->res = pushJsonbValue(&result->parseState, WJB_BEGIN_ARRAY, &va); + result->res = pushJsonbValue(&result->parseState, WJB_ELEM, &jb); + result->res = pushJsonbValue(&result->parseState, WJB_END_ARRAY, NULL); + } + else + { + JsonbValue *o = &result->parseState->contVal; + + switch (o->type) + { + case jbvArray: + result->res = pushJsonbValue(&result->parseState, WJB_ELEM, &jb); + break; + case jbvObject: + result->res = pushJsonbValue(&result->parseState, + key_scalar ? WJB_KEY : WJB_VALUE, + &jb); + break; + default: + elog(ERROR, "unexpected parent of nested structure"); + } + } +} + +/* + * Process a single dimension of an array. + * If it's the innermost dimension, output the values, otherwise call + * ourselves recursively to process the next dimension. + */ +static void +array_dim_to_jsonb(JsonbInState *result, int dim, int ndims, int *dims, Datum *vals, + bool *nulls, int *valcount, JsonbTypeCategory tcategory, + Oid outfuncoid) +{ + int i; + + Assert(dim < ndims); + + result->res = pushJsonbValue(&result->parseState, WJB_BEGIN_ARRAY, NULL); + + for (i = 1; i <= dims[dim]; i++) + { + if (dim + 1 == ndims) + { + datum_to_jsonb(vals[*valcount], nulls[*valcount], result, tcategory, + outfuncoid, false); + (*valcount)++; + } + else + { + array_dim_to_jsonb(result, dim + 1, ndims, dims, vals, nulls, + valcount, tcategory, outfuncoid); + } + } + + result->res = pushJsonbValue(&result->parseState, WJB_END_ARRAY, NULL); +} + +/* + * Turn an array into JSON. + */ +static void +array_to_jsonb_internal(Datum array, JsonbInState *result) +{ + ArrayType *v = DatumGetArrayTypeP(array); + Oid element_type = ARR_ELEMTYPE(v); + int *dim; + int ndim; + int nitems; + int count = 0; + Datum *elements; + bool *nulls; + int16 typlen; + bool typbyval; + char typalign; + JsonbTypeCategory tcategory; + Oid outfuncoid; + + ndim = ARR_NDIM(v); + dim = ARR_DIMS(v); + nitems = ArrayGetNItems(ndim, dim); + + if (nitems <= 0) + { + result->res = pushJsonbValue(&result->parseState, WJB_BEGIN_ARRAY, NULL); + result->res = pushJsonbValue(&result->parseState, WJB_END_ARRAY, NULL); + return; + } + + get_typlenbyvalalign(element_type, + &typlen, &typbyval, &typalign); + + jsonb_categorize_type(element_type, + &tcategory, &outfuncoid); + + deconstruct_array(v, element_type, typlen, typbyval, + typalign, &elements, &nulls, + &nitems); + + array_dim_to_jsonb(result, 0, ndim, dim, elements, nulls, &count, tcategory, + outfuncoid); + + pfree(elements); + pfree(nulls); +} + +/* + * Turn a composite / record into JSON. + */ +static void +composite_to_jsonb(Datum composite, JsonbInState *result) +{ + HeapTupleHeader td; + Oid tupType; + int32 tupTypmod; + TupleDesc tupdesc; + HeapTupleData tmptup, + *tuple; + int i; + + td = DatumGetHeapTupleHeader(composite); + + /* Extract rowtype info and find a tupdesc */ + tupType = HeapTupleHeaderGetTypeId(td); + tupTypmod = HeapTupleHeaderGetTypMod(td); + tupdesc = lookup_rowtype_tupdesc(tupType, tupTypmod); + + /* Build a temporary HeapTuple control structure */ + tmptup.t_len = HeapTupleHeaderGetDatumLength(td); + tmptup.t_data = td; + tuple = &tmptup; + + result->res = pushJsonbValue(&result->parseState, WJB_BEGIN_OBJECT, NULL); + + for (i = 0; i < tupdesc->natts; i++) + { + Datum val; + bool isnull; + char *attname; + JsonbTypeCategory tcategory; + Oid outfuncoid; + JsonbValue v; + + if (tupdesc->attrs[i]->attisdropped) + continue; + + attname = NameStr(tupdesc->attrs[i]->attname); + + v.type = jbvString; + /* don't need checkStringLen here - can't exceed maximum name length */ + v.val.string.len = strlen(attname); + v.val.string.val = attname; + + result->res = pushJsonbValue(&result->parseState, WJB_KEY, &v); + + val = heap_getattr(tuple, i + 1, tupdesc, &isnull); + + if (isnull) + { + tcategory = JSONBTYPE_NULL; + outfuncoid = InvalidOid; + } + else + jsonb_categorize_type(tupdesc->attrs[i]->atttypid, + &tcategory, &outfuncoid); + + datum_to_jsonb(val, isnull, result, tcategory, outfuncoid, false); + } + + result->res = pushJsonbValue(&result->parseState, WJB_END_OBJECT, NULL); + ReleaseTupleDesc(tupdesc); +} + +/* + * Append JSON text for "val" to "result". + * + * This is just a thin wrapper around datum_to_json. If the same type will be + * printed many times, avoid using this; better to do the json_categorize_type + * lookups only once. + */ + +static void +add_jsonb(Datum val, bool is_null, JsonbInState *result, + Oid val_type, bool key_scalar) +{ + JsonbTypeCategory tcategory; + Oid outfuncoid; + + if (val_type == InvalidOid) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("could not determine input data type"))); + + if (is_null) + { + tcategory = JSONBTYPE_NULL; + outfuncoid = InvalidOid; + } + else + jsonb_categorize_type(val_type, + &tcategory, &outfuncoid); + + datum_to_jsonb(val, is_null, result, tcategory, outfuncoid, key_scalar); +} + +/* + * SQL function to_jsonb(anyvalue) + */ +Datum +to_jsonb(PG_FUNCTION_ARGS) +{ + Datum val = PG_GETARG_DATUM(0); + Oid val_type = get_fn_expr_argtype(fcinfo->flinfo, 0); + JsonbInState result; + JsonbTypeCategory tcategory; + Oid outfuncoid; + + if (val_type == InvalidOid) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("could not determine input data type"))); + + jsonb_categorize_type(val_type, + &tcategory, &outfuncoid); + + memset(&result, 0, sizeof(JsonbInState)); + + datum_to_jsonb(val, false, &result, tcategory, outfuncoid, false); + + PG_RETURN_POINTER(JsonbValueToJsonb(result.res)); +} + +/* + * SQL function jsonb_build_object(variadic "any") + */ +Datum +jsonb_build_object(PG_FUNCTION_ARGS) +{ + int nargs = PG_NARGS(); + int i; + Datum arg; + Oid val_type; + JsonbInState result; + + if (nargs % 2 != 0) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid number or arguments: object must be matched key value pairs"))); + + memset(&result, 0, sizeof(JsonbInState)); + + result.res = pushJsonbValue(&result.parseState, WJB_BEGIN_OBJECT, NULL); + + for (i = 0; i < nargs; i += 2) + { + + /* process key */ + + if (PG_ARGISNULL(i)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("arg %d: key cannot be null", i + 1))); + val_type = get_fn_expr_argtype(fcinfo->flinfo, i); + + /* + * turn a constant (more or less literal) value that's of unknown type + * into text. Unknowns come in as a cstring pointer. + */ + if (val_type == UNKNOWNOID && get_fn_expr_arg_stable(fcinfo->flinfo, i)) + { + val_type = TEXTOID; + if (PG_ARGISNULL(i)) + arg = (Datum) 0; + else + arg = CStringGetTextDatum(PG_GETARG_POINTER(i)); + } + else + { + arg = PG_GETARG_DATUM(i); + } + if (val_type == InvalidOid || val_type == UNKNOWNOID) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("arg %d: could not determine data type", i + 1))); + + add_jsonb(arg, false, &result, val_type, true); + + /* process value */ + + val_type = get_fn_expr_argtype(fcinfo->flinfo, i + 1); + /* see comments above */ + if (val_type == UNKNOWNOID && get_fn_expr_arg_stable(fcinfo->flinfo, i + 1)) + { + val_type = TEXTOID; + if (PG_ARGISNULL(i + 1)) + arg = (Datum) 0; + else + arg = CStringGetTextDatum(PG_GETARG_POINTER(i + 1)); + } + else + { + arg = PG_GETARG_DATUM(i + 1); + } + if (val_type == InvalidOid || val_type == UNKNOWNOID) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("arg %d: could not determine data type", i + 2))); + add_jsonb(arg, PG_ARGISNULL(i + 1), &result, val_type, false); + + } + + result.res = pushJsonbValue(&result.parseState, WJB_END_OBJECT, NULL); + + PG_RETURN_POINTER(JsonbValueToJsonb(result.res)); +} + +/* + * degenerate case of jsonb_build_object where it gets 0 arguments. + */ +Datum +jsonb_build_object_noargs(PG_FUNCTION_ARGS) +{ + JsonbInState result; + + memset(&result, 0, sizeof(JsonbInState)); + + result.res = pushJsonbValue(&result.parseState, WJB_BEGIN_OBJECT, NULL); + result.res = pushJsonbValue(&result.parseState, WJB_END_OBJECT, NULL); + + PG_RETURN_POINTER(JsonbValueToJsonb(result.res)); +} + +/* + * SQL function jsonb_build_array(variadic "any") + */ +Datum +jsonb_build_array(PG_FUNCTION_ARGS) +{ + int nargs = PG_NARGS(); + int i; + Datum arg; + Oid val_type; + JsonbInState result; + + memset(&result, 0, sizeof(JsonbInState)); + + result.res = pushJsonbValue(&result.parseState, WJB_BEGIN_ARRAY, NULL); + + for (i = 0; i < nargs; i++) + { + val_type = get_fn_expr_argtype(fcinfo->flinfo, i); + arg = PG_GETARG_DATUM(i + 1); + /* see comments in jsonb_build_object above */ + if (val_type == UNKNOWNOID && get_fn_expr_arg_stable(fcinfo->flinfo, i)) + { + val_type = TEXTOID; + if (PG_ARGISNULL(i)) + arg = (Datum) 0; + else + arg = CStringGetTextDatum(PG_GETARG_POINTER(i)); + } + else + { + arg = PG_GETARG_DATUM(i); + } + if (val_type == InvalidOid || val_type == UNKNOWNOID) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("arg %d: could not determine data type", i + 1))); + add_jsonb(arg, PG_ARGISNULL(i), &result, val_type, false); + } + + result.res = pushJsonbValue(&result.parseState, WJB_END_ARRAY, NULL); + + PG_RETURN_POINTER(JsonbValueToJsonb(result.res)); +} + +/* + * degenerate case of jsonb_build_array where it gets 0 arguments. + */ +Datum +jsonb_build_array_noargs(PG_FUNCTION_ARGS) +{ + JsonbInState result; + + memset(&result, 0, sizeof(JsonbInState)); + + result.res = pushJsonbValue(&result.parseState, WJB_BEGIN_ARRAY, NULL); + result.res = pushJsonbValue(&result.parseState, WJB_END_ARRAY, NULL); + + PG_RETURN_POINTER(JsonbValueToJsonb(result.res)); +} + + +/* + * SQL function jsonb_object(text[]) + * + * take a one or two dimensional array of text as name value pairs + * for a json object. + * + */ +Datum +jsonb_object(PG_FUNCTION_ARGS) +{ + ArrayType *in_array = PG_GETARG_ARRAYTYPE_P(0); + int ndims = ARR_NDIM(in_array); + Datum *in_datums; + bool *in_nulls; + int in_count, + count, + i; + JsonbInState result; + + memset(&result, 0, sizeof(JsonbInState)); + + result.res = pushJsonbValue(&result.parseState, WJB_BEGIN_OBJECT, NULL); + + switch (ndims) + { + case 0: + goto close_object; + break; + + case 1: + if ((ARR_DIMS(in_array)[0]) % 2) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR), + errmsg("array must have even number of elements"))); + break; + + case 2: + if ((ARR_DIMS(in_array)[1]) != 2) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR), + errmsg("array must have two columns"))); + break; + + default: + ereport(ERROR, + (errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR), + errmsg("wrong number of array subscripts"))); + } + + deconstruct_array(in_array, + TEXTOID, -1, false, 'i', + &in_datums, &in_nulls, &in_count); + + count = in_count / 2; + + for (i = 0; i < count; ++i) + { + JsonbValue v; + char *str; + int len; + + if (in_nulls[i * 2]) + ereport(ERROR, + (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED), + errmsg("null value not allowed for object key"))); + + str = TextDatumGetCString(in_datums[i*2]); + len = strlen(str); + + v.type = jbvString; + + v.val.string.len = len; + v.val.string.val = str; + + result.res = pushJsonbValue(&result.parseState, WJB_KEY, &v); + + if (in_nulls[i*2 + 1]) + { + v.type = jbvNull; + } + else + { + str = TextDatumGetCString(in_datums[i*2 + 1]); + len = strlen(str); + + v.type = jbvString; + + v.val.string.len = len; + v.val.string.val = str; + } + + result.res = pushJsonbValue(&result.parseState, WJB_VALUE, &v); + } + + pfree(in_datums); + pfree(in_nulls); + +close_object: + result.res = pushJsonbValue(&result.parseState, WJB_END_OBJECT, NULL); + + PG_RETURN_POINTER(JsonbValueToJsonb(result.res)); +} + +/* + * SQL function jsonb_object(text[], text[]) + * + * take separate name and value arrays of text to construct a json object + * pairwise. + */ +Datum +jsonb_object_two_arg(PG_FUNCTION_ARGS) +{ + ArrayType *key_array = PG_GETARG_ARRAYTYPE_P(0); + ArrayType *val_array = PG_GETARG_ARRAYTYPE_P(1); + int nkdims = ARR_NDIM(key_array); + int nvdims = ARR_NDIM(val_array); + Datum *key_datums, + *val_datums; + bool *key_nulls, + *val_nulls; + int key_count, + val_count, + i; + JsonbInState result; + + memset(&result, 0, sizeof(JsonbInState)); + + result.res = pushJsonbValue(&result.parseState, WJB_BEGIN_OBJECT, NULL); + + if (nkdims > 1 || nkdims != nvdims) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR), + errmsg("wrong number of array subscripts"))); + + if (nkdims == 0) + PG_RETURN_DATUM(CStringGetTextDatum("{}")); + + deconstruct_array(key_array, + TEXTOID, -1, false, 'i', + &key_datums, &key_nulls, &key_count); + + deconstruct_array(val_array, + TEXTOID, -1, false, 'i', + &val_datums, &val_nulls, &val_count); + + if (key_count != val_count) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR), + errmsg("mismatched array dimensions"))); + + for (i = 0; i < key_count; ++i) + { + JsonbValue v; + char *str; + int len; + + if (key_nulls[i]) + ereport(ERROR, + (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED), + errmsg("null value not allowed for object key"))); + + str = TextDatumGetCString(key_datums[i]); + len = strlen(str); + + v.type = jbvString; + + v.val.string.len = len; + v.val.string.val = str; + + result.res = pushJsonbValue(&result.parseState, WJB_KEY, &v); + + if (val_nulls[i]) + { + v.type = jbvNull; + } + else + { + str = TextDatumGetCString(val_datums[i]); + len = strlen(str); + + v.type = jbvString; + + v.val.string.len = len; + v.val.string.val = str; + } + + result.res = pushJsonbValue(&result.parseState, WJB_VALUE, &v); + } + + result.res = pushJsonbValue(&result.parseState, WJB_END_OBJECT, NULL); + + pfree(key_datums); + pfree(key_nulls); + pfree(val_datums); + pfree(val_nulls); + + PG_RETURN_POINTER(JsonbValueToJsonb(result.res)); +} + + +/* + * jsonb_agg aggregate function + */ +Datum +jsonb_agg_transfn(PG_FUNCTION_ARGS) +{ + Oid val_type = get_fn_expr_argtype(fcinfo->flinfo, 1); + MemoryContext oldcontext, aggcontext; + JsonbInState elem; + JsonbTypeCategory tcategory; + Oid outfuncoid; + Datum val; + JsonbInState *result; + bool single_scalar = false; + JsonbIterator *it; + Jsonb *jbelem; + JsonbValue v; + int type; + + if (val_type == InvalidOid) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("could not determine input data type"))); + + if (!AggCheckCallContext(fcinfo, &aggcontext)) + { + /* cannot be called directly because of internal-type argument */ + elog(ERROR, "json_agg_transfn called in non-aggregate context"); + } + + /* turn the argument into jsonb in the normal function context */ + + val = PG_ARGISNULL(1) ? (Datum) 0 : PG_GETARG_DATUM(1); + + jsonb_categorize_type(val_type, + &tcategory, &outfuncoid); + + memset(&elem, 0, sizeof(JsonbInState)); + + datum_to_jsonb(val, false, &elem, tcategory, outfuncoid, false); + + jbelem = JsonbValueToJsonb(elem.res); + + /* switch to the aggregate context for accumulation operations */ + + oldcontext = MemoryContextSwitchTo(aggcontext); + + /* set up the accumulator on the first go round */ + + if (PG_ARGISNULL(0)) + { + result = palloc0(sizeof(JsonbInState)); + result->res = pushJsonbValue(&result->parseState, + WJB_BEGIN_ARRAY, NULL); + + } + else + { + result = (JsonbInState *) PG_GETARG_POINTER(0); + } + + it = JsonbIteratorInit(&jbelem->root); + + while ((type = JsonbIteratorNext(&it, &v, false)) != WJB_DONE) + { + switch (type) + { + case WJB_BEGIN_ARRAY: + if (v.val.array.rawScalar) + single_scalar = true; + else + result->res = pushJsonbValue(&result->parseState, + type, NULL); + break; + case WJB_END_ARRAY: + if (! single_scalar ) + result->res = pushJsonbValue(&result->parseState, + type, NULL); + break; + case WJB_BEGIN_OBJECT: + case WJB_END_OBJECT: + result->res = pushJsonbValue(&result->parseState, + type, NULL); + break; + case WJB_ELEM: + case WJB_KEY: + case WJB_VALUE: + if (v.type == jbvString) + { + /* copy string values in the aggreagate context */ + char *buf = palloc(v.val.string.len + 1);; + snprintf(buf, v.val.string.len +1, "%s", v.val.string.val); + v.val.string.val = buf; + } + else if (v.type == jbvNumeric) + { + /* same for numeric */ + v.val.numeric = DirectFunctionCall1(numeric_uplus,NumericGetDatum(v.val.numeric)); + + } + result->res = pushJsonbValue(&result->parseState, + type, &v); + break; + } + } + + MemoryContextSwitchTo(oldcontext); + + PG_RETURN_POINTER(result); +} + +Datum +jsonb_agg_finalfn(PG_FUNCTION_ARGS) +{ + JsonbInState *result; + Jsonb *out; + + /* cannot be called directly because of internal-type argument */ + Assert(AggCheckCallContext(fcinfo, NULL)); + + if (PG_ARGISNULL(0)) + PG_RETURN_NULL(); /* returns null iff no input values */ + + result = (JsonbInState *) PG_GETARG_POINTER(0); + + result->res = pushJsonbValue(&result->parseState, + WJB_END_ARRAY, NULL); + + + out = JsonbValueToJsonb(result->res); + + PG_RETURN_POINTER(out); +} diff --git a/src/backend/utils/adt/jsonb_util.c b/src/backend/utils/adt/jsonb_util.c index 04f35bf..d546fd1 100644 --- a/src/backend/utils/adt/jsonb_util.c +++ b/src/backend/utils/adt/jsonb_util.c @@ -1328,7 +1328,7 @@ convertJsonbValue(StringInfo buffer, JEntry *header, JsonbValue *val, int level) else if (val->type == jbvObject) convertJsonbObject(buffer, header, val, level); else - elog(ERROR, "unknown type of jsonb container"); + elog(ERROR, "unknown type of jsonb container to convert"); } static void diff --git a/src/include/catalog/pg_aggregate.h b/src/include/catalog/pg_aggregate.h index 3ba9e5e..1ca0158 100644 --- a/src/include/catalog/pg_aggregate.h +++ b/src/include/catalog/pg_aggregate.h @@ -286,6 +286,9 @@ DATA(insert ( 3545 n 0 bytea_string_agg_transfn bytea_string_agg_finalfn - - DATA(insert ( 3175 n 0 json_agg_transfn json_agg_finalfn - - - f f 0 2281 0 0 0 _null_ _null_ )); DATA(insert ( 3197 n 0 json_object_agg_transfn json_object_agg_finalfn - - - f f 0 2281 0 0 0 _null_ _null_ )); +/* jsonb */ +DATA(insert ( 3267 n 0 jsonb_agg_transfn jsonb_agg_finalfn - - - f f 0 2281 0 0 0 _null_ _null_ )); + /* ordered-set and hypothetical-set aggregates */ DATA(insert ( 3972 o 1 ordered_set_transition percentile_disc_final - - - t f 0 2281 0 0 0 _null_ _null_ )); DATA(insert ( 3974 o 1 ordered_set_transition percentile_cont_float8_final - - - f f 0 2281 0 0 0 _null_ _null_ )); diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index d30d21a..923c1ee 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -4595,6 +4595,27 @@ DESCR("I/O"); DATA(insert OID = 3803 ( jsonb_send PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 17 "3802" _null_ _null_ _null_ _null_ jsonb_send _null_ _null_ _null_ )); DESCR("I/O"); +DATA(insert OID = 3263 ( jsonb_object PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 3802 "1009" _null_ _null_ _null_ _null_ jsonb_object _null_ _null_ _null_ )); +DESCR("map text array of key value pairs to jsonb object"); +DATA(insert OID = 3264 ( jsonb_object PGNSP PGUID 12 1 0 0 0 f f f f t f s 2 0 3802 "1009 1009" _null_ _null_ _null_ _null_ jsonb_object_two_arg _null_ _null_ _null_ )); +DESCR("map text array of key value pairs to jsonb object"); +DATA(insert OID = 3787 ( to_jsonb PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 3802 "2283" _null_ _null_ _null_ _null_ to_jsonb _null_ _null_ _null_ )); +DESCR("map input to jsonb"); +DATA(insert OID = 3265 ( jsonb_agg_transfn PGNSP PGUID 12 1 0 0 0 f f f f f f i 2 0 2281 "2281 2283" _null_ _null_ _null_ _null_ jsonb_agg_transfn _null_ _null_ _null_ )); +DESCR("jsonb aggregate transition function"); +DATA(insert OID = 3266 ( jsonb_agg_finalfn PGNSP PGUID 12 1 0 0 0 f f f f f f i 1 0 3802 "2281" _null_ _null_ _null_ _null_ jsonb_agg_finalfn _null_ _null_ _null_ )); +DESCR("jsonb aggregate final function"); +DATA(insert OID = 3267 ( jsonb_agg PGNSP PGUID 12 1 0 0 0 t f f f f f i 1 0 3802 "2283" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ )); +DESCR("aggregate input into jsonb"); +DATA(insert OID = 3259 ( jsonb_build_array PGNSP PGUID 12 1 0 2276 0 f f f f f f s 1 0 3802 "2276" "{2276}" "{v}" _null_ _null_ jsonb_build_array _null_ _null_ _null_ )); +DESCR("build a jsonb array from any inputs"); +DATA(insert OID = 3260 ( jsonb_build_array PGNSP PGUID 12 1 0 0 0 f f f f f f s 0 0 3802 "" _null_ _null_ _null_ _null_ jsonb_build_array_noargs _null_ _null_ _null_ )); +DESCR("build an empty jsonb array"); +DATA(insert OID = 3261 ( jsonb_build_object PGNSP PGUID 12 1 0 2276 0 f f f f f f s 1 0 3802 "2276" "{2276}" "{v}" _null_ _null_ jsonb_build_object _null_ _null_ _null_ )); +DESCR("build a jsonb object from pairwise key/value inputs"); +DATA(insert OID = 3262 ( jsonb_build_object PGNSP PGUID 12 1 0 0 0 f f f f f f s 0 0 3802 "" _null_ _null_ _null_ _null_ jsonb_build_object_noargs _null_ _null_ _null_ )); +DESCR("build an empty jsonb object"); + DATA(insert OID = 3478 ( jsonb_object_field PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 3802 "3802 25" _null_ _null_ "{from_json, field_name}" _null_ jsonb_object_field _null_ _null_ _null_ )); DATA(insert OID = 3214 ( jsonb_object_field_text PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 25 "3802 25" _null_ _null_ "{from_json, field_name}" _null_ jsonb_object_field_text _null_ _null_ _null_ )); DATA(insert OID = 3215 ( jsonb_array_element PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 3802 "3802 23" _null_ _null_ "{from_json, element_index}" _null_ jsonb_array_element _null_ _null_ _null_ )); diff --git a/src/include/utils/jsonb.h b/src/include/utils/jsonb.h index 91e3e14..68312d2 100644 --- a/src/include/utils/jsonb.h +++ b/src/include/utils/jsonb.h @@ -315,6 +315,20 @@ extern Datum jsonb_recv(PG_FUNCTION_ARGS); extern Datum jsonb_send(PG_FUNCTION_ARGS); extern Datum jsonb_typeof(PG_FUNCTION_ARGS); +/* generator routines */ +extern Datum to_jsonb(PG_FUNCTION_ARGS); + +extern Datum jsonb_build_object(PG_FUNCTION_ARGS); +extern Datum jsonb_build_object_noargs(PG_FUNCTION_ARGS); +extern Datum jsonb_build_array(PG_FUNCTION_ARGS); +extern Datum jsonb_build_array_noargs(PG_FUNCTION_ARGS); +extern Datum jsonb_object(PG_FUNCTION_ARGS); +extern Datum jsonb_object_two_arg(PG_FUNCTION_ARGS); + +/* jsonb_agg functions */ +extern Datum jsonb_agg_transfn(PG_FUNCTION_ARGS); +extern Datum jsonb_agg_finalfn(PG_FUNCTION_ARGS); + /* Indexing-related ops */ extern Datum jsonb_exists(PG_FUNCTION_ARGS); extern Datum jsonb_exists_any(PG_FUNCTION_ARGS); diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql index ed266d5..81e0f74 100644 --- a/src/test/regress/sql/jsonb.sql +++ b/src/test/regress/sql/jsonb.sql @@ -256,6 +256,86 @@ SELECT jsonb_typeof('"hello"') AS string; SELECT jsonb_typeof('"true"') AS string; SELECT jsonb_typeof('"1.0"') AS string; +-- jsonb_build_array, jsonb_build_object, jsonb_object_agg + +SELECT jsonb_build_array('a',1,'b',1.2,'c',true,'d',null,'e',json '{"x": 3, "y": [1,2,3]}'); + +SELECT jsonb_build_object('a',1,'b',1.2,'c',true,'d',null,'e',json '{"x": 3, "y": [1,2,3]}'); + +SELECT jsonb_build_object( + 'a', jsonb_build_object('b',false,'c',99), + 'd', jsonb_build_object('e',array[9,8,7]::int[], + 'f', (select row_to_json(r) from ( select relkind, oid::regclass as name from pg_class where relname = 'pg_class') r))); + + +-- empty objects/arrays +SELECT jsonb_build_array(); + +SELECT jsonb_build_object(); + +-- make sure keys are quoted +SELECT jsonb_build_object(1,2); + +-- keys must be scalar and not null +SELECT jsonb_build_object(null,2); + +SELECT jsonb_build_object(r,2) FROM (SELECT 1 AS a, 2 AS b) r; + +SELECT jsonb_build_object(json '{"a":1,"b":2}', 3); + +SELECT jsonb_build_object('{1,2,3}'::int[], 3); + +CREATE TEMP TABLE foo (serial_num int, name text, type text); +INSERT INTO foo VALUES (847001,'t15','GE1043'); +INSERT INTO foo VALUES (847002,'t16','GE1043'); +INSERT INTO foo VALUES (847003,'sub-alpha','GESS90'); + +SELECT jsonb_build_object('turbines',json_object_agg(serial_num,jsonb_build_object('name',name,'type',type))) +FROM foo; + +-- jsonb_object + +-- one dimension +SELECT jsonb_object('{a,1,b,2,3,NULL,"d e f","a b c"}'); + +-- same but with two dimensions +SELECT jsonb_object('{{a,1},{b,2},{3,NULL},{"d e f","a b c"}}'); + +-- odd number error +SELECT jsonb_object('{a,b,c}'); + +-- one column error +SELECT jsonb_object('{{a},{b}}'); + +-- too many columns error +SELECT jsonb_object('{{a,b,c},{b,c,d}}'); + +-- too many dimensions error +SELECT jsonb_object('{{{a,b},{c,d}},{{b,c},{d,e}}}'); + +--two argument form of jsonb_object + +select jsonb_object('{a,b,c,"d e f"}','{1,2,3,"a b c"}'); + +-- too many dimensions +SELECT jsonb_object('{{a,1},{b,2},{3,NULL},{"d e f","a b c"}}', '{{a,1},{b,2},{3,NULL},{"d e f","a b c"}}'); + +-- mismatched dimensions + +select jsonb_object('{a,b,c,"d e f",g}','{1,2,3,"a b c"}'); + +select jsonb_object('{a,b,c,"d e f"}','{1,2,3,"a b c",g}'); + +-- null key error + +select jsonb_object('{a,b,NULL,"d e f"}','{1,2,3,"a b c"}'); + +-- empty key is allowed + +select jsonb_object('{a,b,"","d e f"}','{1,2,3,"a b c"}'); + + + -- extract_path, extract_path_as_text SELECT jsonb_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}','f4','f6'); SELECT jsonb_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}','f2');