Skip to content
Advertisement

Complex multi-level JSON and SQL – what am I doing wrong?

So I am able to query my JSON file fine then I wanted to add another field that is under a few levels and when I try to do this – it doesn’t work. What am I doing wrong?

So the following works correctly and gives me back the first level promoter id

Declare @JSON varchar(max)
SELECT @JSON=BulkColumn
FROM OPENROWSET (BULK 'C:testtest.JSON', SINGLE_CLOB) import
SELECT *
FROM OPENJSON (@JSON,'$.events[0].promoters[0]')
WITH 
(
    [promoterid] varchar(10) '$.promoter.id'

)

This does not work – I get all my fields back except that darn promoter id

Declare @JSON varchar(max)
SELECT @JSON=BulkColumn
FROM OPENROWSET (BULK 'C:testtest.JSON', SINGLE_CLOB) import
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',
  [minPrice]         varchar(10)  '$.minPrice',
  [promoterId]       varchar(10)  '$.promoters.promoter.id'
)

Advertisement

Answer

answering my own question – I finally figured it out with help from this site – https://sqlwithmanoj.com/2015/11/01/reading-json-string-with-nested-elements-sql-server-2016-part-3/

This is what works –

Declare @JSON varchar(max)
SELECT @JSON=BulkColumn
FROM OPENROWSET (BULK 'C:testtest.JSON', SINGLE_CLOB) import
select 
json_value (c.value, '$.venue.venueName') as venueName,
json_value (c.value, '$.venue.venueId') as venueID,
json_value (p.value, '$.promoter.id') as promoterID

from openjson(@json, '$.events') as c
cross apply openjson (c.value, '$.promoters') as p
8 People found this is helpful
Advertisement