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

On Wed, Jun 12, 2013 at 11:38 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

> 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.

Thanks for the suggestion -- hadn't thought of just looking at the first
non-whitespace character. It should be trivial to make this into an
efficient C function that could live in jsonfuncs.c as a built-in. I might
do that and submit a patch for 9.4 CF2.

Cheers,
-Andrew