PostgreSQL's JSON array data type stores ordered collections in JSON format and is similar to standard arrays in programming. Postgres offers various built-in JSON functions for efficient data manipulation, such as ARRAY_TO_JSON(), JSON_BUILD_ARRAY(), JSON_ARRAY_LENGTH(), and more.
This post will demonstrate the basic syntax and several use cases of the JSON_BUILD_ARRAY() function in Postgres.
How to Use JSON_BUILD_ARRAY() Function in PostgreSQL?
The JSON_BUILD_ARRAY() is a built-in JSON function that creates and retrieves a JSON array of different types from a variadic parameter list.
Syntax
Use the below-stated syntax to build a new JSON array from the specified values:
JSON_BUILD_ARRAY(VARIADIC values);
Parameters
The stated function accepts a VARIADIC argument list. It can accept n number of parameters of any data type.
Return Value
It retrieves a heterogeneous JSON array.
Example 1: Using JSON_BUILD_ARRAY()
This example demonstrates the usage of the PostgreSQL JSON_BUILD_ARRAY() function to create a new array from the given values:
SELECT JSON_BUILD_ARRAY(100, 'Johnson', FALSE, row(20, 'h', TRUE), 272);
Here in the above snippet:
- Five parameters/values are passed to the JSON_BUILD_ARRAY() function.
- The stated function converts the provided parameters into JSON values. For this purpose, the JSON_BUILD_ARRAY() function internally utilizes the TO_JSON() function.
- TO_JSON(100) retrieve 100.
- TO_JSON(Johnson) retrieves Johnson.
- TO_JSON(FALSE) retrieves FALSE.
- TO_JSON(row(20, 'h', TRUE)) retrieve {"f1":20,"f2":"h","f3":true}.
- TO_JSON(272) retrieve 272.
- After that, the JSON_BUILD_ARRAY() function joins the parameter values and retrieves them as an ARRAY:
The output snippet demonstrates that a JSON array has been created from the given values.
Conclusion
The JSON_BUILD_ARRAY() is a built-in JSON function that creates and retrieves a JSON array of different types from a variadic parameter list. The stated function accepts a VARIADIC argument list. It can accept n number of parameters of any data type. It retrieves a heterogeneous JSON array. This write-up has demonstrated the working of the JSON_BUILD_ARRAY() function in PostgreSQL.
