Azure Cosmos DB - SQL API range filter in WHERE condition

Question

I am trying to filter data based on "amount" range in SQL API query WHERE condition. The amount value could be an integer value without double quotes or decimal value with double quotes. Basically I need to filter all records between two amount range values

Below is my sample data.

[
    {
        "customer": "001",
        "dt": {
            "Transaction": [
                {
                    "Amount": "999.00",
                    "timestamp": "2019-01-28T15:44:49.215"
                }
            ]
        }
    },
    {
        "customer": "002",
        "dt": {
            "Transaction": [
                {
                    "Amount": "9999.00",
                    "timestamp": "2019-01-28T15:44:49.215"
                }
            ]
        }
    },
    {
        "customer": "003",
        "dt": {
            "Transaction": [
                {
                    "Amount": 9,
                    "timestamp": "2019-01-28T15:44:49.215"
                }
            ]
        }
    }
]

Below is the query that I am trying to run to find records with amount between 99 and 10000

SELECT c.dt[0].Transaction[0].Amount FROM c where c.dt[0].Transaction[0].Amount>"99" 
and  c.dt[0].Transaction[0].Amount<"10000"

But it is not returning any records

Could you please help me with the query. I need to filter records with amount between 99 and 10000

Hi,does my answer helps you?If so,you could mark it to end this case.Thanks a lot. — Jay Gong

Jay Gong Jay Gong · Accepted Answer · 2019-06-14T01:38:18

Please use UDF.

udf:

function test(str){
   if(typeof(str)=='string'){
       return Number(str)
   }else{
       return str
   }
}

sql:

SELECT c.dt.Transaction[0].Amount FROM c where udf.test(c.dt.Transaction[0].Amount) >99
and  udf.test(c.dt.Transaction[0].Amount)<10000

Output:

Azure Cosmos DB - SQL API range filter in WHERE condition

1 Answers