cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Hetvik
Helper I
Helper I

Remove elements from Json array - Power Automate

Hi All

I am getting data from data verse table with specific set of columns in flow and i am getting some unwanted columns.

I want to remove those unwanted columns

 

 

Below is my Json returned in compose .I would like to remove the first 4 columns from the below json array  and last but one column in the  below json "cr12b_campaignid@odata.type":

 

{
        "@odata.type""#Microsoft.Dynamics.CRM.cr12b_campaign",
        "@odata.etag""W/\"10354901\"",
        "@odata.editLink""cr12b_campaigns(6980e4e6-0497-eb11-b1ac-000d3acb93e3)",
        "cr12b_amount""100",
        "cr12b_businesstype""Azure",
        "cr12b_expiry""2-Jan-21",
        "cr12b_location""East",
        "cr12b_name""K1",
        "cr12b_policy""100242",
        "cr12b_region""Europe1",
        "cr12b_state""NY",
        "cr12b_campaignid@odata.type""#Guid",
        "cr12b_campaignid""6980e4e6-0497-eb11-b1ac-000d3acb93e3"
    },

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Paulie78
Super User III
Super User III

you can still do it with a select, but in text mode, as long as the columns you want to remove, are always the same, like this:

2021-04-09_17-08-22.png

As you can see, it does not matter which columns are present, it outputs the whole array, removing unwantedField1 and unwantedField2.

 

Does that make sense?

View solution in original post

Paulie78
Super User III
Super User III

In your case it is actually a little more complex because removeProperty function will not work with any field name with a period/fullstop in it. But this does what you want:

2021-04-09_17-28-31.png

Expressions are:

Select From:
json(replace(string(outputs('Array')), '@odata.', 'odata'))

Select Map:
removeProperty(removeProperty(removeProperty(removeProperty(removeProperty(item(), 'cr12b_campaignid@odatatype'), 'odatatype'), 'odataid'), 'odataetag'), 'odataeditLink')

View solution in original post

9 REPLIES 9
Paulie78
Super User III
Super User III

Simply pass your JSON array into a select action and reformat it to your requirements. There is another method which involves the use of the removeProperty expression, but I think the select action would be best for you. Give it a try, are you familiar with how to use it?

Hetvik
Helper I
Helper I

Thanks Paulie78,
 In my case the number of columns varies so i can't use select . When i tried to use removeproperty i got below error

 

The template language function 'removeProperty' expects its first parameter to be of type object. The provided value is of type 'Array'.'.

 

I converted it into json

json(replace(string(outputs('Compose_2')),'@odata.etag','odataetag')) 
 
but when i use remove property i got the above mentioned error .
 
My output is of the below format
 
[
{
"@odata.type": "#Microsoft.Dynamics.CRM.cr12b_campaign",
"@odata.id": "https://orgd8f49cc8.crm6.dynamics.com/api/data/v9.1/cr12b_campaigns(6980e4e6-0497-eb11-b1ac-000d3acb...)",
"@odata.etag": "W/\"10354901\"",
"@odata.editLink": "cr12b_campaigns(6980e4e6-0497-eb11-b1ac-000d3acb93e3)",
"cr12b_amount": "100",
"cr12b_businesstype": "Azure",
"cr12b_expiry": "2-Jan-21",
"cr12b_location": "East",
"cr12b_name": "K1",
"cr12b_policy": "100242",
"cr12b_region": "Europe1",
"cr12b_state": "NY",
"cr12b_campaignid@odata.type": "#Guid",
"cr12b_campaignid": "6980e4e6-0497-eb11-b1ac-000d3acb93e3"
},
{
"@odata.type": "#Microsoft.Dynamics.CRM.cr12b_campaign",
"@odata.id": "https://orgd8f49cc8.crm6.dynamics.com/api/data/v9.1/cr12b_campaigns(1e086971-0797-eb11-b1ac-000d3acb...)",
"@odata.etag": "W/\"10354902\"",
"@odata.editLink": "cr12b_campaigns(1e086971-0797-eb11-b1ac-000d3acb93e3)",
"cr12b_amount": "101",
"cr12b_businesstype": "Azure",
"cr12b_expiry": "2-Jan-22",
"cr12b_location": "Midwest",
"cr12b_name": "k2",
"cr12b_policy": "K483",
"cr12b_region": "Europe2",
"cr12b_state": "NY",
"cr12b_campaignid@odata.type": "#Guid",
"cr12b_campaignid": "1e086971-0797-eb11-b1ac-000d3acb93e3"
]
Paulie78
Super User III
Super User III

How does the number of columns change if it is coming from a dataverse table? 

I have to filter the table based on some category and for each category the number of columns would be varying
E.g : 

category 1 : 5 columns

category 2: 8 columns

 

I am passing column names to the flow from powerapps 

Paulie78
Super User III
Super User III

you can still do it with a select, but in text mode, as long as the columns you want to remove, are always the same, like this:

2021-04-09_17-08-22.png

As you can see, it does not matter which columns are present, it outputs the whole array, removing unwantedField1 and unwantedField2.

 

Does that make sense?

View solution in original post

Paulie78
Super User III
Super User III

In your case it is actually a little more complex because removeProperty function will not work with any field name with a period/fullstop in it. But this does what you want:

2021-04-09_17-28-31.png

Expressions are:

Select From:
json(replace(string(outputs('Array')), '@odata.', 'odata'))

Select Map:
removeProperty(removeProperty(removeProperty(removeProperty(removeProperty(item(), 'cr12b_campaignid@odatatype'), 'odatatype'), 'odataid'), 'odataetag'), 'odataeditLink')

View solution in original post

Hetvik
Helper I
Helper I

Excellent Paulie78 got the desired result .

Only thing is my column names have "." so can't use removeproperty on it ,  has to rename the column names from output . some thing like this and then used removeproperty.

JSON(replace(replace(replace(replace(string(outputs('Compose_2')), '@odata.type', 'odatatype'),'@odata.id', 'odataid'),'@odata.etag','odataetag'),'@odata.editLink','odataeditLink'))

Paulie78
Super User III
Super User III

Yes, I posted the replace code above also. 😃 Well done! 😘

HI @Paulie78  Just want to check can i use the removeproperty with in apply each loop as my columns are dynamically passed .
I will get an array of columns to be removed and would liek to pass in the item of array to the removeproperty . I tried it but it is not working .

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors
Users online (2,399)