Openjson problem?

• tommy 41661

  SSC Veteran

  Points: 274

  More actions

  January 10, 2019 at 7:43 am

  #381173

  I got a problem with some json data, and getting to read it column by column in sql.

  The json
  {
"api": {
  "results": 2,
  "names": {
  "1": {
   "firstname": "Ole",
   "lastname": "Hansen"
  },
  "2": {
   "firstname": "Jesper",
   "lastname": "Hansen"
  },
  }
}
}

  The Sql:
  SELECT *
FROM OPENJSON(@json,'$.api.names')
WITH(
firstname nvarchar(255),
lastname nvarchar(255)
) a

  My problem is that I cant get it output in rows with:
  firstname and lastname in the same row

  What I can do is  do a
  SELECT *
FROM OPENJSON(@json,'$.api.names')
  And then it will return the json data from  from each "object" in a row as json format

  Whats my problem? Can anybody understand what I mean or is it total jibbirish?

• tommy 41661

  SSC Veteran

  Points: 274

  More actions

  January 10, 2019 at 8:22 am

  #2018539

  And if I do like this

  
SELECT *
FROM OPENJSON(@json,'$.api.names."1"')
WITH(
firstname nvarchar(255),
lastname nvarchar(255)
) a

  I can get the first "row" just like I should, but I want all "rows" in the same query?

• debasis.yours

  Hall of Fame

  Points: 3518

  More actions

  January 10, 2019 at 1:42 pm

  #2018624

  Hi Tommy,

  If you can modify the query (along with the JSON) like below, it might help:
  
DECLARE @MyJson VARCHAR(MAX) = '{
"api": {
"results": 2,
"names": [
     {
      "firstname": "Ole",
      "lastname": "Hansen"
     },
     {
      "firstname": "Jesper",
      "lastname": "Hansen"
     }
    ]
}
}'

  SELECT * FROM OPENJSON(@MyJson, '$.api.names')
WITH(
firstname nvarchar(255),
lastname nvarchar(255)
) a


  Is it desired?

  Thanks.

• tommy 41661

  SSC Veteran

  Points: 274

  More actions

  January 10, 2019 at 1:49 pm

  #2018626

  That is the desired results - however, I would love a solution where I didnt have to change the JSON - is that possible?

• debasis.yours

  Hall of Fame

  Points: 3518

  More actions

  January 10, 2019 at 1:55 pm

  #2018628

  I fear that may not be possible as the format that you gave is not valid as you can check below:
  
DECLARE @TestJson VARCHAR(MAX) = '{
"api": {
"results": 2,
"names": {
"1": {
  "firstname": "Ole",
  "lastname": "Hansen"
},
"2": {
  "firstname": "Jesper",
  "lastname": "Hansen"
},
}
}
}'
SELECT ISJSON(@TestJson)


• tommy 41661

  SSC Veteran

  Points: 274

  More actions

  January 10, 2019 at 1:57 pm

  #2018629

  Ahh thats why:) I will go ahead and change the source JSON, thanks for your input and help:)