CODEWITHSUNDEEP
sqlnosqlazure-cosmosdb
Jeppe Christensen
Jeppe Christensen

Reputation: 1890

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.

Upvotes: 7

Views: 2914

Answers (1)

Mark Brown
Mark Brown

Reputation: 8763

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

Upvotes: 2

Related Questions