Commits

Andrew Dunstan committed 86869d5

working json_build for arrays and objects and json_object_agg

  • Participants
  • Parent commits 3cff187
  • Branches jsonbuild

Comments (0)

Files changed (6)

src/backend/utils/adt/json.c

 				  bool use_line_feeds);
 static void array_to_json_internal(Datum array, StringInfo result,
 					   bool use_line_feeds);
+static void
+datum_to_json(Datum val, bool is_null, StringInfo result,
+			  TYPCATEGORY tcategory, Oid typoutputfunc, bool key_scalar);
+static void add_json(Datum orig_val, bool is_null, StringInfo result, 
+					 Oid val_type, bool key_scalar);
 
 /* the null action object used for pure validation */
 static JsonSemAction nullSemAction =
  */
 static void
 datum_to_json(Datum val, bool is_null, StringInfo result,
-			  TYPCATEGORY tcategory, Oid typoutputfunc)
+			  TYPCATEGORY tcategory, Oid typoutputfunc, bool key_scalar)
 {
 	char	   *outputstr;
 	text	   *jsontext;
 			composite_to_json(val, result, false);
 			break;
 		case TYPCATEGORY_BOOLEAN:
-			if (DatumGetBool(val))
-				appendStringInfoString(result, "true");
+			if (!key_scalar)
+				appendStringInfoString(result, DatumGetBool(val) ? "true" : "false");
 			else
-				appendStringInfoString(result, "false");
+				escape_json(result, DatumGetBool(val) ? "true" : "false");
 			break;
 		case TYPCATEGORY_NUMERIC:
 			outputstr = OidOutputFunctionCall(typoutputfunc, val);
-			/*
-			 * Don't call escape_json here if it's a valid JSON number.
-			 */
-			dummy_lex.input = *outputstr == '-' ? outputstr + 1 : outputstr;
-			dummy_lex.input_length = strlen(dummy_lex.input);
-			json_lex_number(&dummy_lex, dummy_lex.input, &numeric_error);
-			if (! numeric_error)
-				appendStringInfoString(result, outputstr);
-			else
+			if (key_scalar)
+			{
+				/* always quote keys */
 				escape_json(result, outputstr);
+			}
+			else
+			{
+				/*
+				 * Don't call escape_json for a non-key if it's a valid JSON number.
+				 */
+				dummy_lex.input = *outputstr == '-' ? outputstr + 1 : outputstr;
+				dummy_lex.input_length = strlen(dummy_lex.input);
+				json_lex_number(&dummy_lex, dummy_lex.input, &numeric_error);
+				if (! numeric_error)
+					appendStringInfoString(result, outputstr);
+				else
+					escape_json(result, outputstr);
+			}
 			pfree(outputstr);
 			break;
 		case TYPCATEGORY_JSON:
 			break;
 		default:
 			outputstr = OidOutputFunctionCall(typoutputfunc, val);
+			if (key_scalar && *outputstr == '\0')
+				ereport(ERROR,
+						(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+						 errmsg("key value must not be empty")));
 			escape_json(result, outputstr);
 			pfree(outputstr);
 			break;
 		if (dim + 1 == ndims)
 		{
 			datum_to_json(vals[*valcount], nulls[*valcount], result, tcategory,
-						  typoutputfunc);
+						  typoutputfunc, false);
 			(*valcount)++;
 		}
 		else
 		else
 			tcategory = TypeCategory(tupdesc->attrs[i]->atttypid);
 
-		datum_to_json(val, isnull, result, tcategory, typoutput);
+		datum_to_json(val, isnull, result, tcategory, typoutput, false);
 	}
 
 	appendStringInfoChar(result, '}');
 	ReleaseTupleDesc(tupdesc);
 }
 
+static void
+add_json(Datum orig_val, bool is_null, StringInfo result, Oid val_type, bool key_scalar)
+{
+    Datum       val;
+	TYPCATEGORY tcategory;
+	Oid			typoutput;
+	bool		typisvarlena;
+	Oid         castfunc = InvalidOid;
+
+    if (val_type == InvalidOid)
+        ereport(ERROR,
+                (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+                 errmsg("could not determine input data type")));
+
+
+	getTypeOutputInfo(val_type, &typoutput, &typisvarlena);
+
+	if (val_type > FirstNormalObjectId)
+	{
+		    HeapTuple   tuple;
+			Form_pg_cast castForm;
+
+			tuple = SearchSysCache2(CASTSOURCETARGET,
+									ObjectIdGetDatum(val_type),
+									ObjectIdGetDatum(JSONOID));
+			if (HeapTupleIsValid(tuple))
+			{
+				castForm = (Form_pg_cast) GETSTRUCT(tuple);
+
+				if (castForm->castmethod == COERCION_METHOD_FUNCTION)
+					castfunc = typoutput = castForm->castfunc;
+
+				ReleaseSysCache(tuple);
+			}
+	}
+
+	if (castfunc != InvalidOid)
+		tcategory = TYPCATEGORY_JSON_CAST;
+	else if (val_type == RECORDARRAYOID)
+		tcategory = TYPCATEGORY_ARRAY;
+	else if (val_type == RECORDOID)
+		tcategory = TYPCATEGORY_COMPOSITE;
+	else if (val_type == JSONOID)
+		tcategory = TYPCATEGORY_JSON;
+	else
+		tcategory = TypeCategory(val_type);
+	
+	if (key_scalar && 
+		(tcategory == TYPCATEGORY_ARRAY || 
+		 tcategory == TYPCATEGORY_COMPOSITE || 
+		 tcategory ==  TYPCATEGORY_JSON || 
+		 tcategory == TYPCATEGORY_JSON_CAST))
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("key value must be scalar, not array, composite or json")));
+		
+	/*
+	 * If we have a toasted datum, forcibly detoast it here to avoid
+	 * memory leakage inside the type's output routine.
+	 */
+	if (typisvarlena && orig_val != (Datum) 0)
+		val = PointerGetDatum(PG_DETOAST_DATUM(orig_val));
+	else
+		val = orig_val;
+	
+	datum_to_json(val, is_null, result, tcategory, typoutput, key_scalar);
+
+	/* Clean up detoasted copy, if any */
+	if (val != orig_val)
+		pfree(DatumGetPointer(val));
+}
+
 /*
  * SQL function array_to_json(row)
  */
 	else
 		tcategory = TypeCategory(val_type);
 
-	datum_to_json(val, false, result, tcategory, typoutput);
+	datum_to_json(val, false, result, tcategory, typoutput, false);
 
 	PG_RETURN_TEXT_P(cstring_to_text_with_len(result->data, result->len));
 }
 	if (PG_ARGISNULL(1))
 	{
 		val = (Datum) 0;
-		datum_to_json(val, true, state, 0, InvalidOid);
+		datum_to_json(val, true, state, 0, InvalidOid, false);
 		PG_RETURN_POINTER(state);
 	}
 
 		appendStringInfoString(state, "\n ");
 	}
 
-	datum_to_json(val, false, state, tcategory, typoutput);
+	datum_to_json(val, false, state, tcategory, typoutput, false);
 
 	/*
 	 * The transition type for array_agg() is declared to be "internal", which
 }
 
 /*
+ * json_object_agg transition function.
+ *
+ * aggregate two input columns as a single json value.
+ */
+Datum
+json_object_agg_transfn(PG_FUNCTION_ARGS)
+{
+    Oid         val_type;
+    MemoryContext aggcontext,
+                oldcontext;
+    StringInfo  state;
+    Datum       arg;
+
+    if (!AggCheckCallContext(fcinfo, &aggcontext))
+    {
+        /* cannot be called directly because of internal-type argument */
+        elog(ERROR, "json_agg_transfn called in non-aggregate context");
+    }
+
+    if (PG_ARGISNULL(0))
+    {
+        /*
+         * Make this StringInfo in a context where it will persist for the
+         * duration off the aggregate call. It's only needed for this initial
+         * piece, as the StringInfo routines make sure they use the right
+         * context to enlarge the object if necessary.
+         */
+        oldcontext = MemoryContextSwitchTo(aggcontext);
+        state = makeStringInfo();
+        MemoryContextSwitchTo(oldcontext);
+
+        appendStringInfoString(state, "{ ");
+    }
+    else
+    {
+        state = (StringInfo) PG_GETARG_POINTER(0);
+        appendStringInfoString(state, ", ");
+    }
+
+	if (PG_ARGISNULL(1))
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("field name must not be null")));
+
+
+	val_type = get_fn_expr_argtype(fcinfo->flinfo, 1);
+	/* 
+	 * 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, 1))
+	{
+		val_type = TEXTOID;
+		arg = CStringGetTextDatum(PG_GETARG_POINTER(1));
+	}
+	else
+	{
+		arg = PG_GETARG_DATUM(1);
+	}
+
+	if (val_type == InvalidOid || val_type == UNKNOWNOID)
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("arg 1: could not determine data type")));
+
+	add_json(arg, false, state, val_type, true);
+	
+	appendStringInfoString(state," : ");
+
+	val_type = get_fn_expr_argtype(fcinfo->flinfo, 2);
+	/* see comments above */
+	if (val_type == UNKNOWNOID && get_fn_expr_arg_stable(fcinfo->flinfo, 2))
+	{
+		val_type = TEXTOID;
+		if (PG_ARGISNULL(2))
+			arg = (Datum)0;
+		else
+			arg = CStringGetTextDatum(PG_GETARG_POINTER(2));
+	}
+	else
+	{
+		arg = PG_GETARG_DATUM(2);
+	}
+
+	if (val_type == InvalidOid || val_type == UNKNOWNOID)
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("arg 2: could not determine data type")));
+
+	add_json(arg, PG_ARGISNULL(2), state, val_type, false);
+
+	PG_RETURN_POINTER(state);
+}
+
+/*
+ * json_object_agg final function.
+ *
+ */
+Datum
+json_object_agg_finalfn(PG_FUNCTION_ARGS)
+{
+    StringInfo  state;
+
+    /* cannot be called directly because of internal-type argument */
+    Assert(AggCheckCallContext(fcinfo, NULL));
+
+    state = PG_ARGISNULL(0) ? NULL : (StringInfo) PG_GETARG_POINTER(0);
+
+    if (state == NULL)
+        PG_RETURN_TEXT_P(cstring_to_text("{}"));
+
+    appendStringInfoString(state, " }");
+
+    PG_RETURN_TEXT_P(cstring_to_text_with_len(state->data, state->len));
+}
+
+/*
+ * SQL function json_build_object(variadic "any")
+ */
+Datum
+json_build_object(PG_FUNCTION_ARGS)
+{
+	int nargs = PG_NARGS();
+	int i;
+	Datum arg;
+	char *sep = "";
+    StringInfo  result;
+	Oid val_type;
+	
+
+	if (nargs % 2 != 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("invalid number or arguments: object must be matched key value pairs")));
+
+    result = makeStringInfo();
+
+	appendStringInfoChar(result,'{');
+
+	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)));
+		appendStringInfoString(result,sep);
+		sep = ", ";
+		add_json(arg, false, result, val_type, true);
+
+		appendStringInfoString(result," : ");
+
+		/* 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_json(arg, PG_ARGISNULL(i+1), result, val_type, false);
+
+	}
+	appendStringInfoChar(result,'}');
+
+	PG_RETURN_TEXT_P(cstring_to_text_with_len(result->data, result->len));
+	
+}
+
+Datum
+json_build_object_noargs(PG_FUNCTION_ARGS)
+{
+	PG_RETURN_TEXT_P(cstring_to_text_with_len("{}", 2));
+}
+
+/*
+ * SQL function json_build_array(variadic "any")
+ */
+Datum
+json_build_array(PG_FUNCTION_ARGS)
+{
+	int nargs = PG_NARGS();
+	int i;
+	Datum arg;
+	char *sep = "";
+    StringInfo  result;
+	Oid val_type;
+	
+
+    result = makeStringInfo();
+
+	appendStringInfoChar(result,'[');
+
+	for (i = 0; i < nargs; i ++)
+	{
+		val_type = get_fn_expr_argtype(fcinfo->flinfo, i);
+		arg = PG_GETARG_DATUM(i+1);
+		/* see comments in json_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)));
+		appendStringInfoString(result,sep);
+		sep = ", ";
+		add_json(arg, PG_ARGISNULL(i), result, val_type, false);
+	}
+	appendStringInfoChar(result,']');
+
+	PG_RETURN_TEXT_P(cstring_to_text_with_len(result->data, result->len));
+	
+}
+
+Datum
+json_build_array_noargs(PG_FUNCTION_ARGS)
+{
+	PG_RETURN_TEXT_P(cstring_to_text_with_len("[]", 2));
+}
+
+
+
+/*
  * Produce a JSON string literal, properly escaping characters in the text.
  */
 void

src/include/catalog/pg_aggregate.h

 
 /* json */
 DATA(insert ( 3175	n 0 json_agg_transfn	json_agg_finalfn	0	2281	0	_null_ ));
+DATA(insert ( 3197	n 0 json_object_agg_transfn	json_object_agg_finalfn	0	2281	0	_null_ ));
 
 /* ordered-set and hypothetical-set aggregates */
 DATA(insert ( 3972	o 1 ordered_set_transition			percentile_disc_final					0	2281	0	_null_ ));

src/include/catalog/pg_proc.h

 DESCR("json aggregate final function");
 DATA(insert OID = 3175 (  json_agg		   PGNSP PGUID 12 1 0 0 0 t f f f f f i 1 0 114 "2283" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
 DESCR("aggregate input into json");
+DATA(insert OID = 3195 (  json_object_agg_transfn	 PGNSP PGUID 12 1 0 0 0 f f f f f f i 3 0 2281 "2281 2276 2276" _null_ _null_ _null_ _null_ json_object_agg_transfn _null_ _null_ _null_ ));
+DESCR("json object aggregate transition function");
+DATA(insert OID = 3196 (  json_object_agg_finalfn	 PGNSP PGUID 12 1 0 0 0 f f f f f f i 1 0 114 "2281" _null_ _null_ _null_ _null_ json_object_agg_finalfn _null_ _null_ _null_ ));
+DESCR("json object aggregate final function");
+DATA(insert OID = 3197 (  json_object_agg		   PGNSP PGUID 12 1 0 0 0 t f f f f f i 2 0 114 "2276 2276" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
+DESCR("aggregate input into a json object");
+DATA(insert OID = 3198 (  json_build_array	   PGNSP PGUID 12 1 0 2276 0 f f f f f f i 1 0 114 "2276" "{2276}" "{v}" _null_ _null_ json_build_array _null_ _null_ _null_ ));
+DESCR("build a json array from any inputs");
+DATA(insert OID = 3199 (  json_build_array	   PGNSP PGUID 12 1 0 0 0 f f f f f f i 0 0 114  "" _null_ _null_ _null_ _null_ json_build_array_noargs _null_ _null_ _null_ ));
+DESCR("build an empty json array");
+DATA(insert OID = 3200 (  json_build_object	   PGNSP PGUID 12 1 0 2276 0 f f f f f f i 1 0 114 "2276" "{2276}" "{v}" _null_ _null_ json_build_object _null_ _null_ _null_ ));
+DESCR("build a json object from pairwise key/value inputs");
+DATA(insert OID = 3201 (  json_build_object	   PGNSP PGUID 12 1 0 0 0 f f f f f f i 0 0 114  "" _null_ _null_ _null_ _null_ json_build_object_noargs _null_ _null_ _null_ ));
+DESCR("build an empty json object");
 DATA(insert OID = 3176 (  to_json	   PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 114 "2283" _null_ _null_ _null_ _null_ to_json _null_ _null_ _null_ ));
 DESCR("map input to json");
 

src/include/utils/json.h

 extern Datum json_agg_transfn(PG_FUNCTION_ARGS);
 extern Datum json_agg_finalfn(PG_FUNCTION_ARGS);
 
+extern Datum json_object_agg_finalfn(PG_FUNCTION_ARGS);
+extern Datum json_object_agg_transfn(PG_FUNCTION_ARGS);
+
+extern Datum json_build_object(PG_FUNCTION_ARGS);
+extern Datum json_build_object_noargs(PG_FUNCTION_ARGS);
+extern Datum json_build_array(PG_FUNCTION_ARGS);
+extern Datum json_build_array_noargs(PG_FUNCTION_ARGS);
+
 extern void escape_json(StringInfo buf, const char *str);
 
 extern Datum json_typeof(PG_FUNCTION_ARGS);

src/test/regress/expected/json.out

                       | 
 (11 rows)
 
+-- json_build_array, json_build_object, json_object_agg
+SELECT json_build_array('a',1,'b',1.2,'c',true,'d',null,'e',json '{"x": 3, "y": [1,2,3]}');
+                           json_build_array                            
+-----------------------------------------------------------------------
+ ["a", 1, "b", 1.2, "c", true, "d", null, "e", {"x": 3, "y": [1,2,3]}]
+(1 row)
+
+SELECT json_build_object('a',1,'b',1.2,'c',true,'d',null,'e',json '{"x": 3, "y": [1,2,3]}');
+                             json_build_object                              
+----------------------------------------------------------------------------
+ {"a" : 1, "b" : 1.2, "c" : true, "d" : null, "e" : {"x": 3, "y": [1,2,3]}}
+(1 row)
+
+SELECT json_build_object( 
+       'a', json_build_object('b',false,'c',99), 
+       'd', json_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)));
+                                        json_build_object                                        
+-------------------------------------------------------------------------------------------------
+ {"a" : {"b" : false, "c" : 99}, "d" : {"e" : [9,8,7], "f" : {"relkind":"r","name":"pg_class"}}}
+(1 row)
+
+-- empty objects/arrays
+SELECT json_build_array();
+ json_build_array 
+------------------
+ []
+(1 row)
+
+SELECT json_build_object();
+ json_build_object 
+-------------------
+ {}
+(1 row)
+
+-- make sure keys are quoted
+SELECT json_build_object(1,2);
+ json_build_object 
+-------------------
+ {"1" : 2}
+(1 row)
+
+-- keys must be scalar and not null
+SELECT json_build_object(null,2);
+ERROR:  arg 1: key cannot be null
+SELECT json_build_object(r,2) FROM (SELECT 1 AS a, 2 AS b) r;
+ERROR:  key value must be scalar, not array, composite or json
+SELECT json_build_object(json '{"a":1,"b":2}', 3);
+ERROR:  key value must be scalar, not array, composite or json
+SELECT json_build_object('{1,2,3}'::int[], 3);
+ERROR:  key value must be scalar, not array, composite or json
+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 json_build_object('turbines',json_object_agg(serial_num,json_build_object('name',name,'type',type)))
+FROM foo;
+                                                                            json_build_object                                                                            
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ {"turbines" : { "847001" : {"name" : "t15", "type" : "GE1043"}, "847002" : {"name" : "t16", "type" : "GE1043"}, "847003" : {"name" : "sub-alpha", "type" : "GESS90"} }}
+(1 row)
+

src/test/regress/sql/json.sql

                (json '{}'),
                (NULL::json))
       as data(value);
+
+-- json_build_array, json_build_object, json_object_agg
+
+SELECT json_build_array('a',1,'b',1.2,'c',true,'d',null,'e',json '{"x": 3, "y": [1,2,3]}');
+
+SELECT json_build_object('a',1,'b',1.2,'c',true,'d',null,'e',json '{"x": 3, "y": [1,2,3]}');
+
+SELECT json_build_object( 
+       'a', json_build_object('b',false,'c',99), 
+       'd', json_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 json_build_array();
+
+SELECT json_build_object();
+
+-- make sure keys are quoted
+SELECT json_build_object(1,2);
+
+-- keys must be scalar and not null
+SELECT json_build_object(null,2);
+
+SELECT json_build_object(r,2) FROM (SELECT 1 AS a, 2 AS b) r;
+
+SELECT json_build_object(json '{"a":1,"b":2}', 3);
+
+SELECT json_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 json_build_object('turbines',json_object_agg(serial_num,json_build_object('name',name,'type',type)))
+FROM foo;
+