Data Factory Expression Builder SubQuery

Question

Is it possible to do something like this in Data Factory (data flow expression builder)? I am trying to create an array within an array but with a filter so it doesn't create an array with an empty item.

When I do this, it works however produces an array that looks like fooBar: [{ }] note the array contains a single item.

collect(@(
	optionNumber=OptionNo,
	price=OptionPrice,
	salePricePeriods=array(@(
		salePrice=OptionSalePrice,
		priceActiveFrom=toString(OptionSaleFrom),
		priceActiveTo=toString(OptionSaleTo)))))

Ideally, I want to filter this data by using an expression:

collect(@(
    optionNumber=OptionNo,
    price=OptionPrice,
    salePricePeriods=
    filter(
       collect(@(
          salePrice=OptionSalePrice, 
          priceActiveFrom=toString(OptionSaleFrom),
          priceActiveTo=toString(OptionSaleTo))),
       and(not(isNull(#item.salePrice)), and(not(isNull(#item.priceActiveFrom)), not(isNull(#item.priceActiveTo)))))))

When I do the above I get an error stating

Job failed due to reason: It is not allowed to use an aggregate function in the argument of another aggregate function. Please use the inner aggregate function in a sub-query.;; Aggregate [958abb16-5236-430c-9af6-497495d60469#23243], [958abb16-5236-430c-9af6-497495d60469#23243, first(DataSet#22183, false) AS DataSet#23295, first(Realm#22184, false) AS Realm#23297, first(Territory#22185, false) AS Territory#23299, first(ItemNo#22186, false) AS ItemNo#23301, first(PriceGroupCode#22187, false) AS PriceGroupCode#23303, first(MinOptionPrice#22196, false) AS MinOptionPrice#23305, first(MaxOptionPrice#22197, false) AS MaxOptionPrice#23307, min(MinOptionSalePriceForPeriod#22198) AS MinOptionSalePrice#23309, max(MaxOptionSalePriceForPeriod#22199) AS MaxOptionSalePrice#23311, first(OldestDatePointMinPrice#22203, false) AS OldestDatePointMinPrice#23313, first(OldestDatePointMaxPrice#22204, false) AS OldestDatePointMaxPrice#23315, collect_list(named_struct(optionNumber, OptionNo#22189, price, OptionPrice#22191, salePrice

Answer 1

@Onam Chilwan I heard back from product group. Try

collect(@(
    optionNumber=OptionNo,
    price=OptionPrice,
    salePricePeriods=
    filter(  array(@(
          salePrice=OptionSalePrice, 
          priceActiveFrom=toString(OptionSaleFrom),
          priceActiveTo=toString(OptionSaleTo))), and(not(isNull(salePrice)), and(not(isNull(priceActiveFrom)), not(isNull(priceActiveTo)))))))