Power Platform Community Forum Thread Details

Oracle Query to JSON Parse Resulting in Null Array

I'm building a flow to take an Oracle query and insert new rows into a SQL table. The use case for this stems from not having access to control the Oracle source data, so I need to get it out of Oracle and into SQL where I can do with it what I want.

Here's a summary of my flow:

It results in the following error at the last step of the flow:

The execution of template action 'Apply_to_each' failed: the result of the evaluation of 'foreach' expression '@body('Parse_JSON')?['body']?['ResultSets']?['Table1']' is of type 'Null'. The result must be a valid array.

Here's the flow in more detail:

Here is the JSON schema:

{
 "type": "object",
 "properties": {
 "statusCode": {
 "type": "integer"
 },
 "headers": {
 "type": "object",
 "properties": {
 "Pragma": {
 "type": "string"
 },
 "Vary": {
 "type": "string"
 },
 "x-ms-request-id": {
 "type": "string"
 },
 "Strict-Transport-Security": {
 "type": "string"
 },
 "X-Content-Type-Options": {
 "type": "string"
 },
 "X-Frame-Options": {
 "type": "string"
 },
 "Cache-Control": {
 "type": "string"
 },
 "Set-Cookie": {
 "type": "string"
 },
 "x-ms-connection-gateway-object-id": {
 "type": "string"
 },
 "Timing-Allow-Origin": {
 "type": "string"
 },
 "x-ms-apihub-cached-response": {
 "type": "string"
 },
 "Date": {
 "type": "string"
 },
 "Content-Type": {
 "type": "string"
 },
 "Expires": {
 "type": "string"
 },
 "Content-Length": {
 "type": "string"
 }
 }
 },
 "body": {
 "type": "object",
 "properties": {
 "OutputParameters": {
 "type": "object",
 "properties": {}
 },
 "ResultSets": {
 "type": "object",
 "properties": {
 "Table1": {
 "type": "array",
 "items": {
 "type": "object",
 "properties": {
 "PRODUCTLISTPERCUSTOMER_ID": {
 "type": "integer"
 },
 "CUSTOMER_ID": {
 "type": "integer"
 },
 "DESCRIPTION": {
 "type": "string"
 },
 "CODE": {
 "type": "string"
 },
 "DATEINACTIVE": {
 "type": "string"
 }
 },
 "required": [
 "PRODUCTLISTPERCUSTOMER_ID",
 "CUSTOMER_ID",
 "DESCRIPTION",
 "CODE",
 "DATEINACTIVE"
 ]
 }
 }
 }
 }
 }
 }
 }
}

The output from the Parse JSON step is not null - I can see the array. So, why is the array passing through as null when I try to insert rows into my SQL table? I've tried initializing variables, appending to the array, etc. ... and I'm completely stumped at this point. Any thoughts? Thanks in advance for the help!

{ "type": "object", "properties": { "body": { "type": "object", "properties": { "ResultSets": { "type": "object", "properties": { "Table1": { "type": "array", "items": { "type": "object", "properties": { "Var": { "type": "string" } }, "required": [ "Var" ] } } } } } } } }

Quick Links