Load Nested JSON Data into MS SQL (Returning NULLs)

Question

I'm bringing a pretty simple JSON file into an MS SQL DB. I've created the table and when I make a query I can bring all of the data in fine except for a nested section called "Address"

Snippet of the JSON:

{
   "sold_price": Sample,
   "auction_date": "Sample",
"address": {
      "state": "Sample",
      "street": "Sample",
      "number": "Sample",
      "suburb": "Sample",
      "postcode": "Sample",
      "country": "Sample"
   },
   },
   "REA_Agent": "Sample",
   "sale_date": "2018-08-03 00:13:04+00:00",
 },

Process to load JSON:

DECLARE @BT1 VARCHAR(MAX)

SELECT @BT1 = 
BulkColumn
FROM OPENROWSET(BULK'C:\JSON Test\BT1.json', SINGLE_BLOB) JSON

SELECT @BT1 as BT1_Table;

IF (ISJSON(@BT1) = 1)
BEGIN
PRINT 'JSON File is valid';

INSERT INTO BT1
SELECT *
FROM OPENJSON(@BT1)
WITH(

[state]                 VARCHAR(MAX)    '$.state',
[number]                VARCHAR(MAX)    '$.number',
[street]                VARCHAR(MAX)    '$.street',
[suburb]                VARCHAR(MAX)    '$.suburb',
[postcode]              INTEGER         '$.postcode',
[property_type]         VARCHAR(MAX)    '$.property_type',
[sold_price]            VARCHAR(MAX)    '$.sold_price',
[sold_date]             DATE            '$.sold_date',
[settlement_date]       DATE            '$.settlement_date',
[agency_name]           VARCHAR(MAX)    '$.agency_name',
[bedrooms]              INTEGER         '$.bedrooms',
[bathrooms]             INTEGER         '$.bathrooms',
[parking]               INTEGER         '$.parking',
[auction_date]          DATE            '$.auction_date',
[passed_in]             VARCHAR(MAX)    '$.passed_in',
[will_disclose_sold]    VARCHAR(MAX)    '$.will_disclose_sold'

)
END
ELSE
BEGIN
PRINT 'JSON File is invalid';
END

What isn't working:

After it's loaded - I ran just a basic Select* to have a look, and All fields within "Address" returned NULLs.

Is there a trick that i'm missing here? All my calls are returning NULL and I'm not 100% sure why.(happy to receive feedback on my code if I could shorten the process/made errors)

Andrea · Accepted Answer · 2018-08-21 14:34:19Z

You want to read data from a nested element called address, so you must specify .address inside your WITH clause for each value coming from the nested section:

INSERT INTO BT1
SELECT *
FROM OPENJSON(@BT1)
WITH(
[state]                 VARCHAR(MAX)    '$.address.state',   --<< added ".address"
[number]                VARCHAR(MAX)    '$.address.number',  --<< added ".address"
[street]                VARCHAR(MAX)    '$.address.street',  --<< added ".address"
[suburb]                VARCHAR(MAX)    '$.address.suburb',  --<< added ".address"
[postcode]              INTEGER         '$.address.postcode',--<< added ".address"
[sold_price]            VARCHAR(MAX)    '$.sold_price',
[auction_date]          DATE            '$.auction_date',
[sold_date]             DATE            '$.sold_date',
[settlement_date]       DATE            '$.settlement_date',
[agency_name]           VARCHAR(MAX)    '$.agency_name',
[bedrooms]              INTEGER         '$.bedrooms',
[bathrooms]             INTEGER         '$.bathrooms',
[parking]               INTEGER         '$.parking',
[passed_in]             VARCHAR(MAX)    '$.passed_in',
[will_disclose_sold]    VARCHAR(MAX)    '$.will_disclose_sold',
[property_type]         VARCHAR(MAX)    '$.property_type'
)

Results:

Note: since your json snipped is apparently not valid (the parenthesis are not matching, sold_price value is not quoted, postcode should be an integer, but the value is "sample" etc.), I used the following json to test above code:

{
    "sold_price": "100",
    "auction_date": "2018-01-01",
    "address": {
        "state": "NY",
        "street": "street name",
        "number": "123",
        "suburb": "suburb name",
        "postcode": 12345,
        "country": "US"
    },
    "REA_Agent": "REA_Agent name",
    "sale_date": "2018-08-03 00:13:04+00:00"
}

If this is different from your structure please update your question and let me know.

Collectives™ on Stack Overflow

Load Nested JSON Data into MS SQL (Returning NULLs)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related