SQL - JSON_OBJECT() in SQL

JSON_OBJECT() is a SQL function used to create a JSON object from key-value pairs. It is useful when you want to generate JSON data from relational table columns.

Syntax                                                 

JSON_OBJECT(key1, value1, key2, value2, ...)

• key – the name of the JSON field (as a string)
• value – the value corresponding to the key
• Returns a valid JSON object

Example Table

CREATE TABLE users (

  id INT,

  name VARCHAR(50),

  age INT

);

INSERT INTO users VALUES

(1, 'Ashok', 22),

(2, 'Rohan', 25);

Create JSON Object from Table

SELECT JSON_OBJECT('user_id', id, 'user_name', name, 'user_age', age) AS user_json

FROM users;

Result:

user_json

------------------------------------

{"user_id": 1, "user_name": "Ashok", "user_age": 22}

{"user_id": 2, "user_name": "Rohan", "user_age": 25}

Key Points

• Creates JSON objects on-the-fly
• Keys are strings, values can be numbers, strings, or JSON
• Can be combined with other functions like JSON_ARRAY()
• Useful for APIs or JSON output from SQL queries