...
Note: This assumes we are able to remove the “index” table used for lists. This work is tracked in: PLFM-7968.
JSONObect
The main cohort builder use case includes the need to combine columns into a single column. The above json_object column is an example of this type of column combination. It contains three columns: a, b, & c. Therefore, we propose to represent this column by extending ColumnModel to include a new recursive property name “jsonSubColumns”.
...
This allows us to declare that the json_object column contains two faceted column (a & c) and one non-faceted column (b). This would require that we extends the ColumnModel definition to capture the new path and sub-columns: PLFM-7972.
Filtering
In order to filter our example json_object column we will need the jsonPath to identify which sub-column to filter on. The path always starts with ‘$' which represents the “root” of the JSONObject. So to filter on sub-column ‘a’ we would use a path of '$.a’. The following example shows how to find all rows where “a“ = “three“:
...
We will also need to extends the facet selection translator to generate the above SQL from the facet requests. This work is tracked in PLFM-7973.
Facet Statistics
Since the json_object column contains two faceted sub-columns, we will need to generate statistics for both sub-columns when the user’s query request includes partsMask=0x20. Again we can use the jsonPath to identify the stats for each sub-column:
...
We will also need to extend the facet statistics system to generate the stats for faceted JSON sub-column. This work is tracked in: PLFM-7974.
Appendix - Performance Considerations
| Anchor | ||||
|---|---|---|---|---|
|
...