postgres json数据处理

根据您提供的JSON结构，我将创建两个表和一个存储过程。以下是完整的SQL代码：

1. 创建两张表

-- 创建用户表
CREATE TABLE users (id INT PRIMARY KEY,name VARCHAR(100) NOT NULL,email VARCHAR(150) UNIQUE NOT NULL,isActive BOOLEAN DEFAULT true,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);-- 创建产品表
CREATE TABLE products (productId VARCHAR(20) PRIMARY KEY,productName VARCHAR(200) NOT NULL,price DECIMAL(10,2) NOT NULL,category VARCHAR(100),created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2. 创建存储过程

DELIMITER $$CREATE PROCEDURE InsertFromJSON(IN p_json_data TEXT,OUT p_result INT
)
BEGINDECLARE EXIT HANDLER FOR SQLEXCEPTIONBEGINROLLBACK;SET p_result = 0;RESIGNAL;END;START TRANSACTION;-- 清空表数据（如果需要保留历史数据，请删除这两行）DELETE FROM products;DELETE FROM users;-- 插入用户数据INSERT INTO users (id, name, email, isActive)SELECT JSON_EXTRACT(user_obj, '$.id') AS id,JSON_UNQUOTE(JSON_EXTRACT(user_obj, '$.name')) AS name,JSON_UNQUOTE(JSON_EXTRACT(user_obj, '$.email')) AS email,JSON_EXTRACT(user_obj, '$.isActive') AS isActiveFROM JSON_TABLE(JSON_EXTRACT(p_json_data, '$.users'),'$[*]' COLUMNS (user_obj JSON PATH '$')) AS users;-- 插入产品数据INSERT INTO products (productId, productName, price, category)SELECT JSON_UNQUOTE(JSON_EXTRACT(product_obj, '$.productId')) AS productId,JSON_UNQUOTE(JSON_EXTRACT(product_obj, '$.productName')) AS productName,JSON_EXTRACT(product_obj, '$.price') AS price,JSON_UNQUOTE(JSON_EXTRACT(product_obj, '$.category')) AS categoryFROM JSON_TABLE(JSON_EXTRACT(p_json_data, '$.products'),'$[*]' COLUMNS (product_obj JSON PATH '$')) AS products;COMMIT;SET p_result = 1;END
$$DELIMITER ;

3. 使用示例

-- 调用存储过程
SET @json_data = '{"users": [{"id": 1,"name": "张三","email": "zhangsan@example.com","isActive": true},{"id": 2,"name": "李四","email": "lisi@example.com","isActive": false},{"id": 3,"name": "王五","email": "wangwu@example.com","isActive": true}],"products": [{"productId": "P001","productName": "笔记本电脑","price": 5999.99,"category": "电子产品"},{"productId": "P002","productName": "办公椅","price": 899.5,"category": "家具"}]
}';SET @result = 0;
CALL InsertFromJSON(@json_data, @result);
SELECT @result AS 执行结果;

4. 验证插入结果

-- 查看用户表数据
SELECT * FROM users;-- 查看产品表数据
SELECT * FROM products;

存储过程特点说明：
事务处理：使用事务确保数据一致性，要么全部成功，要么全部回滚
错误处理：通过异常处理器捕获错误，失败时返回0
JSON解析：使用MySQL的JSON函数解析JSON字符串
数据清理：存储过程会先清空表数据，如需保留历史数据请删除DELETE语句
返回值：成功返回1，失败返回0
这个存储过程可以正确处理您提供的JSON格式，并将数据分别插入到对应的表中。