Dynamic Values in Filter Array Source From

[IFEJohn]

I am working with a JSON response that I need to query but the objects change (data types do not). In the example below, "2022-11-21:2" and "330.0" change all the time.

{
   "2022-11-21:2": {  
          "330.0": [{
                  "Field1": "TEST",
                   "Field2": "TEST"
           }],
            "333.0": [{
                   "Field1": "TEST",
                    "Field2": "TEST"
           }]
     }
}

So, I need to build an expression along the lines of:  body('Test')?['2022-11-21:2']?['330.0']

My question is...how do I include dynamic values in the expression? Or, is this not the best approach to parsing/querying the JSON?

[grantjenkins]

That's some very strange JSON output you're getting there 😕

What are you trying to get as final output? Will you be outputting just the values of the Field elements, or will you still be wanting to output "2022-11-21:2" and "330.0" as well?

If you can give us an example of what you'd like to see at the end, then we can work backwards to get what you're after.

[IFEJohn]

Thank you for your response. Ha, I know it's strange...but the response of an API I don't have control over. My final output will be values of field elements. But I cannot simply reference array number...need to reference the specific "330" or "333"...which could be any number.

[grantjenkins]

Is this the sort of output you were expecting? Or something else?

[
  {
    "Field 1": "One",
    "Field 2": "Two"
  },
  {
    "Field 1": "Three",
    "Field 2": "Four"
  }
]

The last thing I'm having trouble with is the single colon : within 2022-11-21:2.

[IFEJohn]

Thank you @grantjenkins. I actually just need...

{
    "Field 1": "One",
    "Field 2": "Two"
}

 Yeah, that colon is a tricky part. It seems to be an array sequence number of some sort...strange usage.

[grantjenkins]

Just confirming - I'm assuming even the 2 after the colon could change to something else:

"2022-11-21:2

[IFEJohn]

Yes, that value can change too, unfortunately. It is some kind of sequence number.

[Ajinder31]

@IFEJohn May we know if this response is from a Custom connector or existing published connectors? If it's from custom connector then do you have edit access to the connector.

The reason I am asking because I worked with similar scenario while working on a custom connector and Policy Object to Array helped me in my case. 

[IFEJohn]

It’s the response from an API call using the HTTP connector. 

[Ajinder31]

Hi @IFEJohn , I think we can go Custom connector route in this case as Http also requires a premium license (as per my knowledge). Custom connector also gives us the flexibility to transform response as required using custom code as well. 

At the same time, there might be better solution available which I am not aware of. 

[grantjenkins]

I think I've got that rouge colon sorted. Just wanted to confirm a couple more things.

What will the field names be? At the moment you have Field1 and Field2, but what are some of the actual field names (or are those them)?

And will the field names always be the same?

[IFEJohn]

@Ajinder31 Thanks for your suggestion, but I think building a custom connector for this is not a good approach for my use case.

@grantjenkins Wow, thank you! I really appreciate it. There are a lot of fields, but a couple would be "High Price" and "Low Price". Yes, the field names will always be the same.

[grantjenkins]

Ok, so a bit messy with quite a few replace expressions to clean up the raw input but should get what you're after.

Full flow below. I'll go into each of the actions.

Data is a Compose action that contains the raw JSON input.

{
    "2022-11-2122:2": {
        "330.0": [
            {
                "Field1": "One",
                "Field2": "Two"
            }
        ],
        "333.0": [
            {
                "Field1": "Three",
                "Field2": "Four"
            }
        ]
    }
}

Compose uses a few replace expressions to remove the colon between (in this example) 2122 and 2. It should work regardless of where it is. It actually replaces the valid colons temporarily, then replaces (removes) the non-valid one(s), and finally replaces the valid ones back to their original state. The expression used here is:

replace(replace(replace(string(outputs('Data')), '":', '"+'), ':', ''), '"+', '":')

XML is another Compose that replaces (removes) any periods and new lines, then converts the JSON to XML so we can use XPath. The expression used here is:

xml(json(replace(replace(concat('{"root": ', outputs('Compose'), '}'), '.', ''), decodeUriComponent('%0A'), '')))

The actual XML output we get here is:

<root>
  <_x0032_022-11-21222>
    <_x0033_300>
      <Field1>One</Field1>
      <Field2>Two</Field2>
    </_x0033_300>
    <_x0033_330>
      <Field1>Three</Field1>
      <Field2>Four</Field2>
    </_x0033_330>
  </_x0032_022-11-21222>
</root>

Finally, we have a Select to get our JSON output using XPath.

The input uses the following expression so that we can iterate over each of the fields.

xpath(outputs('XML'), '//root/*/*')

The expressions to get the field values (for this example) are below. Note that you would just need to change the field names to match what you have in your JSON.

xpath(item(), 'string(//Field1/text())')
xpath(item(), 'string(//Field2/text())')

This would give us the output below.

[
  {
    "Field 1": "One",
    "Field 2": "Two"
  },
  {
    "Field 1": "Three",
    "Field 2": "Four"
  }
]

You should then be able to use standard JSON to get what you're after.

[IFEJohn]

@grantjenkins Thank you SO much for taking the time to work on this. I really appreciate it. As much as I would love to say this is what I am looking for, it is a bit off. To add more context to my first post, I am looking to query the JSON payload with something like body('Test')?['2022-11-21:2']?['330.0']...but be able to pass in the date and "330.0" values as they will change. Am I missing something or does your approach allow me to do that? 

[grantjenkins]

If you wanted to get the second object you could do the following:

body('Select')[1]

If you wanted to get the value of Field 2 from the first object you could do the following:

body('Select')[0]?['Field 2']

Is that what you were after?

[IFEJohn]

@grantjenkins Thank you. Yes, I understand that, but I need to pass the dynamic date and decimal values into the select query. I wont know which object number a given record will be in the array. Make sense? 

[grantjenkins]

@IFEJohn Sorry, I'm not actually sure what you after in that sense. How will you know what to query if you don't know what it will be?

[IFEJohn]

@grantjenkins The values that are used to query are dynamic based on other actions in the flow...which is why I need the query string.

[grantjenkins]

I think I may have completely misunderstood (and over complicated) what you were after initially 🙂

Is this what you are after? Note the expression used for Compose Output is:

outputs('Compose')?[variables('date')]?[variables('time')]

This would result in:

Then if you just wanted to get the object from the array you could append [0] to the expression or wrap the entire expression with first.

first(outputs('Compose')?[variables('date')]?[variables('time')])

[IFEJohn]

@grantjenkins Thank you! Yes, this is what I was looking for. Seems so simple now that I see it. Ha. I think I was simply not using the correct syntax. I appreciate your help.