Accessing Append to Array Variable properties from SQL Get Rows v2

[PhoenixPubInc]

I'm new at power automate. Im puzzled about accessing property or value from Append to array variable like the "pr_no" using variables('PRNoRows')[value]['pr_no'] but i can't get it done. Please see below the result of the array. I am gladly happy if someone knows any approaces on how to get the properties of the array. Thank you.

{
"name": "PRNoRows",
"value": {
    "@odata.context": "https://asia-001.azure-apim.net/apim/sql/409448f6fa753b8be39fc/$metadata#datasets('default%2Cdefault')/tables('%5Bdbo%5D.%5BvwCt%5D')/items",
    "value": [
        {
            "@odata.etag": "",
            "ItemInternalId": "906fe260-c67a-49fb-a32e-f6facf75c907",
            "id": 8259,
            "control_number": "P-CR003920",
            "cr_date": "2021-12-13T00:00:00Z",
            "company_id": 1,
            "code_count": 3920,
            "cr_no": "CR# 03320",
            "pr_no": "25342",
            "pr_date": "12/10/2021"
           
        }
    ]
}

[Expiscornovus]

Hi @PhoenixPubInc,

It looks like you have two value properties in your array. If that is correct, the second value property also needs to be in the expression.

Because it is an array you need to specify which item you want to retrieve. In this case you have only one item, so an index [0] would be sufficient.

With more items a Select items might be more useful.

Try an expression like below to get the pr_no value for the sample you shared in your post:

variables('PRNoRows')['value']['value'][0]['pr_no']

[PhoenixPubInc]

Hi @Expiscornovus,

I'm still getting an error using your solution.

Unable to process template language expressions in action 'Compose' inputs at line '0' and column '0': 'The template language expression 'variables('PRNoRows')['value']['value'][0]['pr_no']' cannot be evaluated because property 'value' cannot be selected. Array elements can only be selected using an integer index. Please see https://aka.ms/logicexpressions for usage details.'.

Here's is the screenshot of the flow

[Expiscornovus · Accepted solution]

Hi @PhoenixPubInc,

Apologies, I now see in your screenshot it is a nested array. I missed that part 😁

In this case you need two index numbers. Try something like below:

variables('PRNoRows')[0]['value'][0]['pr_no']

[PhoenixPubInc]

Hi @Expiscornovus,

It worked! Awesome, thank you for this. Been banging my head for this 😁. 

[PhoenixPubInc]

Question, @Expiscornovus how come it's nested?

[Expiscornovus]

Hi @PhoenixPubInc,

No problem. Nested arrays are always fun 😁

Btw, the sample I shared earlier only retrieves the pr_no of the first item of your array. In case you want to get the pr_no of all your array items you could use a Select like below.

[Expiscornovus]

Hi @PhoenixPubInc,

It might be nested because of the configuration in the append to array variable.

Can you show/share a screenshot which fields you are using from the SQL Get Rows (v2) in the Select an output from previous steps field in the Condition action and in the Value field of your append array variable action?

[PhoenixPubInc]

Hi @Expiscornovus 

Here's the screenshot of my flow from array variable to sql get rows v2 and append to array variable.

Below is the SQL Get Rows v2 Outputs

{
  "statusCode": 200,
  "headers": {
    "Pragma": "no-cache",
    "Transfer-Encoding": "chunked",
    "Vary": "Accept-Encoding",
    "x-ms-request-id": "d6477811-19cb-425a-a07c-8e43962a07c3",
    "Strict-Transport-Security": "max-age=31536000; includeSubDomains",
    "X-Content-Type-Options": "nosniff",
    "X-Frame-Options": "DENY",
    "x-ms-connection-gateway-object-id": "07807ecc-61c7-436a-869c-cfcf161f434f",
    "Timing-Allow-Origin": "*",
    "x-ms-apihub-cached-response": "false",
    "x-ms-apihub-obo": "true",
    "Cache-Control": "no-store, no-cache",
    "Date": "Tue, 12 Jul 2022 06:09:55 GMT",
    "Content-Type": "application/json; charset=utf-8; odata.metadata=minimal",
    "Expires": "-1",
    "Content-Length": "1374"
  },
  "body": {
    "@odata.context": "https://asia-001.azure-apim.net/apim/sql/409448f6testtest/$metadata#datasets('default%2Cdefault')/tables('%5Bdbo%5D.%5BvwCRW%5D')/items",
    "value": [
      {
        "@odata.etag": "",
        "ItemInternalId": "906fe260-c67a-49fb-a32e-f6facf75c907",
        "id": 8259,
        "control_number": "P-CR003920",
        "cr_date": "2021-12-13T00:00:00Z",
        "company_id": 1,
        "code_count": 3920,
        "cr_no": "CR# 03320",
        "pr_no": "25342",
        "pr_date": "12/10/2021",
        "with_tin": "",
        "customer_name": "RELIGIOUS OF THE VIRGIN MARY (RVM)"
      }
    ]
  }
}

I'm thinking if my flow is redundant for the data, since I have get rows and also make to append the rows to an array variable. I just want to get the "pr_no" and "customer_name". I'm not sure if I can directly access the action SQL Get Rows to get the column I want and put it on my email send action?

[Expiscornovus]

Hi @PhoenixPubInc,

I see you used the body field in the append to array variable action. I think that is the reason why it is nested. You could use the current item property instead. 

And yes, you could also use a Select directly after the Get Rows. Technically it would not be needed to append it to an array variable.

Try something like below. 

Double check if the expression with the item() is still valid. You might need to update this.

[PhoenixPubInc]

Hi @Expiscornovus 

I successfully set an action Select after the Get Rows but I can't seem to extract the values. I'm using body('Select')['Pr No.'] but it wont work. I also tried body('Select')[0] but won't do. 

Below is the screenshot for Select body