PostgreSQL – JSON_AGG Function

ByAdmin

The json_agg function is used to aggregate values into a JSON array in PostgreSQL database. It takes one or more values and returns a JSON array that contains those values as elements. This function is particularly useful when you want to combine rows or values into a single JSON array in the result of a query.

Here is the basic syntax for the json_agg function:

json_agg(expression)

where expression is the value or column you want to aggregate into the JSON array.

Here’s an example of how to use the json_agg function to aggregate values from a table:

Consider a table named employees:

CREATE TABLE employees ( id serial PRIMARY KEY, name text, department text );

And it contains the following data:

| id | name | department | 
|----|--------|------------| 
| 1 | Alice | HR | 
| 2 | Bob | IT | 
| 3 | Carol | HR | 
| 4 | David | IT |

You can use the json_agg function to aggregate the names of employees in the “HR” department into a JSON array:

SELECT department, json_agg(name) AS employees 
FROM employees 
WHERE department = 'HR' 
GROUP BY department;

The result of this query will be:

| department | employees | 
|------------|------------------| 
| HR | ["Alice", "Carol"] |

In this example:

  • We selected the department and used json_agg(name) to aggregate the names of employees in the “HR” department into a JSON array.
  • The GROUP BY clause groups the results by department.

The json_agg function is useful for creating JSON arrays from query results, which can be particularly handy when working with JSON data in PostgreSQL.

Similar Posts

PostgreSQL – JSON_EXTRACT_PATH_TEXT Function

ByAdmin

The json_extract_path_text function is used to extract a specific value from a JSON object or array by specifying a path to the desired element. This function returns the value as text. The basic syntax of the json_extract_path_text function is as shown below: json_extract_path_text(json_object_or_array, path_element1, path_element2, …, path_elementN) Here’s an example of using the json_extract_path_text function:…

PostgreSQL – JSON_ARRAY_LENGTH Function

ByAdmin

The json_array_length function is used to determine the number of elements in a JSON array. It’s particularly useful when you’re working with JSON data and need to find out how many items are present in an array within a JSON document. The syntax for using the json_array_length function is as follows: json_array_length(json_array) Here’s an example…