PostgreSQL按分组构建无重复键压缩JSON对象技术问询

Absolutely doable! You can leverage PostgreSQL's powerful JSON aggregation functions to build exactly the nested structure you need directly in your query—no extra JavaScript processing required (though you could post-process, why do the extra work?).

Step-by-Step Solution #

Your existing query already filters and groups the relevant data, so we'll build on that to add nested JSON aggregation. Here's the modified query:

SELECT json_object_agg(type_bien, mutation_list) AS final_json
FROM (
  SELECT
    type_bien,
    json_agg(
      json_build_object(
        'nature_mutation', nature_mutation,
        'options', json_agg(option)
      )
    ) AS mutation_list
  FROM layer.dvf a
  LEFT JOIN (
    SELECT value, option
    FROM form_select
    WHERE name = 'nature_mutation'
  ) b ON LOWER(a.nature_mutation) = LOWER(b.value)
  WHERE option IS NOT NULL
  GROUP BY type_bien, nature_mutation
) AS grouped_mutations
GROUP BY type_bien
ORDER BY type_bien;

How It Works #

Let’s break down the key parts:

• Inner Grouping: First, we group by type_bien and nature_mutation, then use json_agg(option) to collect all unique options for each mutation into an array.
• Build Mutation Objects: json_build_object creates a structured object for each nature_mutation, pairing the mutation name with its options array.
• Aggregate Mutations by Type: We then group those mutation objects by type_bien using another json_agg, creating an array of mutations for each property type.
• Final JSON Object: json_object_agg converts the type_bien values into top-level keys, each mapped to their corresponding array of mutation objects.

Example Output #

The result will be a clean, JS-friendly JSON structure with no duplicate keys, like this:

{
  "maison": [
    {
      "nature_mutation": "Vente",
      "options": ["option_vente_1", "option_vente_2"]
    },
    {
      "nature_mutation": "Echange",
      "options": ["option_echange_1"]
    }
  ],
  "appartement": [
    {
      "nature_mutation": "Vente",
      "options": ["option_vente_1", "option_appart_vente"]
    }
  ]
}

This structure is ready to be parsed directly in JavaScript with JSON.parse() and used without any further cleanup.

内容的提问来源于stack exchange，提问作者Maxime