I am attempting to import JSON into my db and I'm getting stuck on a few nested objects. In this particular JSON fields are omitted when there isn't a value for that particular record, and that includes the nested objects. However, in some cases there is more than one nested object and I only want to grab a specific one. How do I accomplish this?
This is a sample of the file with the multiple nested objects. I am only interested in the outer layer of data and the PrimaryObject from the secondary data.
This is how I'm handling the import:
declare @json nvarchar(max)
declare @command nvarchar(1000)
set @command = N'select @json1 = BulkColumn from openrowset (bulk ''' + @json_file + ''', single_clob) as j'
exec sp_executesql @command, N'@json1 nvarchar(max) output', @json1 = @json output
insert into ProjectVicData(MediaId, PrimaryObjectValue)
select MediaID, PrimaryObjectValue
from openjson(@json, '$.value')
with (MediaID int '$.MediaID', SecondaryData nvarchar(max) as JSON) x
cross apply openjson (x.SecondaryData)
with (PrimaryObjectValue nvarchar(max) '$.FieldValue')
The import works but a second record gets inserted when the second object is present in the SecondaryData section.