如何将表中存储的 JSON 数据转换为新表

最新推荐文章于 2025-11-21 10:35:07 发布

原创最新推荐文章于 2025-11-21 10:35:07 发布 · 1k 阅读

5 ·

CC 4.0 BY-SA版权

文章标签：

#json #sql #postgresql

如何将表中存储的 JSON 数据转换为新表

引言

本文介绍如何使用 PostgreSQL 的 JSON 函数，将 log_table 表中的 param_json 字段（存储为 JSON 数组）转换为一张新的表，并提取出具体的字段值。

表结构说明

假设 log_table 表的结构如下：

record_id: 主键
param_json: 存储 JSON 数组的字段，类型为 character varying

param_json 字段中的 JSON 数据示例如下：

[
    {
        "item_id": 1001,
        "code": "ABC123",
        "resource_id": "RES001",
        "flag": "1",
        "external_id": "EXT001",
        "serial_number": "SN001",
        "name": "Test Device 01",
        "internal_id": "INT001",
        "network_id": "NET001",
        "location": "Location A",
        "table_name": "TABLE_A"
    }
]

步骤

1. 确保 `param_json` 字段为 `jsonb` 类型

ALTER TABLE log_table
ALTER COLUMN param_json TYPE jsonb USING param_json::jsonb;

2. 使用 `jsonb_array_elements` 函数展开 JSON 数组

SELECT 
    record_id AS original_id,
    param_json->>'item_id' AS item_id,
    param_json->>'code' AS code,
    param_json->>'resource_id' AS resource_id,
    param_json->>'flag' AS flag,
    param_json->>'external_id' AS external_id,
    param_json->>'serial_number' AS serial_number,
    param_json->>'name' AS name,
    param_json->>'internal_id' AS internal_id,
    param_json->>'network_id' AS network_id,
    param_json->>'location' AS location,
    param_json->>'table_name' AS table_name
FROM 
    log_table,
    jsonb_array_elements(param_json) AS param_json;

3. 处理无效的 JSON 数据

SELECT 
    record_id AS original_id,
    param_json->>'item_id' AS item_id,
    param_json->>'code' AS code,
    param_json->>'resource_id' AS resource_id,
    param_json->>'flag' AS flag,
    param_json->>'external_id' AS external_id,
    param_json->>'serial_number' AS serial_number,
    param_json->>'name' AS name,
    param_json->>'internal_id' AS internal_id,
    param_json->>'network_id' AS network_id,
    param_json->>'location' AS location,
    param_json->>'table_name' AS table_name
FROM 
    log_table,
    jsonb_array_elements(CASE WHEN param_json::jsonb IS NOT NULL THEN param_json::jsonb ELSE '[]'::jsonb END) AS param_json;

实际应用案例

示例数据

INSERT INTO log_table (record_id, param_json) VALUES
(1, '[{"item_id": 1001, "code": "ABC123", "resource_id": "RES001", "flag": "1", "external_id": "EXT001", "serial_number": "SN001", "name": "Test Device 01", "internal_id": "INT001", "network_id": "NET001", "location": "Location A", "table_name": "TABLE_A"}]');

执行查询

SELECT 
    record_id AS original_id,
    param_json->>'item_id' AS item_id,
    param_json->>'code' AS code,
    param_json->>'resource_id' AS resource_id,
    param_json->>'flag' AS flag,
    param_json->>'external_id' AS external_id,
    param_json->>'serial_number' AS serial_number,
    param_json->>'name' AS name,
    param_json->>'internal_id' AS internal_id,
    param_json->>'network_id' AS network_id,
    param_json->>'location' AS location,
    param_json->>'table_name' AS table_name
FROM 
    log_table,
    jsonb_array_elements(CASE WHEN param_json::jsonb IS NOT NULL THEN param_json::jsonb ELSE '[]'::jsonb END) AS param_json;

查询结果

 original_id | item_id |  code  | resource_id | flag | external_id | serial_number |      name      | internal_id | network_id | location   | table_name
-------------+---------+--------+-------------+------+-------------+---------------+----------------+-------------+------------+------------+------------
           1 |    1001 | ABC123 | RES001      | 1    | EXT001      | SN001         | Test Device 01 | INT001      | NET001     | Location A | TABLE_A

创建新表并插入数据

创建新表

CREATE TABLE new_table (
    original_id INT,
    item_id INT,
    code VARCHAR(50),
    resource_id VARCHAR(50),
    flag VARCHAR(1),
    external_id VARCHAR(50),
    serial_number VARCHAR(50),
    name VARCHAR(100),
    internal_id VARCHAR(50),
    network_id VARCHAR(50),
    location VARCHAR(50),
    table_name VARCHAR(50)
);

插入数据

INSERT INTO new_table (original_id, item_id, code, resource_id, flag, external_id, serial_number, name, internal_id, network_id, location, table_name)
SELECT 
    record_id AS original_id,
    param_json->>'item_id' AS item_id,
    param_json->>'code' AS code,
    param_json->>'resource_id' AS resource_id,
    param_json->>'flag' AS flag,
    param_json->>'external_id' AS external_id,
    param_json->>'serial_number' AS serial_number,
    param_json->>'name' AS name,
    param_json->>'internal_id' AS internal_id,
    param_json->>'network_id' AS network_id,
    param_json->>'location' AS location,
    param_json->>'table_name' AS table_name
FROM 
    log_table,
    jsonb_array_elements(CASE WHEN param_json::jsonb IS NOT NULL THEN param_json::jsonb ELSE '[]'::jsonb END) AS param_json;