Re: Add json_typeof() and json_is_*() functions.

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