Skip to main content
Firebolt
start free →

Processing Semi-Structured Data with the NYC Philharmonic Dataset

31MB (JSON) · 164K flattened rows

ON THIS PAGE

Not all data fits a neat tabular model. Firebolt can handle semi-structured JSON, and this tutorial demonstrates three approaches: storing JSON as raw TEXT, partially flattening it into columns, or fully extracting and flattening it into a structured table.

## Key JSON functions

Firebolt provides these functions for working with semi-structured data:

  • JSON_POINTER_EXTRACT — extracts the value for a key using a JSON pointer expression and an expected data type.
  • JSON_POINTER_EXTRACT_ARRAY — extracts an array of strings from the path provided.
  • JSON_VALUE — converts to a scalar value.
  • UNNEST — converts an array into a set of rows.
  • Lambda functions for array processing.

## The NYC Philharmonic dataset

This dataset covers 175 years of NYC Philharmonic performances, with nested structures for seasons, concerts, composers, soloists, and works. It lives in S3 at:

s3://firebolt-sample-datasets-public-us-east-1/nyc_sample_datasets/nycphilharmonic

Start by creating an external table that reads each JSON file as a single raw TEXT column:

CREATE EXTERNAL TABLE ex_nyc_phil (	raw_data TEXT)URL ='s3://firebolt-sample-datasets-public-us-east-1/nyc_sample_datasets/nycphilharmonic'PATTERN = '*.json'TYPE = (JSON PARSE_AS_TEXT = TRUE);

## Example queries

### Program count

Count the programs in the raw JSON array.

SELECT LENGTH(JSON_POINTER_EXTRACT_ARRAY(raw_data,'/programs')) AS programs_arrays FROM ex_nyc_phil;

Unnest programs and works to rank composers by how often their works were performed.

WITH programs AS (        SELECT JSON_POINTER_EXTRACT_ARRAY(raw_data, '/programs') AS programs_arrays        FROM ex_nyc_phil),      works AS (        SELECT JSON_POINTER_EXTRACT_ARRAY(program, '/works') as works_array        FROM programs, UNNEST(programs_arrays) AS r(program))      SELECT        JSON_VALUE( JSON_POINTER_EXTRACT(work, '/composerName')) as composer_name, count(*)      FROM works, UNNEST(works_array) AS f(work)      WHERE JSON_VALUE(JSON_POINTER_EXTRACT(work, '/composerName')) IS NOT NULL      GROUP BY ALL      ORDER BY count(*) DESC;

### Concert start times

Unnest programs and concerts to see the most common concert start times.

WITH programs AS (          SELECT JSON_POINTER_EXTRACT_ARRAY(raw_data, '/programs') AS programs_arrays          FROM ex_nyc_phil      ),      concerts AS (          SELECT JSON_POINTER_EXTRACT_ARRAY(program, '/concerts') as concerts_arrays          FROM programs, UNNEST(programs_arrays) AS r(program)      )      SELECT JSON_POINTER_EXTRACT(concert, '/Time') as concert_time, count(*)      FROM concerts ,UNNEST(concerts_arrays) AS f(concert)      GROUP BY ALL      ORDER BY count(*) DESC;

## Flattening the JSON into a table

For repeated, column-based querying, flatten the nested JSON into a structured table with a single CREATE TABLE AS SELECT (CTAS). This unnests programs, concerts, works, and soloists into one wide table:

CREATE TABLE nyc_phil ASWITH programs AS (    SELECT JSON_POINTER_EXTRACT_ARRAY(raw_data, '/programs') AS programs_arrays    FROM ex_nyc_phil), concerts_works AS (    SELECT        JSON_POINTER_EXTRACT(program, '/season') AS season,        JSON_POINTER_EXTRACT(program, '/orchestra') AS orchestra,        JSON_POINTER_EXTRACT_ARRAY(program, '/concerts') as concerts_array,        JSON_POINTER_EXTRACT(program, '/programID') as program_id,        JSON_POINTER_EXTRACT_ARRAY(program, '/works') as works_array    FROM programs,        UNNEST(programs_arrays) AS r(program)), concerts_works_soloists AS (    SELECT        season,        orchestra,        JSON_VALUE(JSON_POINTER_EXTRACT(concert, '/Date'))::timestamptz as concert_date,        JSON_POINTER_EXTRACT(concert, '/eventType') as concert_event_type,        JSON_POINTER_EXTRACT(concert, '/Venue') as concert_venue,        JSON_POINTER_EXTRACT(concert, '/Location') as concert_location,        JSON_POINTER_EXTRACT(concert, '/Time') as concert_time,        program_id,        JSON_POINTER_EXTRACT(work, '/workTitle') as work_title,        JSON_POINTER_EXTRACT(work, '/ID') as work_id,        JSON_POINTER_EXTRACT(work, '/conductorName') as conduct_name,        JSON_POINTER_EXTRACT(work, '/composerName') as composer_name,        CASE WHEN JSON_POINTER_EXTRACT_ARRAY(work, '/soloists') = [] THEN ['No soloists'] ELSE JSON_POINTER_EXTRACT_ARRAY(work, '/soloists') END as soloists_array    FROM concerts_works,        UNNEST (concerts_array) as f(concert),        UNNEST (works_array) as p(work))SELECT    JSON_VALUE(season) as season,    JSON_VALUE(orchestra)as orchestra,    concert_date,    JSON_VALUE(concert_event_type) as concert_event_type,    JSON_VALUE(concert_venue) as concert_venue,    JSON_VALUE(concert_location)as concert_location,    JSON_VALUE(concert_time) as concert_time,    JSON_VALUE(program_id) as program_id,    JSON_VALUE(work_title) as work_title,    JSON_VALUE(work_id) as work_id,    JSON_VALUE( conduct_name) as conduct_name,    JSON_VALUE(composer_name) as composer_name,    JSON_POINTER_EXTRACT(soloist, '/soloistName') as soloist_name,    JSON_POINTER_EXTRACT(soloist, '/soloistRoles') as soloist_roles,    JSON_POINTER_EXTRACT(soloist, '/soloistInstrument') as soloist_instrumentFROM concerts_works_soloists,    UNNEST (soloists_array) AS t(soloist);

Once flattened, you can query concert dates, venues, composers, conductors, and soloists directly as columns.