In my database, I have table called library which have columns like dependencies, email, alert, skipped and values are stored as

declare @library table (dependencies varchar(max),email varchar(max),alert varchar(25),skipped varchar(25) )

insert into @library values('{"jar":"azure.com","maven":"azure.com","maven":"azure.com"}','[email protected],[email protected]',true,false)

I want to prepare JSON of these values which will look like as

{
 "libraries":[
    {
      "jar" : { "coordinates":"azure.com"}
    },
    {
      "maven" : {"coordinates":"azure.com" }
    },
    {
      "maven" : {"coordinates":"azure.com" }
    }
 ],
 "email_test":{
   "email": [
     "[email protected]",
     "[email protected]"
    ],
   "alert" : true,
   "skipped": false
 }
}

I have written have code as

select  (select  dependencies as [maven.coordinates] for json path) as libraries,
        (select email from library for json auto) as [email_test.email],
        alert as [email_test.alert],
        skipped as [email_test.skipped]
from library for json path, WITHOUT_ARRAY_WRAPPER

And my JSON doesn't look like desired JSON

 {
   "libraries":[
      {
         "maven":{
            "coordinates":"{"jar":"azure.com","maven":"azure.com"}"
         }
      }
   ],
   "email_test":{
      "email":[
         {
            "email":"[email protected] , [email protected]"
         }
      ],
      "alert":"true",
      "skipped":"false"
   }
}

I am not able to achieve correct json . Can someboby please help me out here ?

Thank you

CodePudding user response：

There are a number of problems with your existing query:

• dependencies contains the whole coordinates data, you can't just select the whole thing. Instead you need break it out and rebuild it. You can use OPENJSON as I have, or JSON_VALUE
• email is not valid JSON, it is a comma-separated list. Unfortunately, SQL Server does not have JSON_AGG which would have made this simpler. So break it open using STRING_SPLIT and rebuild using a combination of STRING_ESCAPE (to escape any problematic characters) and STRING_AGG (to aggregate it again) and JSON_QUERY (to prevent double-escaping).
• true and false appear to be varchar, you need a bit value instead

SELECT
  (
      select
        j.jar as [jar.coordinates],
        j.maven as [maven.coordinates]
      FROM OPENJSON(l.dependencies)
        WITH (
          jar varchar(100),
          maven varchar(100)
        ) j
      FOR JSON PATH
  ) 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;

db<>fiddle