Convert JSON to SQL Format Part 2

Question

Hello Community,

I received some help from community member YitzhakKhabinsky-0887 writing code to convert JSON code to SQL Format.

The code is as follows:

 SELECT report.*  
 FROM OPENROWSET(BULK N'C:\Users\Carlton\Documents\mytestjson.json', SINGLE_CLOB) AS j  
 CROSS APPLY  
 OPENJSON(BulkColumn, N'$.mappings[0].ELLIPSE.columns')  
 WITH   
 (  
     [column]        VARCHAR(100)    '$.column'  
     , [type]        VARCHAR(20)        '$.type'  
     , [length]        INT                '$.length'  
     , [allowNulls]    VARCHAR(5)        '$.allowNulls'   
     , [transform]    VARCHAR(200)    '$.transform'  
     , [mapType]        VARCHAR(20)        '$.mapType'  
     , [source]        NVARCHAR(MAX) AS JSON  
 ) AS report;  

The code appears to be working ok.
However, it missed a column called "column": "DSTRCT_CODE",

Can someone take a look at the code an explain why that column isn't include in output when I execute the code agains the json called mytestjson.json, see attached175074-mytestjson.txt

Answer 1

Hi @Carlton Patterson ,

I looked into the attached JSON file mytestjson.txt.

It seems it has a structural integrity issue due to the fact that the primary key section has that data element in question:

"primaryKey": [  
              {  
                "column": "DSTRCT_CODE",  
                "seqno": 1  
              }  

But that column IS NOT enlisted in the ELLIPSE.columns section.