SQL Server 2019

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":[]}')
2 rows affected

insert into AppRoles(SchemaId,Role,[create],[read],[update],[delete])
values('1','Test','true','true','false','true'),
('2','All','true','true','false','false');
2 rows affected

select fs.SchemaId,fs.SchemaName,JSON_MODIFY(fs.AppSchema,
'$.roles[1]','{"role":"Test","permissions":{"create":true,"read":true,"update":false,"delete":true}}')
as AppSchema
from FormSchema fs
inner join
AppRoles ar
on
fs.SchemaId=ar.SchemaId




SchemaId
SchemaName
AppSchema


1
Car
{"roles":[{"role":"Support","permissions":{"create":false,"read":true,"update":false,"delete":false}}]}

2
Jeep
{"roles":[]}

SchemaId	SchemaName	AppSchema
1	Car	{"roles":[{"role":"Support","permissions":{"create":false,"read":true,"update":false,"delete":false}}]}
2	Jeep	{"roles":[]}