JSONB_OBJECT_KEYS

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

Category: jsonReturns: SETOF STRINGDialect: PostgreSql

Syntax

JSONB_OBJECT_KEYS(jsonb)

Description

## Overview Returns the top-level keys of a JSONB 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 document into rows. Only the outermost object's keys are returned; nested object keys are not traversed. Use this function to audit document shape, enumerate attributes for pivoting, or count the number of attributes per row. For the text-JSON variant, use JSON_OBJECT_KEYS. ## Behavior - Returns one TEXT row per top-level key. - Returns zero rows for an empty object ({}). - Returns NULL for NULL input. - Raises an error if the input is valid JSONB but not an object. - JSONB canonical form determines key ordering; ordering is not guaranteed to match the original textual input. ## Compatibility - Matches the set-returning jsonb_object_keys convention in widely adopted SQL implementations. - JSONB canonical form is used.

Parameters

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

Examples

-- Keys from a JSONB object literal
SELECT JSONB_OBJECT_KEYS('{"name": "Alice", "age": 30}'::JSONB);

-- Only top-level keys are returned
SELECT JSONB_OBJECT_KEYS('{"user": {"id": 1}, "status": "active"}'::JSONB);

-- Used as a set-returning function in FROM
SELECT k
FROM JSONB_OBJECT_KEYS('{"x": 1, "y": 2, "z": 3}'::JSONB) AS k;

-- Realistic: enumerate keys per row for shape auditing
SELECT event_id, JSONB_OBJECT_KEYS(payload) AS key
FROM analytics.telemetry.events
WHERE event_date = DATE '2026-04-19';

-- Count top-level keys per document
SELECT event_id, COUNT(*) AS key_count
FROM analytics.telemetry.events, LATERAL JSONB_OBJECT_KEYS(payload) AS k
GROUP BY event_id;

Pitfalls

Only top-level keys are emitted. Use recursive CTEs or repeated applications for deep extraction.
Raises an error on non-object JSONB. Guard with JSONB_TYPEOF = 'object' when rows may contain mixed shapes.
This is a set-returning function. In a SELECT list without LATERAL it produces cartesian-like expansion, which may not be what you want.
Key order follows JSONB canonical form, not the input text order.
Empty object yields zero rows; NULL input yields NULL. Handle the two cases separately when they carry different meaning.