如何在Amazon Redshift中展平JSON数据并导入结构化数据表？

你的JSON结构以产品名称作为顶层键，对应子对象包含price和quantity字段。Redshift没有直接用通配符迭代JSON键的函数，但可以通过JSON_KEYS+UNNEST的组合拆分所有产品条目，再提取对应字段，具体实现如下：

1. 单条JSON数据处理示例 #

先将JSON存入临时表（如果数据来自外部文件，可先通过COPY命令导入到临时表）：

CREATE TEMP TABLE temp_json_data (json_str VARCHAR);
INSERT INTO temp_json_data VALUES ('{ "lamp": { "price": 18.99, "quantity": 30 }, "desk": { "price": 129.99, "quantity": 12 }, "vase": { "price": 22.49, "quantity": 18 }, "speakers": { "price": 49.99, "quantity": 50 } }');

执行以下SQL拆分数据并导入目标表（假设目标表名为products）：

INSERT INTO products (product, price, quantity)
SELECT
    json_key AS product,
    JSON_EXTRACT_PATH_TEXT(json_str, json_key, 'price')::DECIMAL(10,2) AS price,
    JSON_EXTRACT_PATH_TEXT(json_str, json_key, 'quantity')::INT AS quantity
FROM temp_json_data,
UNNEST(JSON_KEYS(json_str)) AS t(json_key);

2. 核心函数说明 #

• JSON_KEYS(json_str)：提取JSON对象的所有顶层键，返回数组（此处得到['lamp','desk','vase','speakers']）。
• UNNEST(array)：将数组元素拆分为独立行，实现每个产品名对应一行数据。
• JSON_EXTRACT_PATH_TEXT(json_str, key1, key2)：多层遍历JSON，先定位到产品名对应的子对象，再提取price/quantity字段。

3. 批量JSON处理 #

如果表中存储多条JSON数据（每行一条），上述逻辑同样适用，UNNEST会自动对每行JSON展开对应的产品条目。

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