Added support for arrays to to_json (but not from_json).

author: Joey Adams 2010-07-22 18:21:05 +0000
committer: Joey Adams 2010-07-22 18:21:05 +0000
commit: 1c907055d713e4ccbb41f246da2bd7f8f07ed277 (patch)
tree: 8351ea186230a3d4541467577965e09960df2f82
parent: 9dd13c566fe14be9d3c807e9d4e140c5a9c6ad9d (diff)
6 files changed, 479 insertions, 52 deletions
diff --git a/Makefile b/Makefile
index 35057c6..dd5119b 100644
--- a/Makefile
+++ b/Makefile
@@ -3,7 +3,7 @@ OBJS = json.o jsonpath.o json_io.o json_op.o
 
 DATA_built = json.sql
 DATA = uninstall_json.sql
-REGRESS = init json validate condense orig json_get json_set
+REGRESS = init json validate condense orig json_get json_set array_to_json
 
 ifdef USE_PGXS
 PG_CONFIG = pg_config
diff --git a/expected/array_to_json.out b/expected/array_to_json.out
new file mode 100644
index 0000000..be408f3
--- /dev/null
+++ b/expected/array_to_json.out
@@ -0,0 +1,244 @@
+SELECT to_json(ARRAY[1,2,3]);
+ to_json 
+---------
+ [1,2,3]
+(1 row)
+
+SELECT to_json(ARRAY[[1],[2],[3]]);
+    to_json    
+---------------
+ [[1],[2],[3]]
+(1 row)
+
+SELECT to_json(ARRAY[[1,2],[2],[3]]);
+ERROR:  multidimensional arrays must have array expressions with matching dimensions
+SELECT to_json(ARRAY[[1,2],[3,4],[5,6]]);
+       to_json       
+---------------------
+ [[1,2],[3,4],[5,6]]
+(1 row)
+
+SELECT to_json(ARRAY[[],[],[]]);
+ERROR:  cannot determine type of empty array
+LINE 1: SELECT to_json(ARRAY[[],[],[]]);
+                             ^
+HINT:  Explicitly cast to the desired type, for example ARRAY[]::integer[].
+SELECT to_json(ARRAY[[],[],[]]::integer[][]);
+ to_json 
+---------
+ []
+(1 row)
+
+SELECT to_json(ARRAY['hello']);
+  to_json  
+-----------
+ ["hello"]
+(1 row)
+
+SELECT to_json(ARRAY['hello', 'world']);
+      to_json      
+-------------------
+ ["hello","world"]
+(1 row)
+
+SELECT to_json(ARRAY['hello', 'world', 5]);
+ERROR:  invalid input syntax for integer: "hello"
+LINE 1: SELECT to_json(ARRAY['hello', 'world', 5]);
+                             ^
+SELECT to_json(ARRAY['hello', 'world', 5]::text[]);
+        to_json        
+-----------------------
+ ["hello","world","5"]
+(1 row)
+
+SELECT to_json(ARRAY[ARRAY['hello', 'world', 5]]::text[][]);
+         to_json         
+-------------------------
+ [["hello","world","5"]]
+(1 row)
+
+SELECT to_json(ARRAY[]);
+ERROR:  cannot determine type of empty array
+LINE 1: SELECT to_json(ARRAY[]);
+                       ^
+HINT:  Explicitly cast to the desired type, for example ARRAY[]::integer[].
+SELECT to_json(ARRAY[]::text[]);
+ to_json 
+---------
+ []
+(1 row)
+
+SELECT to_json(ARRAY['hello', 'world', null]);
+        to_json         
+------------------------
+ ["hello","world",null]
+(1 row)
+
+SELECT to_json(ARRAY['hello', 'world', null, 5]);
+ERROR:  invalid input syntax for integer: "hello"
+LINE 1: SELECT to_json(ARRAY['hello', 'world', null, 5]);
+                             ^
+SELECT to_json(ARRAY['hello', 'world', null, '5']);
+          to_json           
+----------------------------
+ ["hello","world",null,"5"]
+(1 row)
+
+SELECT to_json(ARRAY[null]);
+ to_json 
+---------
+ [null]
+(1 row)
+
+SELECT to_json(ARRAY[null, null]);
+   to_json   
+-------------
+ [null,null]
+(1 row)
+
+SELECT to_json(ARRAY[[null],[null],[null]]);
+        to_json         
+------------------------
+ [[null],[null],[null]]
+(1 row)
+
+SELECT to_json(ARRAY[[null,null],[null,null],[null,null]]);
+                to_json                
+---------------------------------------
+ [[null,null],[null,null],[null,null]]
+(1 row)
+
+SELECT to_json(ARRAY[[[null,null]],[[null,null]],[[null,null]]]);
+                   to_json                   
+---------------------------------------------
+ [[[null,null]],[[null,null]],[[null,null]]]
+(1 row)
+
+SELECT to_json(ARRAY[[[null,null],[0,1]],[[null,null],[2,3]],[[null,null],[4,5]]]);
+ERROR:  ARRAY could not convert type integer[] to text[]
+LINE 1: SELECT to_json(ARRAY[[[null,null],[0,1]],[[null,null],[2,3]]...
+                                          ^
+SELECT to_json(ARRAY[[[null,null],[0,1]],[[null,null],[2,3]],[[null,null],[4,5]]]::int[][][]);
+                            to_json                            
+---------------------------------------------------------------
+ [[[null,null],[0,1]],[[null,null],[2,3]],[[null,null],[4,5]]]
+(1 row)
+
+SELECT to_json(array[1,2,3,4,5]::int2vector);
+   to_json   
+-------------
+ [1,2,3,4,5]
+(1 row)
+
+SELECT to_json(array[1,2,3,4,5]::oidvector);
+   to_json   
+-------------
+ [1,2,3,4,5]
+(1 row)
+
+SELECT to_json(array[[1,2],[3,4],5]::oidvector);
+ERROR:  syntax error at or near "5"
+LINE 1: SELECT to_json(array[[1,2],[3,4],5]::oidvector);
+                                         ^
+SELECT to_json(array[[1,2],[3,4],[5]]::oidvector);
+ERROR:  multidimensional arrays must have array expressions with matching dimensions
+SELECT to_json(array[[1,2],[3,4],[5,5]]::oidvector);
+       to_json       
+---------------------
+ [[1,2],[3,4],[5,5]]
+(1 row)
+
+SELECT to_json(array[[1,2],[3,4],[5,6]]::oidvector);
+       to_json       
+---------------------
+ [[1,2],[3,4],[5,6]]
+(1 row)
+
+SELECT to_json(array[[1,2],[3,4],[5,6]]::int2vector);
+       to_json       
+---------------------
+ [[1,2],[3,4],[5,6]]
+(1 row)
+
+SELECT to_json(array[[1,2],[3,4],[5,6000]]::int2vector);
+        to_json         
+------------------------
+ [[1,2],[3,4],[5,6000]]
+(1 row)
+
+SELECT to_json(array[[1,2],[3,4],[5,60000]]::int2vector);
+ERROR:  smallint out of range
+SELECT to_json(array[[1,2],[3,4],[5,null]]::int2vector);
+        to_json         
+------------------------
+ [[1,2],[3,4],[5,null]]
+(1 row)
+
+SELECT to_json(array[[1,2],[null,4],[5,null]]::int2vector);
+          to_json          
+---------------------------
+ [[1,2],[null,4],[5,null]]
+(1 row)
+
+SELECT to_json(array[[1,2],[null,4],[5,null]]::oidvector);
+          to_json          
+---------------------------
+ [[1,2],[null,4],[5,null]]
+(1 row)
+
+SELECT to_json(array[[true,false],[false,true]]::bool[][]);
+           to_json           
+-----------------------------
+ [[true,false],[false,true]]
+(1 row)
+
+SELECT to_json(array[[true,false],[false,true],[null,null]]::bool[][]);
+                 to_json                 
+-----------------------------------------
+ [[true,false],[false,true],[null,null]]
+(1 row)
+
+SELECT to_json('{1,2,3}');
+ERROR:  could not determine polymorphic type because input has type "unknown"
+SELECT to_json('{1,2,3}'::text); -- should form "{1,2,3}", not build an array
+  to_json  
+-----------
+ "{1,2,3}"
+(1 row)
+
+SELECT to_json('{1,2,3}'::int[]);
+ to_json 
+---------
+ [1,2,3]
+(1 row)
+
+SELECT to_json('{one,two,three}'::text[]);
+        to_json        
+-----------------------
+ ["one","two","three"]
+(1 row)
+
+SELECT to_json('{one,two,three,null}'::text[]);
+          to_json           
+----------------------------
+ ["one","two","three",null]
+(1 row)
+
+SELECT to_json('{one,two,three,null,five}'::text[]);
+              to_json              
+-----------------------------------
+ ["one","two","three",null,"five"]
+(1 row)
+
+SELECT to_json('{null}'::text[]);
+ to_json 
+---------
+ [null]
+(1 row)
+
+SELECT to_json('{{null}}'::text[][]);
+ to_json  
+----------
+ [[null]]
+(1 row)
+
diff --git a/expected/json.out b/expected/json.out
index b47db58..abcaccd 100644
--- a/expected/json.out
+++ b/expected/json.out
@@ -180,10 +180,22 @@ SELECT to_json(NULL::INT);
  null
 (1 row)
 
+SELECT to_json(NULL::INT[]);
+ to_json 
+---------
+ null
+(1 row)
+
 SELECT to_json(NULL::VOID);
 ERROR:  to_json: cannot convert void to JSON
 SELECT to_json(NULL::MONEY);
 ERROR:  to_json: cannot convert money to JSON
+SELECT to_json('null'::text); -- should yield '"null"', not 'null'
+ to_json 
+---------
+ "null"
+(1 row)
+
 SELECT json_type(to_json(NULL));
 ERROR:  could not determine polymorphic type because input has type "unknown"
 SELECT json_type(to_json(NULL::INT));
@@ -427,7 +439,12 @@ SELECT to_json(FALSE::TEXT)::TEXT = '"' || FALSE || '"';
 
 -- to_json: array
 SELECT to_json(ARRAY[1,2,3]);
-ERROR:  to_json does not support conversion from arrays yet
+ to_json 
+---------
+ [1,2,3]
+(1 row)
+
+-- more tests are in array_to_json.sql
 -- to_json: invalid types
 SELECT to_json(row(1,2));
 ERROR:  to_json: cannot convert record to JSON
diff --git a/json_io.c b/json_io.c
index 14a57db..9e7fe55 100644
--- a/json_io.c
+++ b/json_io.c
@@ -1,5 +1,7 @@
 #include "json.h"
 
+#include "utils/array.h"
+
 PG_MODULE_MAGIC;
 
 PG_FUNCTION_INFO_V1(json_in);
@@ -113,32 +115,6 @@ static void getTypeInfo(TypeInfo *d, Oid type, IOFuncSelector which_func)
 		} \
 	} while(0)
 
-static json_type
-decide_json_type(Oid type, char category)
-{
-	switch (category) {
-		case 'S':
-			return JSON_STRING;
-		case 'P':
-			if (type == CSTRINGOID)
-				return JSON_STRING;
-			return JSON_INVALID;
-		case 'N':
-			if (type == CASHOID)
-				return JSON_INVALID;
-			return JSON_NUMBER;
-		case 'B':
-			if (type == BOOLOID)
-				return JSON_BOOL;
-			return JSON_INVALID;
-		case 'A':
-			elog(ERROR, "to_json does not support conversion from arrays yet");
-			return JSON_INVALID;
-		default:
-			return JSON_INVALID;
-	}
-}
-
 const char *from_json_cstring(const char *input, const char *funcname)
 {
 	size_t		len;
@@ -195,19 +171,19 @@ from_json(PG_FUNCTION_ARGS)
 	}
 }
 
+static json_type decide_json_type(Oid type, char category);
+static const char *datum_to_json(Datum datum, TypeInfo *typeInfo, json_type target_type);
+static const char *array_to_json(Datum datum);
+
 PG_FUNCTION_INFO_V1(to_json);
 Datum		to_json(PG_FUNCTION_ARGS);
 Datum
 to_json(PG_FUNCTION_ARGS)
 {
 	Oid			argtype = get_fn_expr_argtype(fcinfo->flinfo, 0);
-	char		*cstring;
-	jsontype	*vardata = NULL;
 	TypeInfo	*typeInfo;
 	json_type	target_type;
-	json_node	*node;
-	char		*encoded;
-	
+
 	/* Get type info, caching with fn_extra. */
 	FN_EXTRA(typeInfo,
 		initTypeInfo(typeInfo, fcinfo->flinfo->fn_mcxt);
@@ -215,23 +191,56 @@ to_json(PG_FUNCTION_ARGS)
 	getTypeInfo(typeInfo, argtype, IOFunc_output);
 	
 	target_type = decide_json_type(argtype, typeInfo->typcategory);
-	if (target_type == JSON_INVALID) {
-		elog(ERROR, "to_json: cannot convert %s to JSON",
-			format_type_be(argtype));
-	}
-	
+
 	/* If NULL argument is given, return the string "null", not actual NULL.
 	   The null check is done after the type check so users
 	   can identify type bugs earlier. */
-	if (PG_ARGISNULL(0)) {
-		vardata = cstring_to_text("null");
-		PG_RETURN_JSON_P(vardata);
+	if (PG_ARGISNULL(0))
+		PG_RETURN_JSON_P(cstring_to_text("null"));
+
+	PG_RETURN_JSON_P(cstring_to_text(
+		datum_to_json(PG_GETARG_DATUM(0), typeInfo, target_type)));
+}
+
+static json_type decide_json_type(Oid type, char category)
+{
+	switch (category) {
+		case 'S':
+			return JSON_STRING;
+		case 'P':
+			if (type == CSTRINGOID)
+				return JSON_STRING;
+			goto invalid;
+		case 'N':
+			if (type == CASHOID)
+				goto invalid;
+			return JSON_NUMBER;
+		case 'B':
+			if (type == BOOLOID)
+				return JSON_BOOL;
+			goto invalid;
+		case 'A':
+			return JSON_ARRAY;
+		default:
+			goto invalid;
 	}
-	
+
+invalid:
+	elog(ERROR, "to_json: cannot convert %s to JSON",
+		format_type_be(type));
+	return JSON_INVALID;
+}
+
+static const char *datum_to_json(Datum datum, TypeInfo *typeInfo, json_type target_type)
+{
+	char		*cstring;
+	json_node	*node;
+	char		*encoded;
+
 	switch (target_type) {
 		case JSON_STRING:
 		case JSON_NUMBER:
-			cstring = OutputFunctionCall(&typeInfo->proc, PG_GETARG_DATUM(0));
+			cstring = OutputFunctionCall(&typeInfo->proc, datum);
 			
 			if (target_type == JSON_STRING)
 				node = json_mkstring(cstring, strlen(cstring));
@@ -242,22 +251,131 @@ to_json(PG_FUNCTION_ARGS)
 			if (!encoded) {
 				/* This usually means the given string/number type was invalid. */
 				elog(ERROR, "to_json failed to convert %s \"%s\" to a valid JSON %s.\n",
-					format_type_be(argtype),
+					format_type_be(typeInfo->type),
 					cstring,
 					target_type == JSON_STRING ? "string" : "number"
 				);
 			}
-			
-			vardata = cstring_to_text(encoded);
-			break;
+
+			return encoded;
 			
 		case JSON_BOOL:
-			vardata = cstring_to_text(PG_GETARG_BOOL(0) ? "true" : "false");
-			break;
-			
+			return DatumGetBool(datum) ? "true" : "false";
+
+		case JSON_ARRAY:
+			return array_to_json(datum);
+
 		default:
 			Assert(false);
+			return NULL;
 	}
-	
-	PG_RETURN_JSON_P(vardata);
+}
+
+static const char *build_array_string(const char **values, const int *dim, int ndim);
+
+/*
+ * Note: We assume that any type with typcategory = 'A' is compatible
+ *       with array_out.
+ */
+static const char *array_to_json(Datum datum)
+{
+	ArrayType	*v = DatumGetArrayTypeP(datum);
+	Oid			element_type = ARR_ELEMTYPE(v);
+	TypeInfo	element_typeinfo;
+	json_type	target_type;
+	int			*dim;
+	int			ndim;
+	int			nitems;
+	int			i;
+	char		*s;
+	bits8		*bitmap;
+	int			bitmask;
+	Datum		elt;
+	const char	**values;
+	const char	*ret;
+
+	initTypeInfo(&element_typeinfo, CurrentMemoryContext);
+	getTypeInfo(&element_typeinfo, element_type, IOFunc_output);
+
+	target_type = decide_json_type(element_type, element_typeinfo.typcategory);
+
+	ndim = ARR_NDIM(v);
+	dim = ARR_DIMS(v);
+	nitems = ArrayGetNItems(ndim, dim);
+
+	/* Unfortunately, we can't make
+	   SELECT to_json(ARRAY[[],[],[]]::integer[][]);
+	   yield '[[],[],[]]' because ndim is set to 0 when the array
+	   has no items. */
+	if (nitems <= 0)
+		return "[]";
+
+	values = palloc(nitems * sizeof(const char *));
+
+	s = ARR_DATA_PTR(v);
+	bitmap = ARR_NULLBITMAP(v);
+	bitmask = 1;
+
+	for (i = 0; i < nitems; i++) {
+		if (bitmap && (*bitmap & bitmask) == 0) {
+			values[i] = NULL;
+		} else {
+			elt = fetch_att(s, element_typeinfo.typbyval, element_typeinfo.typlen);
+			s = att_addlength_datum(s, element_typeinfo.typlen, elt);
+			s = (char*) att_align_nominal(s, element_typeinfo.typalign);
+
+			values[i] = datum_to_json(elt, &element_typeinfo, target_type);
+		}
+
+		if (bitmap) {
+			bitmask <<= 1;
+			if (bitmask == 0x100) {
+				bitmap++;
+				bitmask = 1;
+			}
+		}
+	}
+
+	ret = build_array_string(values, dim, ndim);
+
+	pfree(values);
+
+	return ret;
+}
+
+static void build_array_string_recurse(StringInfo string, const char **values,
+		const int *dim, int ndim)
+{
+	int stride = 1;
+	int i, count = dim[0];
+
+	/* TODO: Make build_array_string nonrecursive so we don't have to do this. */
+	for (i = 1; i < ndim; i++)
+		stride *= dim[i];
+
+	appendStringInfoChar(string, '[');
+
+	for (i = 0; i < count; i++) {
+		if (ndim > 1)
+			build_array_string_recurse(string, values + i*stride, dim+1, ndim-1);
+		else if (values[i])
+			appendStringInfoString(string, values[i]);
+		else
+			appendStringInfoString(string, "null");
+
+		if (i + 1 < count)
+			appendStringInfoChar(string, ',');
+	}
+
+	appendStringInfoChar(string, ']');
+}
+
+static const char *build_array_string(const char **values, const int *dim, int ndim)
+{
+	StringInfoData	string[1];
+
+	initStringInfo(string);
+	build_array_string_recurse(string, values, dim, ndim);
+
+	return string->data;
 }
diff --git a/sql/array_to_json.sql b/sql/array_to_json.sql
new file mode 100644
index 0000000..ba07ea4
--- /dev/null
+++ b/sql/array_to_json.sql
@@ -0,0 +1,45 @@
+SELECT to_json(ARRAY[1,2,3]);
+SELECT to_json(ARRAY[[1],[2],[3]]);
+SELECT to_json(ARRAY[[1,2],[2],[3]]);
+SELECT to_json(ARRAY[[1,2],[3,4],[5,6]]);
+SELECT to_json(ARRAY[[],[],[]]);
+SELECT to_json(ARRAY[[],[],[]]::integer[][]);
+SELECT to_json(ARRAY['hello']);
+SELECT to_json(ARRAY['hello', 'world']);
+SELECT to_json(ARRAY['hello', 'world', 5]);
+SELECT to_json(ARRAY['hello', 'world', 5]::text[]);
+SELECT to_json(ARRAY[ARRAY['hello', 'world', 5]]::text[][]);
+SELECT to_json(ARRAY[]);
+SELECT to_json(ARRAY[]::text[]);
+SELECT to_json(ARRAY['hello', 'world', null]);
+SELECT to_json(ARRAY['hello', 'world', null, 5]);
+SELECT to_json(ARRAY['hello', 'world', null, '5']);
+SELECT to_json(ARRAY[null]);
+SELECT to_json(ARRAY[null, null]);
+SELECT to_json(ARRAY[[null],[null],[null]]);
+SELECT to_json(ARRAY[[null,null],[null,null],[null,null]]);
+SELECT to_json(ARRAY[[[null,null]],[[null,null]],[[null,null]]]);
+SELECT to_json(ARRAY[[[null,null],[0,1]],[[null,null],[2,3]],[[null,null],[4,5]]]);
+SELECT to_json(ARRAY[[[null,null],[0,1]],[[null,null],[2,3]],[[null,null],[4,5]]]::int[][][]);
+SELECT to_json(array[1,2,3,4,5]::int2vector);
+SELECT to_json(array[1,2,3,4,5]::oidvector);
+SELECT to_json(array[[1,2],[3,4],5]::oidvector);
+SELECT to_json(array[[1,2],[3,4],[5]]::oidvector);
+SELECT to_json(array[[1,2],[3,4],[5,5]]::oidvector);
+SELECT to_json(array[[1,2],[3,4],[5,6]]::oidvector);
+SELECT to_json(array[[1,2],[3,4],[5,6]]::int2vector);
+SELECT to_json(array[[1,2],[3,4],[5,6000]]::int2vector);
+SELECT to_json(array[[1,2],[3,4],[5,60000]]::int2vector);
+SELECT to_json(array[[1,2],[3,4],[5,null]]::int2vector);
+SELECT to_json(array[[1,2],[null,4],[5,null]]::int2vector);
+SELECT to_json(array[[1,2],[null,4],[5,null]]::oidvector);
+SELECT to_json(array[[true,false],[false,true]]::bool[][]);
+SELECT to_json(array[[true,false],[false,true],[null,null]]::bool[][]);
+SELECT to_json('{1,2,3}');
+SELECT to_json('{1,2,3}'::text); -- should form "{1,2,3}", not build an array
+SELECT to_json('{1,2,3}'::int[]);
+SELECT to_json('{one,two,three}'::text[]);
+SELECT to_json('{one,two,three,null}'::text[]);
+SELECT to_json('{one,two,three,null,five}'::text[]);
+SELECT to_json('{null}'::text[]);
+SELECT to_json('{{null}}'::text[][]);
diff --git a/sql/json.sql b/sql/json.sql
index b242918..fa84d43 100644
--- a/sql/json.sql
+++ b/sql/json.sql
@@ -42,8 +42,10 @@ SELECT json_type(j) FROM testjson;
 -- to_json: null
 SELECT to_json(NULL);
 SELECT to_json(NULL::INT);
+SELECT to_json(NULL::INT[]);
 SELECT to_json(NULL::VOID);
 SELECT to_json(NULL::MONEY);
+SELECT to_json('null'::text); -- should yield '"null"', not 'null'
 SELECT json_type(to_json(NULL));
 SELECT json_type(to_json(NULL::INT));
 SELECT json_type(to_json(NULL::VOID));
@@ -98,6 +100,7 @@ SELECT to_json(FALSE::TEXT)::TEXT = '"' || FALSE || '"';
 
 -- to_json: array
 SELECT to_json(ARRAY[1,2,3]);
+-- more tests are in array_to_json.sql
 
 -- to_json: invalid types
 SELECT to_json(row(1,2));
author	Joey Adams	2010-07-22 18:21:05 +0000
committer	Joey Adams	2010-07-22 18:21:05 +0000
commit	1c907055d713e4ccbb41f246da2bd7f8f07ed277 (patch)
tree	8351ea186230a3d4541467577965e09960df2f82
parent	9dd13c566fe14be9d3c807e9d4e140c5a9c6ad9d (diff)