By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Create Table ApplicationRoles(
roleid nvarchar(50),
schemaid nvarchar(50),
RoleName nvarchar(50),
[create] bit not null default 0,
[read] bit not null default 0,
[update] bit not null default 0,
[delete] bit not null default 0,
)
Create Table commonformsschema(
schemaid nvarchar(50),
Tenant nvarchar(50),
[Schema] nvarchar(max),
schemaname nvarchar(50)
)
insert into ApplicationRoles(roleid,schemaid,rolename,[create],[read],[update],[delete])
values('bcd8d5fa-bdb0-49b4-881e-462eda281819','f9ee994a-0637-4827-9ced-f943e281b46a','Construction Manager Admin','false','false','false','false'),
('c4ad299a-6fe9-4232-8e4f-0abdb43ac84b','f9ee994a-0637-4827-9ced-f943e281b46a','Project Manager Admin','true','true','true','true'),
('e6ac3db5-9a6d-4f1d-9c80-0becdeeb340d','f9ee994a-0637-4827-9ced-f943e281b46a','Read','true','false','false','false');
3 rows affected
insert into commonformsschema(schemaid,tenant,[schema],schemaname)
values('f9ee994a-0637-4827-9ced-f943e281b46a','All','{
"roles": [
{
"role": "Default",
"permissions": {
"read": false,
"create": false,
"update": false,
"delete": false
}
},
{
"role": "Read",
"permissions": {
"read": false,
"create": false,
"update": false,
"delete": false
}
}
]
}','car');
1 rows affected
select *
from
(
select fs1.SchemaId,
----------------------------------------------not working as expected
JSON_MODIFY(
fs1.[Schema],
ISNULL(
N'$.roles[' +
(
SELECT j.[key]
FROM OPENJSON(fs1.[Schema], '$.roles') j
WHERE JSON_VALUE(j.value, '$.role') = ar1.rolename
) COLLATE Latin1_General_BIN2 + N']',
N'append $.roles'
),
JSON_QUERY(
(
SELECT ar1.RoleName AS [role],
ar1.[create] AS [permissions.create],
ar1.[read] AS [permissions.read],
ar1.[update] AS [permissions.update],
ar1.[delete] AS [permissions.delete]
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
)
)
) AS [Schema],
-----------------------------------------------------------------
row_number() over (partition by fs1.schemaName
order by case
when fs1.tenant = 'ALL' then
2
else
1
end,
| SchemaId | Schema | seqnum |
|---|---|---|
| f9ee994a-0637-4827-9ced-f943e281b46a | { "roles": [ { "role": "Default", "permissions": { "read": false, "create": false, "update": false, "delete": false } }, { "role": "Read", "permissions": { "read": false, "create": false, "update": false, "delete": false } } ,{"role":"Construction Manager Admin","permissions":{"create":false,"read":false,"update":false,"delete":false}}] } |
1 |
select * from ApplicationRoles
| roleid | schemaid | RoleName | create | read | update | delete |
|---|---|---|---|---|---|---|
| bcd8d5fa-bdb0-49b4-881e-462eda281819 | f9ee994a-0637-4827-9ced-f943e281b46a | Construction Manager Admin | False | False | False | False |
| c4ad299a-6fe9-4232-8e4f-0abdb43ac84b | f9ee994a-0637-4827-9ced-f943e281b46a | Project Manager Admin | True | True | True | True |
| e6ac3db5-9a6d-4f1d-9c80-0becdeeb340d | f9ee994a-0637-4827-9ced-f943e281b46a | Read | True | False | False | False |
select * from commonformsschema
| schemaid | Tenant | Schema | schemaname |
|---|---|---|---|
| f9ee994a-0637-4827-9ced-f943e281b46a | All | { "roles": [ { "role": "Default", "permissions": { "read": false, "create": false, "update": false, "delete": false } }, { "role": "Read", "permissions": { "read": false, "create": false, "update": false, "delete": false } } ] } |
car |