By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
declare @library table (dependencies nvarchar(max),email nvarchar(max),alert varchar(25),skipped varchar(25) )
insert into @library values('{"jar":"azure.com","maven":"azure.com","maven":"azure.com"}','abc@mail.com,adv@mail.com','true','false')
SELECT
JSON_QUERY((
SELECT
'[' + STRING_AGG(
'{"' +
STRING_ESCAPE(j.[key], 'json') +
'":"' +
STRING_ESCAPE(j.value, 'json') +
'"}',
','
) + ']'
FROM OPENJSON(l.dependencies) j
)) as libraries,
JSON_QUERY((
SELECT '[' + STRING_AGG('"' + STRING_ESCAPE(value, 'json') + '"', ',') + ']'
FROM STRING_SPLIT(l.email, ',')
)) as [email_test.email],
CASE WHEN l.alert = 'true' THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END as [email_test.alert],
CASE WHEN l.skipped = 'true' THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END as [email_test.skipped]
FROM @library l
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;
| JSON_F52E2B61-18A1-11d1-B105-00805F49916B |
|---|
| {"libraries":[{"jar":"azure.com"},{"maven":"azure.com"},{"maven":"azure.com"}],"email_test":{"email":["abc@mail.com","adv@mail.com"],"alert":true,"skipped":false}} |