how to flatten an array but it is a complex type instead of an arra

Leon van Kampen 25 Reputation points
2023-03-08T09:47:26.33+00:00

Hi I am trying to flatten a json structure and i have no clue how to do this, this structure is a bit weird and can't get it to work.

{
        "login": "1",
        "arrStatsData": {
                "1244287312": {
                        "id": "1244287312",
                        "data": "test1"
                },
                "1244287313": {
                        "id": "1244287313",
                        "data": "test2"
                },
                "1244287314": {
                        "id": "1244287314",
                        "data": "test3"
                },
                "1244287315": {
                        "id": "1244287315",
                        "data": "test4"
                }
        },
        "totalRows": "928",
        "lastPage": "0",
        "page": "1",
        "currency_symbol": "€"
}

I want to select all the date from the arrStatsData like below, the data is is not in an array so it is hard for me to unroll by this.

{

    "id": "1244287312",

    "data": "test1"

 },

 {

    "id": "1244287313",

    "data": "test2"

},

{

    "id": "1244287314",

    "data": "test3"

},

{

    "id": "1244287315",

    "data": "test4"

}

Edit

So I found out how to change the complex type to an array type by changing the expression from

(({1244287312} as (id as integer, data as string), ({1244287313} as (id as integer, data as string), ({1244287314} as (id as integer, data as string), ({1244287315} as (id as integer, data as string))

to

(id as integer, data as string)[]

but now i get no data in the preview tab all the columns are empty

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,471 questions
{count} votes

Accepted answer
  1. AnnuKumari-MSFT 30,361 Reputation points Microsoft Employee
    2023-03-09T06:23:24.53+00:00

    @Leon van Kampen ,

    Welcome to Microsoft Q&A platform and thanks for posting your query here.

    From the description of your question, I can conclude that you are finding it challenging to unroll by the field arrStatsData since it's not an array field. Kindly let me know if that's not the concern.

    The solution to this is to use aggregate transformation to convert the arrStatsData field into array by using collect function and group by all other columns like below:

    User's image

    User's image

    Data preview:

    User's image

    Flatten transformation to unroll by arrStatsData :

    User's image

    Flattened data:

    User's image


    Hope it helps. Kindly accept the answer by clicking on Accept answer and hit on yes for was the answer helpful survey. Thanks


0 additional answers

Sort by: Most helpful