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 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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy