CODEWITHSUNDEEP
sqljsonsql-servert-sql
cj devin
cj devin

Reputation: 1375

Join a query with CROSS APPLY FOR JSON PATH

I have two SQL Server tables ApplicationRoles and commonformsschema. Applying a join between both table based on SchemaId column.

But FOR JSON PATH column name as roles results getting wrong.

Here is the select query:

SELECT
    fs1.SchemaId, JSON_QUERY(ar.newJson) as roles 
FROM
    commonformsschema fs1 
JOIN 
    ApplicationRoles ar1 on ar1.SchemaId = fs1.SchemaId 
CROSS APPLY 
    (SELECT 
         (SELECT 
              RoleName AS [role], 
              [create] AS [permissions.create], 
              [read] AS [permissions.read],
              [update] AS [permissions.update],
              [delete] AS [permissions.delete] 
          FROM 
              ApplicationRoles ar 
          WHERE 
              fs1.SchemaId = ar1.SchemaId 
          FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS newJson) ar 
WHERE
    fs1.Tenant IN ('test', 'All') 
    AND ar1.RoleName IN ('Project Manager', 'Read')

For the full table details,input please see the db fiddler.

Current result:

SchemaId roles
7a9a2521-45fb-4c9d-b7d9-e41927118119 {"role":"Senior Construction Manager","permissions":{"create":true,"read":true,"update":true,"delete":true}}
ca5d8707-fee8-4a5b-ad61-813785e32575 {"role":"Senior Construction Manager","permissions":{"create":true,"read":true,"update":true,"delete":true}}

Expected result:

SchemaId roles
7a9a2521-45fb-4c9d-b7d9-e41927118119 {"role":"Project Manager","permissions":{"create":true,"read":true,"update":false,"delete":true}}
ca5d8707-fee8-4a5b-ad61-813785e32575 {"role":"Project Manager","permissions":{"create":true,"read":true,"update":false,"delete":true}}

How to solve this issue? Please any help. Any other detail required please let me know.

Thanks in advance.

Upvotes: 0

Views: 769

Answers (1)

Zhorov
Zhorov

Reputation: 29943

You simply need to JOIN the tables and generate the expected JSON for each row:

SELECT 
   fs1.SchemaId,
   (
   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 roles
FROM commonformsschema fs1 
JOIN ApplicationRoles ar1 ON ar1.SchemaId = fs1.SchemaId 
WHERE
   fs1.Tenant IN ('test', 'All') AND 
   ar1.RoleName IN ('Project Manager', 'Read')

Results:

SchemaId roles
7a9a2521-45fb-4c9d-b7d9-e41927118119 {"role":"Project Manager","permissions":{"create":true,"read":true,"update":false,"delete":true}}
ca5d8707-fee8-4a5b-ad61-813785e32575 {"role":"Project Manager","permissions":{"create":true,"read":true,"update":false,"delete":true}}

Upvotes: 1

Related Questions