ROW_TO_JSON

Convert a row or record to a JSON object.

Category: jsonReturns: JSONDialect: PostgreSql

Syntax

ROW_TO_JSON(record)

Description

## Overview Converts a composite value (row or record) to a JSON object. Column names become JSON keys and column values are recursively converted using the same rules as TO_JSON. NULL column values become JSON null entries. Use ROW_TO_JSON to emit row-shaped JSON for exports or downstream consumers that expect document form. If the record has no named columns (for example a bare ROW(...) constructor), keys default to 'f1', 'f2', and so on. To get the JSONB variant, cast the result with ::JSONB or use TO_JSONB on the row. ## Behavior - Column names in the composite type are used as JSON keys. - Unnamed ROW constructors yield synthetic keys 'f1', 'f2', 'f3', ... - NULL column values become JSON null. - Nested composites and arrays are converted recursively. - The result type is JSON (text). Use a ::JSONB cast for the binary variant. - Column order in the output matches the composite's declared column order. ## Compatibility - Follows the SQL/JSON composite conversion rules in SQL:2016. - Output conforms to RFC 8259.

Parameters

Name	Type	Description
`record`		Specifies the row or record value to convert. Column names become JSON keys; column values are converted to their JSON representation using TO_JSON rules. NULL column values produce JSON null entries.

Examples

-- Convert a ROW constructor (anonymous columns become f1, f2, ...)
SELECT ROW_TO_JSON(ROW(1, 'Alice', true)) AS r;

-- Use a subquery alias to preserve column names
SELECT ROW_TO_JSON(t) AS r
FROM (SELECT 1 AS id, 'Alice' AS name, 30 AS age) t;

-- Convert rows from a realistic table
SELECT ROW_TO_JSON(t) AS user_doc
FROM (SELECT user_id, display_name, email FROM iam.directory.users LIMIT 5) t;

-- Embed a subquery result for nested JSON
SELECT ROW_TO_JSON(t) AS user_with_tags
FROM (
  SELECT u.user_id, u.display_name,
         (SELECT JSON_AGG(tag) FROM iam.directory.user_tags ut WHERE ut.user_id = u.user_id) AS tags
  FROM iam.directory.users u
) t;

-- Combine with JSONB cast to index or compare
SELECT ROW_TO_JSON(t)::JSONB AS user_doc_jsonb
FROM (SELECT user_id, display_name FROM iam.directory.users) t;

Pitfalls

Anonymous ROW() constructors produce keys 'f1', 'f2', ... Always use a subquery alias (t) or named columns to get meaningful keys.
NULL column values become JSON null; they are not omitted. Use JSON_STRIP_NULLS to drop null-valued keys after conversion.
Column order in the output follows the declared order, not alphabetical order. Consumers that rely on key order must be compatible.
Composite types from a table include all columns; use an explicit projection in the subquery to exclude columns.
The result is JSON, not JSONB. For indexed access or containment, cast to JSONB.