Filtering null values¶
When constructing a filter object to filter null values, you should construct it as the example below:
1 2 3 4
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 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:
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:
1 2 3 4
When indexing values, you should do something like the Python example below:
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:
1 2 3 4
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.
1 2 3 4
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:
1 2 3 4 5 6 7 8 9 10 11 12