WSO2 EI Iteration with Array with Inner Json Array elements in WSO2 EI 6.5.0

Question

I am receiving jsonpayload as response after calling bigQuery API.

Source Table Data look like this:

JSON Payload for above Table:

{
 "kind": "bigquery#queryResponse",
 "schema": {
  "fields": [
   {
    "name": "Name",
    "type": "STRING",
    "mode": "NULLABLE"
   },
   {
    "name": "Age",
    "type": "INTEGER",
    "mode": "NULLABLE"
   }
  ]
 },
 "jobReference": {
  "projectId": "testbig-235116",
  "jobId": "job_GECobzPaLdbBW-SqIG-WrfOzaqtQ",
  "location": "US"
 },
 "totalRows": "2",
 "rows": [
  {
   "f": [
    {
     "v": "John"
    },
    {
     "v": "45"
    }
   ]
  },
  {
   "f": [
    {
     "v": "Harry"
    },
    {
     "v": "25"
    }
   ]
  }
 ],
 "totalBytesProcessed": "670",
 "jobComplete": true,
 "cacheHit": false
}

Note: In above table structure, only 2 rows. But in runtime 'n' number of rows might came.

I need to iterate over rows array elements, there is inner array named f which contains row data for corresponding columns .

• Columns names are defined in fields array ie Name, Age
• Row values are defined in 'rows' array.

I am trying to fetch each row data inside iteration and store it into property which can be used for further mediation like pushing fetched to another Database.

Iterator - Code:

<iterate expression="json-eval($.rows[*].f)" id="BigQID">
        <target>
            <sequence>
                <log level="custom">
                    <property name="*********Inside" value="Iterator **********"/>
                    <property expression="json-eval($.f[0].v[0])" name="*********v[0]"/>
                    <property expression="json-eval($.f[0].v[1])" name="*********v[1]"/>
                </log>
                <!-- Step 1: Fetch each row values and store it into property -->
                <!-- Step 2: Push data to Database by feed above row data-->
            </sequence>
        </target>
</iterate>

Output:

[2023-03-10 18:36:12,838] []  INFO - LogMediator *********Inside = Iterator **********, *********v[0] = , *********v[1] =

By using expression something like rows[0].f[0].v we can able to fetch, But inside Iterator how can we make this array values as dynamic. How can we fetch these data in dynamic manner ?

Could anyone able to help on this?

Answer 1

When you are Iterating over //rows you will have the following fragment of the payload in each iterate cycle.

{
   "f": [
    {
     "v": "XXXX"
    },
    {
     "v": "YYY"
    }
   ]
}

So in the Iterate, I believe it's always going to be $.f[0].v. Also if you want to iterate just over f values you can update the Iterate JSON Path to json-eval($.rows[*].f) as well. So I don't see why you would need a dynamic index. Am I missing something?

Update

Variation 1

<iterate expression="json-eval($.rows[*].f)" id="1" sequential="true">
    <target>
        <sequence>
            <log level="full">
                <property name="1:::" expression="json-eval($.[0].v)"></property>
                <property name="2:::" expression="json-eval($.[1].v)"></property>   
            </log>
        </sequence>
    </target>
</iterate>

Variation 2

<iterate expression="json-eval($.rows)" id="1" sequential="true">
    <target>
        <sequence>
            <log level="full">
                <property name="1:::" expression="json-eval($.f[0].v)"></property>
                <property name="2:::" expression="json-eval($.f[1].v)"></property>   
            </log>
        </sequence>
    </target>
</iterate>