Join Results of 2 Data Collections
Using the Data Transformations options you can easily join the results of two different data sources such as - a database query and an API response, the results of 2 database queries from 2 different databases, or any other array data. In our example we will show you how to join the results of 2 database queries from 2 different databases.
We have two database queries using two different database connections, so we can't join them using the query builder. We can join them using the Data Transformations available in Server Connect.
This is the first database query:
And this is the second one:
If we run this server action, they return the following data:
The first query returns orders (order ID and product ID) and the second one the product details. We want to join them so that they are returned together as one data source.
Join Data Sources
Add a new step in your API Action:
Open the Data Transformations Category and select Join:
Here we need to select our two data collections. Click the dynamic data picker for the first one:
Select the first query and click the select button:
Do the same for the second data collection:
Select the second query and click the select button:
Now we need to select columns to match them by:
Enter the names of the columns of the first and second collections which you want to match by:
Make sure to enable Output for the join step:
And then disable the output for the two query steps, as we don't need them to be outputted. Save your API Action:
And you can see the results, our two collections are joined: