How to use expression builder to iterate through an array?

Question

How to use expression builder to iterate through an array?

Wei Yang 0

Hi, I am trying to use a lookup activity to build a SQL query to retrieve some data UNIONing multiple tables.

Input: [{"Table":"Accounts"},{"Table":"Users"}]

Output in expression builder:

SELECT * FROM [Accounts]

UNION

SELECT * FROM [Users]

P.S: I understand there is an iterate activity to use, and it helps iterate through multiple tables and fire single query per tables within array and essentially combined the output from each activity to create a combined output, but that is very costly.

Edit: The expression should stay the same as it would handle different length of array and it is able to union all the tables based on the how many input is given

Subashri Vasudevan 11,306 Volunteer Moderator

Hi
Wei Yang

I tried the below:

My varriables:

finalquery: value: select * from <placeholder1> union select * from <placeholder2> (Type: String)

tables: value : ["tbl_adf","tbl_subscribe"] (type: Array)

Screenshot 2023-12-07 at 3.48.51 PM

My lookup expression:

@replace(replace(variables('finalquery'),'<placeholder1>',string(variables('tables')[0])),'<placeholder2>',string(variables('tables')[1]))

As you can see, i am replacing <placeholder1> with first table in the array, and <placeholder2> with second table in the array. As you might see, it involves bit of hardcoding (index)

My look up output: (giving a portion of result)

"count": 100,
	"value": [
		{
			"Order Date": "2010-01-22",
			"Order ID": "925098025",
			"Ship Date": "2010-03-05",
			"Region": "Australia and Oceania",
			"Country": "New Zealand",
			"Item Type": "Beverages",
			"Sales Channel": "Online",
			"Order Priority": "C",
			"Units Sold": "5918",
			"Unit Price": "47.45",
			"Unit Cost": "31.79",
			"Total Revenue": "280809.1",
			"Total Cost": "188133.22",
			"Total Profit": "92675.88"
		},
		{
			"Order Date": "2010-01-27",
			"Order ID": "926436555",
			"Ship Date": "2010-02-11",
			"Region": "Europe",
			"Country": "Luxembourg",
			"Item Type": "Vegetables",
			"Sales Channel": "Online",
			"Order Priority": "C",
			"Units Sold": "2019",
			"Unit Price": "154.06",
			"Unit Cost": "90.93",
			"Total Revenue": "311047.14",
			"Total Cost": "183587.67",
			"Total Profit": "127459.47"
		},

please see if this works for you. Even it works, using a loop and going through tables is the best way :)

Thanks

Wei Yang 0 Reputation points

2023-12-07T13:42:43.29+00:00

Thanks for the answer, does the solution automatically scale with the number of items within the first array? In your case the number of items within the variable tables
Subashri Vasudevan 11,306 Reputation points Volunteer Moderator

2023-12-07T14:51:07.0633333+00:00

It won't. You have to manage the index. [0] [1] and so on. That's why I said it involves some hard-coding.
Wei Yang 0 Reputation points

2023-12-07T15:17:15.18+00:00

Thanks for the effort, it is indeed to reduce these effort of hardcoding, and having the solution to be scalable as the number of array size increase, nothing needs to be done on the expression itself. Sorry for not making that clear in the first place
Subashri Vasudevan 11,306 Reputation points Volunteer Moderator

2023-12-07T15:54:58.0166667+00:00

That's why it's better to loop through.
Subashri Vasudevan 11,306 Reputation points Volunteer Moderator

2023-12-08T03:27:14.8166667+00:00

@Wei Yang if you find the answer to be solving your question, request you to accept it as answer. And as mentioned in the comment, using foreach loop solves the issue of scalability.

Thank you.
Wei Yang 0 Reputation points

2023-12-08T13:05:12.4033333+00:00

Hi Suba, this solution does not solve my problem at the moment, I will keep this open and see if anybody can come up with other alternatives. Thank you
Subashri Vasudevan 11,306 Reputation points Volunteer Moderator

2023-12-08T15:23:41.3733333+00:00

Sure, looping few , @AnnuKumari-MSFT @KranthiPakala-MSFT

1 answer

Your answer

Subashri Vasudevan 11,306 Reputation points Volunteer Moderator

2023-12-07T10:22:09.03+00:00

Hi
Wei Yang

I tried the below:

My varriables:

finalquery: value: select * from <placeholder1> union select * from <placeholder2> (Type: String)

tables: value : ["tbl_adf","tbl_subscribe"] (type: Array)

My lookup expression:

@replace(replace(variables('finalquery'),'<placeholder1>',string(variables('tables')[0])),'<placeholder2>',string(variables('tables')[1]))

As you can see, i am replacing <placeholder1> with first table in the array, and <placeholder2> with second table in the array. As you might see, it involves bit of hardcoding (index)

My look up output: (giving a portion of result)

"count": 100, "value": [ { "Order Date": "2010-01-22", "Order ID": "925098025", "Ship Date": "2010-03-05", "Region": "Australia and Oceania", "Country": "New Zealand", "Item Type": "Beverages", "Sales Channel": "Online", "Order Priority": "C", "Units Sold": "5918", "Unit Price": "47.45", "Unit Cost": "31.79", "Total Revenue": "280809.1", "Total Cost": "188133.22", "Total Profit": "92675.88" }, { "Order Date": "2010-01-27", "Order ID": "926436555", "Ship Date": "2010-02-11", "Region": "Europe", "Country": "Luxembourg", "Item Type": "Vegetables", "Sales Channel": "Online", "Order Priority": "C", "Units Sold": "2019", "Unit Price": "154.06", "Unit Cost": "90.93", "Total Revenue": "311047.14", "Total Cost": "183587.67", "Total Profit": "127459.47" },

please see if this works for you. Even it works, using a loop and going through tables is the best way :)

Thanks
Wei Yang 0 Reputation points

2023-12-07T13:42:43.29+00:00

Thanks for the answer, does the solution automatically scale with the number of items within the first array? In your case the number of items within the variable tables
Subashri Vasudevan 11,306 Reputation points Volunteer Moderator

2023-12-07T14:51:07.0633333+00:00

It won't. You have to manage the index. [0] [1] and so on. That's why I said it involves some hard-coding.
Wei Yang 0 Reputation points

2023-12-07T15:17:15.18+00:00

Thanks for the effort, it is indeed to reduce these effort of hardcoding, and having the solution to be scalable as the number of array size increase, nothing needs to be done on the expression itself. Sorry for not making that clear in the first place
Subashri Vasudevan 11,306 Reputation points Volunteer Moderator

2023-12-07T15:54:58.0166667+00:00

That's why it's better to loop through.
Subashri Vasudevan 11,306 Reputation points Volunteer Moderator

2023-12-08T03:27:14.8166667+00:00

@Wei Yang if you find the answer to be solving your question, request you to accept it as answer. And as mentioned in the comment, using foreach loop solves the issue of scalability.

Thank you.
Wei Yang 0 Reputation points

2023-12-08T13:05:12.4033333+00:00

Hi Suba, this solution does not solve my problem at the moment, I will keep this open and see if anybody can come up with other alternatives. Thank you
Subashri Vasudevan 11,306 Reputation points Volunteer Moderator

2023-12-08T15:23:41.3733333+00:00

Sure, looping few , @AnnuKumari-MSFT @KranthiPakala-MSFT

Answer 1

KranthiPakala-MSFT 46,737 Microsoft Employee Moderator

@Wei Yang Thanks for posting your query.

As you are looking for a dynamic solution, it would be difficult to achieve this using ADF. Rather I would rather prefer to try with Azure Databricks or Azure Synapse spark for writing your custom code logic to achieve the same.

Share via

How to use expression builder to iterate through an array?

1 answer

Your answer