Home > OS >  How I can go deep into a JSON data and loop thorugh an array
How I can go deep into a JSON data and loop thorugh an array

Time:06-14

I have an external api which return the following JSON:-

{
  "resultSetMetaData": {
    "page": 0,
    "numPages": 2,
    "numRows": 3,
    "format": "json",
    "rowType": [
      {
        "name": "FIRST_NAME",
        "database": "PROD",
        "schema": "RIC",
        "table": "OWNER_VW",
        "type": "text",
        "scale": null,
        "precision": null,
        "nullable": true,
        "byteLength": 16777216,
        "collation": null,
        "length": 16777216
      },
      {
        "name": "LAST_NAME",
        "database": "PROD",
        "schema": "RIC",
        "table": "OWNER_VW",
        "type": "text",
        "scale": null,
        "precision": null,
        "nullable": true,
        "byteLength": 16777216,
        "collation": null,
        "length": 16777216
      },
      {
        "name": "EMAIL",
        "database": "PROD",
        "schema": "RIC",
        "table": "OWNER_VW",
        "type": "text",
        "scale": null,
        "precision": null,
        "nullable": true,
        "byteLength": 16777216,
        "collation": null,
        "length": 16777216
      },
      {
        "name": "CITY",
        "database": "PROD",
        "schema": "RIC",
        "table": "OWNER_VW",
        "type": "text",
        "scale": null,
        "precision": null,
        "nullable": true,
        "byteLength": 16777216,
        "collation": null,
        "length": 16777216
      },
      {
        "name": "ZIP",
        "database": "PROD",
        "schema": "RIC",
        "table": "OWNER_VW",
        "type": "text",
        "scale": null,
        "precision": null,
        "nullable": true,
        "byteLength": 16777216,
        "collation": null,
        "length": 16777216
      },
      {
        "name": "STATE",
        "database": "PROD",
        "schema": "RIC",
        "table": "OWNER_VW",
        "type": "text",
        "scale": null,
        "precision": null,
        "nullable": true,
        "byteLength": 16777216,
        "collation": null,
        "length": 16777216
      },
      {
        "name": "ADDRESS",
        "database": "PROD",
        "schema": "RIC",
        "table": "OWNER_VW",
        "type": "text",
        "scale": null,
        "precision": null,
        "nullable": true,
        "byteLength": 16777216,
        "collation": null,
        "length": 16777216
      },
      {
        "name": "PHONE",
        "database": "PROD",
        "schema": "RIC",
        "table": "OWNER_VW",
        "type": "text",
        "scale": null,
        "precision": null,
        "nullable": true,
        "byteLength": 16777216,
        "collation": null,
        "length": 16777216
      },
      {
        "name": "UID",
        "database": "PROD",
        "schema": "RIC",
        "table": "OWNER_VW",
        "type": "text",
        "scale": null,
        "precision": null,
        "nullable": true,
        "byteLength": 16777216,
        "collation": null,
        "length": 16777216
      }
    ]
  },
  "data": [
    [
      "0",
      "HoT",
      "Hot",
      "[email protected]",
      "Miami",
      "33",
      "Florida",
      "",
      "",
      ""
    ],
    [
      "1",
      "Richie",
      "Hot",
      "[email protected]",
      "Miami",
      "331",
      "Florida",
      "",
      "",
      ""
    ],
    [
      "2",
      "Jeff",
      "Hot",
      "[email protected]",
      "Miami",
      "33",
      "Florida",
      "",
      "(0",
      "4"
    ]
  ],
  "code": "090001",
  "statementStatusUrl": "/api/statements/01a4d180-0b03-2ad4-0000-f67903a02826?requestId=4d32040e-ba17-4936-a013-7dd7ab1797aa",
  "requestId": "4d32040e-ba17-4936-a013-7dd7ab1797aa",
  "sqlState": "00000",
  "statementHandle": "01a4d180-0b03-2ad4-0000-f67903a02826",
  "message": "Statement executed successfully.",
  "createdOn": 1654725152956
} 

now i want to loop through the items inside the data[] array, so how i can do so?

Now i wrote a test Flow and i create a Parse JSON action with the above JSON as the input and as the template, then i will get these.. so i only get rowType.. so how i can loop through the data[] array inside the rowType?

johnjohn123_0-1655159045919.png

Thanks

CodePudding user response:

I loaded your data into an object variable called Data and then constructed a For each action beneath that ...

Flow

The expression in the screenshot is simply ...

variables('Data')['data']

That will loop through your array of data elements.

To get each item (which in this case is an array) then follow this pattern.

Firstly, initialise a variable (top arrow, variable is called Initialize Item) that is of type Array ...

Flow

Then within the For each loop, set that variable (as shown) by using the Current item dynamic object.

You will get your result from there.

Result ...

Result

CodePudding user response:

I haven't used power automate personally, but I understand that inline javascript can be run from power automate, so your best bet is to import the https response to a javascript function, and parse it there, like so.

function parseResponse(res){
     dataArr = res["data"];
     for (var i = 0; i < dataArr.length; i  ){
          //do whatever you need to do to the data here..., return it afterwards
     }
}
  • Related