Commits

Andrew Dunstan  committed 6268399

working json_to_record and json_to_resordset

  • Participants
  • Parent commits b3fd5dd
  • Branches jsonbuild

Comments (0)

Files changed (5)

File src/backend/utils/adt/jsonfuncs.c

 		hashentry->val = _state->saved_scalar;
 	}
 }
+
+
+/*
+ * SQL function json_to_record
+ *
+ * turn json into a record of the type specified in the call
+ *
+ * The function os specified to return the pseudotype RECORD, so the
+ * concrete type used has to be supplied by the caller.
+ * 
+ * Modified from json_populate_record above to get type from call
+ * instead of from argument.
+ *
+ * The json is decomposed into a hash table, in which each
+ * field in the record is then looked up by name.
+ */
+Datum
+json_to_record(PG_FUNCTION_ARGS)
+{
+	text	   *json;
+	bool		use_json_as_text;
+	HTAB	   *json_hash;
+	HeapTupleHeader rec;
+	TupleDesc	tupdesc;
+	HeapTupleData tuple;
+	HeapTuple	rettuple;
+	RecordIOData *my_extra;
+	int			ncolumns;
+	int			i;
+	Datum	   *values;
+	bool	   *nulls;
+	char		fname[NAMEDATALEN];
+	JsonHashEntry *hashentry;
+
+	use_json_as_text = PG_ARGISNULL(1) ? false : PG_GETARG_BOOL(1);
+
+	get_call_result_type(fcinfo, NULL, &tupdesc);
+
+	if (PG_ARGISNULL(1))
+		PG_RETURN_NULL();
+
+	rec = NULL;
+
+	json = PG_GETARG_TEXT_P(0);
+
+	json_hash = get_json_object_as_hash(json, "json_to_record", use_json_as_text);
+	ncolumns = tupdesc->natts;
+
+	/*
+	 * We arrange to look up the needed I/O info just once per series of
+	 * calls, assuming the record type doesn't change underneath us.
+	 */
+	my_extra = (RecordIOData *) fcinfo->flinfo->fn_extra;
+	if (my_extra == NULL ||
+		my_extra->ncolumns != ncolumns)
+	{
+		fcinfo->flinfo->fn_extra =
+			MemoryContextAlloc(fcinfo->flinfo->fn_mcxt,
+							   sizeof(RecordIOData) - sizeof(ColumnIOData)
+							   + ncolumns * sizeof(ColumnIOData));
+		my_extra = (RecordIOData *) fcinfo->flinfo->fn_extra;
+		my_extra->record_type = InvalidOid;
+		my_extra->record_typmod = 0;
+		MemSet(my_extra, 0,
+			   sizeof(RecordIOData) - sizeof(ColumnIOData)
+			   + ncolumns * sizeof(ColumnIOData));
+		my_extra->ncolumns = ncolumns;
+	}
+
+	values = (Datum *) palloc(ncolumns * sizeof(Datum));
+	nulls = (bool *) palloc(ncolumns * sizeof(bool));
+
+	for (i = 0; i < ncolumns; ++i)
+	{
+		values[i] = (Datum) 0;
+		nulls[i] = true;
+	}
+
+	for (i = 0; i < ncolumns; ++i)
+	{
+		ColumnIOData *column_info = &my_extra->columns[i];
+		Oid			column_type = tupdesc->attrs[i]->atttypid;
+		char	   *value;
+
+		/* Ignore dropped columns in datatype */
+		if (tupdesc->attrs[i]->attisdropped)
+		{
+			nulls[i] = true;
+			continue;
+		}
+
+		memset(fname, 0, NAMEDATALEN);
+		strncpy(fname, NameStr(tupdesc->attrs[i]->attname), NAMEDATALEN);
+		hashentry = hash_search(json_hash, fname, HASH_FIND, NULL);
+
+		/*
+		 * Prepare to convert the column value from text
+		 */
+		if (column_info->column_type != column_type)
+		{
+			getTypeInputInfo(column_type,
+							 &column_info->typiofunc,
+							 &column_info->typioparam);
+			fmgr_info_cxt(column_info->typiofunc, &column_info->proc,
+						  fcinfo->flinfo->fn_mcxt);
+			column_info->column_type = column_type;
+		}
+		if (hashentry == NULL || hashentry->isnull)
+		{
+			/*
+			 * need InputFunctionCall to happen even for nulls, so that domain
+			 * checks are done
+			 */
+			values[i] = InputFunctionCall(&column_info->proc, NULL,
+										  column_info->typioparam,
+										  tupdesc->attrs[i]->atttypmod);
+			nulls[i] = true;
+		}
+		else
+		{
+			value = hashentry->val;
+
+			values[i] = InputFunctionCall(&column_info->proc, value,
+										  column_info->typioparam,
+										  tupdesc->attrs[i]->atttypmod);
+			nulls[i] = false;
+		}
+	}
+
+	rettuple = heap_form_tuple(tupdesc, values, nulls);
+
+	ReleaseTupleDesc(tupdesc);
+
+	PG_RETURN_DATUM(HeapTupleGetDatum(rettuple));
+}
+
+
+/*
+ * SQL function json_to_recordset
+ *
+ * set fields in a set of records from the argument json,
+ * which must be an array of objects.
+ *
+ * adapted from json_populate_recordset above, but gets
+ * the type from the call instead of from an argument.
+ */
+Datum
+json_to_recordset(PG_FUNCTION_ARGS)
+{
+	text	   *json;
+	bool		use_json_as_text;
+	ReturnSetInfo *rsi;
+	MemoryContext old_cxt;
+	Oid			tupType;
+	int32		tupTypmod;
+	HeapTupleHeader rec;
+	TupleDesc	tupdesc;
+	RecordIOData *my_extra;
+	int			ncolumns;
+	JsonLexContext *lex;
+	JsonSemAction *sem;
+	PopulateRecordsetState *state;
+
+	use_json_as_text = PG_ARGISNULL(1) ? false : PG_GETARG_BOOL(1);
+
+	rsi = (ReturnSetInfo *) fcinfo->resultinfo;
+
+	if (!rsi || !IsA(rsi, ReturnSetInfo) ||
+		(rsi->allowedModes & SFRM_Materialize) == 0 ||
+		rsi->expectedDesc == NULL)
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				 errmsg("set-valued function called in context that "
+						"cannot accept a set")));
+
+
+	rsi->returnMode = SFRM_Materialize;
+
+	/*
+	 * get the tupdesc from the result set info - it must be a record type
+	 * because we already checked that arg1 is a record type.
+	 */
+	(void) get_call_result_type(fcinfo, NULL, &tupdesc);
+
+	state = palloc0(sizeof(PopulateRecordsetState));
+	sem = palloc0(sizeof(JsonSemAction));
+
+
+	/* make these in a sufficiently long-lived memory context */
+	old_cxt = MemoryContextSwitchTo(rsi->econtext->ecxt_per_query_memory);
+
+	state->ret_tdesc = CreateTupleDescCopy(tupdesc);
+	BlessTupleDesc(state->ret_tdesc);
+	state->tuple_store =
+		tuplestore_begin_heap(rsi->allowedModes & SFRM_Materialize_Random,
+							  false, work_mem);
+
+	MemoryContextSwitchTo(old_cxt);
+
+	/* if the json is null send back an empty set */
+	if (PG_ARGISNULL(0))
+		PG_RETURN_NULL();
+
+	json = PG_GETARG_TEXT_P(0);
+
+	rec = NULL;
+
+	tupType = tupdesc->tdtypeid;
+	tupTypmod = tupdesc->tdtypmod;
+	ncolumns = tupdesc->natts;
+
+	lex = makeJsonLexContext(json, true);
+
+	/*
+	 * We arrange to look up the needed I/O info just once per series of
+	 * calls, assuming the record type doesn't change underneath us.
+	 */
+	my_extra = (RecordIOData *) fcinfo->flinfo->fn_extra;
+	if (my_extra == NULL ||
+		my_extra->ncolumns != ncolumns)
+	{
+		fcinfo->flinfo->fn_extra =
+			MemoryContextAlloc(fcinfo->flinfo->fn_mcxt,
+							   sizeof(RecordIOData) - sizeof(ColumnIOData)
+							   + ncolumns * sizeof(ColumnIOData));
+		my_extra = (RecordIOData *) fcinfo->flinfo->fn_extra;
+		my_extra->record_type = InvalidOid;
+		my_extra->record_typmod = 0;
+	}
+
+	if (my_extra->record_type != tupType ||
+		my_extra->record_typmod != tupTypmod)
+	{
+		MemSet(my_extra, 0,
+			   sizeof(RecordIOData) - sizeof(ColumnIOData)
+			   + ncolumns * sizeof(ColumnIOData));
+		my_extra->record_type = tupType;
+		my_extra->record_typmod = tupTypmod;
+		my_extra->ncolumns = ncolumns;
+	}
+
+	sem->semstate = (void *) state;
+	sem->array_start = populate_recordset_array_start;
+	sem->array_element_start = populate_recordset_array_element_start;
+	sem->scalar = populate_recordset_scalar;
+	sem->object_field_start = populate_recordset_object_field_start;
+	sem->object_field_end = populate_recordset_object_field_end;
+	sem->object_start = populate_recordset_object_start;
+	sem->object_end = populate_recordset_object_end;
+
+	state->lex = lex;
+
+	state->my_extra = my_extra;
+	state->rec = rec;
+	state->use_json_as_text = use_json_as_text;
+	state->fn_mcxt = fcinfo->flinfo->fn_mcxt;
+
+	pg_parse_json(lex, sem);
+
+	rsi->setResult = state->tuple_store;
+	rsi->setDesc = state->ret_tdesc;
+
+	PG_RETURN_NULL();
+
+}
+

File src/include/catalog/pg_proc.h

 DESCR("get record fields from a json object");
 DATA(insert OID = 3961 (  json_populate_recordset  PGNSP PGUID 12 1 100 0 0 f f f f f t s 3 0 2283 "2283 114 16" _null_ _null_ _null_ _null_ json_populate_recordset _null_ _null_ _null_ ));
 DESCR("get set of records with fields from a json array of objects");
+DATA(insert OID = 3204 (  json_to_record	   PGNSP PGUID 12 1 0 0 0 f f f f f f s 2 0 2249 "114 16" _null_ _null_ _null_ _null_ json_to_record _null_ _null_ _null_ ));
+DESCR("get record fields from a json object");
+DATA(insert OID = 3205 (  json_to_recordset  PGNSP PGUID 12 1 100 0 0 f f f f f t s 2 0 2249 "114 16" _null_ _null_ _null_ _null_ json_to_recordset _null_ _null_ _null_ ));
+DESCR("get set of records with fields from a json array of objects");
 DATA(insert OID = 3968 (  json_typeof              PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 25 "114" _null_ _null_ _null_ _null_ json_typeof _null_ _null_ _null_ ));
 DESCR("get the type of a json value");
 

File src/include/utils/json.h

 extern Datum json_array_elements(PG_FUNCTION_ARGS);
 extern Datum json_populate_record(PG_FUNCTION_ARGS);
 extern Datum json_populate_recordset(PG_FUNCTION_ARGS);
+extern Datum json_to_record(PG_FUNCTION_ARGS);
+extern Datum json_to_recordset(PG_FUNCTION_ARGS);
 
 #endif   /* JSON_H */

File src/test/regress/expected/json.out

  {"turbines" : { "847001" : {"name" : "t15", "type" : "GE1043"}, "847002" : {"name" : "t16", "type" : "GE1043"}, "847003" : {"name" : "sub-alpha", "type" : "GESS90"} }}
 (1 row)
 
+-- json_object
+-- one dimension
+SELECT json_object('{a,1,b,2,3,NULL,"d e f","a b c"}');
+                      json_object                      
+-------------------------------------------------------
+ {"a" : "1", "b" : "2", "3" : null, "d e f" : "a b c"}
+(1 row)
+
+-- same but with two dimensions
+SELECT json_object('{{a,1},{b,2},{3,NULL},{"d e f","a b c"}}');
+                      json_object                      
+-------------------------------------------------------
+ {"a" : "1", "b" : "2", "3" : null, "d e f" : "a b c"}
+(1 row)
+
+-- odd number error
+SELECT json_object('{a,b,c}');
+ERROR:  array must have even number of elements
+-- one column error
+SELECT json_object('{{a},{b}}');
+ERROR:  array must have two columns
+-- too many columns error
+SELECT json_object('{{a,b,c},{b,c,d}}');
+ERROR:  array must have two columns
+-- too many dimensions error
+SELECT json_object('{{{a,b},{c,d}},{{b,c},{d,e}}}');
+ERROR:  wrong number of array subscripts
+--two argument form of json_object
+select json_object('{a,b,c,"d e f"}','{1,2,3,"a b c"}');
+                     json_object                      
+------------------------------------------------------
+ {"a" : "1", "b" : "2", "c" : "3", "d e f" : "a b c"}
+(1 row)
+
+-- too many dimensions
+SELECT json_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"}}');
+ERROR:  wrong number of array subscripts
+-- mismatched dimensions
+select json_object('{a,b,c,"d e f",g}','{1,2,3,"a b c"}');
+ERROR:  mismatched array dimensions
+select json_object('{a,b,c,"d e f"}','{1,2,3,"a b c",g}');
+ERROR:  mismatched array dimensions
+-- null key error
+select json_object('{a,b,NULL,"d e f"}','{1,2,3,"a b c"}');
+ERROR:  null value not allowed for object key
+-- empty key error
+select json_object('{a,b,"","d e f"}','{1,2,3,"a b c"}');
+ERROR:  empty value not allowed for object key
+-- json_to_record and json_to_recordset
+select * from json_to_record('{"a":1,"b":"foo","c":"bar"}',true) 
+    as x(a int, b text, d text);
+ a |  b  | d 
+---+-----+---
+ 1 | foo | 
+(1 row)
+
+select * from json_to_recordset('[{"a":1,"b":"foo","d":false},{"a":2,"b":"bar","c":true}]',false) 
+    as x(a int, b text, c boolean);
+ a |  b  | c 
+---+-----+---
+ 1 | foo | 
+ 2 | bar | t
+(2 rows)
+

File src/test/regress/sql/json.sql

 select json_object('{a,b,"","d e f"}','{1,2,3,"a b c"}');
 
 
+-- json_to_record and json_to_recordset
 
+select * from json_to_record('{"a":1,"b":"foo","c":"bar"}',true) 
+    as x(a int, b text, d text);
+
+select * from json_to_recordset('[{"a":1,"b":"foo","d":false},{"a":2,"b":"bar","c":true}]',false) 
+    as x(a int, b text, c boolean);