Remove elements from Json array - Power Automate

[Hetvik]

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.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"

    },

[Paulie78]

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]

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]

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

[Hetvik]

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 · Accepted solution]

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:

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?

[Paulie78 · Accepted solution]

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:

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')

[Hetvik]

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]

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

[Hetvik]

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 .