JSON_OBJECT

MySQL

JSON_OBJECT([key, value [, key, value] …])

• key: The key part of a key-value pair. It is a string value. Each key must be unique in a JSON object.
• value: The value associated with a key in a key-value pair. This could be any valid JSON value. If a non-JSON value is provided, the function will attempt to convert it into JSON.
• [, key, value]: Optional. Additional key-value pairs to include in the JSON object. Each key-value pair must be separated by a comma. Pairs are included in the object in the order they are provided.

Example

SELECT JSON_OBJECT('id', 1, 'name', 'John Doe', 'isAlive', true, 'age', 30);

Output

{"id": 1, "name": "John Doe", "isAlive": true, "age": 30}

Explanation

In the provided example, JSON_OBJECT() is used to create a JSON object. The function accepts an alternating series of keys and values: ‘id’, 1, ‘name’, ‘John Doe’, ‘isAlive’, true, ‘age’, 30. The output is a well-formatted JSON object with these key-value pairs.

PostgreSQL

JSON_OBJECT(keys text[] [, values text[]]) returns jsonb

• keys text[]: This parameter refers to an array of text elements serving as keys for the resultant jsonb object. Each element in ‘keys’ array will become a property in the jsonb object.
• values text[]: Corresponding to the ‘keys’ array, this parameter represents an array of text elements which will serve as values in the resultant jsonb object. Each ‘value’ matches with a ‘key’ to represent a (key, value) pair within the jsonb object. If this parameter is omitted, null values are assigned to all keys.

Example

SELECT
  JSON_OBJECT(
    ARRAY['foo', 'bar'],
    ARRAY['Hello World!', 123]
  )
AS example;

Output

{
 "foo" : "Hello World!",
 "bar" : 123
}

Explanation

The JSON_OBJECT function in PostgreSQL takes two arrays as arguments. It pairs each corresponding index between the arrays to create a JSON object.

In this example, ‘foo’ and ‘bar’ from the first array are used as the keys. ‘Hello World!’ and 123 from the second array are their corresponding values. This creates the JSON object { "foo": "Hello World!", "bar": 123 }.

Oracle

JSON_OBJECT( { key : value [ FORMAT JSON ] } [{, key : value [ FORMAT JSON ] }]…)

• key: A string used as the name in the name-value pairs for JSON object.
• value: The pair for the key in the name-value pair. It can be any valid SQL expression.
• format json: Optional clause denoting that the data type of the value is already in the JSON format, and should not be converted to the JSON format during the execution of the JSON_OBJECT function.

Example

SELECT JSON_OBJECT('Employee ID', employee_id, 'First Name', first_name, 'Last Name', last_name)
FROM employees
WHERE employee_id = 100;

Output

{ "Employee ID" : 100, "First Name" : "Steven", "Last Name" : "King" }

Explanation

The JSON_OBJECT function in Oracle is used to create JSON objects. This function takes a list of key-value pairs and returns a JSON object that contains those pairs. In the provided example, the keys are ‘Employee ID’, ‘First Name’, and ‘Last Name’; and their corresponding values are columns employee_id, first_name, last_name from employees table where employee_id is 100. The output is a JSON object with the employee’s id, first name and last name.