BigQuery: Struct data type display

[k-lawecki]

Hi,

One more feature request, this is one seems a bit bigger - we are heavily using STRUCT data type, as well as repeated STRUCTs.

Currently this is displayed in DBCode as jsons, see example below (status column):

in reality, big advantage of STRUCT data type in bigquery is the fact it can be displayed in much better format than JSON / json-like string, see below:

Data format in this example:

• custom_id - string
  • status - struct containing:
  • status.bucket - string
  • status.status_history - struct (repeated) containing:
    • status.status_history.status_group - string
    • status.status_history.status - string
    • status.status_history.status_since - timestamp
    • status.status_history.minutes_in_status - int

Table schema looks in BigQuery like this:

So in ideal world if we could have separate column view for struct(s) subcolumns (with respecting subcolumns data types in display) as well as repeated rows handled that would be amazing

[mikeburgh]

Just FYI, this is a bigger lift.. I have some ideas for it, but it's going to require a lot of work to the current grid/editing.. so not ignoring it, just working up to it.

[mikeburgh]

I have a first look at this released in 1.17.12. You can now right click the heading of a JSON like column and choose to expand it.. it will expand out the json into columns… you can also right click on those to collapse them back.

Once expanded, the grid is no longer editable, and also looses multiple cell selections.. trying to see if I can get those back, and the row numbers are wrong as they are counting spanned rows.

I am still working on the sub array expansion, but getting there.

[k-lawecki]

Hi @mikeburgh
It works quite well! The only thing is the row numbers (which you are aware of) the rest works good.
By sub arrays you mean when one array is inside of another?

[k-lawecki]

@mikeburgh Interestingly this works on STRUCT type, but doesn't work on JSON type or on JSON-like string (but i guess its harder to predict the structure of those to actually properly expand)

[mikeburgh]

I have actually built it to work on any JSON data type, and handled all the normalization, since a lot of other DB's don't have the BQ style struct datatypes.. so wanted to make sure it worked for as many use cases as possible.

When you say it does not work, do you not see the expand option.. or does it make a mess of the expansion ?

If you don't see the option, the datatype is not being mapped to json.. eg if it's a varchar for example, it's just a string to DBCode, so does not know it can be expanded..

If it's making a mess/not expanding right, can you share the json (or the structure of it, no values needed.

[k-lawecki]

on JSON type the "Expand" option is present, but when clicking on it doesnt do anything.

dbcode correctly sees JSON type

but then in the preview / query result (tested both):

json is quoted,
while in BQ console, exact same json column is displayed correctly:

Sample (of correct json, as displayed in bq) with slightly changed values:

{"component_name":"Button","label":"Accept all","referrer":"https://app.rampnetwork.com","referrer_domain":"app.rampnetwork.com","screen_group":"Cookie Consents","screen_name":"Overview","screen_type":"modal","transaction_flow":"ONRAMP","url":"URL_HERE"}

[mikeburgh]

I just shipped 1.17.15

It should fix that quoting issue.. it was causing the JSON to not parse, and hence expand would fail if the JSON could not parse. The issue was in the BQ driver, I was incorrectly quoting already string based json.

The overall expand feature has also been finalized.. rows numbers and row striping should now match the rows properly, sub arrays containing JSON objects should also expand out correctly, and non expanded columns should support editing.

Do you think the BQ struct columns should auto expand ? You can still collapse them back if you needed to edit their data.