How to convert SQL rows to an array of json objects in Azure Data Factory?

Question

I'm working on a Azure Data Factory Pipeline and have following challenge currently: I'd like to make an API call (Post) which requires some data with the syntax of array and in it, multiple objects.

Now - both, the data retrieving (from SQL db) and API call work when used independently (In case of the API call: I've been using hardcoded mock data for the body). The challenge is in connecting both of them. That means: I'd like to get multiple rows out of a SQL table, convert them to the required json structure and fill that data then into the API call. See picture below:

In simple steps explained again:

Get rows from SQL table
Convert each row into an object e.g. { "somekey": valueOfRow}
Collect all objects in an array
Provide array to API call

I'm just unsure how to proceed with the connection.

Additional Information

As requested, some further detailed information.

Currently the API call uses following hardcoded mockdata:

[{"idType": "ID_ISIN", "idValue": "US0123456789" }]

From the dataflow I'll get rows with one column called isin (with row values such as US0123456789)

The goal is to fill API's body dynamically such that it receives something like this:

[
    {
        "idType": "ID_ISIN", 
        "idValue": "US0123456789" 
    },
    {
        "idType": "ID_ISIN", 
        "idValue": "US9876543210" 
    },
    {...}

]

I saw that one can achieve something similiar with SQL query - see: https://learn.microsoft.com/en-us/azure/azure-sql/database/json-features?view=azuresql

But I'd miss the part "idType": "ID_ISIN" in each row/object.

Can you please confirm these: What does the dataflow activity in the given image do? And how may rows are you getting from SQL database? — Saideep Arikontham
– Saideep Arikontham, Commented Aug 31, 2022 at 13:41
I intentionally did not add more information to not distract from the core issue. But to answer your questions: The dataflow activity just accesses a SQL table and applies a filter (since I'm not interested in all rows). There's no sink. And right now only 1 row since I'm just testing but in the future it should handle anything between 1 and 100 rows (the api does not support more data point than 100). — Scorpia
– Scorpia, Commented Aug 31, 2022 at 13:52
Can you please provide the Hardcoded mock data (array of objects) that you have been using for API call — Saideep Arikontham
– Saideep Arikontham, Commented Aug 31, 2022 at 14:35
I added some additional information - let me know if something needs further clarification — Scorpia
– Scorpia, Commented Aug 31, 2022 at 14:56
So, here idType and idValue are the names of columns in your SQL table? Or ID_ISIN is the column name and US0123456789 is its value — Saideep Arikontham
– Saideep Arikontham, Commented Aug 31, 2022 at 15:01

Saideep Arikontham · Accepted Answer · 2022-08-31 16:14:17Z

1

Using dataflow to retrieve records and create an array of objects (where each object is row from SQL table) might not be the right way to achieve your requirement.

We can use Lookup activity which returns the rows based on given table or query as an array of objects. Look at the following demonstration.
The following is the data in my table repro1 which I am going to use (we only need ID_ISIN column).

enter image description here

In your data factory studio, create a new lookup activity. Create a new source dataset for your SQL database table and click OK.
Uncheck First Row only checkbox. There is no need to select a table here, instead we can just query from the required table based on requirement. Therefore, check the Query box.
Now, I have written the following query to extract data as required from the repro1 table (such that it would give results which are in line with hardcoded mock data).

select 'ID_ISIN' as idType, ID_ISIN as idValue from repro1

enter image description here

Now when I debug the pipeline, you can see the debug output of the lookup activity.
The above output consists of lot of other information along with the rows returned as a result of given query.
Now you can directly retrieve the above highlighted array of objects using the following dynamic content (if your lookup activity name is Lookup1).

@activity('Lookup1').output.value

You can use the above dynamic content (an array of objects) to fill your API call's body.

answered Aug 31, 2022 at 16:14

Saideep Arikontham

6,1922 gold badges6 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Scorpia Over a year ago

It worked! Makes totally sense how you approached this.

Collectives™ on Stack Overflow

How to convert SQL rows to an array of json objects in Azure Data Factory?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related