Constructing nested array structs in BigQuery

with animal as
(
  select * from 
    unnest([struct
      (1 as zoo_id, 'Horse' AS animal_name, 1 as animal_count),
      (1, 'Cow', 25),
      (2, 'Old World Swallowtail Butterfly', 1000 ),
      (4, 'Fish' ,5500),
      (5, 'Beetle' ,700000)
    ]) 
)
, zoo as
(
  select * from 
    unnest([struct
      (1 as zoo_id, 'Skansen' as zoo_name, 100 as city_id),
      (2, 'Fjärilshuset', 100 ),
      (3, 'Zoologisk Have', 101  ),
      (4, 'Särkänniemi Aquarium', 102),
      (5, 'Kew Gardens', 103)
    ]) 
)
, city as
(
  select * from 
    unnest([struct
      (100 as city_id, 'Stockholm' as city_name),
      (101, 'København' ),
      (102, 'Tampere'),
      (103, 'London')
    ]) 
)
select
  c.*,
  array( select as struct 
    zoo_id,
    zoo_name,
    array( select as struct 
      animal_name, 
      animal_count 
      from animal a where a.zoo_id = z.zoo_id ) as animal
    from zoo z where z.city_id=c.city_id ) as zoo,
from city c



Comments

Post a Comment

Popular posts from this blog

How to decrypt stored password from SSMS registered servers

Image
Right click your server, choose tasks/export Uncheck "Do not include user names and passwords in the export file" Save the export to a file Open the file in an editor and locate the tag RegisteredServers:ConnectionStringWithEncryptedPassword Copy the contents of password attribute Open powershell and paste the code below, after you amend it with your encrypted string. $base64pass = "PasteEncryptedPasswordHere" [System.Reflection.Assembly]::LoadWithPartialName("System.Security") | Out-Null [System.Text.Encoding]::Unicode.GetString([System.Security.Cryptography.ProtectedData]::Unprotect([System.Convert]::FromBase64String($base64pass), $null, [System.Security.Cryptography.DataProtectionScope]::CurrentUser))
Read more

Cause for Parameter is incorrect 0x80070057 error in ssis

Image
Possible cause for the cryptic error The package failed to load due to error  0x80070057 "The p arameter is incorrect.". This occurs when CPackage::LoadFrommXML fails.  ...when opening or executing and SSIS package In my case i created the ssis package using biml. And accidentally created a duplicate precedence constraint to another task twice. Removing the duplicate constraint and re-generating the package solved it.
Read more

How to fail sql server agent job if powershell script fails

Image
Problem 1 I tried adding a powershell script to a sql server agent job step, but there are some severe limitations where write-host or write-verbose outputs are not allowed instead of ignored, which should make sens. Then the job crashes with the following error:  A job step received an error at line [xx] in a PowerShell script. The corresponding line is 'write-host "test";  '. Correct the script and reschedule the job. The error information returned by PowerShell is: 'A command that prompts the user failed because the host program or the command type does not support user interaction. Try a host program that supports user interaction, such as the Windows PowerShell Console or Windows PowerShell ISE, and remove prompt-related commands from command types that do not support user interaction, such as Windows PowerShell workflows.  '.  Process Exit Code -1.  The step failed. Problem 2 So instead I created a Operating System (CmdExec) step with calling pow...
Read more