Description
Description
I would like Lightdash to support ARRAY data type.
That would help us not to create additional models and can calculate some types of metrics.
- I want to add a metric type for
sum distinct#4329 - Ability to filter on array values and to build charts based on array elements #6217
What problem does this solve?
Duplication of rows prevents us from calculating some types of metrics on Lightdash.
If Lightdash supports ARRAY data type, we don't need to additional models and can calculate some types of metrics.
As far as I know, there are two main approaches to get ARRAY data available in Lightdash.
However, in either case, we can't calculate the sum of a column in one model.
- Expand ARRAY data into multiple rows
- Create another model only with unique key and ARRAY data and join it with the original model
Let's consider if we have a BigQuery table which has a ARRAY field.
We call it the original_table source.
id: INT (unique key)name: STRINGamount: INTitems: ARRAY of STRING
In the first approach, we can expand the items ARRAY into multiple rows.
The expanded model has duplicated rows on id columns.
If we want to calculate the sum of amount column over unique ids on Lightdash, we can't do it because the id column are duplicated.
-- expanded_model.sql
SELECT
id,
name,
amount,
item,
FROM
original_table,
UNNEST(items) AS itemIn the second approach, we can create another model only with unique id and items ARRAY.
Then, we join the original_table with the expanded_tags model on id column with the
We can calculate the sum of amount column over unique ids on Lightdash, if we don't use any field of items ARRAY.
However, if we want to filter rows by element of items ARRAY, we can't do it because the joined data has duplicated rows.
That might be confusing for users.
-- expanded_tags.sql
SELECT
id,
items,
FROM
original_tableversion: 2
models:
- name: original_table
meta:
joins:
- join: expanded_tags
sql_on: ${expanded_tags.id} = ${original_table.id}
type: leftMetadata
Metadata
Assignees
Type
Projects
Status