Add json_typeof() and json_is_*() functions.

On 07/29/2013 03:16 AM, Andrew Tipton wrote:

The attached patch adds four new SQL functions for the JSON type:
json_typeof(json) RETURNS text
json_is_object(json) RETURNS boolean
json_is_array(json) RETURNS boolean
json_is_scalar(json) RETURNS boolean

Please add to the next CommitFest if you have not already done so.

cheers

andrew

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Jul 29, 2013 at 2:16 AM, Andrew Tipton <andrew@kiwidrew.com> wrote:

The attached patch adds four new SQL functions for the JSON type:
json_typeof(json) RETURNS text
json_is_object(json) RETURNS boolean
json_is_array(json) RETURNS boolean
json_is_scalar(json) RETURNS boolean

The motivating use-case for this patch is the ability to easily create a
domain type for what RFC 4627 calls "json text", where the top-level value
must be either an object or array. An example of this usage is:

CREATE DOMAIN json_document AS json CHECK (NOT json_is_scalar(VALUE));

An additional use-case arises when writing functions which can handle
arbitrary JSON values. This can be difficult when nested objects or arrays
are present or when the input may be either an array or an object. Many of
the built-in functions will raise an error when presented with an "invalid"
value, such as when giving an array to json_object_keys(). The
json_typeof() and json_is_*() functions should make it easier to call the
correct function in these cases, e.g.:

CASE json_typeof($1)
WHEN 'object' THEN json_object_keys($1)
WHEN 'array' THEN json_array_elements($1)
ELSE $1
END

These new functions operate by making a single call to json_lex() to get the
first token of the JSON value; this token uniquely determines the value's
type. (Thanks to Merlin Moncure for suggesting this approach.)

The patch also updates the "JSON Functions and Operators" section of the
docs to ensure that the words "value", "object", and "array" are used in a
consistent manner. "JSON object" and "JSON array" refer to parameters which
must be an object or an array or to results which are always an object or an
array. "JSON value" refers to parameters or results which may be any kind
of JSON.

you're welcome! :-).

small point:
Personally I would prune the supplied functions to json_typeof() and
json_is_scalar(). These functions are in the public namespace so
there is a certain minimum bang/buck ratio which IMNSHO
json_is_object() and json_is_array() don't meet -- just call
json_typeof() to get that info.

merlin

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Jul 29, 2013 at 5:36 PM, Merlin Moncure <mmoncure@gmail.com> wrote:

On Mon, Jul 29, 2013 at 2:16 AM, Andrew Tipton <andrew@kiwidrew.com> wrote:

The attached patch adds four new SQL functions for the JSON type:
json_typeof(json) RETURNS text
json_is_object(json) RETURNS boolean
json_is_array(json) RETURNS boolean
json_is_scalar(json) RETURNS boolean

The motivating use-case for this patch is the ability to easily create a
domain type for what RFC 4627 calls "json text", where the top-level value
must be either an object or array. An example of this usage is:

CREATE DOMAIN json_document AS json CHECK (NOT json_is_scalar(VALUE));

An additional use-case arises when writing functions which can handle
arbitrary JSON values. This can be difficult when nested objects or arrays
are present or when the input may be either an array or an object. Many of
the built-in functions will raise an error when presented with an "invalid"
value, such as when giving an array to json_object_keys(). The
json_typeof() and json_is_*() functions should make it easier to call the
correct function in these cases, e.g.:

CASE json_typeof($1)
WHEN 'object' THEN json_object_keys($1)
WHEN 'array' THEN json_array_elements($1)
ELSE $1
END

These new functions operate by making a single call to json_lex() to get the
first token of the JSON value; this token uniquely determines the value's
type. (Thanks to Merlin Moncure for suggesting this approach.)

The patch also updates the "JSON Functions and Operators" section of the
docs to ensure that the words "value", "object", and "array" are used in a
consistent manner. "JSON object" and "JSON array" refer to parameters which
must be an object or an array or to results which are always an object or an
array. "JSON value" refers to parameters or results which may be any kind
of JSON.

you're welcome! :-).

small point:
Personally I would prune the supplied functions to json_typeof() and
json_is_scalar(). These functions are in the public namespace so
there is a certain minimum bang/buck ratio which IMNSHO
json_is_object() and json_is_array() don't meet -- just call
json_typeof() to get that info.

+1, but I'm wondering why we need anything more than just
json_typeof(). Doesn't that pretty much cover it?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Fri, Aug 2, 2013 at 8:12 PM, Robert Haas <robertmhaas@gmail.com> wrote:

+1, but I'm wondering why we need anything more than just
json_typeof(). Doesn't that pretty much cover it?

I agree with Merlin that json_is_object() is superfluous, since it can just
be replaced with json_typeof() = 'object'. Likewise for json_is_array().
But without json_is_scalar(), the choice is one of these two forms:
json_typeof() NOT IN ('object', 'array')
json_typeof() IN ('string', 'number', 'boolean', 'null')

And it protects the user against forgetting about, say, the 'null' typeof()
when constructing their check expression.

Regards,
Andrew Tipton

On Fri, Aug 2, 2013 at 7:22 AM, Andrew Tipton <andrew@kiwidrew.com> wrote:

On Fri, Aug 2, 2013 at 8:12 PM, Robert Haas <robertmhaas@gmail.com> wrote:

+1, but I'm wondering why we need anything more than just
json_typeof(). Doesn't that pretty much cover it?

I agree with Merlin that json_is_object() is superfluous, since it can just
be replaced with json_typeof() = 'object'. Likewise for json_is_array().
But without json_is_scalar(), the choice is one of these two forms:
json_typeof() NOT IN ('object', 'array')
json_typeof() IN ('string', 'number', 'boolean', 'null')

And it protects the user against forgetting about, say, the 'null' typeof()
when constructing their check expression.

right: I was thinking also that if/when json were ever to get new
types, you'd appreciate that function.

merlin

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Merlin Moncure escribi�:

On Fri, Aug 2, 2013 at 7:22 AM, Andrew Tipton <andrew@kiwidrew.com> wrote:

On Fri, Aug 2, 2013 at 8:12 PM, Robert Haas <robertmhaas@gmail.com> wrote:

+1, but I'm wondering why we need anything more than just
json_typeof(). Doesn't that pretty much cover it?

I agree with Merlin that json_is_object() is superfluous, since it can just
be replaced with json_typeof() = 'object'. Likewise for json_is_array().
But without json_is_scalar(), the choice is one of these two forms:
json_typeof() NOT IN ('object', 'array')
json_typeof() IN ('string', 'number', 'boolean', 'null')

And it protects the user against forgetting about, say, the 'null' typeof()
when constructing their check expression.

right: I was thinking also that if/when json were ever to get new
types, you'd appreciate that function.

That was what I thought as well upon seen the code.

--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Fri, Aug 2, 2013 at 8:22 AM, Andrew Tipton <andrew@kiwidrew.com> wrote:

But without json_is_scalar(), the choice is one of these two forms:
json_typeof() NOT IN ('object', 'array')
json_typeof() IN ('string', 'number', 'boolean', 'null')

The first of those is what seemed to make sense to me. The user can
always define their own convenience function if they so desire. I
don't think we need to bloat the default contents of pg_proc for that.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 08/06/2013 08:42 AM, Robert Haas wrote:

On Fri, Aug 2, 2013 at 8:22 AM, Andrew Tipton <andrew@kiwidrew.com> wrote:

But without json_is_scalar(), the choice is one of these two forms:
json_typeof() NOT IN ('object', 'array')
json_typeof() IN ('string', 'number', 'boolean', 'null')

The first of those is what seemed to make sense to me. The user can
always define their own convenience function if they so desire. I
don't think we need to bloat the default contents of pg_proc for that.

I agree. I have committed a version with just the one function.

cheers

andrew

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers