Consider the following, I have a document that looks something like this: I want to get everything from the document EXCEPT $metadata and $version The obvious solution would be to: However, my document may expand dynamically, hence why the above is suboptimal. I therefore figured that it may be better to exclude just $metadata and $version. I looked at different

Select * Except particular properties in Cosmos DB with SQL API

Consider the following, I have a document that looks something like this:

"id": 2
"properties": {
    "desired": {
        "Property1": 10,
        "Property2": 1,
        "Property3": 1,
        "$metadata": { 
        ...
        },
        "$version": 53
    }
},

I want to get everything from the document EXCEPT $metadata and $version The obvious solution would be to:

SELECT c["Property1"], c["Property2"] .... FROM c where c["id"] = "2"

However, my document may expand dynamically, hence why the above is suboptimal. I therefore figured that it may be better to exclude just $metadata and $version. I looked at different “interesting” solutions here on stackoverflow, amongst which one suggests to create a temporary table.

Unfortunately, the query needs to be very efficient, because I want to reduce the amount of RUs used. Also I really want to avoid handling the exclusion in the code.

Therefore, how do I exclude particular “columns” from my document, without writing an excessively long query, which may include creating temporary tables.

Answer

Cosmos DB does not support “Project Away”. You will need to specify properties to project or use * and return all of them.

Advertisement

Answer