Command guide

advanced json 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.

5/5 supported engines validation-green 1 example 2 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

CREATE TABLE users (id INT, name VARCHAR(50), role VARCHAR(20));

INSERT INTO
  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
ORDER BY
  id;

Expected result

id	profile
1	{"name": "Alice", "role": "admin"}
2	{"name": "Bob", "role": "viewer"}

SQL Server

Engine-specific syntax

Source table data

Setup

CREATE TABLE users (id INT, name VARCHAR(50), role VARCHAR(20));

INSERT INTO
  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 FOR JSON PATH,
      WITHOUT_ARRAY_WRAPPER
  ) AS profile
FROM
  users u
ORDER BY
  id;

Expected result

id	profile
1	{"name":"Alice","role":"admin"}
2	{"name":"Bob","role":"viewer"}

PostgreSQL

Engine-specific syntax

Source table data

Setup

CREATE TABLE users (id INT, name VARCHAR(50), role VARCHAR(20));

INSERT INTO
  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
ORDER BY
  id;

Expected result

id	profile
1	{"name" : "Alice", "role" : "admin"}
2	{"name" : "Bob", "role" : "viewer"}

SQLite

Engine-specific syntax

Source table data

Setup

CREATE TABLE users (id INT, name VARCHAR(50), role VARCHAR(20));

INSERT INTO
  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
ORDER BY
  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.

Build A JSON Object From Column Values

Construct a JSON object for each user from name and role columns

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.