Merge into seems to not work correctly

@vsr1

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?

You might have already entries then it added new once. into object. Check your original document.

no, I double checked that.

You can test by running the query once, change for example the country code in one of the objects, and the run again, and you will see that additional unwonted values appear.

So, it seems that the error is on the WHEN MATCHED THEN UPDATE path

@vsr1 is it a bug? I still cannot make it work

MB-44912. There is no solution at present.