Mongodb - Rename a nested json when loading mongodb data

Bernard_rosencrantz — Wed, 19 Mar 2025 23:06:21 GMT

Hello, I have an issue. I have a badly structured mongodb - data that looks like this:

example

This is two jsons from the mongodb database:

{
"_id" : ObjectId("12345"),
"campaign" : NumberInt(54312),
"profile" : "43214125432",
"transaction" : "35",
"platforms" : {
"e7bcd087e0e840c997b487e8118aad71" : {
"conversion" : 1.0,
"conversionAmount" : 287.08923360425405,
"currency" : "EUR"
}
}

{
"_id" : ObjectId("12332445"),
"campaign" : NumberInt(54323412),
"profile" : "123412341",
"transaction" : "35",
"platforms" : {
"532452351345" : {
"conversion" : 1.0,
"conversionAmount" : 110.08923360425405,
"currency" : "EUR"},

                 {
"6435634563456" : {
                   "conversion" : 2.0,
                   "conversionAmount" : 342.08923360425405,
"currency" : "EUR"},

}

When I connect the database and let qlik convert the json format to a tabular dataform the columns does not turn out as i want. The issue is that it creates one column for each platform key (e.g. "platforms_e7bcd087e0e840c997b487e8118aad71_conversion", "platforms_e7bcd087e0e840c997b487e8118aad71_conversionAmount" and "platforms_e7bcd087e0e840c997b487e8118aad71_currency"). This leads me to have over 150 columns, three for each platforms value and the data becomes very sparse.

Is the any way to flatten rename all platform keys to 0, 1 ,2 ... when connection to the database / loading the data?

rename example:

{
"_id" : ObjectId("12345"),
"campaign" : NumberInt(54312),
"profile" : "43214125432",
"transaction" : "35",
"platforms" : {
"0" : {
"conversion" : 1.0,
"conversionAmount" : 287.08923360425405,
"currency" : "EUR"
}
}

{
"_id" : ObjectId("12332445"),
"somthing" : NumberInt(54323412),
"profile" : "123412341",
"transaction" : "35",
"platforms" : {
"0" : {
"conversion" : 1.0,
"conversionAmount" : 110.08923360425405,
"currency" : "EUR"},

"1" : {
"conversion" : 1.0,
"conversionAmount" : 110.08923360425405,
"currency" : "EUR"}

}

Thanks!

topic Mongodb - Rename a nested json when loading mongodb data in Data Movement & Streaming

Mongodb - Rename a nested json when loading mongodb data