Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help searching within this Json file

Status
Not open for further replies.

bmacbmac

IS-IT--Management
Jan 26, 2006
392
US
Hi Everyone,

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?
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top