<think>我们已知源表为关系型数据库中的表：ods_ac_mongo_coderelations，其中包含bs和cs字段（存储的是JSON数组格式的数据）。
目标：将bs和cs字段的数据提取到新表中，并设计新表的结构。
根据之前的讨论，bs和cs都是数组，每个元素是一个对象。我们需要将数组拆分为行（即一行一个元素），同时保留与主文档的关联。
设计思路：
1. 创建一个主表（或保持原表）存储公共字段（如_id, et等），但用户要求只提取bs和cs字段，所以我们可以创建两个新表分别存储bs和cs的明细数据。
2. 由于bs和cs中的每个对象都有多个属性，我们将这些属性作为新表的列。
具体表结构设计：
表1：bs_detail
- id: 自增主键（可选，也可以使用复合主键）
- main_id: 关联原表的主键（即原表的主键，假设原表有主键id，或者是原表中的_id字段，但原表中的_id是MongoDB的ObjectId，在关系型数据库中可能已经映射为字符串或二进制）
- bid: 字符串类型（对应bs中的bid）
- f: 整数类型（对应bs中的f）
- l: 整数类型（对应bs中的l）
- p: 整数类型（对应bs中的p）
表2：cs_detail
- id: 自增主键（可选）
- main_id: 关联原表的主键
- bid: 字符串类型（对应cs中的bid）
- c: 字符串类型（对应cs中的c）
- l: 整数类型（对应cs中的l）
- pc: 字符串类型（对应cs中的pc，可为空）
- t: 时间戳类型（对应cs中的t）
- st: 时间戳类型（可为空，对应cs中的st）
- chd: 文本类型（对应cs中的chd，因为可能较长）
- a: 整数类型（对应cs中的a）
- fa: 整数类型（对应cs中的fa）
- s: 整数类型（对应cs中的s）
- ec: 字符串类型（可为空，对应cs中的ec）
- f: 整数类型（对应cs中的f）
- sf: 整数类型（对应cs中的sf）
- fc: 字符串类型（可为空，对应cs中的fc）
- fid: 整数类型（对应cs中的fid）
- x: 字符串类型（可为空，对应cs中的x）
注意：原表（ods_ac_mongo_coderelations）中必须有一个唯一标识每行的主键（假设为main_id），用于关联这两个明细表。
如果原表没有主键，那么我们可能需要使用原表中的_id字段（它是MongoDB的ObjectId，在关系型数据库中可能是字符串类型）作为关联字段。
假设原表结构：
ods_ac_mongo_coderelations (
_id VARCHAR(24) PRIMARY KEY, -- 或者是一个自增主键，但这里我们假设_id是主键
et TIMESTAMP,
bs JSON, -- 存储bs数组
cs JSON -- 存储cs数组
)
那么，我们需要创建两个新表，并编写SQL将bs和cs数组拆分为行。
由于不同的关系型数据库处理JSON的函数不同，这里以PostgreSQL为例（如果使用MySQL，则需要使用JSON_TABLE函数，但MySQL5.7以上支持，而PostgreSQL有jsonb_array_elements）。
以下是PostgreSQL的SQL示例：
步骤1：创建bs_detail表
```sql
CREATE TABLE bs_detail (
id SERIAL PRIMARY KEY,
main_id VARCHAR(24) NOT NULL REFERENCES ods_ac_mongo_coderelations(_id),
bid VARCHAR(50),
f INTEGER,
l INTEGER,
p INTEGER
);
```
步骤2：创建cs_detail表
```sql
CREATE TABLE cs_detail (
id SERIAL PRIMARY KEY,
main_id VARCHAR(24) NOT NULL REFERENCES ods_ac_mongo_coderelations(_id),
bid VARCHAR(50),
c VARCHAR(50),
l INTEGER,
pc VARCHAR(50),
t TIMESTAMP,
st TIMESTAMP,
chd TEXT,
a INTEGER,
fa INTEGER,
s INTEGER,
ec VARCHAR(50),
f INTEGER,
sf INTEGER,
fc VARCHAR(50),
fid INTEGER,
x VARCHAR(50)
);
```
步骤3：从原表导入bs数据到bs_detail
使用jsonb_array_elements函数（如果原表是jsonb类型）或者json_array_elements（如果原表是json类型）：
```sql
INSERT INTO bs_detail (main_id, bid, f, l, p)
SELECT
_id AS main_id,
elem->>'bid' AS bid,
(elem->>'f')::INTEGER AS f,
(elem->>'l')::INTEGER AS l,
(elem->>'p')::INTEGER AS p
FROM ods_ac_mongo_coderelations,
LATERAL jsonb_array_elements(bs::jsonb) AS elem;
```
注意：如果原表的bs字段是字符串类型，我们需要先转换为jsonb。如果已经是jsonb类型，则直接使用。
步骤4：从原表导入cs数据到cs_detail
```sql
INSERT INTO cs_detail (main_id, bid, c, l, pc, t, st, chd, a, fa, s, ec, f, sf, fc, fid, x)
SELECT
_id AS main_id,
elem->>'bid' AS bid,
elem->>'c' AS c,
(elem->>'l')::INTEGER AS l,
elem->>'pc' AS pc,
(elem->>'t')::TIMESTAMP AS t,
(elem->>'st')::TIMESTAMP AS st, -- 注意：st可能为null，转换时需注意
elem->>'chd' AS chd,
(elem->>'a')::INTEGER AS a,
(elem->>'fa')::INTEGER AS fa,
(elem->>'s')::INTEGER AS s,
elem->>'ec' AS ec,
(elem->>'f')::INTEGER AS f,
(elem->>'sf')::INTEGER AS sf,
elem->>'fc' AS fc,
(elem->>'fid')::INTEGER AS fid,
elem->>'x' AS x
FROM ods_ac_mongo_coderelations,
LATERAL jsonb_array_elements(cs::jsonb) AS elem;
```
注意：
1. 对于可能为null的字段（如st, pc, ec, fc, x等），直接使用->>会得到字符串的null，转换为时间戳或整数会失败。我们可以使用NULLIF函数：
例如：NULLIF(elem->>'st', 'null')::TIMESTAMP 或者更通用的，判断是否为null字符串，但PostgreSQL中jsonb_array_elements展开后，如果原字段是null，则->>返回的是空字符串？还是字符串'null'？实际上，在JSON中，null值在转换为文本时是字符串'null'。但为了安全，我们可以使用：
CASE WHEN elem->'st' IS NULL OR jsonb_typeof(elem->'st') = 'null' THEN NULL ELSE (elem->>'st')::TIMESTAMP END
2. 如果原表数据量很大，考虑分批插入。
对于MySQL 8.0以上版本，可以使用JSON_TABLE函数，示例：
插入bs_detail（MySQL）：
```sql
INSERT INTO bs_detail (main_id, bid, f, l, p)
SELECT
_id AS main_id,
j.bid,
j.f,
j.l,
j.p
FROM ods_ac_mongo_coderelations,
JSON_TABLE(bs, '$[*]' COLUMNS (
bid VARCHAR(50) PATH '$.bid',
f INT PATH '$.f',
l INT PATH '$.l',
p INT PATH '$.p'
)) AS j;
```
类似地，插入cs_detail（MySQL）：
```sql
INSERT INTO cs_detail (main_id, bid, c, l, pc, t, st, chd, a, fa, s, ec, f, sf, fc, fid, x)
SELECT
_id AS main_id,
j.bid,
j.c,
j.l,
j.pc,
j.t,
j.st,
j.chd,
j.a,
j.fa,
j.s,
j.ec,
j.f,
j.sf,
j.fc,
j.fid,
j.x
FROM ods_ac_mongo_coderelations,
JSON_TABLE(cs, '$[*]' COLUMNS (
bid VARCHAR(50) PATH '$.bid',
c VARCHAR(50) PATH '$.c',
l INT PATH '$.l',
pc VARCHAR(50) PATH '$.pc',
t TIMESTAMP PATH '$.t',
st TIMESTAMP PATH '$.st',
chd TEXT PATH '$.chd',
a INT PATH '$.a',
fa INT PATH '$.fa',
s INT PATH '$.s',
ec VARCHAR(50) PATH '$.ec',
f INT PATH '$.f',
sf INT PATH '$.sf',
fc VARCHAR(50) PATH '$.fc',
fid INT PATH '$.fid',
x VARCHAR(50) PATH '$.x'
)) AS j;
```
注意：MySQL中时间戳的处理，如果JSON中的时间是字符串，需要确保格式正确。
总结：
1. 新表结构按照bs和cs中的字段设计，每个字段根据其含义选择合适的数据类型。
2. 使用数据库的JSON解析函数将数组拆分为行，并插入新表。
如果原表没有主键，我们需要先确保有一个唯一标识每行的字段（比如添加自增主键，或者使用原表的_id）。
最后，根据实际数据库类型（PostgreSQL, MySQL, SQL Server等）选择合适的JSON解析方法。
注意：在迁移过程中，需要处理JSON解析可能出现的错误（如字段缺失、类型不匹配等），可以添加异常处理或使用条件表达式。
如果某个字段在JSON对象中不存在，则解析为NULL。
生成的相关问题：
1. 如何在不同类型的数据库（如MySQL、PostgreSQL、SQL Server）中解析JSON数组？
2. 在迁移过程中如何处理JSON字段中的空值或格式错误？
3. 如果bs或cs数组非常大，如何优化迁移性能？
4. 新表结构是否需要添加索引以提高查询效率？
5. 如何验证迁移后数据的完整性和准确性？
</think>### 新表结构设计与SQL语句
#### 表结构设计思路
1. **数据扁平化处理**：将嵌套的JSON数组拆分为关系型表结构
2. **保留关联关系**：通过主键关联保证数据完整性
3. **字段类型映射**：根据MongoDB字段特性选择合适的关系型字段类型
#### 新表设计方案
```sql
-- 批次表 (存储bs字段数据)
CREATE TABLE batch_records (
id BIGINT PRIMARY KEY AUTO_INCREMENT, -- 自增主键
main_id CHAR(24) NOT NULL, -- 关联主文档ID
bid VARCHAR(36) NOT NULL, -- 批次ID (UUID格式)
f_level INT NOT NULL, -- f字段 (层级)
l_count INT NOT NULL, -- l字段 (数量)
p_value INT NOT NULL, -- p字段 (参数值)
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 代码关系表 (存储cs字段数据)
CREATE TABLE code_relations (
id BIGINT PRIMARY KEY AUTO_INCREMENT, -- 自增主键
main_id CHAR(24) NOT NULL, -- 关联主文档ID
batch_id VARCHAR(36) NOT NULL, -- 关联批次ID
code VARCHAR(24) NOT NULL, -- c字段 (代码)
l_level INT NOT NULL, -- l字段 (层级)
parent_code VARCHAR(24), -- pc字段 (父代码)
event_time TIMESTAMP NOT NULL, -- t字段 (时间)
children TEXT, -- chd字段 (子代码列表)
a_value INT NOT NULL DEFAULT 0, -- a字段
fa_count INT NOT NULL DEFAULT 0, -- fa字段
s_value INT NOT NULL DEFAULT 0, -- s字段
fid_value INT NOT NULL DEFAULT 0, -- fid字段
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 创建索引优化查询
CREATE INDEX idx_main_id ON batch_records(main_id);
CREATE INDEX idx_batch_id ON code_relations(batch_id);
CREATE INDEX idx_code_relations_main ON code_relations(main_id);
```
#### 数据提取SQL语句 (PostgreSQL示例)
```sql
-- 提取bs字段数据到batch_records表
INSERT INTO batch_records (main_id, bid, f_level, l_count, p_value)
SELECT
_id AS main_id,
elem->>'bid' AS bid,
(elem->>'f')::INTEGER AS f_level,
(elem->>'l')::INTEGER AS l_count,
(elem->>'p')::INTEGER AS p_value
FROM ods_ac_mongo_coderelations,
LATERAL jsonb_array_elements(bs::jsonb) AS elem;
-- 提取cs字段数据到code_relations表
INSERT INTO code_relations (
main_id, batch_id, code, l_level, parent_code,
event_time, children, a_value, fa_count, s_value, fid_value
)
SELECT
_id AS main_id,
elem->>'bid' AS batch_id,
elem->>'c' AS code,
(elem->>'l')::INTEGER AS l_level,
NULLIF(elem->>'pc', 'null') AS parent_code,
(elem->>'t')::TIMESTAMP AS event_time,
elem->>'chd' AS children,
COALESCE((elem->>'a')::INTEGER, 0) AS a_value,
COALESCE((elem->>'fa')::INTEGER, 0) AS fa_count,
COALESCE((elem->>'s')::INTEGER, 0) AS s_value,
COALESCE((elem->>'fid')::INTEGER, 0) AS fid_value
FROM ods_ac_mongo_coderelations,
LATERAL jsonb_array_elements(cs::jsonb) AS elem;
```
#### MySQL兼容方案
```sql
-- MySQL提取bs字段
INSERT INTO batch_records (main_id, bid, f_level, l_count, p_value)
SELECT
t._id,
JSON_EXTRACT(j.value, '$.bid'),
JSON_EXTRACT(j.value, '$.f'),
JSON_EXTRACT(j.value, '$.l'),
JSON_EXTRACT(j.value, '$.p')
FROM ods_ac_mongo_coderelations t,
JSON_TABLE(t.bs, '$[*]' COLUMNS (
value JSON PATH '$'
)) AS j;
-- MySQL提取cs字段
INSERT INTO code_relations (main_id, batch_id, code, l_level, parent_code,
event_time, children, a_value, fa_count, s_value, fid_value)
SELECT
t._id,
JSON_UNQUOTE(JSON_EXTRACT(j.value, '$.bid')),
JSON_UNQUOTE(JSON_EXTRACT(j.value, '$.c')),
JSON_EXTRACT(j.value, '$.l'),
NULLIF(JSON_UNQUOTE(JSON_EXTRACT(j.value, '$.pc')), 'null'),
JSON_UNQUOTE(JSON_EXTRACT(j.value, '$.t')),
JSON_UNQUOTE(JSON_EXTRACT(j.value, '$.chd')),
COALESCE(JSON_EXTRACT(j.value, '$.a'), 0),
COALESCE(JSON_EXTRACT(j.value, '$.fa'), 0),
COALESCE(JSON_EXTRACT(j.value, '$.s'), 0),
COALESCE(JSON_EXTRACT(j.value, '$.fid'), 0)
FROM ods_ac_mongo_coderelations t,
JSON_TABLE(t.cs, '$[*]' COLUMNS (
value JSON PATH '$'
)) AS j;
```
### 关键设计说明
1. **层级关系处理**：
- `f_level`和`l_level`保留原始层级信息
- `parent_code`存储父节点关联关系
- `children`字段存储逗号分隔的子节点列表
2. **空值处理策略**：
```sql
NULLIF(elem->>'pc', 'null') -- 将JSON中的'null'转为SQL NULL
COALESCE((elem->>'a')::INTEGER, 0) -- 空值转为0
```
3. **性能优化**：
- 使用`LATERAL JOIN`提升JSON解析效率
- 创建索引加速关联查询
- 批量插入减少事务开销
4. **数据类型映射**：
| MongoDB类型 | 关系型类型 | 处理方式 |
|------------|------------|---------|
| ObjectId | CHAR(24) | 直接存储 |
| ISODate | TIMESTAMP | 自动转换 |
| NumberInt | INTEGER | 类型转换 |
### 数据验证查询
```sql
-- 检查数据完整性
SELECT
(SELECT COUNT(*) FROM batch_records) AS batch_count,
(SELECT COUNT(*) FROM code_relations) AS relation_count;
-- 验证层级关系
SELECT
c.code,
c.parent_code,
b.bid
FROM code_relations c
JOIN batch_records b ON c.batch_id = b.bid
WHERE c.l_level = 2;
```
### 嵌套JSON处理建议
对于`chd`字段中的逗号分隔值，建议在应用层处理或在ETL过程中进一步拆分：
```sql
-- 可选：创建子代码关系表
CREATE TABLE child_codes (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
relation_id BIGINT NOT NULL,
child_code VARCHAR(24) NOT NULL,
FOREIGN KEY (relation_id) REFERENCES code_relations(id)
);
-- 拆分chd字段的SQL
INSERT INTO child_codes (relation_id, child_code)
SELECT
cr.id,
TRIM(REGEXP_SPLIT_TO_TABLE(cr.children, ','))
FROM code_relations cr;
```
本文介绍如何通过引入ExtremeComponents库并配置web.xml来使用ec:table标签，简化JSP页面中表格数据的展示及操作，如排序、分页等功能。
扫一扫
1003
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
