Skip to content
Tutorials Center
My learning

Topics

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.

SQL
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

SQL One JSON object per product ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL Nesting, NULLs and real booleans ยท database: shop

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

SQL
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.
SQL
-- 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 jsonb and MySQL's JSON type 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 a text value is passed.
  • NULL stays null. To omit a key when a value is NULL, filter or use the dialect's ABSENT ON NULL option.
  • 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.