Null Values

Filtering null values

When constructing a filter object to filter null values, you should construct it as the example below:

let filter183= cf.Filter('pickup_census_tract')
            .label('pickup_census_tract')
            .operation('IN')
            .value([null]);

Notice that the array passed to the .value() function includes the null value. Passing strings such as “null”, “Null”, or “NULL” will not work.

Following the best practices below allow you to filter visualizations with null values across SQL and No-SQL datasources.

Storing nulls in your data engine

Elasticsearch

Elasticsearch is based on Lucene which is a text search engine that indexes data in an inverted index fashion where a null value cannot be indexed or searched. Therefore, we need to represent nulls by some predefined value. Specifically for keyword fields (e.g. strings) the best practice is to handle nulls as follows:

Index creation

When declaring keyword fields in the index metadata, the best practice is to use the null_value parameter to replace explicit null values with the string "null" so that it can be indexed and searched. Example:

"pickup_community_area": {
    "type": "keyword",
    "null_value": "null"
}

Data indexing

When indexing values, you should do something like the Python example below:

"pickup_community_area": r[8] if r[8] != '' else None,

This tells Elasticsearch that you are indexing a null value and it should follow the index metadata rules declared above.

Before querying the data, the Elasticsearch data provider in ChartFactor converts any null value received in Filter objects into the string “null” so that they are correctly searched.

BigQuery and other relational data engines

BigQuery and other relational data engines (e.g. Postgres, Redshift, SparkSQL) store nulls as null. There is nothing special about storing null values here. Please do not store null values as the string “null” in these data engines. ChartFactor’s data providers simply use the null values in the Filter objects to properly construct the SQL query.

Null values representation inside ChartFactor objects

Null values are stored as null on charts and tables. For example, when inspecting the data values on a chart that was grouped by a field that contains null values, you will see something like this:

_data: Array(10)
    0:
        current: {count: 2719, metrics: {…}}
        group: [null]

What if you store “null” strings in your relational data engine

Filtering by “null” values

You need to construct your Filter objects as shown in the example below. Notice how “null” is a string in the values array.

let filter261= cf.Filter('likeopera')
            .label('likeopera')
            .operation('IN')
            .value(["null"]);

Configuring the Interaction Manager

The Interaction Manager constructs Filter objects with null values any time a “null” string is received in the filter event. Therefore, we need to add a filter:before-add rule to convert it to the “null” string. Example:

let aktive = cf.create();
let myChart = aktive.graph("Interaction Manager")
  .on('filter:before-add', (event) => {
    event.filters.forEach(filter => {
      filter.getValue().forEach((value, idx) => {
        if (value === null) {
          filter.getValue()[idx] = "null";
        }
      });
    });
   })
  .execute();