In Azure Logic Apps, how do I take SQL query results that are JSON and populate an array so I can use the results is a conditional logic?

Question

I want to use conditional logic in an Azure Logic App like, When ID's are in this group of ID's, do some action. The group of ID's stored in a SQL database. The SQL query results look like this:

{
  "ResultSets": {
    "Table1": [
      {
        "ID": 85
      },
      {
        "": 88
      },
      {
        "ID": 177
      }
    ]
  },
  "ReturnCode": 0,
  "OutputParameters": {}
}

If I had these values in a numeric array like [85,88,177] the conditional logic would work, I tested that. I tried to use Parse_JSON to populate the array but the results look like ID:85, ID:88, ID:177 and didn't work in the contains function which is expecting a clean array. How do I cleanly populate an array based on JSON? Or, is there a way to use the sql result directly in the conditional logic? Thanks

Answer 1

To transform your JSON –

{
  "ResultSets": {
    "Table1": [
      {
        "ID": 85
      },
      {
        "ID": 88
      },
      {
        "ID": 177
      }
    ]
  },
  "ReturnCode": 0,
  "OutputParameters": {}
}

to an array –

[ 85, 88, 177 ]

– you need to use the Select action, using the Table1 array as input, and selecting @item()?['ID']:

Result: