SQL Reference ยท JSON and Special Features
SQL json_object() and json_array() functions
Reference for building JSON in SQL with json_object() and json_array(): arguments, NULL and boolean handling, nesting, and PostgreSQL/MySQL equivalents.
json_object() builds a JSON object from alternating keys and values, and json_array() builds a JSON array from a list of values. They let a query return nested, API-ready JSON directly from table columns instead of leaving that assembly to application code.
Both are scalar functions: they produce one JSON value per row. To collapse many rows into one array or object, use the aggregate versions described in JSON aggregates.
SELECT json_object('id', 7, 'name', 'Lamp') AS obj, json_array(1, 'two', NULL) AS arr; -- {"id":7,"name":"Lamp"} [1,"two",null]
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
The second column keeps the NULL as JSON null. '[1,2]' passed as plain text becomes a JSON string, while json('[1,2]') is embedded as a real array. A comparison like 1 = 1 produces the integer 1; wrap json('true') to get a JSON boolean.
Syntax
json_object([key1, value1 [, key2, value2 ...]]) json_array([value1 [, value2 ...]])
| Part | Description |
|---|---|
key |
Must be text. A non-text key raises an error ("json_object() labels must be TEXT"). |
value |
Any SQL value. Numbers stay numbers, text becomes a JSON string, NULL becomes null. A value produced by another JSON function is embedded as JSON. |
Returns: JSON text. With no arguments: {} or []. An odd number of arguments to json_object() is an error.
Because json_object() takes values positionally, adding or reordering columns is a matter of editing the argument list; it does not check that keys are unique.
Dialect differences
| Database | Object | Array | Notes |
|---|---|---|---|
| SQLite | json_object(k, v, ...) |
json_array(v, ...) |
Booleans are integers unless wrapped in json('true'). |
| PostgreSQL | jsonb_build_object(k, v, ...), json_build_object |
jsonb_build_array(...) |
Real boolean type; to_jsonb(row) converts a whole row. |
| MySQL | JSON_OBJECT(k, v, ...) |
JSON_ARRAY(v, ...) |
Keys must be strings; a NULL key is an error. |
| SQL Server | JSON_OBJECT('k': v, ...) (2022+) |
JSON_ARRAY(v, ...) (2022+) |
Older versions use FOR JSON PATH. |
| Oracle | JSON_OBJECT('k' VALUE v, ...) |
JSON_ARRAY(v, ...) |
NULL ON NULL or ABSENT ON NULL clauses control nulls. |
-- PostgreSQL SELECT jsonb_build_object('id', id, 'name', name) FROM products; -- MySQL SELECT JSON_OBJECT('id', id, 'name', name) FROM products; -- SQL Server 2022+ SELECT JSON_OBJECT('id': id, 'name': name) FROM products; -- Oracle SELECT JSON_OBJECT('id' VALUE id, 'name' VALUE name) FROM products;
Usage notes
- Key order is the order you write the arguments (SQLite keeps it; PostgreSQL's
jsonband MySQL'sJSONtype normalize storage and reorder keys, so do not depend on key order). - Text that looks like JSON is still text. Embed existing JSON with
json(...)in SQLite; MySQL and PostgreSQL have real JSON types, so the problem appears when atextvalue is passed. NULLstaysnull. To omit a key when a value isNULL, filter or use the dialect'sABSENT ON NULLoption.- Dates have no JSON type: they are written as strings, so format them (ISO 8601 is best).
- Large numbers. JSON numbers read by JavaScript lose precision above 2^53; send 64-bit ids as strings if clients are JavaScript.
- Combine with aggregates (
json_group_array(json_object(...))) for a full response in one query.