By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Create table FormSchema(
SchemaId varchar(50),
SchemaName varchar(20),
AppSchema varchar(max)
);
Create Table AppRoles(
SchemaId varchar(50),
Role varchar(20),
[create] bit default 0,
[read] bit default 0,
[update] bit default 0,
[delete] bit default 0
);
Insert into FormSchema(SchemaId,SchemaName,AppSchema)
values
('1','Car','{"roles":[{"role":"Support","permissions":{"create":false,"read":true,"update":false,"delete":false}}]}'),
('2','Jeep','{"roles":[]}'),
('3','bus','{}')
3 rows affected
insert into AppRoles(SchemaId,Role,[create],[read],[update],[delete])
values('1','Test','true','true','false','true'),
('2','All','true','true','false','false'),
('3','test2','true','true','true','true');
3 rows affected
SELECT
fs.SchemaId,
fs.SchemaName,
JSON_MODIFY(fs.AppSchema, 'append $.roles', JSON_QUERY(ar.newJson)) AS AppSchema
FROM FormSchema fs
CROSS APPLY (
SELECT (
SELECT Role AS [role],
[create] AS [permissions.create],
[read] AS [permissions.read],
[update] AS [permissions.update],
[delete] AS [permissions.delete]
FROM AppRoles ar
WHERE fs.SchemaId = ar.SchemaId
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
) AS newJson
) ar;
| SchemaId | SchemaName | AppSchema |
|---|---|---|
| 1 | Car | {"roles":[{"role":"Support","permissions":{"create":false,"read":true,"update":false,"delete":false}},{"role":"Test","permissions":{"create":true,"read":true,"update":false,"delete":true}}]} |
| 2 | Jeep | {"roles":[{"role":"All","permissions":{"create":true,"read":true,"update":false,"delete":false}}]} |
| 3 | bus | {"roles":[{"role":"test2","permissions":{"create":true,"read":true,"update":true,"delete":true}}]} |