Обсуждение: 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

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

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

<div dir="ltr"><div class="gmail_extra"><div class="gmail_quote">On Fri, Aug 2, 2013 at 8:12 PM, Robert Haas <span
dir="ltr"><<ahref="mailto:robertmhaas@gmail.com" target="_blank">robertmhaas@gmail.com</a>></span> wrote:<br
/><blockquoteclass="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div id=":7w5"
style="overflow:hidden">+1,but I'm wondering why we need anything more than just<br /> json_typeof().  Doesn't that
prettymuch cover it?</div></blockquote></div><br /></div><div class="gmail_extra">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:<br /></div><div
class="gmail_extra"> json_typeof() NOT IN ('object', 'array')<br /></div><div class="gmail_extra">  json_typeof() IN
('string','number', 'boolean', 'null')<br /><br /></div><div class="gmail_extra">And it protects the user against
forgettingabout, say, the 'null' typeof() when constructing their check expression.<br /><br /><br /></div><div
class="gmail_extra">Regards,<br/>Andrew Tipton<br /></div></div> 

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

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

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

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