json_typeof()

A function returning the type of a json value as a string

json_typeof() is a system function returning the type of the top-level json value as a text string.

json_typeof() was added in PostgreSQL 9.4.

Usage

json_typeof ( json ) → text

One of the following values will be returned:

  • object
  • array
  • string
  • number
  • boolean
  • null

Note that these values, particularly in the case of null, are not direct equivalents of the corresponding SQL data types.

Change history

Examples

Using json_typeof() to determine the type of various json values:

postgres=# SELECT json_typeof('{"foo":[]}'::json);
 json_typeof 
-------------
 object
(1 row)

postgres=# SELECT json_typeof('[]'::json);
 json_typeof 
-------------
 array
(1 row)

postgres=# SELECT json_typeof('"foo"'::json);
 json_typeof 
-------------
 string
(1 row)

postgres=# SELECT json_typeof('123'::json);
 json_typeof 
-------------
 number
(1 row)

postgres=# SELECT json_typeof('false'::json);
 json_typeof 
-------------
 boolean
(1 row)

postgres=# SELECT json_typeof('null'::json);
 json_typeof 
-------------
 null
(1 row)

Note that any numeric value maps to a number type:

postgres=# SELECT json_typeof(123::int::text::json);
 json_typeof 
-------------
 number
(1 row)

postgres=# SELECT json_typeof(1.23::numeric::text::json);
 json_typeof 
-------------
 number
(1 row)

A json null is not equivalent to an SQL NULL:

postgres=# SELECT json_typeof('null'::json),
                  json_typeof(NULL::json) IS NULL;
 json_typeof | ?column? 
-------------+----------
 null        | t
(1 row)

Categories

JSON, System function

See also

jsonb_typeof()