In PostgreSQL, JSON data type plays a significant role in storing the data in key and value pairs. The JSONB is the extended data type that came from the JSON, which assists JSON in analyzing and storing the huge amount of JSON data in binary. There are many functions that are used to manipulate and interpret the JSON/JSONB data in Postgres.
For the scope of this article, we’ll cover one of the JSON/JSONB functions offered by Postgres that is, the jsonb_build_object() function. Let’s learn about this function together.
How to Use jsonb_build_object() Function in PostgreSQL?
The jsonb_build_object() function converts a heterogeneous list of parameters into the JSON object. The fundamental structure of the jsonb_build_object() function looks like the same as given below:
jsonb_build_object(parameter_list VARIADIC)
The syntax of jsonb_build_object() function says that:
● The function takes a list of heterogeneous parameters.
● The jsonb_build_object() function will give a JSONB object in return.
● The keys and values in the JSON object will be the alternative parameters provided in the function.
The jsonb_build_object() function assesses each provided parameter, the key arguments are forced to become TEXT. Whereas, on the values arguments, the to_jsonb() function is applied and the result is returned on the key-value pairs as a JSONB object.
Additional Note: The to_jsonb() just takes an SQL value and converts it into the JSONB format. For a detailed demonstration of the to_jsonb() function, we can head over to the dedicated article for the to_jsonb() function.
Important Points
Beyond the syntax of the jsonb_build_object() function, there are some important points for the implementation of this function, that is:
● The parameters passed in the jsonb_build_object() function should be even in number.
● The function forms alternative key-value pairs i.e. the first parameter will be the key and its very next element will be the value for it. The next element will again be a key and following it, its value will be given, and so on.
The jsonb_build_object() function is almost the same as the json_build_object() function in PostgreSQL, which performs the same function for the JSON data.
Let’s take the help of some examples, to better understand the working of the jsonb_build_object() function.
Example 1: Basic Working of the jsonb_build_object() Function
As stated before, the keys and values are alternatives to each other in the provided parameter list. The key values are simply converted to TEXT with no other change while the “value” in the returned JSONB object(by the jsonb_build_object() function) comes after implementing the to_jsonb() function on it. We will assess a simple example to understand it better.
SELECT jsonb_build_object( 37, now());
By executing this query, we will get:
We can note that the first value i.e. key is simply converted into TEXT format and the second parameter i.e. value comes after implementing the to_jsonb() function on it. We can also verify it by running the query for the to_jsonb() function on the ”value” parameter’s value i.e.
SELECT to_jsonb(now());
Hence, the working of the jsonb_build_object() function is verified by the above illustration. We can have more examples for the jsonb_build_object() function.
Example 2: Understanding the jsonb_build_object() Function
We will provide different parameters for how the jsonb_build_object() function works on it. We will examine the following query:
SELECT jsonb_build_object(185, 'ValString', false, now());
In the above query:
● As we have stated earlier, the keys and values are alternative parameters in the provided list. So, the ‘185’ will be key, and the “ValString” will be assessed as its value. Similarly, the “false” will be the key, and now() will be assessed as its value.
● The keys will simply be converted into the TEXT.
● The Values will be returned to the JSON object after applying the to_jsonb() function on it.
Let’s execute the above query to see what the output of the query is.
We can notice that the key parameters are simply converted into the TEXT. While the value parameters are obtained by the to_jsonb() function implemented on them. They are then returned as the JSONB objects by the jsonb_build_object() function. We will execute the query for the to_jsonb() function to see how it responds to these values.
SELECT to_jsonb('ValString'::text),to_jsonb(now());Upon executing this query we got:
If we carefully observe, we will be able to infer that the “values”, in the JSONB object, got their values after the to_jsonb() function is implemented on them.
Example 3: Arguments List Must be Even
The parameters provided to the jsonb_build_object() function need to be even in number to make the key and value pairs. If we provide an odd number of parameters, the jsonb_build_object() function will throw an error like this:
SELECT jsonb_build_object(185, 'ValString', false, now(),null);
We have provided 5 parameters in the above jsonb_build_object() function. The query will throw an error.
So, for the execution of the jsonb_build_object() function, it is necessary to provide an even number of parameters.
Example 4: Understanding the jsonb_build_object() Function With Arrays
We can also declare arrays in the jsonb_build_object() function. Let’s implement the function by providing a 2-dimensional array to it.
SELECT jsonb_build_object(100, ARRAY[[0,5],[4,7]]);
The execution of the above query gives:
Again the same concept is followed. The key parameters are returned as TEXT while the array returns the value after implementing the to_jsonb() function on it.
The arrays in the jsonb_build_object() function usually work fine. However, the array can be declared as the value parameter, not for the key. The key parameter does not have to be some array, JSON object, or any composite type. If we do so, an error will be thrown by the Postgres.
SELECT jsonb_build_object(ARRAY[[2,7],[4,9]], ARRAY[[0,5],[4,7]]);
In this query, we have specified an array as a key parameter now let’s observe what will it return.
The error is thrown in this case saying that the key parameter can not be an array.
Conclusion
The jsonb_build_object() function takes a heterogeneous list of parameters and converts them into JSON objects. The key and value parameters are alternatively present in the list. The JSON object is formed and returned in such a way that the key parameters are simply placed in the JSON object as TEXT but the value is returned after the to_jsonb() function is applied to them. In this blog, we have observed different use cases/examples for the jsonb_build_object() function.
