Add json_typeof() and json_is_*() functions.

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.

Regards,
Andrew Tipton

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

On Mon, Jul 29, 2013 at 2:16 AM, Andrew Tipton <andrew(at)kiwidrew(dot)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

On Mon, Jul 29, 2013 at 5:36 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> On Mon, Jul 29, 2013 at 2:16 AM, Andrew Tipton <andrew(at)kiwidrew(dot)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

On Fri, Aug 2, 2013 at 8:12 PM, Robert Haas <robertmhaas(at)gmail(dot)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(at)kiwidrew(dot)com> wrote:
> On Fri, Aug 2, 2013 at 8:12 PM, Robert Haas <robertmhaas(at)gmail(dot)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

Merlin Moncure escribió:
> On Fri, Aug 2, 2013 at 7:22 AM, Andrew Tipton <andrew(at)kiwidrew(dot)com> wrote:
> > On Fri, Aug 2, 2013 at 8:12 PM, Robert Haas <robertmhaas(at)gmail(dot)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

On Fri, Aug 2, 2013 at 8:22 AM, Andrew Tipton <andrew(at)kiwidrew(dot)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

On 08/06/2013 08:42 AM, Robert Haas wrote:
> On Fri, Aug 2, 2013 at 8:22 AM, Andrew Tipton <andrew(at)kiwidrew(dot)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