Filtering a dashboard based on a JSON object with a large number of encoded fields

andyhicken

Hi folks,

I have a field that contains JSON-encoded text representing client-defined fields in our application. The data in here is custom user profile fields defined differently by each of thousands of clients. For example, one client will have an object that looks like:

{"gender": "whatever", "suffix": null, "facebook": null, "linkedin": "https://www.linkedin.com/in/whoever/"}

And a different client will have an object that looks like:

{"job_title": "Analyst", "company_name": "Acme Bank", "why_join": "I joined to network, learn, and advocate for our industry."}

So the key thing to understand here is that the fields encoded in the JSON differ per client.

All clients get the same set of dashboards, drawn from the same datasets, filtered to their own data in a Domo app that we embed on the clients' sites (so our external application embeds the Domo app and includes a filter to a client ID when we call it). Our goal: we'd like to allow clients to filter the dashboards by these custom fields. We could flatten them using an ETL, but we would be adding thousands of columns to a dataset, meaning thousands of filters visible to clients on the dataset. It would be hard for clients to find their own fields and they would see the names of other clients' custom fields. And aside from those already-unacceptable issues, I don't know how well Domo would handle that many filters on a dashboard.

One solution, if there's a way to do it, would be to flatten the JSON objects in an ETL (creating a dataset with thousands of columns) and then filter columnwise by client in the embedded version of the app. I don't know if there's a way for an external app to filter columnwise when embedding a Domo app.

Another solution I could imagine would be an app that somehow builds the columns dynamically from the JSON object based on the data that it finds after we filter. So first, we have our embedded app which only displays the client's data in their view of the dashboards. Then our app uses some kind of magic to build out columns in the underlying dataset from only the pre-filtered data, so we end up with columns for only the client's custom user profile fields.

We don't want to build out separate dashboards per client or separate datasets per client. Clients aren't logging into Domo in our solution, so the beta columnwise PDP feature isn't an option.

If anyone has any ideas for our situation here, they would be much appreciated!

All the best,

Andy

ArborRose

You definitely don't want to flatten that JSON into thousands of columns - it just won’t scale, and it creates all sorts of usability and performance issues.

Instead, I’d look at converting the JSON into a key-value pair structure, and include client/user identifiers. That way, you end up with two datasets: one for your core metrics (tied to client and user), and one for the custom fields. You can then join them in a Domo ETL on client_id and user_id.

So something like:

client_id,user_id,key,value

123,1,gender,whatever

123,1,suffix,null

123,1,linkedin,url_path

456,2,job_title,Analyst

456,2,company,Acme Bank

This avoids the explosion of columns and keeps things flexible as different clients have different fields.

Since your users aren't logging into Domo directly, you'd likely handle filtering outside the dashboards - maybe in your embedded app - by querying just the custom fields for that client and letting them choose what they want to filter by. Then you’d pass those filters into the embedded dashboard via the Domo embed SDK or URL params.

It’ll take some custom wiring on your side, but this keeps it scalable and avoids maintaining separate dashboards or datasets per client.