How to move json property from one record value into a new one in SQL

Another possible approach is to use VALUES table value constructor and:

• OPENJSON() with explicit schema to extract the $.setting0 and $.setting1 key\value pairs from the existing JSON data with the appropriate data types and FOR JSON to output the extracted values as JSON.
• JSON_MODIFY() with NULL as value to delete the $.setting0 and $.setting1 keys.

Table:

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}')

Statement:

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)

Result:

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}

You have to read the table row you want to get the data from, remove the code you want to move, update the row and insert the removed data into a new row.

Note that you're not getting any detailed information about how to do this if you're not giving more detailed information about your problem.

• What's the rule for splitting the data?
• What's the setting_type?

The following returns the correct output. First, for each userID it returns 'setting2' (if it exists) as JSON. Second, for each userID it modifies the JSON to remove 'setting2' (if it exists). Something like this

Data

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}');

Query

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;

Output

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}