如何在PostgreSQL中创建嵌套表并加载JSON数组数据？

首先纠正一个误解：PostgreSQL完全支持嵌套数据的存储，只是并非传统意义上的“嵌套表”，可以通过JSONB类型或自定义复合类型+数组两种核心方式实现，以下是具体操作步骤：

方案一：直接用JSONB存储完整嵌套数据 #

适合数据结构灵活、无需频繁查询内部字段的场景，导入成本低。

1. 创建表 #

假设你的JSON数据结构如下（用户订单嵌套商品数组）：

{
  "user_id": 123,
  "username": "johndoe",
  "orders": [
    {
      "order_id": 456,
      "order_date": "2024-01-01",
      "items": [
        {"product_id": 789, "name": "Laptop", "price": 999.99},
        {"product_id": 101, "name": "Mouse", "price": 29.99}
      ]
    }
  ]
}

创建存储表的SQL：

CREATE TABLE user_data (
    id SERIAL PRIMARY KEY,
    data JSONB NOT NULL
);

2. 加载数据 #

• 单条插入：

INSERT INTO user_data (data)
VALUES ('{
  "user_id": 123,
  "username": "johndoe",
  "orders": [
    {
      "order_id": 456,
      "order_date": "2024-01-01",
      "items": [
        {"product_id": 789, "name": "Laptop", "price": 999.99},
        {"product_id": 101, "name": "Mouse", "price": 29.99}
      ]
    }
  ]
}'::JSONB);

• 批量导入（从JSON文件）：
  若数据存于/path/to/data.json（文件每行一个完整JSON对象），用psql命令导入：

\copy user_data (data) FROM '/path/to/data.json' WITH (FORMAT text);

• 顶层为JSON数组的文件导入：
  如果整个文件是一个JSON数组（如[{"user_id":1,...}, {"user_id":2,...}]），用以下SQL拆分导入：

INSERT INTO user_data (data)
SELECT jsonb_array_elements(pg_read_file('/path/to/data.json')::JSONB);

方案二：拆解为结构化嵌套类型（适合高频查询内部字段） #

如果需要频繁查询嵌套层级的字段（如统计特定商品的订单量），可以用自定义复合类型+数组模拟“嵌套表”，性能更优且数据约束更严格。

1. 定义嵌套复合类型 #

-- 定义商品子类型
CREATE TYPE product AS (
    product_id INT,
    name TEXT,
    price NUMERIC
);

-- 定义订单类型（包含商品数组）
CREATE TYPE order_item AS (
    order_id INT,
    order_date DATE,
    items product[]
);

2. 创建主表 #

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username TEXT NOT NULL,
    orders order_item[] NOT NULL
);

3. 加载数据 #

插入示例嵌套数据：

INSERT INTO users (user_id, username, orders)
VALUES (
    123,
    'johndoe',
    ARRAY[
        ROW(
            456,
            '2024-01-01'::DATE,
            ARRAY[
                ROW(789, 'Laptop', 999.99)::product,
                ROW(101, 'Mouse', 29.99)::product
            ]
        )::order_item
    ]
);

4. 查询嵌套字段示例 #

比如查询用户123的所有订单商品：

SELECT unnest(orders).items FROM users WHERE user_id = 123;

两种方案对比 #

• JSONB：无需预先定义结构，适合数据格式多变的场景，但查询内部字段的性能略低于结构化类型。
• 复合类型+数组：数据结构固定，约束严格，查询嵌套字段性能更高，但需要提前明确所有层级的字段定义。

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