JSON_OBJECT_KEYS

Return the top-level keys of a JSON object as a set of text values.

Category: jsonReturns: SETOF STRINGDialect: PostgreSql

Syntax

JSON_OBJECT_KEYS(json)

Description

## Overview Returns the top-level keys of a JSON object as a set of TEXT values, one row per key. As a set-returning function it is typically used in the FROM clause or with LATERAL to expand a JSON document into rows for downstream joins, counts, or pivots. Only the outermost object's keys are emitted; nested object keys are not traversed. For the binary-JSON variant, use JSONB_OBJECT_KEYS. ## Behavior - Returns one TEXT row per top-level key. - Returns zero rows (empty set) for an empty object ({}). - Returns NULL for NULL input. - Raises an error if the input is valid JSON but is not an object. - Key order matches the textual order of keys in the input document. - Does not recurse into nested objects or arrays. ## Compatibility - Matches the set-returning json_object_keys convention in widely adopted SQL implementations. - Parsing follows RFC 8259.

Parameters

Name	Type	Description
`json`		Specifies a JSON object whose top-level keys are returned one per row. Returns NULL for NULL input. Raises an error if the value is valid JSON but is not an object.

Examples

-- Return keys from a literal object
SELECT JSON_OBJECT_KEYS('{"name": "Alice", "age": 30, "active": true}');

-- Only top-level keys are listed; nested object keys are not flattened
SELECT JSON_OBJECT_KEYS('{"user": {"id": 1}, "role": "admin"}');

-- Use in the FROM clause as a set-returning function
SELECT k
FROM JSON_OBJECT_KEYS('{"a": 1, "b": 2, "c": 3}') AS k;

-- Realistic: inspect the top-level shape of variable payloads
SELECT event_id, JSON_OBJECT_KEYS(payload) AS key
FROM analytics.telemetry.events
WHERE event_date = DATE '2026-04-19';

-- Combine with COUNT to measure key diversity per row
SELECT event_id, COUNT(*) AS top_level_key_count
FROM analytics.telemetry.events, LATERAL JSON_OBJECT_KEYS(payload)
GROUP BY event_id;

Pitfalls

Only top-level keys are emitted; nested object keys are not flattened. Use a recursive CTE or repeated applications for deep key extraction.
Raises an error on non-object JSON (arrays, scalars). Guard with JSON_TYPEOF = 'object' when rows may have mixed shapes.
This is a set-returning function. Using it in a SELECT list without LATERAL produces cartesian-like expansion that can surprise downstream joins.
An empty object yields zero rows, which differs from NULL input (also NULL). Handle the two cases separately if they carry different meaning.
Key order from the input is preserved, but downstream JSON parsers may re-order keys. Do not rely on order for equality comparisons.