Sql server import json

This article is tagged with: Sql server import json and mysql

sql server import json

SELECT *
FROM OPENJSON (@JSON, '$.data.assets.parcels')
WITH (id VARCHAR(7),
x INT,
y INT,
price INT '$.auction_price') -- new column name
)

insert json into sql

SELECT *
FROM OPENJSON(@json)
WITH (id int 'strict $.id',
firstName nvarchar(50) 'strict $.info.name',
  lastName nvarchar(50) '$.info.surname',
age int,
  dateOfBirth datetime2)

sql server import json

{
"ok": true,
"data": {
"assets": {
"parcels": [{
"id": "51,100",
"x": 51,
"y": 100,
"auction_price": 4444,
"district_id": "77909d00",
"owner": "Dr Cornwallis",
"data": { "version": 0 }
},
//...+ 2499 more objects in the parcels array
],
total: 2500
},
},
}

sql server import json

If (ISJSON(@JSON)=1)
SELECT @JSON AS 'JSON Text'

sql server import json

SELECT ISJSON(@JSON)

sql server import json

DECLARE @JSON VARCHAR(MAX)
SELECT @JSON = BulkColumn
FROM OPENROWSET
(BULK 'C:\file-location\my-data.json', SINGLE_CLOB)
AS j

This article is tagged with: Sql server import json and mysql