Powerautomate Parsing JSON Array

Question

I've seen the JSON array questions here and I'm still a little lost, so could use some extra help.

Here's the setup: My Flow calls a sproc on my DB and that sproc returns this JSON:

{
  "ResultSets": {
    "Table1": [
      {
        "OrderID": 9518338,
        "BasketID": 9518338,
        "RefID": 65178176,
        "SiteConfigID": 237
      }
    ]
  },
  "OutputParameters": {}
}

Then I use a PARSE JSON action to get what looks like the same result, but now I'm told it's parsed and I can call variables.

Issue is when I try to call just, say, SiteConfigID, I get "The output you selected is inside a collection and needs to be looped over to be accessed. This action cannot be inside a foreach."

After some research, I know what's going on here. Table1 is an Array, and I need to tell PowerAutomate to just grab the first record of that array so it knows it's working with just a record instead of a full array. Fair enough. So I spin up a "Return Values to Virtual Power Agents" action just to see my output. I know I'm supposed to use a 'first' expression or a 'get [0] from array expression here, but I can't seem to make them work. Below are what I've tried and the errors I get:

Tried:

first(body('Parse-Sproc')?['Table1/SiteConfigID']) 
Got: InvalidTemplate. Unable to process template language expressions in action 'Return_value(s)_to_Power_Virtual_Agents' inputs at line '0' and column '0': 'The template language function 'first' expects its parameter be an array or a string. The provided value is of type 'Null'. Please see https://aka.ms/logicexpressions#first for usage details.'.

Also Tried:

body('Parse-Sproc')?['Table1/SiteconfigID']
which just returns a null valued variable

Finally I tried

outputs('Parse-Sproc')?['Table1']?['value'][0]?['SiteConfigID']
Which STILL gives me a null-valued variable. It's the worst. 
In that last expression, I also switched the variable type in the return to pva action to a string instead of a number, no dice. 
Also, changed 'outputs' in that expression for 'body' .. also no dice

Here is a screenie of the setup:

To be clear: the end result i'm looking for is for the system to just return "SiteConfigID" as a string or an int so that I can pipe that into a virtual agent.

I've provided an answer, but, you shouldn't actually need the Parse JSON step, we can avoid that but to do so, I need to see the exact output that the SQL call gives you. — Skin
– Skin, Commented Apr 26, 2022 at 21:03

Skin · Accepted Answer · 2022-04-26 20:56:02Z

1

I believe this is what you need as an expression ...

body('Parse-Sproc')?['ResultSets']['Table1'][0]?['SiteConfigID']

You can see I'm just traversing down to the object and through the array to get the value.

Naturally, I don't have your exact flow but if I use your JSON and load it up into Parse JSON step to get the schema, I am able to get the result. I do get a different schema to you though so will be interesting to see if it directly translates.

answered Apr 26, 2022 at 20:56

Skin

11.7k3 gold badges16 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Powerautomate Parsing JSON Array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related