CODEWITHSUNDEEP
sqlsql-serverjson
tjg92
tjg92

Reputation: 197

Turning JSON into a Table

{
    "ID":1,
    "KEY1":"",
    "ARRAYKEY1":[
        {
             "ARRAYKEY":""
             "OBJECTKEY":{
                 "OBJ":""
             }
        }
    ]
}

Above is a JSON object I have stored in the database and I want to use OPENJSON to create a tabular format. I know I can do the whole OPENJSON WITH ( ) and manually create all the columns. I wanted to know if anyone has any idea of how to recursively or programatically create the table without having to identity each key for the table. I could end up having a couple dozen fields. I'm not worried about excluding any of the items. If I wanted to pull all the records back, where could I even begin with the new 2016 SQL Server?

Upvotes: 0

Views: 91

Answers (1)

harshil9968
harshil9968

Reputation: 3244

Here is the official Documentation.

SET @json =  
N'[  
      { "id" : 2,"info": { "name": "John", "surname": "Smith" }, "age": 25 },  
      { "id" : 5,"info": { "name": "Jane", "surname": "Smith" }, "dob": "2005-11-04T12:00:00" }  
]'  

SELECT *  
FROM OPENJSON(@json)  
 WITH (id int 'strict $.id',  
       firstName nvarchar(50) '$.info.name', lastName nvarchar(50) '$.info.surname',  
       age int, dateOfBirth datetime2 '$.dob')

Upvotes: 2

Related Questions