I am working with JSON that has been inserted into a SQL table and I have been trying to expand the dataset. So far I have been unable to expand beyond a single.
The data looks like this in the database. A single record with JSON.
I have been able to expand the data with the following query:
DECLARE @json NVARCHAR(MAX);
SET @json = (Select [JSON] FROM TableLocation)
SELECT *
FROM OPENJSON (@json)
I have confirmed that all the records are there, however, I haven't been able to expand it beyond this level. Most of the documentation I have found online doesn't reference if the hierarchy is blank. Any assistance would be great.
I have tried reference the ID column (or any other columns), however if I do I get a column of nulls.
DECLARE @json NVARCHAR(MAX);
SET @json = (Select [JSON] FROM TableLocation)
SELECT *
FROM OPENJSON (@json)
WITH (
ID nvarchar(4000) '$.id')
