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

如何将表中存储的 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;

总结

通过上述步骤，我们可以将 log_table 表中的 param_json 字段中的 JSON 数组展开为多行，并提取出具体的字段值，然后将其插入到新表 new_table 中。这使得数据处理更加灵活和方便。

参考资料

• PostgreSQL JSON Functions

希望这篇博客对你有所帮助！如果有任何问题或需要进一步的帮助，请随时联系。

---