On 09/23/2014 12:23 PM, Dmitry Dolgov wrote:
Hi all,
I'm faced with some troubles about the jsonb implementation, and I
hope I'll get little advice =)
If I understand correctly, an abstract function for jsonb modification
should have the following stages:
Jsonb -> JsonbValue -> Modification -> JsonbValue -> Jsonb
One can convert the *JsonbValue* to the *Jsonb* only by
*JsonbValueToJsonb* function. So, my question is can be *JsonbValue*,
that contains few *jbvBinary* elements, converted to *Jsonb* by this
function? It will be very useful, if you want modify only small part
of your JsonbValue (e.g. replace value of some key). But when I'm
trying to do this, an exception "unknown type of jsonb container"
appears. Maybe I missed something? Or is there another approach to do
this conversion?
If you can come up with a way of handling the jbvBinary values then by
all means send a patch.
But this problem is fairly easily worked around by using an iterator
over the binary value. The attached patch, which is work in progress for
adding in the currently missing json functions for jsonb, contains a
sort of example of doing this in jsonb_agg_transfn.
cheers
andrew
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');
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers