Working with Nested Data

The Film and TV Data Share offering currently contains some columns that may contain long objects/arrays. These can be easily extracted into flattened tables using Snowflake functions. Provided below are sample queries to help you get started.

Extract Employees from Companies

Employees can be found in a list column in VW_COMPANIES_DS. For users who may want to extract one row per employee, a sample query can be found below.

select
    f.value:employee_id::varchar as employee_id,
    f.value:first_name::varchar as first_name,
    f.value:last_name::varchar as last_name,
    f.value:middle_name::varchar as middle_name,
    f.value:job_title::varchar as job_title,
    f.value:modified_date::datetime as employhee_modified_date,
    f.value:social_media::object as social_media,
    company_id,
    company_name
from
    VW_COMPANIES_DS c,
    lateral flatten(input => c.EMPLOYEES) f
limit
    20

Extract a full cast list for a project


WITH parsed_casts AS (
  SELECT 
    FTV_LUMINATE_PROJECT_ID,
    TITLE,
    c.value AS cast_member
  FROM 
    vw_film_projects_ds,
    LATERAL FLATTEN(CASTS) c
)

SELECT 
  p.FTV_LUMINATE_PROJECT_ID,
  p.TITLE,
  cast_member:character::STRING AS character_name,
  cast_member:first_name::STRING AS first_name,
  cast_member:last_name::STRING AS last_name,
  cast_member:middle_name::STRING AS middle_name,
  cast_member:talent_id::STRING AS talent_id,
  case when cast_member:main_title_order <> '' then cast_member:main_title_order::INTEGER end AS billing_order
FROM 
  parsed_casts p
WHERE p.FTV_LUMINATE_PROJECT_ID = '58046'
ORDER BY 
  p.FTV_LUMINATE_PROJECT_ID, 
  billing_order nulls last

Similar methods could be used to extract auspices and below the line talent from a film or tv/digital record as well.