CODEWITHSUNDEEP
looker-studio
Jason Nichols
Jason Nichols

Reputation: 3760

Data Studio Filter results to rows whose arrays contain two values

I have a table like this:

Name Array
Dog [Barks, Furry]
Cat [Purrs, Furry, Other]
Broken Duck [Purrs, Feather]

When I use a control and select Purrs and Furry, I get all three rows. The desired outcome is to only get the cat row.

Under the hood in BQ Name is a string and Array is actually an Array<struct<type: string, value:string>> e.g. [{'array_element':'Barks'}] with all type values equal to array_element.

Upvotes: 5

Views: 1164

Answers (1)

Nimantha
Nimantha

Reputation: 6461

One approach is to use the CSV Filter Control Community Visualisation with the Array field, excluding the [ and ] characters:

  • Chart (Community Visualisation): CSV Filter Control
  • Column to Filter on: REGEXP_REPLACE(Array, R"(\]|\[)", "")
  • Interactions: (Tick) Apply Filter
  • Style Tab: (Unchecked - default behaviour) OR instead of AND behaviour

Editable Google Data Studio Report (Embedded Google Sheets Data Source) and a GIF to elaborate:

Upvotes: 2

Related Questions