Select json as table in sql server

 In this article we learn how we can select json as table in sql server.

declare @json varchar(max)

set @json='[

 { "id" : 2,"firstName": "John", "lastName": "Smith",

   "age": 25, "dateOfBirth": "2007-03-25T12:00:00" },

 { "id" : 5,"firstName": "John", "lastName": "Smith",

   "age": 35, "dateOfBirth": "2005-11-04T12:00:00" },

 { "id" : 7,"firstName": "John", "lastName": "Smith",

   "age": 15, "dateOfBirth": "1983-10-28T12:00:00" },

 { "id" : 8,"firstName": "John", "lastName": "Smith",

   "age": 12, "dateOfBirth": "1995-07-05T12:00:00" },

 { "id" : 9,"firstName": "John", "lastName": "Smith",

   "age": 37, "dateOfBirth": "2015-03-25T12:00:00" }

]'

SELECT *

FROM OPENJSON(@json)

     WITH (id int, firstName nvarchar(50), lastName nvarchar(50),

           age int, dateOfBirth datetime2)