Thread: Determining the type (array, object, or scalar) of a JSON value

On Wed, Jun 12, 2013 at 9:02 AM, Andrew Tipton <andrew@kiwidrew.com> wrote:
> Hi all,
>
> I recently wanted to declare a CHECK constraint to ensure that a JSON value
> was an object with keys that were scalars (not nested objects or arrays).
> This proved to be more difficult than I had expected.  In the end, I had to
> write a json_typeof() function in pl/pgsql.  It's a simple function but uses
> a rather horrifying technique:  it traps the exceptions raised when trying
> to apply json_array_length() to a non-array and json_each() to a non-object!
> I couldn't figure out a robust technique to further determine the type of a
> scalar value (int, float, text, or boolean) so this function simply returns
> one of 'array', 'object', or 'scalar'.
>
>   CREATE FUNCTION json_typeof(json_value JSON)
>   RETURNS TEXT AS $$
>   BEGIN
>     BEGIN
>       PERFORM json_array_length(json_value);
>       RETURN 'array';
>     EXCEPTION WHEN invalid_parameter_value THEN NULL;
>     END;
>     BEGIN
>       PERFORM json_each(json_value);
>       RETURN 'object';
>     EXCEPTION WHEN invalid_parameter_value THEN NULL;
>     END;
>     RETURN 'scalar';
>   END;
>   $$ LANGUAGE plpgsql STRICT IMMUTABLE;
>
> With this function, it's fairly simple to define a domain which only permits
> an array or an object as the top-level element:
>
>   CREATE DOMAIN json_document CHECK (json_typeof(VALUE) IN ('array',
> 'object'));
>
> I feel that the json_typeof function is worthy of being a built-in function.
> Thoughts?

no argument against json_typeof, but why can you just peek the first
non-whitespace character?  json  is famously easy to parse (see here:
http://www.json.org/)

create or replace function json_typeof(_json json) returns text as
$$
  select case substring(ltrim($1::text), 1, 1)
    when '[' then 'array'
    when '{' then 'object'
  end;
$$ language sql immutable;

you could expand this mechanic fairly easy to cover all json types.
note exception handlers are very heavy for this type of operation.

merlin