When Harry Met Sally: A Table-JSON Love Story

The beaver gets excited and calls Professor Shellbert. We can connect duck visits from our database with pond locations from the JSON file! Join our pond_visits table with JSON location data!

Your task

Show duck names, pond names, visit dates, and water types by joining the 'data/pond_visits.csv' file with the 'data/pond_locations.json' file. You will be connecting data from two different file formats.

Hint

You need to join two different file sources - one CSV and one JSON - using their common field.

Expected result

A table showing duck names, pond names, visit dates, and water types.

Tables

`duck_diaries` (10 rows)

column	type
`diary_id`	BIGINT
`duck_id`	BIGINT
`entry_date`	DATE
`diary_text`	VARCHAR
`mood`	VARCHAR

`duck_surveys` (5000 rows)

column	type
`survey_id`	BIGINT
`duck_id`	BIGINT
`age`	BIGINT
`favorite_food`	VARCHAR
`favorite_pond`	BIGINT
`swimming_rating`	BIGINT
`migration_preference`	VARCHAR
`social_behavior`	VARCHAR
`health_status`	VARCHAR
`activity_level`	VARCHAR
`survey_date`	DATE
`survey_location`	BIGINT

`ducks` (15 rows)

column	type
`id`	BIGINT
`name`	VARCHAR
`age`	BIGINT
`species`	VARCHAR
`birth_date`	DATE
`arrival_date`	DATE

`ponds` (3 rows)

column	type
`pond_id`	BIGINT
`pond_name`	VARCHAR
`location_lat`	DOUBLE
`location_lng`	DOUBLE
`capacity`	BIGINT
`features`	VARCHAR[]
`amenities`	VARCHAR[]

Files

`data/pond_visits.csv` (40 rows)

column	type
`visit_id`	BIGINT
`duck_id`	BIGINT
`pond_id`	BIGINT
`visit_date`	DATE
`duration_minutes`	BIGINT

`data/pond_locations.json` (3 rows)

column	type
`pond_id`	BIGINT
`pond_name`	VARCHAR
`location`	STRUCT(latitude DOUBLE, longitude DOUBLE, features VARCHAR[])
`capacity`	BIGINT
`amenities`	VARCHAR[]
`water_type`	VARCHAR
`depth_meters`	DOUBLE
`is_public`	BOOLEAN
`maintenance_schedule`	VARCHAR