如何在Presto/Trino/AWS Athena中动态合并多列为JSON对象

Since your column list is dynamic (growing over time), static SQL won’t work—you need to generate the query dynamically using metadata from information_schema. Here’s how to implement this:

Step 1: Generate the JSON object column list #

First, create a string that formats all excluded columns into the key-value pairs required by json_object(). For each column (excluding column_a), we need 'column_name', column_name (the string literal as the JSON key, and the column value as the JSON value).

For MySQL:

-- Increase group_concat limit if you have a large number of columns
SET SESSION group_concat_max_len = 1000000;

SELECT GROUP_CONCAT(
    CONCAT('''', column_name, ''', ', column_name)
    SEPARATOR ', '
) INTO @json_columns
FROM information_schema.columns
WHERE table_schema = 'your_database_name'
  AND table_name = 'your_source_table'
  AND column_name != 'column_a';

Step 2: Build and execute the dynamic INSERT query #

Construct the full INSERT statement using the generated column list, then run it:

For MySQL:

SET @insert_query = CONCAT(
    'INSERT INTO target_table (column_a, all_other_columns_combined_in_a_json) ',
    'SELECT column_a, json_object(', @json_columns, ') ',
    'FROM your_source_table;'
);

PREPARE stmt FROM @insert_query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

For Other SQL Dialects #

If you’re using PostgreSQL, adjust the syntax to use json_build_object() and string_agg:

DO $$
DECLARE
    json_columns text;
BEGIN
    SELECT string_agg(format('%L, %I', column_name, column_name), ', ')
    INTO json_columns
    FROM information_schema.columns
    WHERE table_schema = 'your_schema'
      AND table_name = 'your_source_table'
      AND column_name != 'column_a';

    EXECUTE format(
        'INSERT INTO target_table (column_a, all_other_columns_combined_in_a_json)
         SELECT column_a, json_build_object(%s)
         FROM your_source_table;',
        json_columns
    );
END $$;

Key Notes #

• Replace your_database_name, your_source_table, target_table, and (for PostgreSQL) your_schema with your actual names.
• Dynamic SQL automatically includes any new columns added to the source table in the JSON object—no manual query updates needed.
• For Oracle, use JSON_OBJECT(KEY ... VALUE ...) syntax and execute the dynamic query via EXECUTE IMMEDIATE.

内容的提问来源于stack exchange，提问作者Hrvoje Brešić