In PostgreSQL, JSON_BUILD_OBJECT() is a built-in function that creates a JSON object using a list of key-value pairs provided as arguments. Each argument in the list is evaluated, with keys converted to text and values converted into JSONB values using the TO_JSONB() function. However, the specified number of arguments must be even; otherwise, PostgreSQL will throw an error.
This post will demonstrate the basic syntax and several use cases of the JSON_BUILD_OBJECT() function in Postgres.
How Does the JSON_BUILD_OBJECT() Function Work in PostgreSQL?
The JSON_BUILD_OBJECT() function is used to create and retrieve a JSON object from a variadic parameter list of key-value pairs. Use the below-stated syntax to build a new JSON Object from the specified values:
JSON_BUILD_OBJECT(VARIADIC values);
It accepts mandatory argument “values” that can be of any data type.
Example 1: Using JSON_BUILD_OBJECT()
This example demonstrates the usage of the PostgreSQL JSON_BUILD_OBJECT() function to create a new JSON object from the given values:
SELECT JSON_BUILD_OBJECT(100, 'Johnson', FALSE, row(20, 'h', TRUE), 'Num', 117);
Here in the above snippet:
- Six parameters/values are passed to the JSON_BUILD_OBJECT() function.
- Here, “100”, “FALSE”, and “Num” are the keys, and “Johnson”, “row(20, 'h', TRUE)”, and “117” are their respective values.
The stated function converts the provided parameters into keys and values as follows:
- 100 represents a key, which is transformed into 100.
- Johnson is the corresponding value of the key 100, which is converted into 100.
- FALSE represents a key, which is converted into FALSE.
- “row(20, 'h', TRUE)” represents the value of the key FALSE which is converted into {"f1":20,"f2":"h","f3":true}.
- Num indicates a key that is converted into “Num”.
- 117 is the corresponding value of the key “Num”.
- After that, the JSON_BUILD_OBJECT() function joins all the key-value pairs and retrieves them as a JSONB object:
Example 2: ERROR: Argument List Must Have Even Number of Elements
An error will occur if a user passes an odd number of arguments. The error message will indicate that the argument list must contain an even number of elements:
SELECT JSON_BUILD_OBJECT(100, 'Johnson', FALSE, row(20, 'h', TRUE), 'Num');
That’s all about using the JSON_BUILD_OBJECT() function in PostgreSQL.
Conclusion
The JSON_BUILD_OBJECT() function is used to create and retrieve a JSON object from a variadic parameter list of key-value pairs. Each argument in the list is evaluated, with keys converted to text and values converted into JSONB values using the TO_JSONB() function. This write-up has demonstrated the complete process of using the JSON_BUILD_OBJECT() function in PostgreSQL.
