Flatten JSON data

Question

I am trying to use Tabulator to create a list of tickets, The data is imported via AJAX url from the ticket system as a JSON as below.

{
    "results": [
        {
            "cc_emails": [
                "ram@freshdesk.com",
                "diana@freshdesk.com"
            ],
            "fwd_emails": [],
            "reply_cc_emails": [
                "ram@freshdesk.com",
                "diana@freshdesk.com"
            ],
            "ticket_cc_emails": [
                "ram@freshdesk.com",
                "diana@freshdesk.com"
            ],
            "fr_escalated": false,
            "spam": false,
            "email_config_id": null,
            "group_id": 35000204315,
            "priority": 1,
            "requester_id": 35020281588,
            "responder_id": 35004154466,
            "source": 2,
            "company_id": null,
            "status": 2,
            "subject": "Support Needed...",
            "association_type": null,
            "to_emails": null,
            "product_id": null,
            "id": 188261,
            "type": null,
            "due_by": "2019-09-17T15:12:07Z",
            "fr_due_by": "2019-07-01T15:12:07Z",
            "is_escalated": false,
            "description": "<div>Details about the issue...</div>",
            "description_text": "Details about the issue...",
            "custom_fields": {
                "cf_category": null,
                "cf_firstname": null,
                "cf_surname": null,
                "cf_user_trainging": null,
                "cf_email_address": null,
                "cf_office_365": null,
                "cf_start_date": null,
                "cf_permission_level": null,
                "cf_hardware_type": null,
                "cf_additional_information_specsoftware_etc": null,
                "cf_vpn_access_required": false,
                "cf_securitydistribution_group_membership": null,
                "cf_mapped_network_driveslogin_script": null,
                "cf_printers": null,
                "cf_phone_extension": null,
                "cf_ddi": null,
                "cf_phone_group_membership": null,
                "cf_user_who_requires_the_equipment": null,
                "cf_requirment_date": null,
                "cf_correctclosureused": null,
                "cf_location": "A1"
            },
            "created_at": "2019-06-24T15:11:47Z",
            "updated_at": "2019-06-24T15:59:00Z",
            "associated_tickets_count": null,
            "tags": []
        }
    ],
    "total": 1
}

The problem is the "custom_fields" is a JSON Object inside the main JSON object, is there a way to flatten this data out and display this as all one row in Tabulator? Any help appreciated?

My current result in Tabulator is it returns [object Object] for the custom_fields column. I would like to be able to see each of custom_fields in the row.

Answer 1

Handling Nested Data

There is no need to flatten the object, Tabulator can handle nested data for columns, if you use dot notation in the field name:

var table = new Tabulator("#example-table", {
    columns:[
        {title:"Category", field:"custom_fields.cf_category"},  //link column to nested field
    ],
});

Full details about nested data handling can be found in the Columns Documentation

Column Grouping

If you wanted to you could also use column grouping to show that the fields are a subset of a another property, for example we could define the top level columns as usual and then add column group to hold the custom columns

var table = new Tabulator("#example-table", {
    columns:[
        {title:"Subject", field:"subject"},  //standard column
        {title:"Priorty", field:"priority"}, //standard column
        {title:"Custom", columns:[ //column group to hold columns in custom_fields property
            {title:"Category", field:"custom_fields.cf_category"}, 
            {title:"First Name", field:"custom_fields.cf_firstname"}, 
        ]},
    ],
});

Full details can be found in the Column Grouping Documentation

Answer 2

Here is a slightly different solution relying on function generators to traverse the original object, giving the possibility to eventually detect some duplicated keys.

This example can, of course, be altered by adding further checks (like whether you want to traverse all objects inside the main object and so on).

The current example takes care of:

• Traversing the original object by excluding primitives and arrays.
• Providing a flattenObject method that accepts an object as an argument and a callback as an eventual second argument that will be raised when a duplicated key is met. In that case, the default behavior is to take the "next" nested value as the new one. If false is returned in the callback, the current value is kept. The callback will provide the key and the value of the new value.

So, in a nutshell, the "real" code to acquire the desired result is this one:

// Case usage:
// Map the existing values.
input.results = input.results.map(i => flattenObject(i, (duplicatedKeyValuePair) => {
  return false; // <-- keep the existing value if a duplicate is matched.
}));
console.log(input.results)

Of course, it's slightly more complicated then just flattening the desired property, but I wanted to give a more elastic flavour to it.

const input = {
    "results": [
        {
            "cc_emails": [
                "ram@freshdesk.com",
                "diana@freshdesk.com"
            ],
            "fwd_emails": [],
            "reply_cc_emails": [
                "ram@freshdesk.com",
                "diana@freshdesk.com"
            ],
            "ticket_cc_emails": [
                "ram@freshdesk.com",
                "diana@freshdesk.com"
            ],
            "fr_escalated": false,
            "spam": false,
            "email_config_id": null,
            "group_id": 35000204315,
            "priority": 1,
            "requester_id": 35020281588,
            "responder_id": 35004154466,
            "source": 2,
            "company_id": null,
            "status": 2,
            "subject": "Support Needed...",
            "association_type": null,
            "to_emails": null,
            "product_id": null,
            "id": 188261,
            "type": null,
            "due_by": "2019-09-17T15:12:07Z",
            "fr_due_by": "2019-07-01T15:12:07Z",
            "is_escalated": false,
            "description": "<div>Details about the issue...</div>",
            "description_text": "Details about the issue...",
            "test_duplicated_key": "hello! I should keep this!",
            "custom_fields": {
                "cf_category": null,
                "cf_firstname": null,
                "cf_surname": null,
                "cf_user_trainging": null,
                "cf_email_address": null,
                "cf_office_365": null,
                "cf_start_date": null,
                "cf_permission_level": null,
                "cf_hardware_type": null,
                "cf_additional_information_specsoftware_etc": null,
                "cf_vpn_access_required": false,
                "cf_securitydistribution_group_membership": null,
                "cf_mapped_network_driveslogin_script": null,
                "cf_printers": null,
                "cf_phone_extension": null,
                "cf_ddi": null,
                "cf_phone_group_membership": null,
                "cf_user_who_requires_the_equipment": null,
                "cf_requirment_date": null,
                "cf_correctclosureused": null,
                "cf_location": "A1",
                "test_duplicated_key": "You should not see that."
            },
            "created_at": "2019-06-24T15:11:47Z",
            "updated_at": "2019-06-24T15:59:00Z",
            "associated_tickets_count": null,
            "tags": []
        }
    ],
    "total": 1
}

/**
  Traverse every property of the desired object, by returning the currently key-value pair looped. If the value is an object, it keeps traversing.
*/
function* traverseObject(obj) {
  for ([key, value] of Object.entries(obj)) {
    if (value && typeof(value) === 'object' && !Array.isArray(value)) {
      yield* traverseObject(obj[key]);
    }
    else yield {key: key, value: value};
  }
}

/**
  Flattens the object by traversing every object inside it.
*/
function flattenObject(obj, onDuplicatedKey) {
  let res = {};
  for (keyValuePair of traverseObject(obj)) {
    let add = true;
    if (res.hasOwnProperty(keyValuePair.key)) {
      add = onDuplicatedKey ? onDuplicatedKey.call(onDuplicatedKey, keyValuePair) : true; // default behavior: override with nested propeties.
    }
    if (add) res[keyValuePair.key] = keyValuePair.value;
  }
  return res;
}

/*
Sample usage.
const flattened = flattenObject(input.results[0], (record) => {
  console.log('detected key value pair duplicate. Key:', record.key, ' value: ', record.value);
  // true will override the value, false will not override the value.
  return false;
});
*/
//console.log(flattened);

// Case usage:
// Map the existing values.
input.results = input.results.map(i => flattenObject(i, (duplicatedKeyValuePair) => {
  return false; // <-- keep the existing value if a duplicate is matched.
}));
console.log(input.results);

Please note that the above case is just an example, I didn't spend much time testing every single property type, hence it can (of course) be reviewed and code quality and performances can be improved. It was just an example to show a different approach relying on different operators and logics.

As a (final) side note, I Think you can handle that with tabulator in some way, though I'm not sure you can render multiple columns out of a single property, which leads me to believe that altering the original object is probably the desired solution.

Answer 3

If you're using es6+, you could easily achieve this by using rest in object destructuring and object spread.

const input =  {
    "results": [
        {
            "custom_fields": {...},
            ...
        }
    ],
    "total": 1
}

const expanded = input.results.map(result => {
   const { custom_fields, ...rest } = result;
   return { ...rest, ...custom_fields };
})