Reputation: 35
I am trying to take a JSON output from an API into a SQL Server Table using the JSON Parsing in SQL Server 2016. However, I'm having a very hard time getting it to work. I've exhausted all my searching efforts.
The JSON output is very simple.
[{"Jobs":[{"Agricultural":false,"AlternateTitle":null,"DateInJob":"/Date(1454997600000)/","DirectLabor":false,"EffectiveDate":"/Date(-62135575200000)/","EmployeeIdentifier":{"EmployeeNumber":"000001","CompanyCode":"60639"},"EmployeeType":"REG","FullOrPartTime":"F","HourlyOrSalaried":"S","JobCode":"HRCOORD","JobGroup":null,"LocalUnion":null,"NationalUnion":null,"OrgLevel1":null,"OrgLevel2":null,"OrgLevel3":null,"OrgLevel4":null,"PayFrequency":"B","PayGroup":"60639","PayScaleCode":null,"Project":null,"Promotion":false,"ReasonCode":"100","ScheduledHours":80,"Seasonal":false,"SelfServiceProperties":null,"ShiftCode":"Z","ShiftGroup":"Z","StepNo":null,"Supervisor":null,"TimeClock":null,"Transfer":false,"YouthTraining":false}],"CompanyCode":"60639","EmployeeNumber":"000001","FirstName":"George","LastName":"TestEmployee"},{"Jobs":[{"Agricultural":false,"AlternateTitle":"Client Care Representative","DateInJob":"/Date(1497592800000)/","DirectLabor":false,"EffectiveDate":"/Date(-62135575200000)/","EmployeeIdentifier":{"EmployeeNumber":"003613","CompanyCode":"60637"},"EmployeeType":"TES","FullOrPartTime":"F","HourlyOrSalaried":"H","JobCode":"CCREP","JobGroup":null,"LocalUnion":null,"NationalUnion":null,"OrgLevel1":"000","OrgLevel2":"001","OrgLevel3":null,"OrgLevel4":null,"PayFrequency":"B","PayGroup":"60637","PayScaleCode":null,"Project":"STAFF","Promotion":false,"ReasonCode":"Z","ScheduledHours":80,"Seasonal":false,"SelfServiceProperties":null,"ShiftCode":"Z","ShiftGroup":"Z","StepNo":null,"Supervisor":{"EmployeeNumber":"003639","CompanyCode":"60637","ExtensionData":{}},"TimeClock":null,"Transfer":false,"YouthTraining":false}],"CompanyCode":"60637","EmployeeNumber":"003613","FirstName":"George","LastName":"TestEmployee"}]
I've used this SQL code to pull the JSON data into SQL.
Declare @JSON nvarchar(max)
SELECT @JSON = BulkColumn-- Replace(Replace(BulkColumn,'',''),'','')
FROM OPENROWSET (BULK 'C:\JSON\JobService.json', SINGLE_CLOB) as j
Select *
from OPENJSON(@JSON, N'$')
WITH (
LastName nvarchar(100) N'$.LastName'
,FirstName nvarchar(100) N'$.FirstName'
,ScheduledHours INT N'$.Jobs.ScheduledHours')
The problem I'm having is getting to the deeper nested data. LastName and FirstName pull in fine, but I cannot get anything inside the "Jobs" object or deeper, and I cannot figure out why. Any help would be great appreciated.
Upvotes: 2
Views: 1487
Reputation: 5694
You can use an APPLY to call OPENJSON again, like this:
DECLARE @JSON NVARCHAR(MAX)=' [{"Jobs":[{"Agricultural":false,"AlternateTitle":null,"DateInJob":"/Date(1454997600000)/","DirectLabor":false,"EffectiveDate":"/Date(-62135575200000)/","EmployeeIdentifier":{"EmployeeNumber":"000001","CompanyCode":"60639"},"EmployeeType":"REG","FullOrPartTime":"F","HourlyOrSalaried":"S","JobCode":"HRCOORD","JobGroup":null,"LocalUnion":null,"NationalUnion":null,"OrgLevel1":null,"OrgLevel2":null,"OrgLevel3":null,"OrgLevel4":null,"PayFrequency":"B","PayGroup":"60639","PayScaleCode":null,"Project":null,"Promotion":false,"ReasonCode":"100","ScheduledHours":80,"Seasonal":false,"SelfServiceProperties":null,"ShiftCode":"Z","ShiftGroup":"Z","StepNo":null,"Supervisor":null,"TimeClock":null,"Transfer":false,"YouthTraining":false}],"CompanyCode":"60639","EmployeeNumber":"000001","FirstName":"George","LastName":"TestEmployee"},{"Jobs":[{"Agricultural":false,"AlternateTitle":"Client Care Representative","DateInJob":"/Date(1497592800000)/","DirectLabor":false,"EffectiveDate":"/Date(-62135575200000)/","EmployeeIdentifier":{"EmployeeNumber":"003613","CompanyCode":"60637"},"EmployeeType":"TES","FullOrPartTime":"F","HourlyOrSalaried":"H","JobCode":"CCREP","JobGroup":null,"LocalUnion":null,"NationalUnion":null,"OrgLevel1":"000","OrgLevel2":"001","OrgLevel3":null,"OrgLevel4":null,"PayFrequency":"B","PayGroup":"60637","PayScaleCode":null,"Project":"STAFF","Promotion":false,"ReasonCode":"Z","ScheduledHours":80,"Seasonal":false,"SelfServiceProperties":null,"ShiftCode":"Z","ShiftGroup":"Z","StepNo":null,"Supervisor":{"EmployeeNumber":"003639","CompanyCode":"60637","ExtensionData":{}},"TimeClock":null,"Transfer":false,"YouthTraining":false}],"CompanyCode":"60637","EmployeeNumber":"003613","FirstName":"George","LastName":"TestEmployee"}]'
SELECT * FROM OPENJSON(@JSON)
WITH (
LastName nvarchar(100) N'$.LastName',
FirstName nvarchar(100) N'$.FirstName',
Jobs NVARCHAR(MAX) AS JSON
) j1
OUTER APPLY (
SELECT * FROM OPENJSON(j1.Jobs)
WITH (
ScheduledHours INT '$.ScheduledHours'
)
) x
Upvotes: 2