advancedjson MySQL MariaDB SQL Server PostgreSQL SQLite
Build A JSON Object From Column Values
Construct a JSON object literal from individual column values within a query, returning structured data without a separate serialization step.
Created Last updated 5/5 supported engines validation-green1 example2 scenarios
Docker-validated Not currently validation-green
Example 1
Construct a JSON object for each user from name and role columns
Each row produces a JSON object string containing name and role. The key names come from the string literals passed as alternating arguments in MySQL/MariaDB/PostgreSQL, or from the selected column names in the SQL Server subquery. SQL Server's FOR JSON PATH normally wraps output in a JSON array; WITHOUT_ARRAY_WRAPPER removes that outer bracket to yield a bare object string. The whitespace differences between engines are cosmetic — the JSON is semantically equivalent.
MySQL MariaDB
Engine-specific syntax
Source table data
Setup
CREATETABLE users (id INT, name VARCHAR(50), role VARCHAR(20));
INSERTINTO
users (id, name, role)
VALUES
(1, 'Alice', 'admin'),
(2, 'Bob', 'viewer');
Validated query
SQL
SELECT
id,
JSON_OBJECT ('name', name, 'role', role) AS profile
FROM
users
ORDERBY
id;
Expected result
id
profile
1
{"name": "Alice", "role": "admin"}
2
{"name": "Bob", "role": "viewer"}
SQL Server
Engine-specific syntax
Source table data
Setup
CREATETABLE users (id INT, name VARCHAR(50), role VARCHAR(20));
INSERTINTO
users (id, name, role)
VALUES
(1, 'Alice', 'admin'),
(2, 'Bob', 'viewer');
Validated query
SQL
SELECT
id,
(
SELECT
name,
role
FROM
users u2
WHERE
u2.id = u.id FORJSONPATH,
WITHOUT_ARRAY_WRAPPER
) AS profile
FROM
users u
ORDERBY
id;
Expected result
id
profile
1
{"name":"Alice","role":"admin"}
2
{"name":"Bob","role":"viewer"}
PostgreSQL
Engine-specific syntax
Source table data
Setup
CREATETABLE users (id INT, name VARCHAR(50), role VARCHAR(20));
INSERTINTO
users (id, name, role)
VALUES
(1, 'Alice', 'admin'),
(2, 'Bob', 'viewer');
Validated query
SQL
SELECT
id,
JSON_BUILD_OBJECT ('name', name, 'role', role) AS profile
FROM
users
ORDERBY
id;
Expected result
id
profile
1
{"name" : "Alice", "role" : "admin"}
2
{"name" : "Bob", "role" : "viewer"}
SQLite
Engine-specific syntax
Source table data
Setup
CREATETABLE users (id INT, name VARCHAR(50), role VARCHAR(20));
INSERTINTO
users (id, name, role)
VALUES
(1, 'Alice', 'admin'),
(2, 'Bob', 'viewer');
Validated query
SQL
SELECT
id,
json_object ('name', name, 'role', role) AS profile
FROM
users
ORDERBY
id;
Expected result
id
profile
1
{"name":"Alice","role":"admin"}
2
{"name":"Bob","role":"viewer"}
MySQL and MariaDB use JSON_OBJECT(); PostgreSQL uses JSON_BUILD_OBJECT(). SQL Server uses a correlated FOR JSON PATH, WITHOUT_ARRAY_WRAPPER subquery. Engines differ in whitespace: MySQL/MariaDB emit a space after the colon, PostgreSQL emits spaces around the colon, SQL Server emits no spaces. The JSON content is semantically identical.
Useful when
Where this command helps.
returning structured JSON rows from a query for direct consumption by an API layer
composing a JSON payload from split columns before inserting into a JSON column
Explanation
What the command is doing.
Building a JSON object inline lets a query return structured output for API responses or downstream processing without requiring application-level serialization. MySQL and MariaDB use JSON_OBJECT(key, value, ...), which accepts alternating key-value pairs and returns a JSON-typed value. PostgreSQL uses JSON_BUILD_OBJECT(key, value, ...) with the same calling convention. SQL Server has no equivalent scalar function; instead, FOR JSON PATH, WITHOUT_ARRAY_WRAPPER serializes the selected columns of a subquery row into a JSON object string.