SQL OpenJson - Cannot create a column for json inside of json

Question

I've been playing around with OPENJSON in sql and have a question.

Say I have the following JSON,

[
 { "id" : 2,"firstName": "John", "lastName": "Smith",
   "age": 25, "dateOfBirth": "2007-03-25T12:00:00", "data":{"$source":"Online"} },
]

I am able to use OPENJSON to create columns on the fly for all apart from source.

Here is my code:

  SELECT *
  FROM OPENJSON(@json)
  WITH (id int, firstName nvarchar(50), lastName nvarchar(50),
        age int, dateOfBirth datetime2, [$Source] varchar(50))

I am getting every column back apart from the nested json data.

Feel free to edit the question title

Answer 1

Not quite sure why your source starts with '$'. if you want to get data from nested json, you need to identity the roots for the column. for example

DECLARE @JSON NVARCHAR(max) = 
N'[
 { "id" : 2,"firstName": "John", "lastName": "Smith",
   "age": 25, "dateOfBirth": "2007-03-25T12:00:00", "data":{"source":"Online"} }
]'

SELECT *
  FROM OPENJSON(@json)
  WITH (id int, firstName nvarchar(50), lastName nvarchar(50),
        age int, dateOfBirth datetime2, 
        source VARCHAR(50) '$.data.source',
        data nvarchar(max) AS JSON)