jsonb in additional_metadata was fixed
Additional metadata was chosen to be of type jsonb to facilitate access to its contents (as if it had been a predefined database field).
Example:
toardb_v2=# SELECT additional_metadata FROM stationmeta_core WHERE id=25;
additional_metadata
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"station_alt_flag": 0, "google_resolution": 19, "station_google_alt": 140, "station_reported_alt": 137, "station_landcover_description": "Cropland/Naturalvegetationmosaic:34.7%,Croplands:32.0%,Urbanandbuilt-up:17.2%,Mixedforest:14.8%", "station_max_population_density_5km": 16191}
(1 row)
toardb_v2=# SELECT additional_metadata->'station_landcover_description' FROM stationmeta_core WHERE id=25;
?column?
---------------------------------------------------------------------------------------------------
"Cropland/Naturalvegetationmosaic:34.7%,Croplands:32.0%,Urbanandbuilt-up:17.2%,Mixedforest:14.8%"
(1 row)
toardb_v2=# SELECT additional_metadata->'station_google_alt'>additional_metadata->'station_reported_alt' FROM stationmeta_core WHERE id=25;
?column?
----------
t
(1 row)
There are still problems in inserting additional metadata via FastAPI.
The string which has been inserted is just a string but not jsonb. Therefore, all advantages of jsonb are unavailable.
All empty jsonb entries which were inserted as strings can be converted via:
UPDATE stationmeta_core SET additional_metadata='{}' WHERE additional_metadata='"{}"';
This is an issue with stationmeta_core and timeseries.
Harvesting/ingestion python scripts should add additional metadata entries as:
additional_metadata = {}
additional_metadata['name_french'] = name_french
entry['additional_metadata'] = json.dumps(additional_metadata)
stationmeta_data = {}
stationmeta_data['stationmeta'] = entry
...
r = requests.post(TOAR_SERVICE_URL + 'stationmeta/',
data=json.dumps(stationmeta_data), headers=headers)
And the FastAPI script should use them in crud as:
db_stationmeta.additional_metadata = json.loads(str(db_stationmeta.additional_metadata).replace("'",'"'))
This works!
Edited by Sabine Schröder