Nested and Repeated Records
The Simba Google BigQuery JDBC Connector fully supports nested and repeated records. The connector returns the base type as a text representation of the JSON object.
Querying STRUCT Data
Standard SQL syntax represents the sub-components of record data as nested sub-types. The dot operator (.) is used to select sub-components. In the examples below, city and years belong to the base record type of address.
If the record column is specified in a query projection list, the connector returns the base record as a text representation of the JSON record object, and no flattening occurs.
See the following examples to see how to retrieve base records and sub-components from STRUCT data.
Selecting Base Record
Sample query to retrieve a base record from a STRUCT column:
select (STRUCT("Vancouver" as city, 5 as years)) as address
The connector returns the results as a text reinterpretation of the JSON object, as shown here:
{
"v": {
"f": [
{
"v": "Vancouver"
},
{
"v": "5"
}
]
}
}
The BigQuery console would represent the query results as a table, as shown here:
| Row | f0_.city | f0_.years |
| 1 | Vancouver | 5 |
Select Sub-Components
Sample query to retrieve a sub-component from a STRUCT column:
select address.city from (select (STRUCT("Vancouver" as city, 5 as years)) as address)
The connector returns the results as a text reinterpretation of the JSON object, as shown here:
[
{
"city": "Vancouver"
}
]
The BigQuery console would represent the query results as a table, as shown here:
| Row | city |
| 1 | Vancouver |
Querying Arrays
The Simba Google BigQuery JDBC Connector fully supports array data types. The connector returns the base array type as a text representation of the JSON array object.
Selecting Arrays of Primitive Type
Sample query to select a primitive array:
SELECT [1,2,3]
The connector returns the results as a text reinterpretation of the JSON object, as shown here:
{
"v": [
{
"v": "1"
},
{
"v": "2"
},
{
"v": "3"
}
]
}
The BigQuery console would represent the query results as a table, as shown here:
| Row | f0_ |
| 1 | 1 |
| 2 | |
| 3 |
Selecting Arrays of STRUCT Data
Sample query to select multiple objects from a STRUCT array:
SELECT [STRUCT("Vancouver" as city, 5 as years), STRUCT("Boston" as city, 10 as years)]
The connector returns the results as a text reinterpretation of the JSON object, as shown here:
{
"v": [
{
"v": {
"f": [
{
"v": "Vancouver"
},
{
"v": "5"
}
]
}
},
{
"v": {
"f": [
{
"v": "Boston"
},
{
"v": "10"
}
]
}
}
]
}
The BigQuery console would represent the query results as a table, as shown here:
| Row | f0_.city | f0_.years |
| 1 | Vancouver | 5 |
| Boston | 10 |