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

Question

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

Answer 1

@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:

Data preview:

Flatten transformation to unroll by arrStatsData :

Flattened data:

---

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