Hi Everyone,
I have the following test.json file:
I'm trying to search within SQL on this file, but I can't get results on anything specific below the "documents" level...
I can see the json row just fine with this query:
However, I get 0 results if I try to look at a specific array, like grantees:
Any idea what I'm doing wrong?
I have the following test.json file:
Code:
{
"imageRoot": "[URL unfurl="true"]https://path/",[/URL]
"name": "Test",
"documents": [
{
"imageUrl": "D0205936.tif",
"json": {
"indexingRecordId": "281972",
"bookType": "OPR",
"bookNumber": "14",
"pageNumber": "982",
"instrumentNumber": "4018743",
"instrumentType": "MORTGAGE",
"marketSource": "123 TITLE",
"filedDate": "2022-06-01 00:00:00",
"instrumentDate": "2022-05-28 00:00:00",
"consideration": 8721.85,
"comment": "MISSING PAGE 2",
"caseNumber": "CSV-12345",
"grantees": [
{
"nameLast": "SECRETARY OF HOUSING AND URBAN DEVELOPMENT"
},
{
"nameLast": "SMITH",
"nameFirst": "DONNIE",
"nameMiddle": "MICHAEL"
}
],
"grantors": [
{
"nameFirst": "MICHAEL",
"nameLast": "COLBERG"
},
{
"nameFirst": "CARRIE",
"nameLast": "COLBERG"
}
],
"subdivisionLegal": [
{
"addition": "EVERGREEN SUB AMD PLAT OF BLKS 5-8 AMD",
"bookNumber": "P",
"pageNumber": "123",
"pid": "998877",
"comment": "E55' LT 27; W4.88' LT 28",
"block": [
{
"rangeMax": "6",
"rangeMin": "6"
}
],
"lot": [
{
"rangeMax": "28",
"rangeMin": "27"
}
]
},
{
"addition": "BITTERROOT HEIGHTS SUBDIVISION 2ND FILING",
"bookNumber": "G",
"pageNumber": "12",
"pid": "12115",
"unit": [
{
"rangeMax": "14",
"rangeMin": "14"
}
]
}
],
"acreageLegal": [
{
"quarters": "NW",
"range": "26E",
"section": "5",
"township": "1S"
},
{
"quarters": "NE;",
"range": "26E",
"section": "1",
"township": "1S",
"comment": "N 1/2 GOV LOT 1",
"govLot": [
{
"rangeMax": "1",
"rangeMin": "2"
}
]
}
],
"related": [
{
"instrumentNumber": "3740643"
}
],
"related": [
{
"bookType": "MORTGAGE",
"bookNo": "121",
"pageNo": "14"
}
]
}
}
]
}
I'm trying to search within SQL on this file, but I can't get results on anything specific below the "documents" level...
I can see the json row just fine with this query:
Code:
Declare @JSON varchar(max)
SELECT @JSON=BulkColumn FROM OPENROWSET (BULK 'D:\test.json', Single_CLOB) import
select * from openjson (@json)
select * from openjson (@json, '$.documents')
However, I get 0 results if I try to look at a specific array, like grantees:
Code:
Declare @JSON varchar(max)
SELECT @JSON=BulkColumn FROM OPENROWSET (BULK 'D:\test.json', Single_CLOB) import
select * from openjson (@json, '$.documents.imageUrl')
select * from openjson (@json, '$.documents.json.grantees')
select * from openjson (@json, '$.documents.json.consideration')
Any idea what I'm doing wrong?