Hello,
I'm trying to exploit a french API to retrieve coordinate of each town. (https://api.gouv.fr/api/api-geo)
LIB CONNECT TO 'API Geo';
RestConnectorMasterTable:
SQL SELECT
"__KEY_root",
(SELECT
"type",
"__KEY_centre",
"__FK_centre",
(SELECT
"@Value",
"__FK_coordinates"
FROM "coordinates" FK "__FK_coordinates" ArrayValueAlias "@Value")
FROM "centre" PK "__KEY_centre" FK "__FK_centre")
FROM JSON (wrap on) "root" PK "__KEY_root";
[coordinates]:
LOAD [@Value] AS [@Value],
[__FK_coordinates] AS [__KEY_centre]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_coordinates]);
[centre]:
LOAD [type] AS [type],
[__KEY_centre] AS [__KEY_centre],
[__FK_centre] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_centre]);
Infortunatly, the coordinate data latitude and longitude are mixted after loading into coordinate table.
From the API, coordinate's data are ordonate and this order is lost after loading. I can't exploit any extra field (index) to keep my data correctly ordonate. Neither i can't use rowNo() on loading because the data isn't correctly ordonate on RestConnectorMasterTable.
My question is: it's possible to add an index during the JSON loading? or keep the original array from coordinate?
The resulting JSON produce by the API
Response body
Download
[
{
"centre": {
"type": "Point",
"coordinates": [
4.926,
46.1567
]
},
"nom": "L'Abergement-Clémenciat",
"code": "01001"
},
Thanks.
Guerric