CODEWITHSUNDEEP
jsonsql-servert-sqlsql-server-2016
William Treadway
William Treadway

Reputation: 35

SQL Server 2016 Parse JSON into Table

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

Answers (1)

Razvan Socol
Razvan Socol

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

Related Questions