Skip to main content

json_object

1. JSON_OBJECT Function​

The JSON_OBJECT function in H2 database is used to create a JSON object with the specified key-value pairs.

2. Syntax​

The syntax for the JSON_OBJECT function in H2 database is as follows:

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

Arguments​

  • key1, key2, ...: The keys for the JSON object. These should be strings.
  • value1, value2, ...: The corresponding values associated with the keys. These can be any valid JSON values.

Return​

  • The JSON_OBJECT function returns a JSON object with the specified key-value pairs.

3. Notes​

  • The keys and values can be column names, constants, or expressions.
  • If a key or value is NULL, it will be converted to the JSON null value in the resulting object.
  • The JSON_OBJECT function can be used to construct complex JSON objects by nesting multiple JSON_OBJECT functions or combining with other JSON functions.
  • H2 database supports the JSON format, but it does not provide the full range of JSON functions and operators available in other databases such as PostgreSQL or MySQL.

4. Examples​

Here are a few examples demonstrating the usage of the JSON_OBJECT function in H2 database:

Example 1 - Creating a simple JSON object:​

SELECT JSON_OBJECT('name', 'John', 'age', 30) AS json_object;

Output:

json_object
-------------------------
{"name":"John","age":30}

Example 2 - Creating a JSON object with values from columns:​

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  department VARCHAR(100)
);

INSERT INTO employees VALUES (1, 'Alice', 'HR'), (2, 'Bob', 'Finance');

SELECT JSON_OBJECT('id', id, 'name', name, 'department', department) AS json_object
FROM employees;

Output:

json_object
----------------------------
{"id":1,"name":"Alice","department":"HR"}
{"id":2,"name":"Bob","department":"Finance"}
  • json_array - Create a JSON array with specified values.
  • JSON_VALUE - Extract a scalar value from a JSON object or array.
  • JSON_ARRAYAGG - Aggregate values into a JSON array.