jsonb_build_array in PostgreSQL: Build JSON Arrays from Mixed-Type Values

jsonb_build_array is a PostgreSQL function that assembles a JSON array from the arguments you list, and each argument can be a different type. Unlike a regular SQL array, where every element must share one type, here numbers, strings, dates, booleans, and nested objects can sit side by side.

Basic syntax

You pass any number of arguments, and the function returns a jsonb value with element order preserved.

SELECT jsonb_build_array(1, 'two', true, NULL);
-- [1, "two", true, null]

The key difference from a PostgreSQL array is heterogeneity. The call below is impossible with ARRAY[...] because the elements have different types:

SELECT jsonb_build_array(id, name, salary, dept)
FROM employees
WHERE id = 42;
-- [42, "Ada", 95000.00, "engineering"]

Each argument goes through the usual JSON casting: numbers stay numbers, text becomes strings, boolean becomes true/false, NULL becomes JSON null. A date or timestamp turns into an ISO-formatted string.

Tuples and table rows as JSON

jsonb_build_array shines when you want a fixed-shape "tuple" — a positional array rather than a keyed object. It is more compact than an object and fits tabular data where the client already knows the column order.

SELECT jsonb_agg(
         jsonb_build_array(id, email, country, created_at)
       ) AS rows
FROM users
WHERE country = 'PT';
-- [[1,"a@x.io","PT","2024-01-10T00:00:00"], [2,"b@x.io","PT", ...]]

Here jsonb_build_array forms one tuple per row, and jsonb_agg folds every row into an array of arrays — a compact dump of the result set.

Nesting with jsonb_build_object

The real power appears when you mix it with jsonb_build_object. An array can hold objects, and objects can hold arrays; nesting has no fixed limit.

SELECT jsonb_build_object(
         'user_id', u.id,
         'recent_orders',
         (SELECT jsonb_agg(
                   jsonb_build_array(o.id, o.amount, o.status)
                 )
          FROM orders o
          WHERE o.user_id = u.id)
       ) AS payload
FROM users u
WHERE u.id = 7;
-- {"user_id": 7, "recent_orders": [[100, 49.90, "paid"], [101, 12.00, "pending"]]}

The inner array is a positional order tuple, and the outer object gives it readable keys. This is how you build API responses without an intermediate assembly step in the application.

The difference from to_jsonb of an array

A common point of confusion is jsonb_build_array(a, b, c) versus to_jsonb(ARRAY[a, b, c]). The results look similar at first glance, but the semantics differ.

• jsonb_build_array takes mixed-type arguments and keeps their native JSON types.
• to_jsonb of a SQL array requires the array to be single-typed and converts it as a whole.

-- Works: mixed types
SELECT jsonb_build_array(1, 'x', true);   -- [1, "x", true]

-- Error: ARRAY needs a common type
SELECT to_jsonb(ARRAY[1, 'x', true]);     -- ERROR: cannot mix types

-- Works: single-typed array
SELECT to_jsonb(ARRAY[1, 2, 3]);          -- [1, 2, 3]

Gotcha: PostgreSQL will try to coerce literals in ARRAY[1, 'x'] to a common type and fail with a type-mismatch error. When you truly need a set of differently typed values, reach for jsonb_build_array, and keep to_jsonb for an already-homogeneous array or a table row (to_jsonb(t.*)).

One more NULL subtlety: in jsonb_build_array, a SQL NULL becomes JSON null and stays in the array rather than being dropped. If you need to remove empty values, filter them earlier in a subquery.

Other databases

• MySQL uses JSON_ARRAY(...) — a direct equivalent with the same mixed-argument idea. For objects there is JSON_OBJECT(...).
• ClickHouse historically treats JSON differently: there is a JSON type and functions like toJSONString, but no positional "Postgres-style" builder — tuples are usually assembled via tuple() and then serialized.

If your code moves between engines, keep the JSON-shaping layer in one place — the syntax differs enough that it pays off.