CosmosDB SQL query syntax for if statement

Question

I'm trying to find the correct syntax for doing an If/Case type of statement in an Azure ComsmosDB SQL query. Here is the document that I have

{
"CurrentStage": "Stage2",
"Stage1": {
    "Title": "Stage 1"
},
"Stage2": {
    "Title": "Stage 2"
},
"Stage3": {
    "Title": "Stage 3"
}

}

What I want to do is create a query that looks something like

        Select c.CurrentStage, 
if (CurrentStage == 'Stage1') { c.Stage1.Title } 
else if (CurrentStage == 'Stage2') { c.Stage2.Title } 
else if (CurrentStage == 'Stage3') { c.Stage3.Title } as Title
    From c

Obviously the document and query that I have is a lot more complicated then this, but this gives you the general idea of what I'm trying to do. I have 1 of the fields in the select to be variable based on some other fields in the document.

Imre Pühvel · Accepted Answer · 2018-04-26T10:32:58.830

While udf suggested by Jay Gong may be more comfortable to use if you need to reuse this function a lot, you can do this without udf using ternary operator syntax.

For example:

select
    c.CurrentStage = 'stage1' ? c.Stage1.Title
        : c.CurrentStage = 'stage2' ? c.Stage2.Title
        : c.CurrentStage = 'stage3' ? c.Stage3.Title
        : 'your default value should you wish one'
    as title
from c

Advice: Provider SQL solution has the benefit over UDF that it is self-contained and does not require setting up the logic on the server before executing. Also, note that logic versioning is simpler if logic is stored in client apps entirely, not shared across client and server as in the UDF case. UDF does have it's uses (ex:heavy reuse across queries), but usually it's better to do without.

Pretty answer. Thanks for your sharing. – Jay Gong Apr 27 '18 at 06:11 — Jay Gong, Apr 27 '18 at 06:11

score 4 · Answer 2 · answered Apr 25 '18 at 01:42

I suggest you using User Defined Function in Cosmos DB.

udf code:

function stage(c){
    switch(c.CurrentStage){
        case "Stage1" : return c.Stage1.Title;
        case "Stage2" : return c.Stage2.Title;
        case "Stage3" : return c.Stage3.Title;
        default: return "";
    }
}

SQL ：

Select c.CurrentStage, 
udf.stage(c) as Title
From c

Output result:

Hope it helps you.

CosmosDB SQL query syntax for if statement

2 Answers2