JSON_TYPEOF

Return the type of the outermost JSON value as text.

Category: jsonReturns: STRINGDialect: PostgreSql

Syntax

JSON_TYPEOF(json)

Description

## Overview Returns a TEXT value identifying the type of the outermost JSON node. Possible values are 'object', 'array', 'string', 'number', 'boolean', and 'null'. Use this function to branch on shape before applying functions that require a specific type (such as JSON_ARRAY_LENGTH or JSON_OBJECT_KEYS). The distinction between SQL NULL and JSON null is important: SQL NULL input returns SQL NULL, while a document containing JSON null returns the three-character TEXT 'null'. ## Behavior - Returns one of 'object', 'array', 'string', 'number', 'boolean', 'null'. - Returns SQL NULL for SQL NULL input. - Returns the TEXT 'null' for a JSON null input value. - Only the outermost node is inspected; nested types are not reported. - Raises an error on unparseable JSON. ## Compatibility - Values and semantics match the json_typeof convention in widely adopted SQL implementations. - JSON parsing conforms to RFC 8259.

Parameters

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

Examples

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

-- Identify an array
SELECT JSON_TYPEOF('[1, 2, 3]') AS t;

-- Identify a string
SELECT JSON_TYPEOF('"hello"') AS t;

-- Identify a number
SELECT JSON_TYPEOF('42') AS t;

-- JSON null returns the text 'null', not SQL NULL
SELECT JSON_TYPEOF('null') AS t;

-- Guard extraction by shape on a realistic table
SELECT event_id,
       CASE WHEN JSON_TYPEOF(payload) = 'object' THEN JSON_EXTRACT_PATH(payload, 'user', 'id') END AS user_id
FROM analytics.telemetry.events;

Pitfalls

SQL NULL input returns SQL NULL, but JSON null returns the TEXT 'null'. Test both when the column may contain either.
Only the top-level type is reported. A JSON object whose only key is an array still returns 'object'.
Parse errors raise an exception. Pre-filter or wrap when data quality is mixed.
Comparison against a bare string literal like = 'object' is case-sensitive and space-sensitive; the function always returns lowercase with no whitespace.
Number is a single type; it does not distinguish integer, decimal, or float. Use regex or CAST to further classify numeric values.