JSON Unexpected Character Found at Position 0-CodePudding

The file is valid from what I can tell, it doesn't have "blanks" or other common issues that create this error. Using SQL Server Tools 2018, get the same error using Visual Studio 2019 running SSIS. What am I doing wrong here?

JSON Example:

{"organizationAffiliations": [{"organizationId": 2001, "locationId": 3029960, "planIds": [5, 13, 19, 20, 24, 40]}]}

SQL Code in Question

-- organization affiliations query -- This should work but doesn't --
DECLARE @JSON VARCHAR(MAX)
SELECT @JSON=BulkColumn
FROM OPENROWSET (BULK 'C:\FileDirectory\filename.json', SINGLE_CLOB) IMPORT
SELECT  
JSON_Value (c.value, '$.organizationId') as organizationId, 
JSON_Value (c.value, '$.locationId') as locationId,
JSON_Value (p.value, '$.planIds') as planIds
FROM OPENJSON (@JSON, '$.organizationAffiliations') as c
CROSS APPLY OPENJSON (c.value, '$.planIds') as p

CodePudding user response：

PlanIDs is an array. Based on the CROSS APPLY, I'm assuming this is the structure you are looking for

SELECT  
JSON_Value (c.value, '$.organizationId') as organizationId, 
JSON_Value (c.value, '$.locationId') as locationId,
P.value as planID
FROM OPENJSON (@JSON, '$.organizationAffiliations') as c
CROSS APPLY OPENJSON (c.value, '$.planIds') as p

Results