如何让PostgreSQL查询更符合DRY原则？

Nice spot on the repeated code—your current query works perfectly, but those duplicate json_build_object blocks with identical filter logic are exactly where we can apply DRY (Don't Repeat Yourself) principles to clean things up. Here are two practical, scalable approaches to refactor this in PostgreSQL:

Approach 1: Use a CTE to Unpivot Categories First #

This method flattens your nested JSON category values into a single dataset first, then reuses the same count/filter logic for each category when building the final JSON objects. It’s easy to extend if you need to add more categories later.

WITH category_scores AS (
  SELECT
    date(created_at) AS survey_date,
    category,
    score
  FROM survey_results,
  LATERAL (
    -- Add new categories here by adding more VALUES entries
    VALUES
      ('food_insecurity', scores#>>'{medic,categories,food_insecurity}'),
      ('motivation', scores#>>'{medic,categories,motivation}')
  ) AS cat(category, score)
)
SELECT
  survey_date,
  -- Build food_insecurity JSON using reused filter logic
  json_build_object(
    'high', COUNT(*) FILTER (WHERE category = 'food_insecurity' AND score = 'high'),
    'medium', COUNT(*) FILTER (WHERE category = 'food_insecurity' AND score = 'medium'),
    'low', COUNT(*) FILTER (WHERE category = 'food_insecurity' AND score = 'low')
  ) AS food_insecurity,
  -- Build motivation JSON with the same pattern
  json_build_object(
    'high', COUNT(*) FILTER (WHERE category = 'motivation' AND score = 'high'),
    'medium', COUNT(*) FILTER (WHERE category = 'motivation' AND score = 'medium'),
    'low', COUNT(*) FILTER (WHERE category = 'motivation' AND score = 'low')
  ) AS motivation
FROM category_scores
GROUP BY survey_date;

Approach 2: Aggregate Stats First, Then Pivot (Max Scalability) #

If you want to eliminate duplicate json_build_object calls entirely, this approach calculates category stats in one place, then maps them to your desired column structure. It’s ideal if you anticipate adding multiple new categories down the line.

WITH unpivoted AS (
  SELECT
    date(created_at) AS survey_date,
    category,
    score
  FROM survey_results,
  LATERAL (
    VALUES
      ('food_insecurity', scores#>>'{medic,categories,food_insecurity}'),
      ('motivation', scores#>>'{medic,categories,motivation}')
  ) AS cat(category, score)
),
category_stats AS (
  SELECT
    survey_date,
    category,
    -- Define the JSON build logic ONCE here
    json_build_object(
      'high', COUNT(*) FILTER (WHERE score = 'high'),
      'medium', COUNT(*) FILTER (WHERE score = 'medium'),
      'low', COUNT(*) FILTER (WHERE score = 'low')
    ) AS stats
  FROM unpivoted
  GROUP BY survey_date, category
)
SELECT
  survey_date,
  -- Map each category's pre-built stats to its own column
  MAX(CASE WHEN category = 'food_insecurity' THEN stats END) AS food_insecurity,
  MAX(CASE WHEN category = 'motivation' THEN stats END) AS motivation
FROM category_stats
GROUP BY survey_date;

Both approaches produce exactly the same output as your original query, but with far less redundant code. If you ever need to add a new category (like stress_level), you only need to add one line to the VALUES clause and (for Approach 2) one MAX(CASE...) line—no copying and pasting entire blocks of code.

内容的提问来源于stack exchange，提问作者Mateusz Urbański