How to Use jsonb_to_record in PostgreSQL: Syntax and Examples

Use jsonb_to_record in PostgreSQL to convert a single JSONB object into a set of columns as a table row. You specify the expected output columns and their types inside AS clause, and then select from the function to extract JSON fields as columns.

Syntax

The jsonb_to_record function takes a single jsonb value and returns a set of columns defined by you. You must specify the output columns and their data types using AS with a column list.

Basic syntax:

jsonb_to_record(jsonb_value) AS alias(column_name data_type, ...)

Here:

• jsonb_value is the JSONB object you want to convert.
• alias is a table alias for the output.
• column_name data_type defines each column extracted from the JSONB object.

sql

SELECT * FROM jsonb_to_record('{"name": "Alice", "age": 30}'::jsonb) AS x(name text, age int);

Output

 name  | age 
-------+-----
 Alice |  30
(1 row)

Example

This example shows how to extract fields from a JSONB object into columns using jsonb_to_record. It converts the JSONB data into a table row with columns name and age.

sql

WITH data AS (
  SELECT '{"name": "Bob", "age": 25, "city": "NY"}'::jsonb AS info
)
SELECT *
FROM data,
jsonb_to_record(data.info) AS rec(name text, age int, city text);

Output

 name | age | city 
------+-----+------
 Bob  |  25 | NY
(1 row)

Common Pitfalls

• Incorrect column types: If the JSON value type does not match the declared column type, the query will error.
• Missing keys: If a key is missing in the JSON, the corresponding column will be NULL.
• Using jsonb_to_record with arrays: This function works only with single JSON objects, not arrays. Use jsonb_to_recordset for arrays of objects.

sql

SELECT * FROM jsonb_to_record('{"name": "Eve", "age": "not_a_number"}'::jsonb) AS x(name text, age int);
-- This will cause an error because "age" is not an integer.

-- Correct usage:
SELECT * FROM jsonb_to_record('{"name": "Eve", "age": 28}'::jsonb) AS x(name text, age int);

Output

ERROR:  invalid input syntax for integer: "not_a_number"

 name | age 
------+-----
 Eve  |  28
(1 row)

Quick Reference

Feature	Description
Input	A single JSONB object
Output	A table row with columns defined by you
Column Definition	Specify column names and types in AS clause
Use Case	Extract JSON fields as columns for querying
Limitation	Does not work with JSON arrays (use jsonb_to_recordset)

Key Takeaways

Use jsonb_to_record to convert a single JSONB object into table columns by specifying column names and types.

Ensure JSON keys match the column names and data types to avoid errors.

Missing JSON keys result in NULL values in the output columns.

For JSON arrays, use jsonb_to_recordset instead of jsonb_to_record.

Always cast your JSON string to jsonb before using jsonb_to_record.