Hola amigos de la #nerdytud
Quiero compartirles la funcionalidad que posee Sql Server para poder leer e importar datos de un archivo json.
Descargar datos de paises
https://docs.openaq.org/#/v2/countries_get_v2_countries_get
Ejemplo de datos obtenidos
[
{
"code": "AD",
"name": "Andorra",
"locations": 3,
"firstUpdated": "2017-09-14T23:00:00+00:00",
"lastUpdated": "2021-02-01T12:00:00+00:00",
"parameters": [
"co",
"no2",
"o3",
"pm10",
"so2"
],
"count": 93301027,
"cities": 2,
"sources": 1
},
{
"code": "AE",
"name": "United Arab Emirates",
"locations": 5,
"firstUpdated": "2017-12-26T23:00:00+00:00",
"lastUpdated": "2021-02-01T13:29:47+00:00",
"parameters": [
"o3",
"pm1",
"pm10",
"pm25",
"um010",
"um025",
"um100"
],
"count": 5764752629,
"cities": 3,
"sources": 5
}
]
Importar datos a SQL Server
Declare @JSON varchar(max)
SELECT @JSON=BulkColumn
FROM OPENROWSET (BULK '/home/user/Downloads/data.json', SINGLE_CLOB) import
SELECT *
FROM OPENJSON (@JSON)
WITH
(
[code] nvarchar(2),
[name] nvarchar(50),
[locations] nvarchar(50),
[firstUpdated] datetime2,
[lastUpdated] datetime2,
[parameters] nvarchar(max),
[count] bigint,
[cities] bigint,
[sources] bigint
)