How to transform canal message? Which is flag map Array data and add field

[yaohuacheng]

I have a canal message in the following format:

{
  "data": [{
    "create_time": "2022-01-09 00:00:19",
    "id": "9204245",
    "reason": "4",
    "user_id": "134048732"
  },{
    "create_time": "2022-01-09 00:00:19",
    "id": "9204246",
    "reason": "4",
    "user_id": "134048733"
  }],
  "database": "rc_live_chat_statistics",
  "es": 1641686418000,
  "id": 8892351,
  "isDdl": false,
  "old": null,
  "pkNames": ["id"],
  "sql": "",
  "table": "rc_delete_reason",
  "ts": 1641686418914,
  "type": "INSERT"
}

And I want to convert the message to the following format:

[{
    "id": "9204245",
    "create_time": "2022-01-09 00:00:19",
    "feedback": "",
    "gender": "1",
    "reason": "4",
    "score": "0",
    "user_id": "134048732",
    "_meta_data":{
      "database": "rc_live_chat_statistics",
      "table": "rc_delete_reason",
      "ts": 1641686418914,
      "type": "INSERT"
    }
  },{
    "id": "9204246",
    "create_time": "2022-01-09 00:00:19",
    "feedback": "",
    "gender": "2",
    "reason": "4",
    "score": "0",
    "user_id": "134048733"
    "_meta_data":{
      "database": "rc_live_chat_statistics",
      "table": "rc_delete_reason",
      "ts": 1641686418914,
      "type": "INSERT"
    }
  }
]

What shall I do?

[spencergilbert]

I pulled together the following transform:

transforms:
  unnest:
    type: remap
    inputs:
      - file
    source: |
      message = del(.message)
      . = object!(parse_json!(message))
      . = unnest!(.data)
  remap:
    type: remap
    inputs:
      - unnest
    source: |
      data = del(.data)
      . |= object!(data)

      ._meta_data.database = del(.database)
      ._meta_data.table = del(.table)
      ._meta_data.ts = del(.ts)
      ._meta_data.type = del(.type)

      del(.es)
      del(.isDdl)
      del(.old)
      del(.pkNames)
      del(.sql)

Which from your original event:

{"data":[{"create_time":"2022-01-09 00:00:19","id":"9204245","reason":"4","user_id":"134048732"},{"create_time":"2022-01-09 00:00:19","id":"9204246","reason":"4","user_id":"134048733"}],"database":"rc_live_chat_statistics","es":1641686418000,"id":8892351,"isDdl":false,"old":null,"pkNames":["id"],"sql":"","table":"rc_delete_reason","ts":1641686418914,"type":"INSERT"}

The following two events are emitted:

{"_meta_data":{"database":"rc_live_chat_statistics","table":"rc_delete_reason","ts":1641686418914,"type":"INSERT"},"create_time":"2022-01-09 00:00:19","id":"9204245","reason":"4","user_id":"134048732"}
{"_meta_data":{"database":"rc_live_chat_statistics","table":"rc_delete_reason","ts":1641686418914,"type":"INSERT"},"create_time":"2022-01-09 00:00:19","id":"9204246","reason":"4","user_id":"134048733"}

Pretty printing the first event...

{
  "_meta_data": {
    "database": "rc_live_chat_statistics",
    "table": "rc_delete_reason",
    "ts": 1641686418914,
    "type": "INSERT"
  },
  "create_time": "2022-01-09 00:00:19",
  "id": "9204245",
  "reason": "4",
  "user_id": "134048732"
}

Which compared to your expected outcome is missing .feedback, .gender, and .score which don't appear in the original event so I'm not sure how to dynamically populate them.