Fields of Dreams: Customizing fields in MongoDB Charts

MongoDB Charts now lets you add new fields in the chart builder, and change the types of existing fields, making it easier than ever to build the charts of your dreams.

Introduction

MongoDB Charts is the data visualization tool built from the ground up to support MongoDB data. For example, it natively understands MongoDB’s flexible document model, where documents in a collection may have different fields or use different data types.

When you open the chart builder and select a data source, Charts will sample your selected data source and show a hierarchical list of the fields that it discovered, ready for you to encode onto your chart. While the sampled fields are normally all you need to build a chart, collections with a highly variable schema can result in some fields not appearing in the list. And at other times you may want to build a chart using data that is not in any field at all but is derived from one or more other fields in the collection.

The latest release of Charts, now available as a part of MongoDB Atlas, addresses these challenges by letting you manipulate fields in three ways: adding missed fields, adding calculated fields, and changing the type of existing fields. (For Charts on-prem users, we haven’t forgotten about you; these features will be rolled into the next version, due to be released by the end of 2019).

Let’s dive deeper into the different ways you can manipulate fields.

Missed Fields

A missed field is a field that exists in some (but not all) documents in your collection; one that Charts did not discover as a part of the sampling process. Perhaps the field was only added in the latest version of your application, or perhaps you use a polymorphic schema where the field only appears in a minority of documents. Either way, it may still be data you want to visualize on your charts!

To add a missed field to the field panel for use on a chart, simply click the Add Field link, and enter the field’s name or dotted path.

Once you click Save Field, Charts will search the collection for the field and list it in the field panel. Fields that have been manually added to the field panel render in italics.

Calculated Fields

You can use a similar approach to add a calculated field to the field panel. Unlike a missed field, a calculated field doesn’t appear in the underlying collection at all. It is actually a new field that you define, derived from one or more fields in the original collection. Some scenarios where this may be helpful include:

• Converting units, e.g. changing seconds to hours or degrees Fahrenheit to degrees Celsius
• Applying simple formulas to fields, e.g. multiplying price by quantity to derive a total
• Using MQL expressions to perform more complex data processing within each document.

To add a calculated field, you use the same Add Field button and then select the Calculated toggle. From here, you enter the name or dotted path of the field, and an expression representing the formula. For simple calculations, you can combine field names with basic arithmetic operators (+-*/ and brackets) and numbers, as in the following example:

For more complex calculations, you can use MQL expressions to define your calculated field. The following example shows how we could combine multiple order line items to calculate a total for the shopping basket:

Similar to Missed fields, Calculated fields show in the field panel rendered in italics.

Type Conversions

Finally, there may be times when a field shows in the field panel, but it’s not showing the data type that you want. There are a few reasons that may be the case:

• The data is semantically of a certain type, but it isn’t stored as that type. For example, dates may be represented as strings like “2019-11-01T00:00:00Z” or as Unix timestamps like 1572412235345, rather than using the correct BSON Date type
• The field uses an ObjectID type, but you want to treat it as a date so you can do some temporal analysis without having a timestamp field
• The field’s data type varies between documents (e.g. you have some strings and some numbers), and you want to force charts to treat them in a certain way.

You can support all of these cases by using the Convert Type option that appears on the context menu for any field (except for calculated fields). From this dialog, you can choose a specific type, or select “Auto Detect” to return the field to the type derived from the sample.

Note that if some (or all) values cannot be converted to the target type, they will be returned as null to your visualization.

Fields whose type has been converted from the default render with a green type icon in the field panel.

Summary

The new options to manipulate fields in the chart builder make it easier than ever to create the perfect visualization no matter how your data is structured.