如何在 SQL 中将 json 属性从一个记录值移动到一个新记录值

Question

我使用 SQL Server，我需要将"setting0"和"setting1"从一个 JSON 值移动到新记录中的新 JSON 值;

以下是表结构：

userId | setting type | settings
-------+--------------+-----------------------------------------------
id0    | type0        | {"setting0": 0, "setting1": 1, "setting2": 2, "...", "setting100": 100}

我需要得到这个结果：

userId | setting type | settings
-------+--------------+---------------------------------
id0    | type0        | {"setting2": 2, "...", "setting100": 100}
id0    | type1        | {"setting0": 0, "setting1": 1}

更新：

type0的 JSON 值中有很多设置，由于业务逻辑，我只需要将setting0和setting1与type0分开，并将其移动到type1。

提前非常感谢

Answer 1

另一种可能的方法是使用VALUES表值构造函数和：

• OPENJSON()显式架构，以使用适当的数据类型从现有 JSON 数据中提取$.setting0和$.setting1键\值对，FOR JSON将提取的值输出为 JSON。
• JSON_MODIFY()NULL作为值以删除$.setting0和$.setting1键。

桌子：

CREATE TABLE Data (
userID varchar(3),
settingType varchar(5),
settings varchar(1000)
)
INSERT INTO Data (userID, settingType, settings) 
VALUES
('id0', 'type0', '{"setting0":0,"setting1":1,"setting2":2,"setting3":3}'),
('id1', 'type0', '{"setting0":0,"setting1":1,"setting2":2,"setting3":3,"setting4":4}')

陈述：

SELECT d.userID, v.settingType, v.settings
FROM Data d
CROSS APPLY (VALUES 
-- Existing row
(
d.settingType, 
JSON_MODIFY(JSON_MODIFY(d.settings, '$.setting0', NULL), '$.setting1', NULL)
),
-- New row
(
'type1', 
(
SELECT setting0, setting1
FROM OPENJSON(d.settings) WITH (
setting0 int '$.setting0',
setting1 int '$.setting1'
)
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
)
)
) v (settingType, settings)

结果：

userID settingType settings
-----------------------------------------------------------
id0    type0       {"setting2":2,"setting3":3}
id0    type1       {"setting0":0,"setting1":1}
id1    type0       {"setting2":2,"setting3":3,"setting4":4}
id1    type1       {"setting0":0,"setting1":1}

Answer 2

您必须读取要从中获取数据的表行，删除要移动的代码，更新该行并将已删除的数据插入到新行中。

请注意，如果您没有提供有关问题的更多详细信息，则不会获得有关如何执行此操作的任何详细信息。

• 拆分数据的规则是什么？
有什么
• setting_type？

Answer 3

下面返回正确的输出。 首先，对于每个用户 ID，它返回 'setting2'(如果存在)作为 JSON。 其次，对于每个用户 ID，它会修改 JSON 以删除"setting2"(如果存在)。 像这样的东西

数据

drop table if exists #JsonSettings;
go
create table #JsonSettings(
userID        varchar(12) not null,
settingType   varchar(12) not null,
settings      nvarchar(max));
insert #JsonSettings(userID, settingType, settings) values
('id0', 'type0', N'{"setting0": 0, "setting1": 1, "setting2": 2}'),
('id2', 'type0', N'{"setting0": 0, "setting1": 1, "setting2": 2, "setting3": 3}');

查询

select distinct userId, settingType, 
(select json_value(settings, '$.setting2') setting2 
for json path, without_array_wrapper) settings
from #JsonSettings js
cross apply openjson(js.settings) oj
where oj.[key]=N'setting2'
union all
select distinct userId, settingType,
json_modify(js.settings,'$.setting2', null) settings
from #JsonSettings js
cross apply openjson(js.settings) oj
where oj.[key]=N'setting2'
order by 1, 3 desc;

输出

userId  settingType settings
id0     type0       {"setting2":"2"}
id0     type0       {"setting0": 0, "setting1": 1}
id2     type0       {"setting2":"2"}
id2     type0       {"setting0": 0, "setting1": 1, "setting3": 3}