# Processing Semi-Structured Data with the NYC Philharmonic Dataset (/free-sample-datasets/nyc-philharmonic)



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 [#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 [#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:

```sql
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 [#example-queries]

### Program count [#program-count]

Count the programs in the raw JSON array.

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

### Most popular composers [#most-popular-composers]

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

```sql
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 [#concert-start-times]

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

```sql
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 [#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:

```sql
CREATE TABLE nyc_phil AS
WITH 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_instrument
FROM concerts_works_soloists,    UNNEST (soloists_array) AS t(soloist);
```

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

<InlineCta ctaLabel="Get started for free" ctaUrl="https://go.firebolt.io/signup">
  Spin up an engine and load this dataset yourself — Firebolt is free to try.
</InlineCta>