[Solved] How can you modify this query to exclude NULL product names from the JSON array?SELECT region, JSON_AGG(product) FROM sales GROUP BY region; | PostgreSQL

PostgreSQL - Aggregate Functions and GROUP BY

How can you modify this query to exclude NULL product names from the JSON array?

SELECT region, JSON_AGG(product) FROM sales GROUP BY region;

ASELECT region, JSON_AGG(product) FROM sales GROUP BY region HAVING product IS NOT NULL;

BSELECT region, JSON_AGG(product) FROM sales WHERE product IS NOT NULL GROUP BY region;

CSELECT region, JSON_AGG(product) HAVING product IS NOT NULL FROM sales GROUP BY region;

DSELECT region, JSON_AGG(product) FILTER (WHERE product IS NOT NULL) FROM sales GROUP BY region;

Step-by-Step Solution

Solution:

Step 1: Understand filtering inside aggregation
To exclude NULLs inside JSON_AGG, use the FILTER (WHERE ...) clause.
Step 2: Analyze options
SELECT region, JSON_AGG(product) FILTER (WHERE product IS NOT NULL) FROM sales GROUP BY region; correctly applies a filter inside JSON_AGG to exclude NULL products. SELECT region, JSON_AGG(product) FROM sales WHERE product IS NOT NULL GROUP BY region; filters rows before grouping, which also works but changes the dataset. Options C and D misuse HAVING clause, which filters groups, not individual values inside aggregation.
Final Answer:
SELECT region, JSON_AGG(product) FILTER (WHERE product IS NOT NULL) FROM sales GROUP BY region; -> Option D
Quick Check:
Use FILTER clause inside JSON_AGG to exclude NULLs [OK]

Quick Trick: Use FILTER (WHERE condition) inside JSON_AGG to exclude values [OK]

Common Mistakes:

Master "Aggregate Functions and GROUP BY" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

How can you modify this query to exclude NULL product names from the JSON array?