CODEWITHSUNDEEP
sqljsonsql-server
franklin
franklin

Reputation: 23

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

I use SQL Server and I need to move "setting0" and "setting1" from one JSON value into a new JSON value in a new record;

Here is the table structure:

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

and I need to get this result:

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

Update:

There are a lot of settings in JSON value with type0 and due to business logic I need to separate only setting0 and setting1 from type0 and move it to type1.

Many thanks in advance

Upvotes: 2

Views: 325

Answers (3)

Zhorov
Zhorov

Reputation: 29993

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}

Upvotes: 2

SteveC
SteveC

Reputation: 6015

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}

Upvotes: 0

EinLinuus
EinLinuus

Reputation: 675

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?

Upvotes: 0

Related Questions