JSONB_TYPEOF

Return the type of the outermost JSONB value as text.

Category: jsonReturns: STRINGDialect: PostgreSql

Syntax

JSONB_TYPEOF(jsonb)

Description

## Overview Returns a TEXT value identifying the type of the outermost JSONB node. Possible values are 'object', 'array', 'string', 'number', 'boolean', and 'null'. Use this function to branch on shape before calling functions that require a specific type (such as JSONB_ARRAY_LENGTH, JSONB_OBJECT_KEYS). As with JSON_TYPEOF, SQL NULL and JSONB null are distinct cases: SQL NULL returns SQL NULL, while JSONB null returns the TEXT 'null'. ## Behavior - Returns one of 'object', 'array', 'string', 'number', 'boolean', 'null'. - Returns SQL NULL for SQL NULL input. - Returns TEXT 'null' for JSONB null input. - Only the outermost node is inspected. - Does not raise on well-formed JSONB; JSONB is always valid by construction. ## Compatibility - Values and semantics match the jsonb_typeof convention in widely adopted SQL implementations.

Parameters

Name	Type	Description
`jsonb`		Specifies the JSONB value whose outermost type is returned. SQL NULL input returns SQL NULL; a JSONB null input returns the TEXT value 'null'.

Examples

-- Identify an object
SELECT JSONB_TYPEOF('{"a": 1}'::JSONB) AS t;

-- Identify an array
SELECT JSONB_TYPEOF('[1, 2]'::JSONB) AS t;

-- Identify a string
SELECT JSONB_TYPEOF('"hello"'::JSONB) AS t;

-- Identify a number
SELECT JSONB_TYPEOF('42'::JSONB) AS t;

-- JSONB null returns the text 'null'
SELECT JSONB_TYPEOF('null'::JSONB) AS t;

-- Guard shape-dependent operations in a realistic query
SELECT event_id,
       CASE WHEN JSONB_TYPEOF(payload) = 'array' THEN JSONB_ARRAY_LENGTH(payload) ELSE 0 END AS items
FROM analytics.telemetry.events;

Pitfalls

SQL NULL returns SQL NULL, but JSONB null returns TEXT 'null'. Test both.
Only the top-level type is reported; nested types are not exposed.
Comparisons are case-sensitive; the function always returns lowercase.
Number is a single type; it does not distinguish integer, decimal, or float. Use CAST or regex to subclassify.
This function does not raise errors, so it cannot be used to detect malformed JSON; use JSON_TYPEOF on the source STRING for that.