SQL Query not returning expected output from cosmos container

Question

I am trying cosmos query to retrieve latest version item for each feature Tag category from my cosmos container but its returning all the items from the container but not the latest item(having highest version), need help to fix this please?

trying below sql query

SELECT  c
FROM c JOIN (
    SELECT c.featureTag, MAX(c.version) AS latest_version
    FROM c
    GROUP BY c.featureTag
) AS latest_versions
WHERE c.featureTag = latest_versions.featureTag AND 
latest_versions.latest_version = c.version

Below is one sample item from container(you can create more items, just increase version number with same featureTag. similarly create few items with different featureTag also)

{
    "featureName": "Extra File Download",
    "featureTag": "Extrafile-download",
    "startTime": "2024-03-21T17:00:00",
    "endTime": "2024-03-22T16:00:00",
    "version": "1",
    "groups": [
        "EXTENSION-Factory",
        "EXTENSION-Factory03"
    ],
    "factoryAffiliations": [

        "AAA",
        "BBB",
        "CCC",
        "DDD"
    ],
    "dependencyFeatures": [
        "usecase-code"
    ]
}

Answer 1

You can only join with properties within the same document. That's why you get all documents back since the subquery just returns the version and tag for that particular document.

A join across documents is not supported by Cosmos. It's probably easiest to handle it client side. Or make changes to your schema to allow you to either identify the latest version by properties or contain info about versions within the same document.