How to use OPENJSON if column is same in SQL?

Question

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

Do ensure that's *really* your input and it cannot be changed on the producing end, because duplicate property names are a bad idea (the standard doesn't outright forbid it, but notes the behavior of implementations on receiving it is not consistent). You'd normally expect an array of values, as per your output, but not your input. This would typically indicate a fault in the process generating this output (it could easily happen if the output is manually generated rather than serialized, for example). — Jeroen Mostert, Mar 28 '22 at 16:04

Charlieface · Accepted Answer · 2022-03-29T16:02:00.740

1

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

edited Mar 29 '22 at 16:02

answered Mar 28 '22 at 15:59

Charlieface

52,284
6
19
43

If I have null , so It is not working. Then what should we do ? i want to make it more generic – blac040 Mar 29 '22 at 15:22
1

You can use `CONCAT` instead, which ignores nulls – Charlieface Mar 29 '22 at 15:30
But If values are null, then i don't that property to include in my JSON. Like I tested, with null values, It is not property null. But I don't want that property in json if null – blac040 Mar 29 '22 at 15:35
1

OK then use the original `+`, it works fine for me? https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=28e68dd5bd47130937cdbca589566369 Can you construct a fiddle showing it not working and showing what you would like to see – Charlieface Mar 29 '22 at 15:37
Okay got it. Its working now – blac040 Mar 29 '22 at 15:39

How to use OPENJSON if column is same in SQL?

1 Answers1