How to control column order when using 'dynamic' component

[lozdown]

This discussion follows on from a previous one

I build a table of data create using the 'dynamic' component definition below (the details of the 'from' tables is not important to the question)

SELECT
	    'dynamic' AS component,
	    jsonb_build_object( 'Name', f."Last Name" ,'Cnt', e."at" ) ||
	    jsonb_object_agg(
	        f.eventdate,
	        CASE WHEN f.participation THEN '✅' END
	        ORDER BY f.eventdate
	) AS properties
	FROM filled_data f left join eventcount e on f."Contact ID"=e."Contact ID"
	GROUP BY f."Last Name", e."at"
	ORDER BY f."Last Name"

This gives a column display order of

Cnt, Name, 2024-01-17, 2024-02-14, ....

I want to control the display order of the columns but just changing the order of columns in the definition does not work because jsonb optimisation does not preserve order

What I actually want is

Name, Cnt, 2024-01-17, 2024-02-14, ....

but I cannot see a clean way to achieve this.

[lovasoa]

To control the display order of columns in the resulting JSON object, we will need to get rid of jsonb object merging with ||.
The issue lies in the fact that jsonb does not preserve key order, whereas json_object_agg can be used to explicitly define the order of keys.

Here's the solution, using an intermediate request to create the final ordered keys and values list before calling json_object_agg :

    
 WITH src AS (
   -- This CTE extracts the necessary fields and maintains the correct order.
   -- It includes both the "Last Name" and "at" fields to satisfy the GROUP BY requirement.
   SELECT
      f."Last Name" AS name,
      e."at" AS cnt,
      f.eventdate,
      CASE WHEN f.participation THEN '✅' END AS participation
   FROM filled_data f
   LEFT JOIN eventcount e ON f."Contact ID" = e."Contact ID"
   ORDER BY f."Last Name", e."at"
),
object_items AS (
  -- Here we create the first part of the aggregation (for 'Name' and 'Cnt' columns).
  -- We need to generate two separate keys for the GROUP BY: one for the name, one for the 'cnt'.
  SELECT name AS group_key_1, cnt AS group_key_2, 1 AS order_index, 'Name' AS key, to_json(name) AS value
  FROM src
  GROUP BY name, cnt
  UNION ALL
  SELECT name AS group_key_1, cnt AS group_key_2, 2 AS order_index, 'Cnt' AS key, to_json(cnt) AS value
  FROM src
  GROUP BY name, cnt
  UNION ALL
  -- Next, we aggregate event dates. Since event dates are ordered per each (name, cnt), we assign row numbers
  -- to maintain the correct order within each group defined by group_key_1 and group_key_2.
  SELECT name AS group_key_1, cnt AS group_key_2, 
         ROW_NUMBER() OVER (PARTITION BY name, cnt ORDER BY eventdate) + 2 AS order_index, 
         eventdate::text AS key, to_json(participation) AS value
  FROM src
)
-- Final aggregation step where we group by both group_key_1 and group_key_2.
-- The order of columns is controlled by the order_index, ensuring 'Name', 'Cnt', and the event dates are ordered correctly.
SELECT 
   'dynamic' AS component, 
   json_object_agg(key, value ORDER BY order_index) AS properties
FROM object_items
GROUP BY group_key_1, group_key_2;

Explanation:

1. src CTE:

   • Extracts the necessary fields from the input tables, grouping and ordering by Last Name and at.

2. object_items CTE:

   • Defines the keys (Name, Cnt, and event dates) along with their values.
   • Assigns an explicit order index (order_index) to enforce the desired key order.
   • Event dates are dynamically included with their corresponding participation status.

3. Final Query:

   • Uses json_object_agg with ORDER BY order_index to ensure keys appear in the desired order (Name, Cnt, followed by event dates).

[lozdown]

Thank you for such a full and helpful reply. It is much appreciated