Trying to read entire complex JSON file into SQL-CodePudding

So I have a complex JSON file that I want to read into a SQL table.
The below lets me get one record by changing the number in $.events1.venue

Declare @JSON varchar(max)
SELECT @JSON=BulkColumn
FROM OPENROWSET (BULK 'C:\test\test.JSON', SINGLE_CLOB) import
SELECT *
FROM OPENJSON (@JSON,'$.events[1].venue')
WITH 
(
[venueName] varchar(20),
[venueId] varchar(20),
[legacyVenueId] varchar(20),
[venueZipCode] varchar(20),
[venueStreet] varchar(50),
[venueCity] varchar(20),
[venueStateCode] varchar(10),
[venueCountryCode] varchar(10)
)

results set

I have found answers that say it will work with cross apply but I can't seem to wrap my head around it because I have to go another level deep after I increase the number

CodePudding user response：

You can specify the path to each projected column in the WITH clause of OPENJSON, so (making some assumptions about the structure of the JSON file), something like:

SELECT *
FROM OPENJSON (@JSON,'$.events') 
WITH 
(
  [venueName]        varchar(20)  '$.venue.venueName',
  [venueId]          varchar(20)  '$.venue.venueId',
  [legacyVenueId]    varchar(20)  '$.venue.legacyVenueId',
  [venueZipCode]     varchar(20)  '$.venue.venueZipCode',
  [venueStreet]      varchar(50)  '$.venue.venueStreet',
  [venueCity]        varchar(20)  '$.venue.venueCity',
  [venueStateCode]   varchar(10)  '$.venue.venueStateCode',
  [venueCountryCode] varchar(10)  '$.venue.venueCountryCode'
)

CodePudding user response：

Should you want a CROSS APPLY version achieving the same thing David's answer does:

    SELECT ven.*
    FROM OPENJSON (@JSON,'$.events') ev
        CROSS APPLY OPENJSON(ev.value,'$.venue')
    WITH 
    (
        [venueName] varchar(20),
        [venueId] varchar(20),
        [legacyVenueId] varchar(20),
        [venueZipCode] varchar(20),
        [venueStreet] varchar(50),
        [venueCity] varchar(20),
        [venueStateCode] varchar(10),
        [venueCountryCode] varchar(10)
    ) ven