How to use OPENJSON if column is same in SQL?-CodePudding

My database table is created as :

declare @library table (dependencies varchar(max))

insert into @library values('{"jar":"azure.com","maven":"azure.com","maven":"azure.com","maven":"azurebook.com"}')

I am trying to have JSON as

{
 "libraries":[
    {
      "jar" : { "coordinates":"azure.com"}
    },
    {
      "maven" : {"coordinates":"azure.com" }
    },
    {
      "maven" : {"coordinates":"azurebook.com" }
    }
 ]
}

And my SQL code looks like

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
  
FROM @library l
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

And my output which is not same as desired json

 {
 "libraries":[
    {
      "jar" : { "coordinates":"azure.com"}
    },
    {
      "maven" : {"coordinates":"azure.com" }
    }
 ]
}

As in my output, my other property or column maven does not including. Can somebody help me out?

Thank you

CodePudding user response：

You can't do this using OPENJSON with a schema, because JSON normally expects only unique property names. You can use OPENJSON without a schema, but you also cannot use FOR JSON to create such a JSON value. You would need STRING_AGG again

SELECT
  JSON_QUERY((
      SELECT
        '['   STRING_AGG(
          '{"'  
          STRING_ESCAPE(j.[key], 'json')  
          '":"'  
          STRING_ESCAPE(j.value, 'json')  
          '"}',
          ','
        )   ']'
      FROM OPENJSON(l.dependencies) j
  )) as libraries
FROM @library l
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

db<>fiddle