将字典列表格式JSON转换为PostgreSQL行列结构的方法

Hey there! Handling large, heterogeneous JSON datasets like this in PostgreSQL doesn't require manual entry at all—we can leverage PostgreSQL's robust JSON support to automate the entire process efficiently. Here's a step-by-step solution:

Step 1: Create a temporary table to store raw JSON data #

First, we'll load all your JSON entries into a temporary staging table. This is a fast, efficient way to handle large datasets:

-- Create temp table to hold raw JSON objects (jsonb is better for performance than json)
CREATE TEMP TABLE temp_json_staging (data jsonb);

If your JSON is stored in a file, use COPY (or \copy in psql for user-level permissions) to load it quickly:

-- Replace with your actual file path
COPY temp_json_staging (data) FROM '/path/to/your/large_data.json';

-- If using psql client without server-side file access, use:
-- \copy temp_json_staging (data) FROM '/path/to/your/large_data.json'

Step 2: Dynamically create the target table with all unique keys as columns #

Since your JSON objects have varying keys, we'll auto-detect all unique keys and generate the table schema dynamically:

DO $$
DECLARE
    column_defs text;
BEGIN
    -- Collect all unique keys, format them as text columns (we can adjust types later)
    SELECT string_agg(DISTINCT quote_ident(jsonb_object_keys(data)), ' text, ') || ' text'
    INTO column_defs
    FROM temp_json_staging;

    -- Create the final table (replace 'your_target_table' with your desired name)
    EXECUTE 'CREATE TABLE your_target_table (' || column_defs || ')';
END $$;

• quote_ident ensures we handle keys with spaces or special characters correctly (avoids SQL syntax errors).
• We start with text columns for flexibility—you can modify column types later if needed.

Step 3: Insert JSON data into the target table, filling missing values with '-' #

Next, we'll generate a dynamic INSERT statement that maps each JSON key to the corresponding column, using '-' for missing values (matching your example):

DO $$
DECLARE
    insert_query text;
BEGIN
    -- Build the INSERT query: map each key to its value, use '-' if the key is missing
    SELECT 'INSERT INTO your_target_table (' || string_agg(DISTINCT quote_ident(key), ', ') || ') ' ||
           'SELECT ' || string_agg(DISTINCT 'COALESCE(data->>' || quote_literal(key) || ', ''-'')', ', ') ||
           ' FROM temp_json_staging'
    INTO insert_query
    FROM (SELECT DISTINCT jsonb_object_keys(data) AS key FROM temp_json_staging) AS all_keys;

    -- Execute the insert
    EXECUTE insert_query;
END $$;

• COALESCE replaces NULL values (from missing keys) with '-'. If you prefer NULL instead, just remove the ', ''-''' part.

Step 4: Optional: Adjust column types (if needed) #

If some columns should be specific types (like timestamps, integers), you can alter the table after insertion:

-- Convert Date_and_Time to timestamp type
ALTER TABLE your_target_table ALTER COLUMN "Date_and_Time" TYPE timestamp USING "Date_and_Time"::timestamp;

-- Convert Age to integer type
ALTER TABLE your_target_table ALTER COLUMN "Age" TYPE integer USING "Age"::integer;

Verify the result #

Run this to check your imported data matches your desired output:

SELECT * FROM your_target_table;

This approach is fully automated and optimized for large datasets—no manual entry required!

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