Introduce jsonb, a structured format for storing json.

The new format accepts exactly the same data as the json type. However, it is
stored in a format that does not require reparsing the orgiginal text in order
to process it, making it much more suitable for indexing and other operations.
Insignificant whitespace is discarded, and the order of object keys is not
preserved. Neither are duplicate object keys kept - the later value for a given
key is the only one stored.

The new type has all the functions and operators that the json type has,
with the exception of the json generation functions (to_json, json_agg etc.)
and with identical semantics. In addition, there are operator classes for
hash and btree indexing, and two classes for GIN indexing, that have no
equivalent in the json type.

This feature grew out of previous work by Oleg Bartunov and Teodor Sigaev, which
was intended to provide similar facilities to a nested hstore type, but which
in the end proved to have some significant compatibility issues.

Authors: Oleg Bartunov,  Teodor Sigaev, Peter Geoghegan and Andrew Dunstan.
Review: Andres Freund

7 files changed, 223 insertions, 5 deletions

diff --git a/contrib/hstore/Makefile b/contrib/hstore/Makefile
index 43b7e5f9e7..2b60fbed0e 100644
--- a/contrib/hstore/Makefile
+++ b/contrib/hstore/Makefile
@@ -5,7 +5,8 @@ OBJS = hstore_io.o hstore_op.o hstore_gist.o hstore_gin.o hstore_compat.o \
 	crc32.o
 
 EXTENSION = hstore
-DATA = hstore--1.2.sql hstore--1.1--1.2.sql hstore--1.0--1.1.sql \
+DATA = hstore--1.3.sql hstore--1.2--1.3.sql \
+	hstore--1.1--1.2.sql hstore--1.0--1.1.sql \
 	hstore--unpackaged--1.0.sql
 
 REGRESS = hstore
diff --git a/contrib/hstore/expected/hstore.out b/contrib/hstore/expected/hstore.out
index 2114143022..9749e45c14 100644
--- a/contrib/hstore/expected/hstore.out
+++ b/contrib/hstore/expected/hstore.out
@@ -1453,7 +1453,7 @@ select count(*) from testhstore where h = 'pos=>98, line=>371, node=>CBA, indexe
      1
 (1 row)
 
--- json
+-- json and jsonb
 select hstore_to_json('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4');
                                          hstore_to_json                                          
 -------------------------------------------------------------------------------------------------
@@ -1472,6 +1472,24 @@ select hstore_to_json_loose('"a key" =>1, b => t, c => null, d=> 12345, e => 012
  {"b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 2.345e+4, "a key": 1}
 (1 row)
 
+select hstore_to_jsonb('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4');
+                                         hstore_to_jsonb                                         
+-------------------------------------------------------------------------------------------------
+ {"b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4", "a key": "1"}
+(1 row)
+
+select cast( hstore  '"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4' as jsonb);
+                                              jsonb                                              
+-------------------------------------------------------------------------------------------------
+ {"b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4", "a key": "1"}
+(1 row)
+
+select hstore_to_jsonb_loose('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4');
+                                 hstore_to_jsonb_loose                                 
+---------------------------------------------------------------------------------------
+ {"b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 23450, "a key": 1}
+(1 row)
+
 create table test_json_agg (f1 text, f2 hstore);
 insert into test_json_agg values ('rec1','"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4'),
        ('rec2','"a key" =>2, b => f, c => "null", d=> -12345, e => 012345.6, f=> -1.234, g=> 0.345e-4');
diff --git a/contrib/hstore/hstore--1.2--1.3.sql b/contrib/hstore/hstore--1.2--1.3.sql
new file mode 100644
index 0000000000..0a7056015b
--- /dev/null
+++ b/contrib/hstore/hstore--1.2--1.3.sql
@@ -0,0 +1,17 @@
+/* contrib/hstore/hstore--1.2--1.3.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION hstore UPDATE TO '1.3'" to load this file. \quit
+
+CREATE FUNCTION hstore_to_jsonb(hstore)
+RETURNS jsonb
+AS 'MODULE_PATHNAME', 'hstore_to_jsonb'
+LANGUAGE C IMMUTABLE STRICT;
+
+CREATE CAST (hstore AS jsonb)
+  WITH FUNCTION hstore_to_jsonb(hstore);
+
+CREATE FUNCTION hstore_to_jsonb_loose(hstore)
+RETURNS jsonb
+AS 'MODULE_PATHNAME', 'hstore_to_jsonb_loose'
+LANGUAGE C IMMUTABLE STRICT;
diff --git a/contrib/hstore/hstore--1.2.sql b/contrib/hstore/hstore--1.3.sql
index f415a7230b..995ade1b3c 100644
--- a/contrib/hstore/hstore--1.2.sql
+++ b/contrib/hstore/hstore--1.3.sql
@@ -1,4 +1,4 @@
-/* contrib/hstore/hstore--1.1.sql */
+/* contrib/hstore/hstore--1.3.sql */
 
 -- complain if script is sourced in psql, rather than via CREATE EXTENSION
 \echo Use "CREATE EXTENSION hstore" to load this file. \quit
@@ -247,6 +247,19 @@ RETURNS json
 AS 'MODULE_PATHNAME', 'hstore_to_json_loose'
 LANGUAGE C IMMUTABLE STRICT;
 
+CREATE FUNCTION hstore_to_jsonb(hstore)
+RETURNS jsonb
+AS 'MODULE_PATHNAME', 'hstore_to_jsonb'
+LANGUAGE C IMMUTABLE STRICT;
+
+CREATE CAST (hstore AS jsonb)
+  WITH FUNCTION hstore_to_jsonb(hstore);
+
+CREATE FUNCTION hstore_to_jsonb_loose(hstore)
+RETURNS jsonb
+AS 'MODULE_PATHNAME', 'hstore_to_jsonb_loose'
+LANGUAGE C IMMUTABLE STRICT;
+
 CREATE FUNCTION hstore(record)
 RETURNS hstore
 AS 'MODULE_PATHNAME', 'hstore_from_record'
diff --git a/contrib/hstore/hstore.control b/contrib/hstore/hstore.control
index 9daf5e2e00..dcc3b687cf 100644
--- a/contrib/hstore/hstore.control
+++ b/contrib/hstore/hstore.control
@@ -1,5 +1,5 @@
 # hstore extension
 comment = 'data type for storing sets of (key, value) pairs'
-default_version = '1.2'
+default_version = '1.3'
 module_pathname = '$libdir/hstore'
 relocatable = true
diff --git a/contrib/hstore/hstore_io.c b/contrib/hstore/hstore_io.c
index 65e4438d32..6cfc935788 100644
--- a/contrib/hstore/hstore_io.c
+++ b/contrib/hstore/hstore_io.c
@@ -12,6 +12,7 @@
 #include "libpq/pqformat.h"
 #include "utils/builtins.h"
 #include "utils/json.h"
+#include "utils/jsonb.h"
 #include "utils/lsyscache.h"
 #include "utils/memutils.h"
 #include "utils/typcache.h"
@@ -1374,3 +1375,167 @@ hstore_to_json(PG_FUNCTION_ARGS)
 
 	PG_RETURN_TEXT_P(cstring_to_text(dst.data));
 }
+
+PG_FUNCTION_INFO_V1(hstore_to_jsonb);
+Datum		hstore_to_jsonb(PG_FUNCTION_ARGS);
+Datum
+hstore_to_jsonb(PG_FUNCTION_ARGS)
+{
+	HStore	   *in = PG_GETARG_HS(0);
+	int			i;
+	int			count = HS_COUNT(in);
+	char	   *base = STRPTR(in);
+	HEntry	   *entries = ARRPTR(in);
+	JsonbParseState *state = NULL;
+	JsonbValue *res;
+
+	res = pushJsonbValue(&state, WJB_BEGIN_OBJECT, NULL);
+
+	for (i = 0; i < count; i++)
+	{
+		JsonbValue key, val;
+
+		key.estSize = sizeof(JEntry);
+		key.type = jbvString;
+		key.string.len = HS_KEYLEN(entries, i);
+		key.string.val = pnstrdup(HS_KEY(entries, base, i), key.string.len);
+		key.estSize += key.string.len;
+
+		res = pushJsonbValue(&state, WJB_KEY, &key);
+
+		if (HS_VALISNULL(entries, i))
+		{
+			val.estSize = sizeof(JEntry);
+			val.type = jbvNull;
+		}
+		else
+		{
+			val.estSize = sizeof(JEntry);
+			val.type = jbvString;
+			val.string.len = HS_VALLEN(entries, i);
+			val.string.val = pnstrdup(HS_VAL(entries, base, i), val.string.len);
+			val.estSize += val.string.len;
+		}
+		res = pushJsonbValue(&state, WJB_VALUE, &val);
+	}
+
+	res = pushJsonbValue(&state, WJB_END_OBJECT, NULL);
+
+	PG_RETURN_POINTER(JsonbValueToJsonb(res));
+}
+
+PG_FUNCTION_INFO_V1(hstore_to_jsonb_loose);
+Datum		hstore_to_jsonb_loose(PG_FUNCTION_ARGS);
+Datum
+hstore_to_jsonb_loose(PG_FUNCTION_ARGS)
+{
+	HStore	   *in = PG_GETARG_HS(0);
+	int			i;
+	int			count = HS_COUNT(in);
+	char	   *base = STRPTR(in);
+	HEntry	   *entries = ARRPTR(in);
+	JsonbParseState *state = NULL;
+	JsonbValue *res;
+	StringInfoData tmp;
+	bool        is_number;
+
+	initStringInfo(&tmp);
+
+	res = pushJsonbValue(&state, WJB_BEGIN_OBJECT, NULL);
+
+	for (i = 0; i < count; i++)
+	{
+		JsonbValue key, val;
+
+		key.estSize = sizeof(JEntry);
+		key.type = jbvString;
+		key.string.len = HS_KEYLEN(entries, i);
+		key.string.val = pnstrdup(HS_KEY(entries, base, i), key.string.len);
+		key.estSize += key.string.len;
+
+		res = pushJsonbValue(&state, WJB_KEY, &key);
+
+		val.estSize = sizeof(JEntry);
+
+		if (HS_VALISNULL(entries, i))
+		{
+			val.type = jbvNull;
+		}
+		/* guess that values of 't' or 'f' are booleans */
+		else if (HS_VALLEN(entries, i) == 1 && *(HS_VAL(entries, base, i)) == 't')
+		{
+			val.type = jbvBool;
+			val.boolean = true;
+		}
+		else if (HS_VALLEN(entries, i) == 1 && *(HS_VAL(entries, base, i)) == 'f')
+		{
+			val.type = jbvBool;
+			val.boolean = false;
+		}
+		else
+		{
+			is_number = false;
+			resetStringInfo(&tmp);
+
+			appendBinaryStringInfo(&tmp, HS_VAL(entries, base, i), HS_VALLEN(entries, i));
+
+			/*
+			 * don't treat something with a leading zero followed by another
+			 * digit as numeric - could be a zip code or similar
+			 */
+			if (tmp.len > 0 &&
+				!(tmp.data[0] == '0' &&
+				  isdigit((unsigned char) tmp.data[1])) &&
+				strspn(tmp.data, "+-0123456789Ee.") == tmp.len)
+			{
+				/*
+				 * might be a number. See if we can input it as a numeric
+				 * value. Ignore any actual parsed value.
+				 */
+				char	   *endptr = "junk";
+				long		lval;
+
+				lval = strtol(tmp.data, &endptr, 10);
+				(void) lval;
+				if (*endptr == '\0')
+				{
+					/*
+					 * strol man page says this means the whole string is
+					 * valid
+					 */
+					is_number = true;
+				}
+				else
+				{
+					/* not an int - try a double */
+					double		dval;
+
+					dval = strtod(tmp.data, &endptr);
+					(void) dval;
+					if (*endptr == '\0')
+						is_number = true;
+				}
+			}
+			if (is_number)
+			{
+				val.type = jbvNumeric;
+				val.numeric = DatumGetNumeric(
+					DirectFunctionCall3(numeric_in, CStringGetDatum(tmp.data), 0, -1));
+				val.estSize += VARSIZE_ANY(val.numeric) +sizeof(JEntry);
+			}
+			else
+			{
+				val.estSize = sizeof(JEntry);
+				val.type = jbvString;
+				val.string.len = HS_VALLEN(entries, i);
+				val.string.val = pnstrdup(HS_VAL(entries, base, i), val.string.len);
+				val.estSize += val.string.len;
+			}
+		}
+		res = pushJsonbValue(&state, WJB_VALUE, &val);
+	}
+
+	res = pushJsonbValue(&state, WJB_END_OBJECT, NULL);
+
+	PG_RETURN_POINTER(JsonbValueToJsonb(res));
+}
diff --git a/contrib/hstore/sql/hstore.sql b/contrib/hstore/sql/hstore.sql
index 9518f569ec..5a9e9ee5ae 100644
--- a/contrib/hstore/sql/hstore.sql
+++ b/contrib/hstore/sql/hstore.sql
@@ -331,11 +331,15 @@ set enable_seqscan=off;
 select count(*) from testhstore where h #># 'p=>1';
 select count(*) from testhstore where h = 'pos=>98, line=>371, node=>CBA, indexed=>t';
 
--- json
+-- json and jsonb
 select hstore_to_json('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4');
 select cast( hstore  '"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4' as json);
 select hstore_to_json_loose('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4');
 
+select hstore_to_jsonb('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4');
+select cast( hstore  '"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4' as jsonb);
+select hstore_to_jsonb_loose('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4');
+
 create table test_json_agg (f1 text, f2 hstore);
 insert into test_json_agg values ('rec1','"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4'),
        ('rec2','"a key" =>2, b => f, c => "null", d=> -12345, e => 012345.6, f=> -1.234, g=> 0.345e-4');