to_jsonb in PostgreSQL: Turn a Whole Row into a JSON Object

When you need to expose a table row as JSON or assemble a nested structure for an API, PostgreSQL gives you to_jsonb. It takes any SQL value, row or array and turns it into jsonb without losing types. Let's see how to use it and how it compares to json_build_object and row_to_json.

What to_jsonb does

to_jsonb(value) converts a single value into jsonb. Scalars become JSON scalars, arrays become JSON arrays, and a table row becomes a JSON object whose keys are the column names:

-- Scalars and arrays keep their natural JSON shape
SELECT to_jsonb(42)                AS num,
       to_jsonb('hello'::text)     AS str,
       to_jsonb(ARRAY[1, 2, 3])    AS arr,
       to_jsonb(true)              AS flag;

The most useful trick is passing a whole row. A table alias inside to_jsonb refers to the entire row:

-- A whole row becomes one JSON object: column -> value
SELECT to_jsonb(u) AS user_json
FROM users u
WHERE u.country = 'DE';

Each row comes out like {"id": 7, "email": "a@b.de", "name": "Anna", "country": "DE", "created_at": "2026-01-10T08:00:00"}. Notice that created_at is serialized to an ISO string, numbers stay numbers, and NULL becomes JSON null.

Types are preserved, and that matters

to_jsonb respects the source types. An integer stays a number, not a string; a boolean stays a real true/false. That sets it apart from hand-built string concatenation:

SELECT jsonb_typeof(to_jsonb(o.amount))  AS amount_kind,   -- number
       jsonb_typeof(to_jsonb(o.status))  AS status_kind,   -- string
       jsonb_typeof(to_jsonb(NULL::int)) AS null_kind       -- null
FROM orders o
LIMIT 1;

• integer and numeric become JSON numbers.
• text, varchar, date, timestamp are serialized as strings.
• boolean stays boolean.
• PostgreSQL arrays become JSON arrays, nesting preserved.

Gotcha: a timestamp without time zone is converted with no Z suffix, while timestamptz includes an offset. If the frontend expects strict UTC, cast the column to timestamptz first.

Nested objects and picking columns

Often you don't want the entire row -- you want to select fields or nest related data. There are two paths. First, build the object from the row and strip unwanted keys with -:

-- Drop sensitive or noisy keys from the row object
SELECT to_jsonb(u) - 'created_at' - 'id' AS public_user
FROM users u;

The second, far more common in practice, is nesting a user's orders as an array. jsonb_agg aggregates order rows, each turned into an object via to_jsonb:

-- Nest each user's orders as an array of JSON objects
SELECT to_jsonb(u) || jsonb_build_object(
         'orders',
         (SELECT jsonb_agg(to_jsonb(o))
          FROM orders o
          WHERE o.user_id = u.id)
       ) AS user_with_orders
FROM users u;

The || operator merges two jsonb objects, adding the orders key to the user object. This is how you build a tree of any depth.

to_jsonb vs json_build_object

When you need an explicit shape -- different key names, computed fields, a fixed order -- reach for json_build_object (or jsonb_build_object). You list key-value pairs by hand:

-- Explicit shape: rename keys and add a computed field
SELECT jsonb_build_object(
         'user_id',   u.id,
         'label',     u.name || ' <' || u.email || '>',
         'is_local',  u.country = 'DE'
       ) AS card
FROM users u;

How to choose:

• to_jsonb(row) -- when you want the object as-is, with column names. Minimal code, the shape follows the schema.
• jsonb_build_object(...) -- when you need an exact shape: renaming, computation, field selection, stable key order.

They are often combined: build the base with to_jsonb, then layer computed keys on top with || and jsonb_build_object.

to_jsonb vs row_to_json

The older row_to_json(row) does nearly the same, but returns type json, not jsonb. The difference is fundamental. The accessor operators ->, ->>, #> and #>> work on both json and jsonb, so reaching into a key is not what sets them apart. What json cannot do is the rest of the toolbox:

• json stores the text verbatim: it preserves key order, duplicates and whitespace, but does not support the jsonb-only -, ||, @> operators or GIN indexing.
• jsonb stores a parsed binary form: key order is lost, duplicates collapse, but you get those extra operators and fast indexed lookups.

-- Both json and jsonb support -> ; the jsonb-only operators are -, ||, @>
SELECT row_to_json(e)            AS as_json,
       to_jsonb(e)               AS as_jsonb,
       to_jsonb(e) -> 'salary'   AS salary_node   -- -> works on json too
FROM employees e
WHERE e.dept = 'eng';

Practical rule: for new code reach for to_jsonb, because jsonb can be manipulated with the extra operators, indexed with GIN, and is faster to query. Keep row_to_json for cases where exact textual output matters.

In MySQL the closest analog is JSON_OBJECT('k', v, ...), similar to jsonb_build_object; there is no direct "whole row to JSON", so you list keys by hand. In ClickHouse the JSON model differs: it has a JSON type and a toJSONString function, but the "row as object" semantics differ, so queries won't port one to one.