Скачать или смотреть How to Use DISTINCT in json_agg for PostgreSQL Queries

Learn how to effectively use `DISTINCT` within json_agg to eliminate duplicate records in PostgreSQL JSON aggregation.
---
This video is based on the question https://stackoverflow.com/q/62917911/ asked by the user 'MAK' ( https://stackoverflow.com/u/4268241/ ) and on the answer https://stackoverflow.com/a/62917974/ provided by the user 'Laurenz Albe' ( https://stackoverflow.com/u/6464308/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Add distinct within json_agg

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Use DISTINCT in json_agg for PostgreSQL Queries

When working with PostgreSQL, especially with JSON data types, you may encounter situations where you want to aggregate data into a JSON format while ensuring there are no duplicate entries. In this guide, we'll dive into how to achieve this using the DISTINCT keyword with the json_agg() function. We'll walk through some sample data and queries to illustrate the solution clearly.

The Problem

Consider a scenario where you have three tables: EmpMap, EmpInfo, and EmpAdd. Each table holds relevant employee data, but as you aggregate this data into JSON format, you might end up with duplicate addresses for the same employee. Here's a glimpse into our sample data:

Sample Data Overview

EmpMap: Maps employee IDs

EmpInfo: Contains employee names

EmpAdd: Has addresses associated with each employee ID

Current SQL Query

The following SQL query aggregates employee addresses:

[[See Video to Reveal this Text or Code Snippet]]

Output Challenge

When executing the query, you may receive an output like this:

idcountsemp_json_address13[{"EmpID" : 1, "EmpAdd" : "Addr1"}, {"EmpID" : 1, "EmpAdd" : "Addr1"}, {"EmpID" : 1, "EmpAdd" : "Addr1"}]22[{"EmpID" : 2, "EmpAdd" : "Add2"}, {"EmpID" : 2, "EmpAdd" : "Add2"}]32[{"EmpID" : 3, "EmpAdd" : "Address3"}, {"EmpID" : 3, "EmpAdd" : "Add3"}]As evidenced, there are redundant addresses for employees, particularly for Employee 1.

The Solution: Using DISTINCT

To eliminate duplicates in your JSON aggregation, you can leverage the DISTINCT keyword in combination with json_agg(). By doing so, you can ensure that only unique entries are included in the resulting JSON array.

Updated SQL Query

Here's how to modify your original query:

[[See Video to Reveal this Text or Code Snippet]]

Key Changes Explained

Using DISTINCT: By appending DISTINCT to json_agg(), the aggregation will remove any duplicate JSON objects.

Switching to jsonb_build_object: It’s important to use jsonb_build_object instead of json_build_object for two reasons:

JSONB allows for the use of equality operators, which is essential for the DISTINCT keyword to function correctly.

It generally provides better performance when performing comparisons or indexes.

Expected Output

Following the query adjustment, the expected output should look like this:

idcountsemp_json_address11[{"EmpID" : 1, "EmpAdd" : "Addr1"}]21[{"EmpID" : 2, "EmpAdd" : "Add2"}]32[{"EmpID" : 3, "EmpAdd" : "Address3"}, {"EmpID" : 3, "EmpAdd" : "Add3"}]Notice how the duplicate addresses have been effectively removed from the results, resulting in a much cleaner dataset.

Conclusion

In summary, using the DISTINCT keyword with json_agg allows you to create JSON objects that contain only unique entries, improving the quality and interpretability of your data. By transitioning to jsonb_build_object, you can ensure that your database queries perform efficiently and as expected.

Explore the potential of PostgreSQL JSON functionalities, and don't hesitate to apply these tips in your future database queries!