Filter Json Array with Power Automate

(0) Share

Report

Posted on by ndpro

Hi All,

I am getting below Json array and want to filter values.

[{

"start": {

"dateTime": "2022-07-29T07:00:00.0000000",

"timeZone": "UTC"

"end": {

"dateTime": "2022-07-29T07:30:00.0000000",

"timeZone": "UTC"

}

]

I used below query to filter the date time time value with current date but its not returning any data.

item()?['start']?['datetime']

how we use filter query for getting the values.

Categories:

General topics

I have the same question (0)

All responses (6)

Answers (0)

DamoBird365 8,942 Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @ndpro

In your example you have an array with a single object containing a start and end object. Whilst you can call the start and end object by key name, the main object {} needs to be called by integer index or by using the first() expression. Item is generally used within a loop / apply to each or select.

But based on what is visible in your question, this should work:

first(item())?['start']?['datetime']

OR

item()?[0]?['start']?['datetime']

Cheers
Damien

Please take a look and subscribe to my YouTube Channel for more Power Platform ideas and concepts. Thanks

Was this reply helpful? Yes No
ndpro 28 on at

Like (0)

Report
Copy link

Link copied!

@DamoBird365 Thanks for quick response.

I am not having single object and its containing more than 100 objects. I copied only single object in array. I need to filter whole json file with start datetime with current date.

[{

    "start": {
      "dateTime": "2022-07-29T07:00:00.0000000",
      "timeZone": "UTC"
    },
    "end": {
      "dateTime": "2022-07-29T07:30:00.0000000",
      "timeZone": "UTC"
    }
},
{

    "start": {
      "dateTime": "2022-07-29T07:00:00.0000000",
      "timeZone": "UTC"
    },
    "end": {
      "dateTime": "2022-07-29T07:30:00.0000000",
      "timeZone": "UTC"
    }
},
{

    "start": {
      "dateTime": "2022-07-29T07:00:00.0000000",
      "timeZone": "UTC"
    },
    "end": {
      "dateTime": "2022-07-29T07:30:00.0000000",
      "timeZone": "UTC"
    }
},
]

Was this reply helpful? Yes No
VictorIvanidze 13,079 on at

Like (0)

Report
Copy link

Link copied!

Have a look at this: https://chercher.tech/power-automate/handling-json-files-power-automate

Was this reply helpful? Yes No
ndpro 28 on at

Like (0)

Report
Copy link

Link copied!

@VictorIvanidze I am having same solution for my power automate to filter. its just working only with below type of date time not with my json which i mentioned in above comments

{
"date":"datetime"

}

Was this reply helpful? Yes No
DamoBird365 8,942 Microsoft Employee on at

Like (2)

Report
Copy link

Link copied!

Hi @ndpro

You can use a select to repurpose your array and provide an output as follows:

I have put your array into a compose (you have a spare , after the last object which I have remove and then I have created the following select:

item()?['start/datetime']

item()?['end/datetime']

If you want to filter on the original array, you can do the following:

I updated the three start dates to 27,28 and 29 and the output was 2 of the 3 objects based on the above filter.

Cheers
Damien

Please take a look and subscribe to my YouTube Channel for more Power Platform ideas and concepts. Thanks

Was this reply helpful? Yes No
DamoBird365 8,942 Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

@ndpro of course you might just be looking for a way to format the current date?

formatDateTime(utcNow(),'yyyy-MM-dd') so that it is the same format as the date in your array.

Damien

Was this reply helpful? Yes No