January 1, 2019 at 9:02 pm
Hi all.
For some reason i cant access nested data from a Json file im importing.
I can get to the first layer by the insert, but the rest are coming up null, and not sure why. In previous Json files i have been able to 'qualify' the field names for example '$.property.forSalePropertyCampaignList.listingMethod'
but for some reason any data below "forSalePropertyCampaignList": [ will not appear. I have looked online, and on here for Json specific help but none seem to deal with getting to the nested data when loading in a file via BULK.
Any help is greatly appreciated!
CREATE TABLE #API(
[Address] NVARCHAR(MAX) NULL,
[Listing] NVARCHAR(MAX) NULL,);
DECLARE @#API VARCHAR(MAX)
SELECT @#API = BulkColumn FROM OPENROWSET(BULK 'C:\Users\jmcarthur\Desktop\Hills Python Project\API2.JSON', SINGLE_BLOB) J
INSERT INTO #API
SELECT *
FROM OPENJSON(@#API)
WITH(
[Address] NVARCHAR(MAX) '$.property.address.singleLine',
[Listing] NVARCHAR(MAX) '$.property.forSalePropertyCampaignList.listingMethod')
SELECT * FROM #API
JSON Data Below
{
"property": {
"address": {
"councilArea": "KING",
"councilAreaId": 12345,
"isDerivedUnit": false,
"singleLine": "ADDRESS",
"startNumber": 1235,
"street": {
"extension": "ROAD",
"id": 219968,
"locality": {
"id": 4192,
"name": "KING",
"postcode": {
"id": 101842,
"name": "2289",
"singleLine": "2289 NSW",
"state": "NSW"
},
"singleLine": "KING NSW 2289"
},
"name": "GLEBE",
"nameAndNumber": "ADDRESS ",
"singleLine": "KING"
},
"unitNumber": 2
},
"forSalePropertyCampaignList": [
{
"advertisementId": 181502878,
"agency": {
"company": {
"companyName": "Robinson Property - The Junction"
},
"phone": {
"phoneNumber": "(02) 4902 7222"
}
},
"agent": {
"agent": "Darren Bender",
"phone": {
"phoneNumber": "0414 430 172"
}
},
"daysListed": 36,
"daysOnMarket": 43,
"firstPublishedPrice": "Expressions of Interest",
"fromDate": "2018-09-19",
"isActiveCampaign": false,
"latestAdvertisementPrice": 460000,
"listingMethod": "Normal Sale",
"priceDescription": "$430,000 - $460,000",
"saleDate": "2018-10-31",
"toDate": "2018-10-24"
},
{
"advertisementId": 28855061,
"agency": {
"company": {
"companyName": "Dalton Partners The Junction / New Lambton"
}
},
"agent": {
"agent": "John Kerr",
"phone": {
"phoneNumber": "0423 375 591"
}
},
"daysListed": 5,
"daysOnMarket": 7,
"firstAdvertisementPrice": 325000,
"firstPublishedPrice": "$325,000",
"fromDate": "2013-07-30",
"isActiveCampaign": false,
"latestAdvertisementPrice": 325000,
"listingMethod": "Normal Sale",
"percentPriceVariationAtSale": -3.08,
"percentPriceVariationFirstToLast": 0,
"priceDescription": "$325,000",
"saleDate": "2013-08-05",
"toDate": "2013-08-03"
},
{
"advertisementId": 58547985,
"agency": {
"company": {
"companyName": "Street Real Estate"
}
},
"daysListed": 36,
"daysOnMarket": 48,
"firstAdvertisementPrice": 219000,
"firstPublishedPrice": "$219,000",
"fromDate": "2007-12-08",
"isActiveCampaign": false,
"latestAdvertisementPrice": 215000,
"listingMethod": "Normal Sale",
"percentPriceVariationAtSale": -3.2,
"percentPriceVariationFirstToLast": -1.83,
"priceDescription": "$215,000",
"saleDate": "2008-01-24",
"toDate": "2008-01-12"
}
],
"id": 7951804,
"occupancyType": "Rented",
"propertySubType": "Unit",
"propertyType": "UNIT",
"saleList": [
{
"agency": {
"company": {
"companyName": "Robinson Property - The Junction"
}
},
"contractDate": "2018-10-31",
"isAgentsAdvice": true,
"isArmsLength": true,
"isPriceWithheld": false,
"isReaRecentSale": false,
"isStandardTransfer": true,
"price": 437000,
"saleMethod": "Normal Sale",
"settlementDate": "2018-10-31",
"transferId": 45606936,
"type": "Unknown"
},
{
"agency": {
"company": {
"companyName": "AAA"
}
},
"contractDate": "2013-08-05",
"isAgentsAdvice": false,
"isArmsLength": true,
"isPriceWithheld": false,
"isReaRecentSale": false,
"isStandardTransfer": true,
"price": 315000,
"saleMethod": "Normal Sale",
"settlementDate": "2013-08-30",
"transferId": 34096541,
"type": "Unknown"
},
{
"agency": {
"company": {
"companyName": "Street Real Estate"
}
},
"contractDate": "2008-01-24",
"isAgentsAdvice": false,
"isArmsLength": true,
"isDerivedAgency": true,
"isPriceWithheld": false,
"isReaRecentSale": false,
"isStandardTransfer": true,
"price": 212000,
"settlementDate": "2008-02-28",
"transferId": 33220976,
"type": "Unknown"
},
{
"contractDate": "2000-11-27",
"isAgentsAdvice": false,
"isArmsLength": true,
"isPriceWithheld": false,
"isReaRecentSale": false,
"isStandardTransfer": true,
"price": 108000,
"settlementDate": "2000-12-20",
"transferId": 29171975,
"type": "Unknown"
},
{
"contractDate": "1991-09-02",
"isAgentsAdvice": false,
"isArmsLength": true,
"isPriceWithheld": false,
"isReaRecentSale": false,
"isStandardTransfer": true,
"price": 100000,
"settlementDate": "1991-09-02",
"transferId": 29173218,
"type": "Unknown"
}
]
},
"systemInfo": {
"instanceName": "77:8080",
"requestDate": "2019-01-02T09:13:40.225+10:00"
}
}
January 2, 2019 at 12:34 pm
try this, it returns the AdvertisementID, but you can tweak as needed
declare @j-2 nvarchar(max) = '{
"property": {
"address": {
"councilArea": "KING",
"councilAreaId": 12345,
"isDerivedUnit": false,
"singleLine": "ADDRESS",
"startNumber": 1235,
"street": {
"extension": "ROAD",
"id": 219968,
"locality": {
"id": 4192,
"name": "KING",
"postcode": {
"id": 101842,
"name": "2289",
"singleLine": "2289 NSW",
"state": "NSW"
},
"singleLine": "KING NSW 2289"
},
"name": "GLEBE",
"nameAndNumber": "ADDRESS ",
"singleLine": "KING"
},
"unitNumber": 2
},
"forSalePropertyCampaignList": [
{
"advertisementId": 181502878,
"agency": {
"company": {
"companyName": "Robinson Property - The Junction"
},
"phone": {
"phoneNumber": "(02) 4902 7222"
}
},
"agent": {
"agent": "Darren Bender",
"phone": {
"phoneNumber": "0414 430 172"
}
},
"daysListed": 36,
"daysOnMarket": 43,
"firstPublishedPrice": "Expressions of Interest",
"fromDate": "2018-09-19",
"isActiveCampaign": false,
"latestAdvertisementPrice": 460000,
"listingMethod": "Normal Sale",
"priceDescription": "$430,000 - $460,000",
"saleDate": "2018-10-31",
"toDate": "2018-10-24"
},
{
"advertisementId": 28855061,
"agency": {
"company": {
"companyName": "Dalton Partners The Junction / New Lambton"
}
},
"agent": {
"agent": "John Kerr",
"phone": {
"phoneNumber": "0423 375 591"
}
},
"daysListed": 5,
"daysOnMarket": 7,
"firstAdvertisementPrice": 325000,
"firstPublishedPrice": "$325,000",
"fromDate": "2013-07-30",
"isActiveCampaign": false,
"latestAdvertisementPrice": 325000,
"listingMethod": "Normal Sale",
"percentPriceVariationAtSale": -3.08,
"percentPriceVariationFirstToLast": 0,
"priceDescription": "$325,000",
"saleDate": "2013-08-05",
"toDate": "2013-08-03"
},
{
"advertisementId": 58547985,
"agency": {
"company": {
"companyName": "Street Real Estate"
}
},
"daysListed": 36,
"daysOnMarket": 48,
"firstAdvertisementPrice": 219000,
"firstPublishedPrice": "$219,000",
"fromDate": "2007-12-08",
"isActiveCampaign": false,
"latestAdvertisementPrice": 215000,
"listingMethod": "Normal Sale",
"percentPriceVariationAtSale": -3.2,
"percentPriceVariationFirstToLast": -1.83,
"priceDescription": "$215,000",
"saleDate": "2008-01-24",
"toDate": "2008-01-12"
}
],
"id": 7951804,
"occupancyType": "Rented",
"propertySubType": "Unit",
"propertyType": "UNIT",
"saleList": [
{
"agency": {
"company": {
"companyName": "Robinson Property - The Junction"
}
},
"contractDate": "2018-10-31",
"isAgentsAdvice": true,
"isArmsLength": true,
"isPriceWithheld": false,
"isReaRecentSale": false,
"isStandardTransfer": true,
"price": 437000,
"saleMethod": "Normal Sale",
"settlementDate": "2018-10-31",
"transferId": 45606936,
"type": "Unknown"
},
{
"agency": {
"company": {
"companyName": "AAA"
}
},
"contractDate": "2013-08-05",
"isAgentsAdvice": false,
"isArmsLength": true,
"isPriceWithheld": false,
"isReaRecentSale": false,
"isStandardTransfer": true,
"price": 315000,
"saleMethod": "Normal Sale",
"settlementDate": "2013-08-30",
"transferId": 34096541,
"type": "Unknown"
},
{
"agency": {
"company": {
"companyName": "Street Real Estate"
}
},
"contractDate": "2008-01-24",
"isAgentsAdvice": false,
"isArmsLength": true,
"isDerivedAgency": true,
"isPriceWithheld": false,
"isReaRecentSale": false,
"isStandardTransfer": true,
"price": 212000,
"settlementDate": "2008-02-28",
"transferId": 33220976,
"type": "Unknown"
},
{
"contractDate": "2000-11-27",
"isAgentsAdvice": false,
"isArmsLength": true,
"isPriceWithheld": false,
"isReaRecentSale": false,
"isStandardTransfer": true,
"price": 108000,
"settlementDate": "2000-12-20",
"transferId": 29171975,
"type": "Unknown"
},
{
"contractDate": "1991-09-02",
"isAgentsAdvice": false,
"isArmsLength": true,
"isPriceWithheld": false,
"isReaRecentSale": false,
"isStandardTransfer": true,
"price": 100000,
"settlementDate": "1991-09-02",
"transferId": 29173218,
"type": "Unknown"
}
]
},
"systemInfo": {
"instanceName": "77:8080",
"requestDate": "2019-01-02T09:13:40.225+10:00"
}
} '
SELECT *
FROM OPENJSON(@j)
WITH (advertisementId nvarchar(50) '$.property.forSalePropertyCampaignList[0].advertisementId')
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 2, 2019 at 3:42 pm
Hi SSCrazy Eights.
Thank you, this worked perfectly.
Can i follow up though - it seems like the only addition to the query was putting the [0] as a qualifier in each row. What did that do in this instance?
I only ask so i can do some follow up learning on the matter, as this matter is solved.
Thanks again !
January 3, 2019 at 6:50 am
forSalePropertyCampaignList is an array [ ] of objects { } rather than an object itself, therefore you need to reference the first element of the array [0] - arrays are zero based.
Below is the data formatted with nesting. You can see that there are multiple object in the array for the advertisements, each of which is itself a complex object
[
{
"advertisementId":181502878,
"agency":{
"company":{
"companyName":"Robinson Property - The Junction"
},
"phone":{
"phoneNumber":"(02) 4902 7222"
}
},
"agent":{
"agent":"Darren Bender",
"phone":{
"phoneNumber":"0414 430 172"
}
},
"daysListed":36,
"daysOnMarket":43,
"firstPublishedPrice":"Expressions of Interest",
"fromDate":"2018-09-19",
"isActiveCampaign":false,
"latestAdvertisementPrice":460000,
"listingMethod":"Normal Sale",
"priceDescription":"$430,000 - $460,000",
"saleDate":"2018-10-31",
"toDate":"2018-10-24"
},
{
"advertisementId":28855061,
"agency":{
"company":{
"companyName":"Dalton Partners The Junction / New Lambton"
}
},
"agent":{
"agent":"John Kerr",
"phone":{
"phoneNumber":"0423 375 591"
}
},
"daysListed":5,
"daysOnMarket":7,
"firstAdvertisementPrice":325000,
"firstPublishedPrice":"$325,000",
"fromDate":"2013-07-30",
"isActiveCampaign":false,
"latestAdvertisementPrice":325000,
"listingMethod":"Normal Sale",
"percentPriceVariationAtSale":-3.08,
"percentPriceVariationFirstToLast":0,
"priceDescription":"$325,000",
"saleDate":"2013-08-05",
"toDate":"2013-08-03"
},
{
"advertisementId":58547985,
"agency":{
"company":{
"companyName":"Street Real Estate"
}
},
"daysListed":36,
"daysOnMarket":48,
"firstAdvertisementPrice":219000,
"firstPublishedPrice":"$219,000",
"fromDate":"2007-12-08",
"isActiveCampaign":false,
"latestAdvertisementPrice":215000,
"listingMethod":"Normal Sale",
"percentPriceVariationAtSale":-3.2,
"percentPriceVariationFirstToLast":-1.83,
"priceDescription":"$215,000",
"saleDate":"2008-01-24",
"toDate":"2008-01-12"
}
],
"id":7951804,
"occupancyType":"Rented",
"propertySubType":"Unit",
"propertyType":"UNIT",
"saleList":[
{
"agency":{
"company":{
"companyName":"Robinson Property - The Junction"
}
},
"contractDate":"2018-10-31",
"isAgentsAdvice":true,
"isArmsLength":true,
"isPriceWithheld":false,
"isReaRecentSale":false,
"isStandardTransfer":true,
"price":437000,
"saleMethod":"Normal Sale",
"settlementDate":"2018-10-31",
"transferId":45606936,
"type":"Unknown"
},
{
"agency":{
"company":{
"companyName":"AAA"
}
},
"contractDate":"2013-08-05",
"isAgentsAdvice":false,
"isArmsLength":true,
"isPriceWithheld":false,
"isReaRecentSale":false,
"isStandardTransfer":true,
"price":315000,
"saleMethod":"Normal Sale",
"settlementDate":"2013-08-30",
"transferId":34096541,
"type":"Unknown"
},
{
"agency":{
"company":{
"companyName":"Street Real Estate"
}
},
"contractDate":"2008-01-24",
"isAgentsAdvice":false,
"isArmsLength":true,
"isDerivedAgency":true,
"isPriceWithheld":false,
"isReaRecentSale":false,
"isStandardTransfer":true,
"price":212000,
"settlementDate":"2008-02-28",
"transferId":33220976,
"type":"Unknown"
},
{
"contractDate":"2000-11-27",
"isAgentsAdvice":false,
"isArmsLength":true,
"isPriceWithheld":false,
"isReaRecentSale":false,
"isStandardTransfer":true,
"price":108000,
"settlementDate":"2000-12-20",
"transferId":29171975,
"type":"Unknown"
},
{
"contractDate":"1991-09-02",
"isAgentsAdvice":false,
"isArmsLength":true,
"isPriceWithheld":false,
"isReaRecentSale":false,
"isStandardTransfer":true,
"price":100000,
"settlementDate":"1991-09-02",
"transferId":29173218,
"type":"Unknown"
}
]
January 3, 2019 at 2:14 pm
Ah ok, that makes sense thanks!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply