CosmosDB problem with query params timestamp

[strzeluk]

Describe the problem/error/question

I am having an issue with the Azure Cosmos DB node (Execute Query) in n8n.

The same SQL query returns correct results in Azure Cosmos DB Data Explorer, but returns an empty result set when executed via n8n, even though the configuration appears correct.

when I use static data for second param $2 (timestamp as a Number) it works:

What is the error message (if any)?

The query returns an empty array when executed using params via the n8n Cosmos DB node.

Information on your n8n setup

• n8n version: 1.115.2
• Database (default: SQLite): Postgres
• n8n EXECUTIONS_PROCESS setting (default: own, main):
• Running n8n via (Docker, npm, n8n cloud, desktop app): Docker on-permise
• Operating system: MacOs

[KhemOptimal]

It seems like a bug. The issue appears when doing a substitution for a number. You should see the same issue with just startDate and no projectId. That’s what I’m seeing as well.

Khem

[KhemOptimal]

With your setup, I was able to see the payload sent to Azure cosmosdb. We can see the issue below– numbers are converted to strings:

{
    "query": "SELECT * from c where c.projectId=@Param1 and  c.startDate=@Param2",
    "parameters": [
        {
            "name": "@Param1",
            "value": "P12223"
        },
        {
            "name": "@Param2",
            "value": "1737062400000"
        }
    ]
}

The above is with $1 and $2 in use. 1737062400000 is a string which leads to no results. I have these values for the query params:

{
    "query": "SELECT * from c where c.projectId=@Param1 and  c.startDate>=1737062400000",
    "parameters": [
        {
            "name": "@Param1",
            "value": "P12223"
        }
    ]
}

Above is without $2 and works just fine (as you have seen). So, it reaches azure cosmosdb with 1737062400000 as a number, not string.

Unfortunately, I don’t have a workaround on the n8n side. You can file a bug report if you wish; I tried to look for existing bug reports quickly but was not successful.

Khem

[Parintele_Damaskin]

Hey @KhemOptimal !

Nice break trough issue… I am not able at the moment to test methods on stri gsmyself with this service… but this will work? :

{{ $json.startDate.toInt() }}

{{ $json.startDate.toFloat() }}

{{ $json.startDate.toWholeNumber() }}

If this asw well doesn’t do it…Cosmos db node may still serialize it as a string when sending the query…

Cheers!

[KhemOptimal]

Hi @Parintele_Damaskin ,
Those are good things to try, and, luckily, I still have my n8n+mitmproxy up . I tried them and same thing. I captured the data over wire and looks like it becomes a string before it gets sent over to Azure.

Khem

[strzeluk]

Guys, thanks for your help with the analysis. I thought it was a node issue. Does anyone know how I can report this as a bug? Or convert it to a bug?

[KhemOptimal]

I would file an issue/bug report against the github repository: GitHub - n8n-io/n8n: Fair-code workflow automation platform with native AI capabilities. Combine visual building with custom code, self-host or cloud, 400+ integrations.

We can even see that the unit tests doesn’t cover any scenario where a parameter could be something other than a string: n8n/packages/nodes-base/nodes/Microsoft/AzureCosmosDb/test/helpers/utils.test.ts at master · n8n-io/n8n · GitHub

			expect((result.body as RequestBodyWithParameters).parameters).toEqual([
				{ name: '@Param1', value: 'value1' },
				{ name: '@Param2', value: 'value2' },
			]);

(mostly something like the above)

The code must be just converting everything to a string. The code surrounding this (utils.ts) does suggest that’s the case but I did not dig further.

Khem

[strzeluk]

I’ve issue a new bug on Github for this problem: [CosmosDB Node] problem with query params timestamp converted to string · Issue #23398 · n8n-io/n8n · GitHub