I have a PHP-script to SELECT data from Postgres in geoJSON-format. That works fine. This is the SQL-code.
SELECT jsonb_build_object(
'type', 'FeatureCollection',
'features', json_agg(features.feature)
)
FROM (SELECT jsonb_build_object(
'type', 'Feature',
'id', id,
'geometry', st_AsGeojson(st_SetSrid(st_MakePoint(split_part(to_jsonb(row)->'data'->'location'->>'value', ',', 2)::double precision, split_part(to_jsonb(row)->'data'->'location'->>'value', ',', 1)::double precision), 4326))::json,
'properties', to_jsonb(row) - 'id'
) AS feature
FROM (SELECT * FROM cs_fiets_json WHERE last_updated > '$sel_f') row) features;
I need to include a time-key with value into the properties (to be able to use TimeDimension in Leaflet) and I am stuck at that. The time-value I need is somewhere deep in the row. Played with json_set and json_insert. But don't know if, how and where to use that. So I need "time": "2022-03-02T14:32:37.00Z" directly under "properties".
Thanks in advance!
{
"id": 1581083302,
"type": "Feature",
"geometry": {
"type": "Point",
"coordinates": [
5.0545584,
52.1574455
]
},
"properties": {
"data": {
"id": "359215101322999",
"voc": {
"type": "Number",
"value": 42,
"metadata": {
"dateCreated": {
"type": "DateTime",
"value": "2021-11-25T10:49:10.00Z"
},
"dateModified": {
"type": "DateTime",
"value": "2022-03-02T14:32:37.00Z"
}
}
},
"pm10": {
"type": "Number",
"value": 14,
"metadata": {
"dateCreated": {
"type": "DateTime",
"value": "2021-11-25T10:49:10.00Z"
},
"dateModified": {
"type": "DateTime",
"value": "2022-03-02T14:32:37.00Z"
}
}
},