Nested JSON in Tableau

I was able to get the JDBC driver for Rockset functioning with Tableau. It pulls my data effectively but the nested JSON columns that looks very nice in Rockset are empty in Tableau. Per this article, it seems it’s still possible using UNNEST. And in reading the documentation I’m led to believe I may need to use UNNEST in combination with JSON_PARSE. Any insight or tips on this?

Hi @jn1!

I’m reaching out to some team members- I’m not the tableau expert. I’ll follow up soon!

–n

Yes, UNNEST or just simple dot notation are great ways to get JSON data out of Rockset and into a table format that Tableau understands. UNNEST is useful if there are lots of elements within a parent node, and you want to create many rows from one record. JSON_PARSE is not useful in this context so you shouldn’t need it.

1 Like

Will dot notation work with Tableau itself? Because my nested ‘Json’ field from Rockset appears to be empty in Tableau. Or are you saying I should use dot notation within Rockset to prepare the tables for Tableau? If so, the usual method I would use that would be to create a Rockset view that pulls out each JSON element into its own column. But it doesn’t appear that views show up in Tableau.

Within Tableau, you should be able to use SQL to pull data out of Rockset, rather than just pull a full collection or view. The button you want says “New Custom SQL” below the table names on the left panel. Use that, and use dot notation in the query to pull out the JSON data into columns for your table.

I am seeing a different issue now. When I attempt to execute a SQL query it returns, “Bad Connection: Tableau could not connect to the data source. Error Code: FAB9A2C5”. Any ideas on that? I can see my data in the bottom pane on Tableau so it’s making some kind of connection.

I’d confirm that your SQL is correct and you are getting strings and numbers back in your results. I would filter out all that JSON so it never arrives in TB. That error is from the TB side, not the Rockset side, so I have a feeling it doesn’t like some of the data sent over. I just tried a simple query and was able to get the data without that error.

Thanks for your help Jon. With some tinkering, I was able to get some commands running. One more question. Given that the JDBC driver is on my machine locally, what does that mean when it comes to making a dashboard that receives live data? If I publish a dashboard is there any way for it to receive data updates? Or will the dashboard just retain the latest information that I publish?

In comparison, I’m also exploring using Retool which can just hit Rockset’s query APIs anytime the dashboard refreshes.

Good question! I have played around with this a little and it seems to work if you use Tableau Bridge to pull the live data.

Retool is a great alternative. Same goes with Superblocks. Both of them utilize the API which is a better approach than JDBC.

1 Like