azure-logic-appsazure-logic-app-standard

How to pass several Id to SQL filter Logic Add?

In case i have 100 ids has to be passed into database table:

select * from A.Imports where ImportId in (1,..., 60)

How to pass those Id int OData filter for SQL Action? So, i have variable where i store Id to query -> ImportToProcess = [1, ..., 60]

Than i'm passing those to SQL like(havnt found any refences to similar filter like WHERE IN)

"queries": {
      "$filter": "@if(not(empty(variables('ImportsToProcess'))), concat('(ImportId eq ', join(variables('ImportsToProcess'), ' or ImportId eq '), ')'), '(ImportId eq -1)')"
    }

it turns out that the inpu during the wound is very large like: ImportId eq 1 or Import eq * ... ImportId eq 60

that causes the error for: OData query syntax tree has exceeded nodes count limit of '100'

Is there any type similarly filter like WHERE IN? If not, whats the options to pass correct parametrs to filter?

Solution 
  • "queries": {
"$filter": "@if(not(empty(variables('ImportsToProcess'))), concat('(ImportId eq ', join(take(variables('ImportsToProcess'), 20), ' or ImportId eq '), ')'), '(ImportId eq -1)')"

    }

    If you’re hitting the OData query syntax tree limit of 100 nodes, split your list of IDs into smaller chunks. For example, if you have 60 IDs, break it into groups of 10 or 20 and make multiple requests and iterate them from your code.