从上百个字段到1个CLOB:Oracle 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中的字段作为查询条件

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字段中的数组查询

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数据中的某个字段！

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）

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` 数据类型。

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