How do I add time-key to properties in geoJSON during SELECT from Postgres

Viewed 98

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"
                    }
                }
            },
0 Answers
Related