Can we build a dashboard using JSON columns in a BIG Query table which is the data source

[Chanukya]

Hello All,

Currently, we have a table name A and under this the source team created a column as VALUE which will have Log Header,  Request, Response, Vertex Request, Vertex XML Request, Response, Vertex XML response columns. I am confused how to showcase this type of data format in Looker dashboard. Few columns I would like to use under drop down to select the data by users and other columns in a table with JSON format. 

I have no idea how can I showcase the JSON data format or pull the SINGLE COLUMN VALUE (Under this we have many columns as request & response) into LOOKER Dashboard. 

To be precise, we usually pull the standard columns into dashboard using BQ data source and create the design. In this scenario we got JSON format in the table with one COLUMN. Please do suggest how can this be achieved. 

NOTE: I did tried through CUSTOM SQL Query and observed as only single column as VALUE showing in the dashboard.

[Spreadsheep · Accepted solution]

Personally I think you need to work with this table in bigquery and transform what you have into what you want using JSON functions. Here is a link to the JSON functions in BigQuery.

https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions

I think it will be quite a complicated task, but I don't think there is a way to transform this data within looker studio.

[Chanukya]

Thank you for your reply. I was able to pull the JSON field using custom query in a custom scorecard which showed the count of the records I needed.