Null objets after mapping input from MySQL

[daniender (Daniel Alvarez)]

Hi all.

I have a working config file for getting a list of objects from MySQL database and inserting them on elastic.

But I have a weir behaviour and I can not figure out the reason.

I receive a list of 'ticket' objects from mysql. Each ticket is related to a 'queue', a 'service_point', a 'tenant' and a 'user'.

When I execute the query in MySQL I get data for all of them.

However, when I look into ES, queue, servicepoint and tenant have their values, meanwhile user is empty.

Here is my mapping:

filter {
aggregate {
	task_id => "%{ticket_id}"
	code => "
		map['ticketId'] = event.get('ticket_id')
		map['name'] = event.get('ticket_name')
		map['queue'] = {
			'id' => event.get('queue_id'),
			'name' => event.get('queue_name')
		}			
		map['tenant'] = {
			'id' => event.get('tenant_id'),
			'name' => event.get('tenant_name')
		}
		map['servicePoint'] = {
			'id' => event.get('service_point_id'),
			'name' => event.get('service_point_name'),
			'serviceTime' => event.get('service_time')
		}
		map['servicedBy'] =  {
			'id' => event.get('servicedBy_id'),
			'userName' => event.get('servicedBy_username'),
			'firstName' => event.get('servicedBy_first_name'),
			'familyName' => event.get('servicedBy_family_name'),
			'secondFamilyName' => event.get('servicedBy_second_family_name'),
			'roles' => [],
			'workProfiles' => []
		}
	"
}

}

The only difference betweem these objects is, as far as I can see, that some of the fields in 'servicedBy (the user), may be null (username or secondFamiliName for instance).

MySQL query is returning all the columns needed:

SELECT
    tc.id 'ticket_id',
	tc.name 'ticket_name',
	qe.id 'queue_id',
	qe.name 'queue_name',
	sp.id 'service_point_id',
	sp.name 'service_point_name',
	sp.sla_service_time 'service_time',
	te.id 'tenant_id',
	te.name 'tenant_name',
	su.id 'servicedBy_id',
	su.name 'servicedBy_username',
	su.first_name 'servicedBy_first_name',
	su.family_name 'servicedBy_family_name',
	su.second_family_name 'servicedBy_second_family_name'
FROM ticket tc
LEFT OUTER JOIN queue qe ON tc.queue_id=qe.id
LEFT OUTER JOIN service_point sp ON tc.service_point_id=sp.id
LEFT OUTER JOIN tenant te ON qe.tenant_id=te.id
LEFT OUTER JOIN user su ON su.id=tc.serviced_by_id
WHERE tc.id > :sql_last_value AND tc.serviced_by_id IS NOT NULL
ORDER BY tc.id ASC;

And here the result in ES:

        "_index": "ticket_stadistics_index",
    "_type": "ticket",
    "_id": "295553",
    "_score": null,
    "_source": {
      "issueTime": "2018-02-02T12:36:40.000Z",
      "@timestamp": "2018-03-09T08:35:26.198Z",
      "servicedBy": {
        "firstName": null,
        "secondFamilyName": null,
        "workProfiles": [],
        "familyName": null,
        "roles": [],
        "completeName": "  ",
        "id": null,
        "userName": null
      },
      "name": "B001",
      "@version": "1",
      "smsNotificationTime": null,
      "id": 295553,
      "servicePoint": {
        "name": "Cliente - Atención al Cliente sin cita",
        "id": 2,
        "serviceTime": 300
      },
      "waitTime": 17,
      "ticketId": 295553,
      "queue": {
        "name": "Oficina de pruebas",
        "id": 1
      },
      "tenant": {
        "name": "Orange",
        "id": 1
      },
      "calledIn": "2018-02-02T12:36:58.000Z",
      "status": 2
    }

As you can see, queue, service and tenant are populated with data, but serviced_by has only null values.

Any idea about what I'm doing wrong?

Thanks.