jsonb_typeof()

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

jsonb_typeof() is a system function returning type of the top-level jsonb value as a text string

jsonb_typeof() was added in PostgreSQL 9.4.

Usage

json_typeof ( jsonb ) → 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 jsonb_typeof() to determine the type of various jsonb values:

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

postgres=# SELECT jsonb_typeof('[]'::jsonb);
 jsonb_typeof
--------------
 array
(1 row)

postgres=# SELECT jsonb_typeof('"foo"'::jsonb);
 jsonb_typeof
--------------
 string
(1 row)

postgres=# SELECT jsonb_typeof('123'::jsonb);
 jsonb_typeof
--------------
 number
(1 row)

postgres=# SELECT jsonb_typeof('false'::jsonb);
 jsonb_typeof
--------------
 boolean
(1 row)

postgres=# SELECT jsonb_typeof('null'::jsonb);
 jsonb_typeof
--------------
 null
(1 row)

Note that any numeric value maps to a number type:

postgres=# SELECT jsonb_typeof(123::int::text::jsonb);
 jsonb_typeof
--------------
 number
(1 row)

postgres=# SELECT jsonb_typeof(1.23::numeric::text::jsonb);
 jsonb_typeof
-------------
 number
(1 row)

A jsonb null is not equivalent to an SQL NULL:

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

Categories

JSON, System function

See also

json_typeof()