How to create a new table with nested data in big query from another tables?

user_15186452 0
Viewed 860

I found lots of example to create nested data in google bigquery manual but there is no example to do this from another tables.

I want to create a new table (for example solar_system_moons_nested) with nested data (write SQL statement to generate the nested data) using two existing tables (for example planets and moons tables). I want the new table look as follows:

enter image description here

I create the moon and planet tables as below:

  1. moon table

enter image description here

  1. planet table:

enter image description here

Is there anyway to create a nested table from existing tables? any help would be appreciated.

Here is how I made the new table(as below):

WITH solar_system_moons_nested AS (
  SELECT p.planet,
          STRUCT(moon ,Distance_from_Planet__km_,Diameter__km_) AS moons, 
          from test.planets p inner join test.moons m on m.planet=p.planet
)

select * from solar_system_moons_nested

and here is how it look like: enter image description here

As you see, the select did not do what I expected.

bigdata google-bigquery google-cloud-platform
2 Answers

If all you want is a nested structure, you can use array_agg and do something like below

WITH solar_system_moons_nested AS (
  SELECT p.planet,
          ARRAY_AGG(STRUCT(moon ,Distance_from_Planet__km_,Diameter__km_)) AS moons, 
          from test.planets p inner join test.moons m on m.planet=p.planet
  GROUP BY 1
)

select * from solar_system_moons_nested

More on array_agg here https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate_functions

user_21385170

Use array_agg to construct an array:

WITH solar_system_moons_nested AS (
  SELECT 
    p.planet,
    array_agg(STRUCT(moon ,Distance_from_Planet__km_,Diameter__km_)) AS moons, 
  from test.planets p inner join test.moons m on m.planet=p.planet
  group by p.planet
)

select * from solar_system_moons_nested
user_134735250
Related