SQL- Merging json with a node name

Question

I have a table with json vaules in nvarchar columns

ID    ConfigKey     ConfigValue
1     employee      {"name":"jhon","age":"23","salary":"5000"}
2     Dept          {"name": "Marketing", "code":"12", "manager":"sam"}
3     manager       {"managername":"abc", "dept":"AB"}

I need to get merged json for selected ConfiKey by adding ConfigKey value as node name for the ConfigValue and also merged json as below.

For example when i want to get for employee and manager. Output should be like

{
 "employee": {
   "name": "jhon",
   "age": "23",
   "salary": "5000"
 },
 "manager": {
   "managername": "abc",
   "dept": "AB"
 }
}

When I want to get for only ConfigKey value employee output should be

{
 "employee": {
   "name": "jhon",
   "age": "23",
   "salary": "5000"
 }
}

I tried using JSON_MODIFY but couldn't achieve this. How I can achieve this in SQL query.

user_390122 · Answer

You can do this with PIVOT and JSON_QUERY but the code will quickly get messy as you add more ConfigKey rows...

/*
 * Setup example type, table and data...
 */
create type ConfigKeyTableType as table (
  ConfigKey nvarchar(10)
);

create table dbo.Example (
  ID int,
  ConfigKey nvarchar(10),
  ConfigValue nvarchar(max)
);

insert dbo.Example (ID, ConfigKey, ConfigValue) values
  (1, N'employee', N'{"name":"jhon","age":"23","salary":"5000"}'),
  (2, N'Dept', N'{"name": "Marketing", "code":"12", "manager":"sam"}'),
  (3, N'manager', N'{"managername":"abc", "dept":"AB"}');
go
/*
 * Stored procedure to return the JSON based on the supplied ConfigKey values...
 */
create procedure dbo.usp_GetConfigJSON (
  @ConfigKeys ConfigKeyTableType readonly
)
as
begin
  set nocount on;
  select
    json_query(dept) as [dept],
    json_query(employee) as [employee],
    json_query(manager) as [manager]
  from (
    select Ex.ConfigKey, Ex.ConfigValue
    from dbo.Example Ex
    join @ConfigKeys keys on keys.ConfigKey=Ex.ConfigKey
  ) src
  pivot (max(ConfigValue) for ConfigKey in ([dept], [employee], [manager])) pvt
  for json auto, without_array_wrapper;
end
go

To invoke it from SQL...

declare @ConfigKeys ConfigKeyTableType;
insert @ConfigKeys (ConfigKey) values
  (N'employee'),
  (N'manager');
exec dbo.usp_GetConfigJSON @ConfigKeys;
go
declare @ConfigKeys ConfigKeyTableType;
insert @ConfigKeys (ConfigKey) values
  (N'employee');
exec dbo.usp_GetConfigJSON @ConfigKeys;
go

Which yields the results...

{
    "employee": {
        "name": "jhon",
        "age": "23",
        "salary": "5000"
    },
    "manager": {
        "managername": "abc",
        "dept": "AB"
    }
}

...and...

{
    "employee": {
        "name": "jhon",
        "age": "23",
        "salary": "5000"
    }
}

SQL- Merging json with a node name

1 Answers