cosmos db nested query with geometry locations

Question

I can´t figure it out how to select.

json example

    "inserted": "2022-11-22T17:40:40.0303547Z",
    "packagekey": "b53b8bfb-68b7-4ad1-aacb-b5868d4b4657",
    "ismoved": true,
    "geometry": {
        "type": "Point",
        "coordinates": [
            16.788592,
            54.454656
        ]
    },
    "id": "d8d4c25e-9800-4dc8-8185-fe18802ab1ff",
    "partionkey": "b53b8bfb-68b7-4ad1-aacb-b5868d4b4657",
    "key": "8d8cc237-3940-41e3-badf-116cea092756",
    "type": "Feature",
    "properties": {}
}

same package new location new time

    "inserted": "2022-11-23T17:40:40.0303547Z",
    "packagekey": "b53b8bfb-68b7-4ad1-aacb-b5868d4b4657",
    "ismoved": true,
    "geometry": {
        "type": "Point",
        "coordinates": [
            17.788592,
            55.454656
        ]
    },
    "id": "5bb05a5e-d06c-4c69-a76e-5c84cbfdea24",
    "partionkey": "b53b8bfb-68b7-4ad1-aacb-b5868d4b4657",
    "key": "3e1d4166-893d-41eb-86e9-c4521e5119f9",
    "type": "Feature",
    "properties": {}
}

another package on the same locaion that the other had passed

    "inserted": "2022-09-23T17:40:40.0303547Z",
    "packagekey": "945fcf8a-e03d-4686-aecb-e54dceb7bd6e",
    "ismoved": true,
    "geometry": {
        "type": "Point",
        "coordinates": [
             16.788592,
            54.454656
        ]
    },
    "id": "659b284c-febe-4428-bda7-9a356e9a7b3b",
    "partionkey": 945fcf8a-e03d-4686-aecb-e54dceb7bd6e",
    "key": "78be825f-de86-4244-99f8-5bc1b2e938b8",
    "type": "Feature",
    "properties": {}
}

I have many object like these and the packagekey indicates a package and the geometry is a location. Each package have 1 or more locations depedning how it is moved.

How can I select all packages(each package have a packagekey) on a certain location I use the where like

where ST_DISTANCE(c.geometry, {'type':'Point', 'coordinates':[16.788592, 54.454656]}) <= 1

And there can be many packages on the same location(stacked)

But in the example above I just want to see packagekey 945fcf8a-e03d-4686-aecb-e54dceb7bd6e

I have tried like

SELECT c.packagekey, c.geometry, c.inserted
FROM c
JOIN (
    SELECT MAX(c1.inserted) AS maxInserted, c1.packagekey
    FROM c c1
    GROUP BY c1.packagekey
) maxDates
ON c.inserted = maxDates.maxInserted AND c.packagekey = maxDates.packagekey

But its not correct

Answer 1

not much experienced but pleas let me know the result ?

SELECT TOP 1 * FROM c
WHERE c.inserted = (SELECT MAX(c2.inserted) FROM c c2)
ORDER BY c.inserted DESC

here c represents the alias for the documents in the collection. We use the TOP 1 clause to retrieve only the latest package and then where the inserted property is equal to the maximum inserted value in the collection. The result is ordered by inserted in descending order.