I have this query:
MERGE INTO`default` as t USING [
{"country_code": "ro",
"chart_type": "paid",
"platform": "i",
"genre": "6017",
"time_stamp": "1614643200",
"downloads": 11,
"app_uuid": "app_uuid_0",
"key_id": "meta:app_uuid_0:e:1"},
{
"country_code": "gb",
"chart_type": "free",
"platform": "i",
"genre": "6014",
"time_stamp": "1614643200",
"downloads": 111,
"app_uuid": "app_uuid_1",
"key_id": "meta:app_uuid_1:e:1"}
] AS d ON KEY d.key_id
WHEN MATCHED THEN UPDATE
SET t.app_s = IFMISSING(t.app_s, {}),
t.app_s.downloads = IFMISSING(t.app_s.downloads, {}),
t.app_s.downloads.[d.country_code] = IFMISSING(t.app_s.downloads.[d.country_code], {}),
t.app_s.downloads.[d.country_code].[d.chart_type] = IFMISSING(t.app_s.downloads.[d.country_code].[d.chart_type], {}),
t.app_s.downloads.[d.country_code].[d.chart_type].[d.platform] = IFMISSING(t.app_s.downloads.[d.country_code].[d.chart_type].[d.platform], {}),
t.app_s.downloads.[d.country_code].[d.chart_type].[d.platform].[d.genre] = IFMISSING(t.app_s.downloads.[d.country_code].[d.chart_type].[d.platform].[d.genre], {}),
t.app_s.downloads.[d.country_code].[d.chart_type].[d.platform].[d.genre].[d.time_stamp] = IFMISSING(t.app_s.downloads.[d.country_code].[d.chart_type].[d.platform].[d.genre].[d.time_stamp], {}),
t.app_s.downloads.[d.country_code].[d.chart_type].[d.platform].[d.genre].[d.time_stamp] = d.downloads
WHEN NOT MATCHED THEN INSERT ( {"app_s": {"downloads": {d.country_code: {d.chart_type: {d.platform: {d.genre: { d.time_stamp: d.downloads } } } } } }, "app_uuid": d.app_uuid, "key": d.key_id, "type": "tracker-app", "sub_type": "app-extra", "version": 1, "created_timestamp": TRUNC(NOW_MILLIS()/1000,0) } )
and I get these results:
[
{
"d": {
"app_uuid": "app_uuid_0",
"chart_type": "paid",
"country_code": "ro",
"downloads": 11,
"genre": "6017",
"key_id": "meta:app_uuid_0:e:1",
"platform": "i",
"time_stamp": "1614643200"
},
"t": {
"app_s": {
"downloads": {
"ro": {
"free": {
"i": {
"6014": {
"1614643200": 111
},
"6017": {
"1614643200": 111
}
}
},
"paid": {
"i": {
"6014": {
"1614643200": 111
},
"6017": {
"1614643200": 111
}
}
}
},
"us": {
"free": {
"i": {
"6014": {
"1614643200": 43155
}
}
}
}
}
},
"app_uuid": "app_uuid_0",
"created_timestamp": 1615470654,
"key": "meta:app_uuid_0:e:1",
"sub_type": "app-extra",
"type": "tracker-app",
"version": 1
}
},
{
"d": {
"app_uuid": "app_uuid_1",
"chart_type": "free",
"country_code": "gb",
"downloads": 111,
"genre": "6014",
"key_id": "meta:app_uuid_1:e:1",
"platform": "i",
"time_stamp": "1614643200"
},
"t": {
"app_s": {
"downloads": {
"gb": {
"free": {
"i": {
"6014": {
"1614643200": 111
},
"6017": {
"1614643200": 111
}
}
},
"paid": {
"i": {
"6014": {
"1614643200": 111
},
"6017": {
"1614643200": 111
}
}
}
},
"us": {
"free": {
"i": {
"6014": {
"1614643200": 18159
}
}
}
}
}
},
"app_uuid": "app_uuid_1",
"created_timestamp": 1615470654,
"key": "meta:app_uuid_1:e:1",
"sub_type": "app-extra",
"type": "tracker-app",
"version": 1
}
}
]
But what I would have expected would be this:
[
{
"d": {
"country_code": "ro",
"chart_type": "paid",
"platform": "i",
"genre": "6017",
"time_stamp": "1614643200"
"downloads": 11,
"key_id": "meta:app_uuid_0:e:1",
"app_uuid": "app_uuid_0",
},
"t": {
"app_s": {
"downloads": {
"ro": {
"paid": {
"i": {
"6017": {
"1614643200": 11
}
}
}
}
}
},
"app_uuid": "app_uuid_0",
"created_timestamp": 1615470654,
"key": "meta:app_uuid_0:e:1",
"sub_type": "app-extra",
"type": "tracker-app",
"version": 1
}
},
{
"d": {
"country_code": "gb",
"chart_type": "free",
"platform": "i",
"genre": "6014",
"time_stamp": "1614643200"
"downloads": 111,
"key_id": "meta:app_uuid_1:e:1",
"app_uuid": "app_uuid_1",
},
"t": {
"app_s": {
"downloads": {
"gb": {
"free": {
"i": {
"6014": {
"1614643200": 111
}
}
}
}
}
},
"app_uuid": "app_uuid_1",
"created_timestamp": 1615470654,
"key": "meta:app_uuid_1:e:1",
"sub_type": "app-extra",
"type": "tracker-app",
"version": 1
}
}
]
So, somehow it seems that the objects (from the “USING” ) seems to be somehow merged together. Can you please pinpoint me what I am doing wrong?