从上百个字段到1个CLOB：Oracle JSON存储实战指南

欢迎大家来到IT世界,在知识的湖畔探索吧!

陆沉盯着左右两个屏幕上显示的数据格式文档，右手小拇指无意思地一下又一下的敲击着机械键盘的Ctrl键，在清脆的“哒哒”声中思考着。

[又有新的字段设计……]

面前的小屏幕上显示的是公司Oracle数据库里面的目标表，看着上百个字段的表结构，还有历史记录中先辈们留下里面一大堆`ALTER TABLE ADD COLUMN`语句……

[不是吧？不是吧！难道现在还有人不知道关系型数据库也可以存储对象吗……]

陆沉双手放上键盘，快速敲击起来……

建表

Oracle12c以上的版本已经开始支持JSON格式的存储，并且原生带有json操作函数，可以灵活的实现JSON格式校验、JSON数据抽取、甚至将JSON字段值作为查询条件！

只需要定义一个`CLOB`字段，就可以存储JSON了。当然如果想要限定长度，也可以使用`VARCHAR2`。

CREATE TABLE source_raw_data (     id               NUMBER DEFAULT seq_raw_data_id.NEXTVAL,     source           VARCHAR2(50),     raw_data         CLOB,          CONSTRAINT pk_source_raw_data PRIMARY KEY (id),          -- 主键约束     CONSTRAINT ensure_json  CHECK (raw_data IS JSON)       -- JSON格式校验约束 );

欢迎大家来到IT世界,在知识的湖畔探索吧!

在这个表里面陆沉耍了个小心机，用`IS JSON`检查来约束格式，这样做方便后续使用内置的JSON函数而不会出错。

插入

JSON数据本质上就是一段格式化的文本，直接当成文本插入数据表就可以了，不过要小心，如果JSON里面的字段值带有双引号，记得转义。

欢迎大家来到IT世界,在知识的湖畔探索吧!INSERT INTO source_raw_data (source, raw_data)  VALUES (     'source_A',     '{         "NAV_DATE": "2025-03-03",         "FUND": "ABCD",         "FUND_DATA": {             "Data_A": "XXX",             "DATA_B": "YYYY",             "MARKET_A_VALUE": 1234,             "CREATED_AT": "2025-03-03"         },         "TAGS": ["CN", "EA", "PR"]     }' ); INSERT INTO source_raw_data (source, raw_data)  VALUES (     'source_B',     '{         "VALUE_DATE": "2025-03-03",         "FUND_NAME": "ABCD",         "FUND_DATA": {             "FUND_DATA_C": "XXX",             "MARKET_C_VALUE": "1234",             "CREATED_AT": "2025-03-13"         },         "TAGS": ["UK", "RD", "PR"]     }' );

看着测试数据稳稳落入表中，陆沉扶了扶左眼的防蓝光单片眼镜，在技术文档中敲下新的章节标题。

查询

可以直接使用JSON路径进行查询，用`

作为路径的开头！

– 在查询结果中直接显示出JSON的特定字段值

SELECT      JSON_VALUE(raw_data, '$.FUND_DATA.MARKET_A_VALUE') AS A_VALUE  -- 类型自动转换     JSON_VALUE(raw_data, '$.FUND_DATA.CREATED_AT' RETURNING DATE) AS CREATED FROM source_raw_data;

陆沉还细心地在设计文档中写道：`JSON_VALUE`是Oracle的内置函数，可以解析JSON格式的文本，通过路径直接抽取字段值，并且可以在函数中通过`RETUNING`关键字直接转换为目标格式，比如范例中的时间格式。

– 将JSON中的字段作为查询条件

欢迎大家来到IT世界,在知识的湖畔探索吧!SELECT *  FROM source_raw_data  WHERE JSON_VALUE(raw_data, '$.FUND') = 'ABCD';  -- 支持复杂条件

同样是使用`JSON_VALUE`函数解析后查询。

肯定会有人会找茬，这样查询性能会有问题吧？

想到这里，陆沉嘴角微微一翘……

– 解决性能陷阱，给JSON字段创建索引

CREATE INDEX idx_source_fund_created  ON source_raw_data (     JSON_VALUE(raw_data, '$.FUND_DATA.CREATED_AT') );  -- 给JSON字段上索引

当然如果还有人一定要like来查询JSON文本，只能说尊重他人命运吧。

– 特殊类型：JSON字段中的数组查询

欢迎大家来到IT世界,在知识的湖畔探索吧!SELECT      id,      JSON_QUERY(raw_data, '$.TAGS') AS tags  -- 提取TAGS数组字段 FROM source_raw_data WHERE      JSON_EXISTS(raw_data, '$.TAGS?(@ == "PR")');  -- 过滤包含"PR"的记录

这里用到的 `JSON_QUERY` 可以将`raw_data`字段中的 `TAGS` 数组的全部内容提取出来，查询结果的格式大概是这样： `[“UK”, “RD”, “PR”]`。

其中 `@` 符号是一种遍历操作的声明，代表要对数组元素进行遍历访问的操作。

– 高级解析：将JSON转成临时表

SELECT      jt.nav_date,      jt.fund,      jt.data_a,      jt.data_b,      jt.market_a_value,      jt.created_at FROM      source_raw_data s,     JSON_TABLE(         s.raw_data,          '#39;          COLUMNS (             nav_date         VARCHAR2(10) PATH '$.NAV_DATE' NULL ON EMPTY,             fund             VARCHAR2(10) PATH '$.FUND' NULL ON EMPTY,             data_a           VARCHAR2(10) PATH '$.FUND_DATA.Data_A' NULL ON EMPTY,             data_b           VARCHAR2(10) PATH '$.FUND_DATA.DATA_B' NULL ON EMPTY,             market_a_value   NUMBER       PATH '$.FUND_DATA.MARKET_A_VALUE' NULL ON EMPTY,             created_at       VARCHAR2(10) PATH '$.FUND_DATA.CREATED_AT' NULL ON EMPTY         )     ) jt WHERE      s.source = 'source_A';

JSON_TABLE也是Oracle内置函数，可以用来将JSON数据映射为临时表，进而可以执行更加复杂的查询操作。

在每个字段后面加上 `NULL ON EMPTY` 是为了避免字段确实的时候查询会报错。

– 更新操作

没错，我们可以直接更新JSON数据中的某个字段！

欢迎大家来到IT世界,在知识的湖畔探索吧!UPDATE source_raw_data SET raw_data = JSON_MERGEPATCH(     raw_data,     '{"nav_date": "2025-03-12"}' ) WHERE id = 2;

– 路径大小写敏感

陆沉在自己写的范例中，埋藏了个JSON操作中要严格区分大小写的彩蛋……

---

屏幕上弹出技术文档保存成功的提示，陆沉右眼闪过一丝红光，随手切出黑色的IDEA编辑器，打开那个陈年的Java项目……

MyBatis Plus的隐藏技能——自动JSON转换！

通过 MyBatisPlus 的 `@TableName(autoResultMap=true)` 开启自动结果集映射，配合 `@TableField` 注解的 `typeHandler` 属性，可以实现：

– 写入时自动将对象序列化为JSON字符串

– 读取时自动将JSON反序列化为对象

– 完全注解式开发，零XML配置

实现步骤

1. 实体类配置

@TableName(autoResultMap = true) public class SourceRawData {     @TableField(typeHandler = JsonTypeHandler.class)     private RawData rawData;  //  自动序列化/反序列化 }

注意这里使用了自动创建 ResultMap 映射关系，避免手动编写XML配置，同时JSON字段自动反序列化功能，需要依赖此特性实现。

2. 自定义 `TypeHandler`（只在需要特殊处理逻辑的时候添加，否则可以直接使用原生的JsonTypeHandler）

欢迎大家来到IT世界,在知识的湖畔探索吧!public class SpecialJsonTypeHandler extends BaseTypeHandler<Object> {     private static final ObjectMapper mapper = new ObjectMapper();     @Override     public void setNonNullParameter(...) {  // 写入时转JSON         ps.setString(i, mapper.writeValueAsString(parameter));     }     @Override     public Object getNullableResult(...) {  // 读取时转对象         return mapper.readValue(json, returnType);     } }

3. 高级配置（如有自定义的TypeHandler，需要这一步）

// 注册TypeHandler到 MyBatis 配置 @Configuration public class MybatisConfig {     @Bean     public ConfigurationCustomizer configurationCustomizer() {         return configuration ->              configuration.getTypeHandlerRegistry()                              .register(SpecialJsonTypeHandler.class);     } }

踩坑记录

• 类型匹配问题：如果有自己写的`TypeHandler`，一定要确保TypeHandler 处理的Java类型与字段类型一致。

• 空值处理：如果害怕出现空值，可以使用 `@TableField(updateStrategy = FieldStrategy.IGNORED)`。

• 日期格式：统一配置ObjectMapper的日期序列化格式，默认的 `ObjectMapper` 用的可是`UTC+0` 时区。

• 存储占用过高：格式化的JSON文本有很多冗余的空格，可以尝试对CLOB字段添加压缩 `ALTER TABLE source_raw_data MODIFY raw_data CLOB COMPRESS;`

---

凌晨两点半，陆沉跑完最后一个JSON存储与读取测试用例，一口喝光那罐标着[3.21MB算力]的黑牛。

忽然电脑桌面上弹出架构团队的通知：[经公司董事会批准，公司将升级底层数据库为Oracle21c+。]

伴随着右眼中的电子义眼散发出的红光，陆沉在技术文档的最后一段添加上一句话：构建新表时，强制使用Oracle原生 `JSON` 数据类型。

[哼，在我写出这句话之前的都算是旧表，无需遵守这个约定……]

欢迎大家来到IT世界,在知识的湖畔探索吧!