1SELECT *
2FROM OPENJSON(@json)
3 WITH (id int 'strict $.id',
4 firstName nvarchar(50) 'strict $.info.name',
5 lastName nvarchar(50) '$.info.surname',
6 age int,
7 dateOfBirth datetime2)
1SELECT *
2FROM OPENJSON (@JSON, '$.data.assets.parcels')
3WITH (id VARCHAR(7),
4 x INT,
5 y INT,
6 price INT '$.auction_price') -- new column name
7)
8
1DECLARE @JSON VARCHAR(MAX)
2
3SELECT @JSON = BulkColumn
4FROM OPENROWSET
5(BULK 'C:\file-location\my-data.json', SINGLE_CLOB)
6AS j
7
1{
2 "ok": true,
3 "data": {
4 "assets": {
5 "parcels": [{
6 "id": "51,100",
7 "x": 51,
8 "y": 100,
9 "auction_price": 4444,
10 "district_id": "77909d00",
11 "owner": "Dr Cornwallis",
12 "data": { "version": 0 }
13 },
14 //...+ 2499 more objects in the parcels array
15 ],
16 total: 2500
17 },
18 },
19}
20