PostgreSQL offers many JSON functions like json_each(), json_each_text(), json_object(), json_populate_record(), etc. These functions are used to manipulate the data of JSON data type in some way. The primary/major focus of this particular post will be on the json_object() function. We will see how the json_object() function can be utilized in the Postgres queries.

How to Use json_object() Function in PostgreSQL?

The json_object() function is used to construct a JSON object using a text array or two arrays. The array/ arrays are provided to the json_object() function as arguments. Now there are two cases in terms of the arguments provided to the json_object() function. Let’s discuss them.

Case 1: Providing A Text Array to the json_object() Function

In the case of a single array provided to the json_object() function. The syntax of the json_object() function can be illustrated as:

json_object(key_val_array TEXT[])

In the above syntax:

● A single key-value array is provided to the json_object() function.

● The key_value array must have a data type of TEXT.

● The json_object() function converts this key-value text array into a JSON object. This means that the returned data type of this function is JSON.

In this case, the keys and values of the JSON object will be from the TEXT array provided to the json_object() function.

We can provide:

● One-dimensional TEXT array to the json_object() function. The count of elements in the provided array, in this case, must be even. The keys and values are declared as alternative entries to each other.

● Two-dimensional TEXT array to the json_object() function. Each inner array will be present in the key-value pairs.

Let’s go over the examples of these cases one by one.

Example 1: Using json_object() Function With Single One-Dimensional Text Array

To cover an example for this case, we will provide the json_object() function with a one-dimensional array. Consider the following query for this:

SELECT json_object('{Stud_name, Peter, stud_id, 82, grad_batch, 2023}');

We have provided a single one-dimensional array to the json_object() function i.e. {Stud_name, Peter, stud_id, 82, grad_batch, 2023}. The function json_object() will return a JSON object in a way that the keys and values are alternative elements. This means that the “stud_name” will be the key and its value will be “Peter”. Similarly “stud_id” will be the key and “82” will be its paired value and so on. We can see the output for this:

We can observe that the json_object() function has given the JSON object for the provided text array. The keys and values are alternative elements of the provided array.

This is how the json_object() function works.

If the data type of the provided array is not TEXT, the function will throw an error. So in this case we can typecast the array into a TEXT. For example, if we provide {'Stud_name', 'Peter', 'stud_id', 82, 'grad_batch', 2023} array to the json_object() function, we can see that this array is not a TEXT array(as it is not quoted by quotes), the function won’t execute. So we will have to typecast it into a TEXT array. The following query is written to do so.

SELECT json_object(
  ARRAY['Stud_name', 'Peter', 'stud_id', 82, 'grad_batch', 2023]::TEXT[]);

Now this query will work fine.

This proves that the json_object() function will only work when the array provided to it is a TEXT array.

Now, let’s see the impact of the two-dimensional single array on the function result.

Example 2: Using json_object() Function with Single Two-Dimensional Text Array

The function can also take a two-dimensional TEXT array as input. In this particular case, the inner array of the two-dimensional array will be acting as key-value pairs. We will consider the following query as an example to demonstrate this:

SELECT json_object('{{radius_1, 26}, {radius_2, 14}, {radius_3,6}}');

In this query, we have provided a two-dimensional array to the json_object() function. Now the inner arrays i.e {radius_1, 26} , {radius_2, 14}, {radius_3,6} are considered as key-value pairs. This means that for the first inner array, “radius_3” is the key, and “26” is the paired value and the same with all. The outcome of the above query is illustrated below.

The output clearly verifies that in the case of a 2-dimensional text array provided to the json_object() function, the elements of the inner array act like key-value pairs.

NOTE: Note that the elements of inner arrays have to be 2. Otherwise, the json_object() function will not work on that array and will not return anything.

That was all about providing a single text array to the json_object() function. We will next see what happens if we provide 2 arrays to the json_object() function.

Case 2: Providing Two Text Arrays to the json_object() Function

We can provide 2 one dimensional text arrays to the json_object() function. In this case, the first array will act like the keys array and the second array will act like a values array. Each key from the first array pairs with the corresponding value at the same place in the second text array. The syntax looks like this:

json_object(keys_arr TEXT[], val_arr TEXT[])

In this syntax:

● The function json_object() is provided with 2 arrays.

● The first array will always be the keys array.

● The second array will always be a values array.

● Both the arrays have to be TEXT arrays.

● The json_object() function will return the JSON object built from these two arrays.

● The return type of the json_object() function is JSON.

NOTE: here one thing is most important for the function to execute properly, that is the the element of both the arrays should be equal.

Consider the given examples for proper clarity of the concept.

Example 1: Using json_object() Function with Two Text Arrays

The following query is written as an example to understand how the query performs when we provide 2 text arrays to the json_object() function.

SELECT json_object('{Williams, Tim, Kate}', '{7,9,   6}' );

In such a case, the first array i.e. provided {Williams, Tim, Kate } will act as the keys array, and the second array i.e. {7,9, 6} acts as the array of values. So each element of the key array pairs with the corresponding given value in the values array. The above query returns the following output.

The output validates the concept we discussed. In this way, the json_object() function builds the JSON object from the provided TEXT arrays.

Additional Information: Alternative to json_object() Function

We can get the keys and values separated from the JSON object, which is returned by the json_object(), by using the json_each() or json_each_text() functions. The query can be written as:

SELECT * FROM json_each(json_object('{Williams, Tim, Kate}', '{7,9,   6}' )) ;

The query returns the following results.

We can clearly observe that all the keys and values are separated. In case we use the json_each_text() function only the difference will be in the returned data type of the values that will be TEXT.

Conclusion

The PostgreSQL json_object() function builds a JSON object from the provided TEXT array or arrays. The json_object() function takes a text array or two text arrays. In the case of a single text one-dimensional array, the alternative elements act as keys and values, the elements need to be even in number. In the case of a single-text two-dimensional array, the inner arrays will be the keys and value pairs. For the two arrays, the first array will be the keys array and the second will be the values array. Each element of the key array pairs with the corresponding element in the value array.