达梦 Json函数SQL 直接更换字段存储的Json值

收心

修改于 2025-12-03 10:42:08

1.2K0

文章被收录于专栏：Java实战博客Java实战博客

以下是一个达梦数据库（DM V8）中关于 JSON 字段的 SQL 示例，包括数据脱敏、更新 JSON 字段、删除 JSON 键与值、查询 JSON 中的一个键，以及一些日常使用的示例。

其他成熟的数据库一般也支持类似的Json函数。自己找GPT处理下即可。

本文涉及一些函数

JSON_VALUE：https://eco.dameng.com/document/dm/zh-cn/pm/json#18.2.1.2%20json_value

JSON_QUERY：https://eco.dameng.com/document/dm/zh-cn/pm/json#18.2.1.3%20json_query

Demo前置条件

1、创建自己的数据表，以及初始化数据

CREATE TABLE "USER_DATA"
(
"ID" VARCHAR(50) NOT NULL,
"USER_INFO" VARCHAR(500),
NOT CLUSTER PRIMARY KEY("ID")
)  ;

COMMENT ON TABLE "USER_DATA" IS '测试达梦Json函数';
COMMENT ON COLUMN "USER_DATA"."ID" IS '用户ID';
COMMENT ON COLUMN "USER_DATA"."USER_INFO" IS '用户信息，Json形式存储';

INSERT INTO "USER_DATA" ("ID","USER_INFO" ) VALUES ('1','{"name":"张三","age":10,"email":"abc@qq.com","address":"北京市海淀区XX街道"}');
INSERT INTO "USER_DATA" ("ID","USER_INFO" ) VALUES ('2','{"name":"李四","age":18,"email":"abc@qq.com","address":"西安市XX区XX街道"}');

UPDATE "USER_DATA" SET "ID" = '1',"USER_INFO" = '{"name":"张三","age":10,"email":"abc@qq.com","address":"北京市海淀区XX街道"}' WHERE "ID" = '1';
UPDATE "USER_DATA" SET "ID" = '2',"USER_INFO" = '{"name":"李四","age":18,"email":"abc@qq.com","address":"西安市XX区XX街道"}' WHERE "ID" = '2';

1. 数据脱敏示例

假设你的表名为 USER_DATA，有一个 JSON 字段 USER_INFO，

{
    "name": "张三",
    "age": 10,
    "email": "abc@qq.com",
    "address": "北京市海淀区XX街道"
}

其中包含用户的敏感信息。我们将对 email 字段进行脱敏处理。

UPDATE USER_DATA
SET USER_INFO = JSON_SET(USER_INFO, '$.email', '****@example.com')
WHERE USER_INFO IS NOT NULL;

2. 更新 JSON 字段示例

假设你想在 JSON 字段 USER_INFO 中添加一个新的键 lastLogin，并设置其值为当前时间。

UPDATE USER_DATA
SET USER_INFO = JSON_SET(USER_INFO, '$.lastLogin', CURRENT_TIMESTAMP)
WHERE USER_INFO IS NOT NULL;

3. JSON_REMOVE 删除 JSON 键与值示例

狗日的达梦，竟然在文档里面，没这个函数！实际可以使用

如果你想删除 address 键及其对应的值，可以使用以下 SQL：

UPDATE USER_DATA
SET USER_INFO = JSON_REMOVE(USER_INFO, '$.address')
WHERE USER_INFO IS NOT NULL;

4. 查询 JSON 中的一个键示例

如果你想查询 JSON 字段 USER_INFO 中的 name 值，可以使用以下 SQL：

SELECT JSON_VALUE(USER_INFO, '$.name') AS username
FROM USER_DATA
WHERE USER_INFO IS NOT NULL;

5. 日常使用示例

查询所有用户的敏感信息（脱敏后）

SELECT 
    JSON_VALUE(USER_INFO, '$.name') AS name,
    JSON_VALUE(USER_INFO, '$.email') AS email, -- 这里的 email 已经脱敏
    JSON_VALUE(USER_INFO, '$.lastLogin') AS lastLogin
FROM USER_DATA
WHERE USER_INFO IS NOT NULL;

统计用户数量

SELECT COUNT(*) AS user_count
FROM USER_DATA
WHERE USER_INFO IS NOT NULL;

总结

数据脱敏：通过更新 JSON 字段来隐藏敏感信息。
更新 JSON 字段：可以添加新键或更新现有键。
删除 JSON 键：可以使用 JSON_REMOVE 删除不需要的键。
查询 JSON 键：使用 JSON_VALUE 提取特定的值。

JsonQuery

这段比较特殊，我特别记录一下

JSON_QUERY：https://eco.dameng.com/document/dm/zh-cn/pm/json#18.2.1.3%20json_query

SELECT
	JSON_QUERY('[{"name":"1","value":"1"},{"name":"2","value":"2"}]', '$.value' WITH WRAPPER)
FROM
	dual;

真实使用JSON_QUERY

SELECT
	JSON_QUERY('[{"food":[{"name":"西瓜"},{"name":"东瓜"}]}]', '$.food.name' WITH WRAPPER) AS "仅第一项查找",
    JSON_QUERY('[{"food":[{"name":"西瓜"},{"name":"东瓜"}]},{"food":[{"name":"南瓜"},{"name":"北瓜"}]}]', '$.food[*].name' WITH WRAPPER) AS "递归查找(建议最后一级的上游每一层级都添加[*])"
FROM
	DUAL