Можно ли упростить выражение sql, чтобы избежать такой вложенности?

Question

[Вадим]

WITH table_columns AS (
  SELECT table_name, column_name, data_type
  FROM information_schema.columns
  WHERE table_schema = 'public'
)
SELECT table_name, json_object_agg(element_number, temp_columns) as columns FROM
(SELECT
   table_name, element_number, json_object_agg(column_name, data_type) as temp_columns
FROM (SELECT
    table_name, ROW_NUMBER() OVER (PARTITION BY table_name ORDER BY column_name) AS element_number, column_name, data_type
    FROM table_columns) AS new_table
    GROUP BY table_name, element_number ORDER BY element_number) AS final_table
GROUP BY table_name;

Answer 1

[Дмитрий]

with t as(SELECT table_name, column_name, data_type, ROW_NUMBER() OVER (PARTITION BY table_name ORDER BY column_name) as num
  FROM information_schema.columns
  WHERE table_schema = 'public')
select json_agg(json_build_object(num, json_build_object(column_name, data_type))) from t group by table_name;

Comments

[Вадим]

не совсем такой формат - проверьте сами!

[Дмитрий]

Вадим, ну подправьте

with t as(SELECT table_name, column_name, data_type, ROW_NUMBER() OVER (PARTITION BY table_name ORDER BY column_name) as num
  FROM information_schema.columns
  WHERE table_schema = 'public')
select concat('{', string_agg(concat('"', num, '"', ':', json_build_object(column_name, data_type))::text, ','), '}')::json from t group by table_name;