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

JoshuaG · Accepted Answer · 2021-02-03 16:24:55Z

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)

answered Feb 3, 2021 at 16:24

JoshuaG

4003 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Greg Over a year ago

Thank you; It was just the way I was setting up constants in another bits of my project. I used ` source VARCHAR(50) '$.data."$source"',`

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related