Parsing the Cohost Export with DuckDB

Posted on October 10, 2024

• cohost
• DuckDB
• data
• ops
• programming

Cohost recently emailed me with a link to all the metadata for my account. There’s a lot there. There’s all your posts, images / attachments of course. But there’s also a lot of other useful metadata.

I want to take all my cohost posts and turn them into the format Astro expects. It shouldn’t be too hard, and we could do it with a simple node or python script, but it’d be a lot more fun to use DuckDB, so that’s what we’re going to do.

We are going to complete the task of migrating this cohost metadata into mdx files/attachments that Astro can consume with no assistance from a higher level programming language (like Node.js / python). We will use a little bash but even then the bash scripts will be generated by DuckDB queries and executed from within the DuckDB shell.

Some of this data is sort of personal, so I won’t host the whole payload (at least until I’ve gone through it) but I’ll include screenshots and samples of outputs as we go along so you can get an idea of how it all works and how cool DuckDB is.

What is DuckDB

In a nutshell, DuckDB is an in-process database that is designed to query a lot of data on your local machine. It’s also well known for its modern and convenient features. DuckDB can handle modern formats and syntax we’d never expect from a traditional database. It’s also very fast, and famous for running queries orders of magnitude faster than competing DBs (for its specific use case).

DuckDB is very good at importing random CSV’s or JSON data and turning it into structured and (easily queryable) binary.

DuckDB is designed to be used for offline data processing but that hasn’t stopped the larger community from using DuckDB in other contexts.
DuckDB has a partnership with a company Motherduck, that runs DuckDB on the cloud (primarily for use as a data warehouse). Lots of companies are also running DuckDB in WASM to power low latency dataviz and data analysis either entirely client side or hybrid client/server.

I think the most important thing to say about DuckDB is that it is a joy to use. You can feel the love that has gone into it. I would recommend it for anyone who is not super familiar with databases, but with the proviso that you are comfortable in some other existing host context (like Python, Bash, Node etc). Because DuckDB runs “in-process”, it is more of a native library than an external system, you always need some host context where you can run it.

Set up

First step, install DuckDB.

In my projects I usually have two git ignored folders input and output. I use it for tasks exactly like this, where I need to do some intermediary data processing but I don’t want to commit anything.

So step one, lets create those folders and ensure they are git ignored:

mkdir input output
echo "
input
output
" >> .gitignore

Now I’m going to put the cohost export in that folder and name it cohost-export

Querying the file system

Let’s query the filesystem using DuckDB to see what this export looks like. I’ve opened a terminal and run the DuckDB cli from ./input directory in this repo.

select filename 
from read_text('./cohost-export/**')
;

Hmm that’s a lot of data, its hard for me to get a sense of the structure. Let’s see if we can aggregate it a bit.

First let’s parse the path into usable metadata:

select 
    (filename)
    .regexp_extract(
        '(.*)/(.*)(\..*)'
        , ['dir', 'filename', 'ext']
    ) as struct
from read_text('./cohost-export/**')
limit 5
;

That’s great, but it’d be better if dir, filename and ext were separate columns, so let’s use unnest()

select 
    (filename)
    .regexp_extract(
        '(.*)/(.*)(\..*)'
        , ['dir', 'filename', 'ext']
    )
    .unnest()

from read_text('./cohost-export/**')
limit 5
;

Great!

Casually introducing advanced features

Already we’ve seen three pretty advanced features of DuckDB.

1. We’re querying the filesystem with SQL
2. We’re able to build nested structs of data
3. We’re able to dot chain methods within SQL

All of this is quite convenient but also quite unusual (for most databases). Normally a database runs on a server and doesn’t have access to local data on your filesystem.

There are other in-process databases (like SQLite) but they likely don’t have the same syntactic convenience.

Other databases like Postgres have nested data in various forms but there is no easy to use anonymous structs. You can only deal with nested data with predefined types.

Having nested anonymous binary data in your result sets is incredible powerful. It has surprised me how often I reach for it now.

Aggregating results

Okay, lets get back to aggregating. Let’s group by dir and collect filename/ext into an array and take a sample of the files so we can get a sense of the contents.

Let’s take the query we already had, and wrap it in a Common Table Expression (CTE). Which is basically just a pipeline of SQL

The CTE below is equivalent to the query before, but we can start to add more blocks.

with parsed as (
    select 
        (filename)
        .regexp_extract(
            '(.*)/(.*)(\..*)'
            , ['dir', 'filename', 'ext']
        )
        .unnest()
    from read_text('./cohost-export/**')
)
select * from parsed
limit 5
;

By the way, I’m just writing all this SQL in a markdown file in VSCode. In the terminal I’ve got DuckDB running in the VSCode terminal, and I select the text and send it to the terminal (I’ve bound that to Ctrl/Command + Enter).

I’ve also told DuckDB to output tables in markdown format

.mode markdown

So I can just paste the results straight back into this blogpost.

Now let’s aggregate!

with parsed as (
    select 
        (filename)
        .regexp_extract(
            '(.*)/(.*)(\..*)'
            , ['dir', 'filename', 'ext']
        )
        .unnest()
    from read_text('./cohost-export/**')
)
select 
    dir
    , (array_agg(distinct (ext))) as contents
from parsed
group by all
;

It’s the little things. group by all is another DuckDB nicety. Normally we’d have to specify every column we want to aggregate by. group by all just means: assume any unaggregated column is what we are grouping by. This lets us constantly revise our query without needing to update the group by to match the select.

Still too many results for me to comprehend, but the bulk of the data is posts so let’s focus on just the posts/*/ results.

select 
    (filename)
    .regexp_extract(
        '(.*)/(.*)(\..*)'
        , ['dir', 'filename', 'ext']
    )
    .unnest()
from read_text(
    './cohost-export/project/*/posts/*/**'
)
order by filename desc
limit 5
;

Let’s parse that path a little more specifically now:

select 
    (filename)
    .regexp_extract(
        './cohost-export/project/(.*)/posts/(.*)/(.*)/(.*)\.(.*)', 
        [ 'project'
        , 'status'
        , 'slug'
        , 'filename'
        , 'extension'
        ]
    )
    .unnest()
from read_text(
    './cohost-export/project/*/posts/*/**'
)
order by filename desc
limit 5
;

That’s a useful query, lets give it a name:

create or replace view vw_post_files as
select 
    (filename)
    .regexp_extract(
        './cohost-export/project/(.*)/posts/(.*)/(.*)/(.*)\.(.*)', 
        [ 'project'
        , 'status'
        , 'slug'
        , 'filename'
        , 'extension'
        ]
    )
    .unnest()
    , filename as filepath
from read_text(
    './cohost-export/project/*/posts/*/**'
)
;

And we can query that by name now:

select * from vw_post_files
order by filename desc
limit 5
;

DuckDB can arbitrarily unnest CTE’s, view definitions, and nested queries there is no runtime performance penality for composing SQL like this (not necessarilly true in other DB systems, DuckDB is special here).

Exploring JSON data

Let’s dive in to the actual post.json files now.

We can glob them from the filesystem trivially:

select 
    * exclude(blocks, filename)
from read_json('./cohost-export/**/*/*/post.json', union_by_name=true, filename=true)
order by singlePostPageURL desc
limit 5
;

I’ll explain union_by_name=true momentarily…

Yep, we just read json files from disk and DuckDB inferred a unified schema and returned it back to us as native rows.

Because the cohost post content is itself markdown its hard to nest in a markdown table, so I’ve excluded the blocks column (exclude(blocks)) just to get a sense of the high level structure. blocks is the column that contains each content block (markdown/attachments etc).

select * exclude ( columnA, columnB ) is another DuckDB nicety. Normally if we want to exclude a column in SQL we have to explicitly list every column we want to include, not so with DuckDB!

But because that content is just json, we can unnest again and take a look at the block content. We’ll deliberately skip blocks with nested codeblocks (as it would confuse this blog’s renderer).

with xs as (
    select 
        singlePostPageURL
        , unnest(blocks, recursive:= true)
    from read_json(
        './cohost-export/**/*/*/post.json'
        , union_by_name=true
    )
)
select * 
from xs
-- ignore code blocks that would 
-- confuse Astro (`)
where content not ilike '%`%' 
order by singlePostPageURL desc
limit 5
;

Automatic schema detection

You can see we’ve got all these other columns appearing that are never used in our sample. By default DuckDB does a sample read when reading JSON / CSV / etc so it can infer the correct schema.

If different files have different structures that cannot be merged into a shared schema DuckDB will tell you it needs your input on how to proceed. You can ignore rows with errors in them, you can manually specifiy the schema, you can increase the sample threshold. For my usage I find union_by_name fixes 99% of my problems. It allows columns and keys to appear out of order (or not at all), but relies on the name of the field to join similar rows.

Transforming our data

We have discovered the structure of our data, we now need to convert it into an mdx file that Astro can digest.

It’s tempting to reach for a host language at this point. We want to create directories, and we want to generate frontmatter, typescript schemas, markdown files. Move file attachments around, update references in the markdown to the local reference and on and on.

But let’s see how far we can get with just the DuckDB CLI though. You might be surprised!

Frontmatter

Frontmatter is the name for the metadata at the top of a markdown document. It looks like this:

---
title: Parsing the Cohost Export with DuckDB
subtitle: Code really is data.
created: 2024-10-10T04:28:15.002Z
archived: false
featured: false
tags:
- cohost
- DuckDB
- data
- ops
- programming
---

That is the frontmatter for this post at time of writing. This metadata helps this blog generate web pages. We can query it at build time to e.g. get back all the posts that have a particular tag. Embedding the metadata in the post is a neat way of avoiding having separate external config files (or even a database) for collating the posts.

We want to generate this frontmatter to prefix the mdx file for all the cohost posts. DuckDB has no built in support for YAML (the underlying data type of frontmatter). But we can at least create a DuckDB structure of all the posts and then manually construct the yaml string.

Let’s query those fields and name them using this blog’s naming conventions:

select 
    headline as title
    , publishedAt as created
    , true as archived
    , false as featured
    , tags
    , regexp_extract(
        singlePostPageURL
        , '.*/post/(.*)'
        , ['slug']
    )
    .unnest()

from read_json(
    './cohost-export/**/*/*/post.json'
    , union_by_name=true
    , filename=true
)
order by singlePostPageURL desc
limit 5
;

With mode set to .mode line it almost looks like yaml.

    title = Samorost
  created = 2023-02-01T11:05:41.324Z
 archived = true
 featured = false
     tags = [gamedev, art, games, documentary]
     slug = 943274-samorost

    title = Listening to Frusciante / Klinghoffer today
  created = 2023-02-01T07:07:44.007Z
 archived = true
 featured = false
     tags = [music, klinghoffer, frusciante, rhcp]
     slug = 942742-listening-to-fruscia

    title = Starting the next song
  created = 2023-01-27T06:56:51.647Z
 archived = true
 featured = false
     tags = [Midi, home recording, songwriting, crosspost:jmsfbs.com]
     slug = 915980-starting-the-next-so

    title = New Tracks
  created = 2023-01-27T06:48:57.228Z
 archived = true
 featured = false
     tags = [music, home recording, crosspost:jmsfbs.com]
     slug = 915951-new-tracks

    title = New Track - Wicked
  created = 2023-01-26T03:09:33.404Z
 archived = true
 featured = false
     tags = [music, songwriting, home recording, crosspost:jmsfbs.com]
     slug = 909617-new-track-wicked

In fact, this would be the equivalent valid yml/frontmatter:

- title: Samorost
  created: 2023-02-01T11:05:41.324Z
  archived: true
  featured: false
  tags: [gamedev, art, games, documentary]
  slug: 943274-samorost

- title: Listening to Frusciante / Klinghoffer today
  created: 2023-02-01T07:07:44.007Z
  archived: true
  featured: false
  tags: [music, klinghoffer, frusciante, rhcp]
  slug: 942742-listening-to-fruscia

- title: Starting the next song
  created: 2023-01-27T06:56:51.647Z
  archived: true
  featured: false
  tags: [Midi, home recording, songwriting, crosspost:jmsfbs.com]
  slug: 915980-starting-the-next-so

  title: New Tracks
  created: 2023-01-27T06:48:57.228Z
  archived: true
  featured: false
  tags: [music, home recording, crosspost:jmsfbs.com]
  slug: 915951-new-tracks

- title: "New Track - Wicked"
  created: 2023-01-26T03:09:33.404Z
  archived: true
  featured: false
  tags: [music, songwriting, home recording, crosspost:jmsfbs.com]
  slug: 909617-new-track-wicked

When I first saw this output I was tempted to write this native DuckDB format to a file and regex replace it into yaml as it is so close. But instead we’ll generate the frontmatter using a general purpose technique because the intention of this post is to show off DuckDB not solve this specific problem.

Let’s adjust our query to produce a text column frontmatter that uses all the metadata from the previous query. Note the use of E'\n' we use this because the newline character is automatically escaped and it won’t render as expected. E'\n' is an unescaped / “raw” string.

select
    (
    ''
    || E'---'
    || E'\ntitle: ' 
    ||  printf('"%s"', headline)
    || E'\ncreated: ' 
    ||  publishedAt
    || E'\nfeatured: false'
    || E'\narchived: true'
    || E'\ntags: ' 
    ||  (tags || ['from:cohost'])
            .to_json()::text
    || E'\nslug: ' 
    ||  regexp_extract(
            singlePostPageURL
            , '.*/post/(.*)'
            , ['slug']
        )
        .slug
    || E'\n---'
    )
    as frontmatter
from read_json(
    './cohost-export/**/*/*/post.json'
    , union_by_name=true
)
order by singlePostPageURL desc
limit 5
;

We get this in .mode list

---
title: Samorost
created: 2023-02-01T11:05:41.324Z
featured: false
archived: true
tags: ["gamedev","art","games","documentary","from:cohost"]
slug: 943274-samorost
---
---
title: Listening to Frusciante / Klinghoffer today
created: 2023-02-01T07:07:44.007Z
featured: false
archived: true
tags: ["music","klinghoffer","frusciante","rhcp","from:cohost"]
slug: 942742-listening-to-fruscia
---
---
title: Starting the next song
created: 2023-01-27T06:56:51.647Z
featured: false
archived: true
tags: ["Midi","home recording","songwriting","crosspost:jmsfbs.com","from:cohost"]
slug: 915980-starting-the-next-so
---
---
title: New Tracks
created: 2023-01-27T06:48:57.228Z
featured: false
archived: true
tags: ["music","home recording","crosspost:jmsfbs.com","from:cohost"]
slug: 915951-new-tracks
---
---
title: New Track - Wicked
created: 2023-01-26T03:09:33.404Z
featured: false
archived: true
tags: ["music","songwriting","home recording","crosspost:jmsfbs.com","from:cohost"]
slug: 909617-new-track-wicked
---

Let’s bring that filename parsing logic in and add some of that metadata to the frontmatter too:

with xs as (
    select 
        * exclude (filename)
        , (filename)
            .regexp_extract(
                './cohost-export/project/(.*)/posts/(.*)/(.*)/(.*)\.(.*)', 
                [ 'project'
                , 'status'
                , 'slug'
                , 'filename'
                , 'extension'
                ]
            )
            .unnest()
    from read_json(
        './cohost-export/**/*/*/post.json'
        , union_by_name=true
        , filename=true
    )
)
select 
    (
        ''
        || E'---'
        || E'\ntitle: ' 
        ||  printf('"%s"', headline)
        || E'\ncreated: ' ||  publishedAt
        || E'\featured: false'
        || E'\narchived: true'
        || E'\ncohost_project: ' || project
        || E'\ncohost_status: ' || status
        || E'\ntags: ' 
        ||  (tags || ['from:cohost'])
                .to_json()::text
        || E'\nslug: ' ||  slug
        || E'\n---'
    )
    as frontmatter
from xs
order by singlePostPageURL desc
limit 5
;

Let’s name that query via a view and with a few metadata columns for joining / debugging.

I’ve also added a few minor changes to handle edgecases I discovered while testing different pages.

create or replace view vw_post_frontmatter as
with xs as (
    select 
        * exclude (filename)
    , (filename)
        .regexp_extract(
            './cohost-export/project/(.*)/posts/(.*)/(.*)/(.*)\.(.*)', 
            ['project'
            , 'status'
            , 'slug'
            , 'filename'
            , 'extension'
            ]
        )
        .unnest()
    
    from read_json(
        './cohost-export/**/*/*/post.json'
        , union_by_name=true
        , filename=true
    )
)
select 
    postId
    , headline
    , slug
    , printf('"%s"', headline) as title
    -- drafts don't have publishedAt
    , coalesce(
        publishedAt
        -- to ISO8601:
        , now()::json->>'$'
    ) as created 
    , false as featured
    , true as archived
    , project as cohost_project
    , status as cohost_status
    , (
        tags || ['from:cohost']
    )
      .to_json()::text as tags_text
    , (
    ''
    || E'---'
    || E'\ntitle: ' || title
    || E'\ncreated: ' || created
    || E'\nfeatured: ' || featured
    || E'\narchived: ' || archived
    || E'\ncohost_project: ' 
    ||  cohost_project
    || E'\ncohost_status: ' 
    ||  cohost_status
    || E'\ntags: ' || tags_text
    || E'\nslug: ' || slug
    || E'\n---'
    )
    as frontmatter
from xs
;

Joining the Markdown content

Now what about the markdown content? We need to glue that all together. Let’s try it with an aggregate function string_agg.

with xs as (
    select 
        headline
        , unnest(blocks) as block
    from read_json(
        './cohost-export/**/*/*/post.json'
        , union_by_name=true
    )
)
select 
    headline
    , string_agg(
        block.markdown.content
        , E'\n\n'
    )[0:100] as content
from xs
where block.type = 'markdown'
group by all
limit 5
;

I’ve sliced the first 100 chars, and limited to the first 5 rows. But we can see this is working.

But we also need to handle including attachments in the blog post. First we’ll build up a data structure for the non markdown blocks. Then we’ll integrate it into the post content.

with xs as (
    select 
        headline
        , unnest(
            blocks
            , recursive:= true
        ) as block
    from read_json(
        './cohost-export/**/*/*/post.json'
        , union_by_name=true
    )
)
select *
from xs
where type <> 'markdown'
;

Let’s see what type of attachments we have:

with xs as (
    select 
        headline
        , unnest(
            blocks
            , recursive:= true
        ) as block
    from read_json(
        './cohost-export/**/*/*/post.json'
        , union_by_name=true
    )
)
, ys as (
    select 
        type
        , kind
        , parse_filename(fileURL)
            .regexp_split_to_array('\.')[-1] 
            as ext
    from xs
    where type = 'attachment'
)
pivot ys
on ext
using count(*)
;

At least for my dataset, this is all I have to consider:

• audio/mp3
• image/jpg
• image/png
• image/webp

The cohost export comes prebundled with all the file attachments. This makes a lot of sense as they are planning to delete all user data at the end of the year. But at time of writing, those fileURL and previewURL URLs are still live. So for simplicity I’m just going to re-download them instead of joining on filename (which can be a little risky in terms of name collisions).

Let’s start by making a virtual directory struture for our posts. We’ll drop the attachments into this virtual file system.

with xs as (
    select 
        headline
        , unnest(
            blocks
            , recursive:= true
        ) as block
        , * exclude (blocks)
    from read_json(
        './cohost-export/**/*/*/post.json'
        , union_by_name=true
    )
)
select 
    (singlePostpageURL)
        .parse_filename() as slug
    , (fileURL)
        .parse_path()[-2:-1]
        .array_to_string('-') as filename
    , fileURL as src
    , printf(
        './%s/%s', slug, filename
    ) as dest
from xs
where type <> 'markdown'
;

Now lets add a command colum cmd to download each file to the result set.

printf(
    'mkdir -p "%s"; wget "%s" -O "%s"'
    , parent_dir
    , src
    , dest
)

with xs as (
    select 
        headline
        , unnest(
            blocks
            , recursive:= true
        ) as block
        , * exclude (blocks)
    from read_json(
        './cohost-export/**/*/*/post.json'
        , union_by_name=true
    )
)
select 
    (singlePostpageURL)
        .parse_filename() as slug
    , (fileURL)
        .parse_path()[-2:-1]
        .array_to_string('-') as filename
    , fileURL as src
    , printf(
        '../output/posts/%s/attachments'
        , slug
    ) as parent_dir
    , printf(
        '%s/%s'
        , parent_dir
        , filename
    ) as dest
    , printf(
        'mkdir -p "%s"; wget "%s" -O "%s"'
        , parent_dir
        , src
        , dest
    ) as cmd
from xs
where type <> 'markdown'
;

Here’s a sample of one of those generated commands:

mkdir -p "../output/posts/881830-midi-thoughts/attachments"; 
wget "https://staging.cohostcdn.org/attachment/fc7337ec-1135-484f-9ede-cf9afc075300/PXL_20230121_083234984_2.jpg" 
    \ -O "../output/posts/881830-midi-thoughts/attachments/fc7337ec-1135-484f-9ede-cf9afc075300-PXL_20230121_083234984_2.jpg"

Now if we just select cmd only, turn headers off (.header off), turn off all table formatting (.mode list) and set the output mode to a file (.once $outputFile), we can write these commands to a script on disk.

.mode list
.header off
.once ../output/download-attachment.sh

with xs as (
    select 
        headline
        , unnest(
            blocks
            , recursive:= true
        ) as block
        , * exclude (blocks)
    from read_json(
        './cohost-export/**/*/*/post.json'
        , union_by_name=true
    )
)
, ys as (
    select 
        (singlePostpageURL)
            .parse_filename() as slug
        , (fileURL)
            .parse_path()[-2:-1]
            .array_to_string('-') as filename
        , fileURL as src
        , printf(
            '../output/posts/%s/attachments'
            , slug
        ) as parent_dir
        , printf(
            '%s/%s'
            , parent_dir
            , filename
        ) as dest
        , printf(
            'mkdir -p "%s"; wget "%s" -O "%s"'
            , parent_dir
            , src
            , dest
        ) as cmd
    from xs
    where type <> 'markdown'
)
select cmd from ys
;

Now we have a script on our filesystem where each line downloads 1 attachment via wget. It looks like this (substituting the $variables for real values):

mkdir -p "$parent_dir"; 
wget "$src1" -O "$dest1";
mkdir -p "$parent_dir"; 
wget "$src2" -O "$dest2";
mkdir -p "$parent_dir"; 
wget "$src3" -O "$dest3";
# etc...

We can also run bash commands from the DuckDB shell.

E.g.

.shell ls cohost-export

project  user-notes  user.json

So let’s run our script:

.shell bash ../output/download-attachment.sh

And after running it, we can see the downloads worked as expected:

select 
    filename
    , size
    , last_modified
from read_text('../output/posts/**')
;

Building the post content

Previously we authored a query to build the post content but we skipped attachments. We’re now going to reference our locally downloaded attachments instead of skipping them.

with xs as (
    select 
        headline
        , unnest(blocks) as block
    from read_json(
        './cohost-export/**/*/*/post.json'
        , union_by_name=true
    )
)
select 
    headline
    , block
    , (block.attachment.fileURL)
        .parse_path()[-2:-1]
        .array_to_string('-') as dest
    , (
        case block.type
            when 'markdown' 
                then block.markdown.content 
            when 'attachment' then 
                case block.attachment.kind
                    when 'audio' then 
                        printf(
                            '<Audio title={"%s"} artist={"%s"} src={import("./attachments/%s")} />'
                            , block.attachment.title
                            , block.attachment.artist, dest
                        )
                                            when 'image' then 
                        printf(
                            '<Image alttext={"%s"} src={import("./attachments/%s")} />'
                            , block.attachment.alttext
                            , dest
                        )
                end
        end
        
    ) as content
from xs
where headline = 'China Doll Sessions'
;

Gives us:

Now that content field can be string_agg’d

with xs as (
    select 
        headline
        , unnest(blocks) as block
    from read_json(
        './cohost-export/**/*/*/post.json'
        , union_by_name=true
    )
)
, ys as (
    select 
        headline
        , block
        , (block.attachment.fileURL)
            .parse_path()[-2:-1]
            .array_to_string('-') as dest
        , (
            case block.type
                when 'markdown' then 
                    block.markdown.content 
                when 'attachment' then 
                    case block.attachment.kind
                        when 'audio' then 
                            printf(
                                '<Audio title={"%s"} artist={"%s"} src={import("./attachments/%s")} />'
                                , block.attachment.title
                                , block.attachment.artist
                                , dest
                            )
                                                    when 'image' then 
                            printf(
                                '<Image alttext={"%s"} src={import("./attachments/%s")} />'
                                , block.attachment.alttext
                                , dest
                            )
                    end
            end
        ) as content
    from xs
)
select 
    headline
    , string_agg(content, E'\n\n') as content
from ys
group by all
;

If we make that a view too…

create or replace view vw_post_content as
with xs as (
    select 
        postId
        , headline
        , unnest(blocks) as block 
    from read_json(
        './cohost-export/**/*/*/post.json'
        , union_by_name=true
    )
)
, ys as (
    select 
        postId
        , headline
        , block
        , (block.attachment.fileURL)
            .parse_path()[-2:-1]
            .array_to_string('-') as dest
        , (
            case block.type
                when 'markdown' then 
                    block.markdown.content 
                when 'attachment' then 
                    case block.attachment.kind
                        when 'audio' then 
                            printf(
                                '<Audio title={"%s"} artist={"%s"} src={import("./attachments/%s")} />'
                                , block.attachment.title
                                , block.attachment.artist
                                , dest
                            )
                        when 'image' then 
                            printf(
                                '<Image alttext={"%s"} src={import("./attachments/%s")} />'
                                , block.attachment.alttext
                                , dest
                            )
                    end
            end
        ) as content
    from xs
)
select 
    postId
    , headline
    , string_agg(content, E'\n\n') as content
from ys
group by all
;

We can have a look at one of the posts:

.mode list

select 
    content
from vw_post_content
where headline = 'China Doll Sessions'
;

And we get this:

<Audio title={"china doll guitar chain experiment"} artist={"jmsfbs, baby x"} src={import("./attachments/c7457348-f770-408a-8d54-317f7ac33ffe-China%20Doll%202.mp3")} />

<Image alttext={"I re-arranged my room, this is the new recording setup."} src={import("./attachments/e41ee3e0-be75-402c-8b5c-507fbca1124a-PXL_20240429_062918414.jpg")} />

<Audio title={"china doll guitar chain experiment 2"} artist={"jmsfbs, baby x"} src={import("./attachments/1039f2fa-9519-4b66-8b52-2704b1967a0e-China%20Doll%202.mp3")} />

This is an old song from my early [baby x](https://babyx.bandcamp.com/album/i-am-1-am) days, in fact, the first baby x jam predated Drew as the drummer and the song we rehearsed was China Doll!

The lyrics (aside from the breakdown section) were written by an old friend (Dom Gilchrist) but the words meant a lot to me at the time because the exact situation described in the song was happening where I was the friend who knew his other friend was being cheated on but didn't want to say anything to ruin their relationship.  That whole situation led us to being temporarily estranged.  This was myspace drama days!

It's a been a long time since I last recorded anything.  I re-arranged my room months ago and life got in the way and I hadn't even plugged anything in.

I've done a few things differently this time which I might write about later, but the most interesting thing I guess is I've sort of got a hold on making guitars sound big.  This eluded me for so long, and I'm just using free and stock plugins.

I tried piping the guitars into BiasFX and it instantly sounded amazing, but a) I don't want to become too dependent on paid plugins, and b) Bias is so slow when you start adding lots of layers.  So instead I've been struggling with impulse response files and stock amp / cabinet simulators.  

Then using a lot of side chain compression and EQ to make sure the lead cuts through the droning guitars whenever it comes in, but trying to keep it subtle enough that it doesn't feel like its being ducked.

Attached is a loop of that approach in practice.

My current thinking is I'm not going to release anything for a while, just keeping noodling and blogging and when I've got a set of songs spend some time refining them and making them work well together.

You’ll note I’m dropping components into my markdown. This is only possible because Astro supports MDX. I’m using MDX instead of Markdown as it makes it possible to drop in rich media for embeds.

Now we can glue the frontmatter with the content with a simple join.

select 
    frontmatter 
    || E'\n\n' 
    || content as content
from vw_post_content
inner join vw_post_frontmatter using(headline)
where headline = 'China Doll Sessions'
;

And we get this:

---
title: China Doll Sessions
created: 2024-04-30T09:59:48.880Z
featured: false
archived: true
cohost_project: jmsfbs
cohost_status: published
tags: ["recording","baby x","band","myspace","Drama","music","from:cohost"]
slug: 5777910-china-doll-sessions
---

<Audio title={"china doll guitar chain experiment"} artist={"jmsfbs, baby x"} src={import("./attachments/c7457348-f770-408a-8d54-317f7ac33ffe-China%20Doll%202.mp3")} />

<Image alttext={"I re-arranged my room, this is the new recording setup."} src={import("./attachments/e41ee3e0-be75-402c-8b5c-507fbca1124a-PXL_20240429_062918414.jpg")} />

<Audio title={"china doll guitar chain experiment 2"} artist={"jmsfbs, baby x"} src={import("./attachments/1039f2fa-9519-4b66-8b52-2704b1967a0e-China%20Doll%202.mp3")} />

This is an old song from my early [baby x](https://babyx.bandcamp.com/album/i-am-1-am) days, in fact, the first baby x jam predated Drew as the drummer and the song we rehearsed was China Doll!

The lyrics (aside from the breakdown section) were written by an old friend (Dom Gilchrist) but the words meant a lot to me at the time because the exact situation described in the song was happening where I was the friend who knew his other friend was being cheated on but didn't want to say anything to ruin their relationship.  That whole situation led us to being temporarily estranged.  This was myspace drama days!

It's a been a long time since I last recorded anything.  I re-arranged my room months ago and life got in the way and I hadn't even plugged anything in.

I've done a few things differently this time which I might write about later, but the most interesting thing I guess is I've sort of got a hold on making guitars sound big.  This eluded me for so long, and I'm just using free and stock plugins.

I tried piping the guitars into BiasFX and it instantly sounded amazing, but a) I don't want to become too dependent on paid plugins, and b) Bias is so slow when you start adding lots of layers.  So instead I've been struggling with impulse response files and stock amp / cabinet simulators.  

Then using a lot of side chain compression and EQ to make sure the lead cuts through the droning guitars whenever it comes in, but trying to keep it subtle enough that it doesn't feel like its being ducked.

Attached is a loop of that approach in practice.

My current thinking is I'm not going to release anything for a while, just keeping noodling and blogging and when I've got a set of songs spend some time refining them and making them work well together.

Importing components

Finally, if an Audio or Image attachment is used we need to import that component in the MDX content. Let’s inject that if it is required for the post. First let’s detect if there’s any audio in a post.

select 
    json_contains(
        blocks
        , { kind: 'audio' }
    ) as import_audio
    ,json_contains(
        blocks
        , { kind: 'image' }
    ) as import_image
    -- ...

And then inject the imports:

select
    -- ...
    || E'\n' 
    || case when import_audio 
        then E'\nimport Audio from "../../../components/Audio.astro"' 
        else '' 
    end
    || case when import_image 
        then E'\nimport Image from "../../../components/Image.astro"' 
        else '' 
    end
    || E'\n\n'
    -- ...

In context:

select 
    json_contains(
        blocks
        , { kind: 'audio' }
    ) as import_audio
    ,json_contains(
        blocks
        , { kind: 'image' }
    ) as import_image
    
    ,frontmatter 
    || E'\n' 
    || case when import_audio 
        then E'\nimport Audio from "../../../components/Audio.astro"' 
        else '' end
    || case when import_image 
        then E'\nimport Image from "../../../components/Image.astro"' 
        else '' end
    || E'\n\n'
    || content as content
from read_json(
    './cohost-export/**/*/*/post.json'
    , union_by_name=true
) as data
inner join vw_post_content 
    as content using(postId)
inner join vw_post_frontmatter 
    as frontmatter using(postId)
where postId = (
    select postId from vw_post_content 
    where headline = 'China Doll Sessions'
)
;

That gives us:

---
title: China Doll Sessions
created: 2024-04-30T09:59:48.880Z
featured: false
archived: true
cohost_project: jmsfbs
cohost_status: published
tags: ["recording","baby x","band","myspace","Drama","music","from:cohost"]
slug: 5777910-china-doll-sessions
---

import Audio from "../../../components/Audio.astro"
import Image from "../../../components/Image.astro"

<Audio title={"china doll guitar chain experiment"} artist={"jmsfbs, baby x"} src={import("./attachments/c7457348-f770-408a-8d54-317f7ac33ffe-China%20Doll%202.mp3")} />

<Image alttext={"I re-arranged my room, this is the new recording setup."} src={import("./attachments/e41ee3e0-be75-402c-8b5c-507fbca1124a-PXL_20240429_062918414.jpg")} />

<Audio title={"china doll guitar chain experiment 2"} artist={"jmsfbs, baby x"} src={import("./attachments/1039f2fa-9519-4b66-8b52-2704b1967a0e-China%20Doll%202.mp3")} />

This is an old song from my early [baby x](https://babyx.bandcamp.com/album/i-am-1-am) days, in fact, the first baby x jam predated Drew as the drummer and the song we rehearsed was China Doll!

The lyrics (aside from the breakdown section) were written by an old friend (Dom Gilchrist) but the words meant a lot to me at the time because the exact situation described in the song was happening where I was the friend who knew his other friend was being cheated on but didn't want to say anything to ruin their relationship.  That whole situation led us to being temporarily estranged.  This was myspace drama days!

It's a been a long time since I last recorded anything.  I re-arranged my room months ago and life got in the way and I hadn't even plugged anything in.

I've done a few things differently this time which I might write about later, but the most interesting thing I guess is I've sort of got a hold on making guitars sound big.  This eluded me for so long, and I'm just using free and stock plugins.

I tried piping the guitars into BiasFX and it instantly sounded amazing, but a) I don't want to become too dependent on paid plugins, and b) Bias is so slow when you start adding lots of layers.  So instead I've been struggling with impulse response files and stock amp / cabinet simulators.  

Then using a lot of side chain compression and EQ to make sure the lead cuts through the droning guitars whenever it comes in, but trying to keep it subtle enough that it doesn't feel like its being ducked.

Attached is a loop of that approach in practice.

My current thinking is I'm not going to release anything for a while, just keeping noodling and blogging and when I've got a set of songs spend some time refining them and making them work well together.

We never left the DuckDB shell

I think it is worth calling out at this point that technically we never left the DuckDB shell. And as foreign as this may seem, the total code to do this is pretty concise. It’s also a lot easier to debug because we deal with result sets (data) until the very last moment (code is data and data is code).

Yeah, at one point we generated a shell script from data and executed that from within the DuckDB shell.

But that bash script is just an artifact, its an executable result set.

Postgres’ repl has a slash command gexec which lets you natively execute each row in a shell and get the result back as a new line in a result set. So I’m definitely not the first person to do this, its kind of cool!

Last step: Write the generated mdx to disk

We’ve generated the content, we’ve downloaded the attachment files. Let’s write it to disk.

Step 1, lets just write it to our output folder. And when we’re absolutely sure everything is perfect we’ll copy it into the real posts directory (used by posts like this), and astro should pick them up.

When in doubt, base64

Now for each post we need to write the content to disk at a different location.

We’ll use base64 so we don’t have to worry about shell escaping. We’ll encode the post as base64, and embed that in a command like so:

echo $base64 \
| base64 --decode \
> ./posts/$slug/index.mdx

First we change the output format, and set up the next command to write to our shell script

.mode list
.header off
.once ../output/write-content-to-disk.sh

with xs as (
    select 
        json_contains(
            blocks
            , { kind: 'audio' }
        ) as import_audio
        ,json_contains(
            blocks
            , { kind: 'image' }
        ) as import_image
        ,headline
        ,postId
        ,slug
        ,frontmatter 
        || E'\n' 
        || case 
            when import_audio 
                then E'\nimport Audio from "../../../components/Audio.astro"' 
                else '' 
            end
        || case 
            when import_image 
                then E'\nimport Image from "../../../components/Image.astro"' 
                else '' 
            end
        || E'\n\n'
        || content as content
    from read_json(
        './cohost-export/**/*/*/post.json'
        , union_by_name=true
    ) as data
    inner join vw_post_content 
        as content using(postId, headline)
    inner join vw_post_frontmatter 
        as frontmatter using(postId, headline)
)
select 
    printf(
        'mkdir -p ../output/posts/%s; echo "%s" | base64 --decode > "../output/posts/%s/index.mdx"'
        , slug
        , to_base64(
            encode(content))
            , slug
        ) as cmd
from xs
;

We now have a script that looks like this (with a lot more lines):

echo "Ci0tLQp0aXRsZTogTW92aW5nIHRvIGdob3N0PwpjcmVhdGVkOiAyMDIzLTA4LTI2VDAyOjU0OjEzLjM3OFoKcHVpYmxpc2hlZDogdHJ1ZQphcmNoaXZlZDogZmFsc2UKdGFnczogWyJnaG9zdCIsImJsb2ciLCJmcm9tOmNvaG9zdCJdCnNsdWc6IDI2MjU0NzAtbW92aW5nLXRvLWdob3N0Ci0tLQoKCkknbSB0aGlua2luZyBvZiBtb3ZpbmcgdGhpcyBwYWdlIHRvIGdob3N0LiAgSSB3YXMgdXNpbmcgY29ob3N0J3MgcnNzIEFQSSB0byBmZWVkIG15IHdlYnNpdGUsIGJ1dCB0aGVyZSdzIGJlZW4gYW4gb3V0c3RhbmRpbmcgaXNzdWUgZm9yIHVzZXJuYW1lcyB3aXRoIGh5cGhlbnMgaW4gdGhlbSBmb3IgbWFueSBtb250aHMgbm93IGFuZCBJIGFsc28gZG9uJ3QgdGhpbmsgY29ob3N0IGlzIHRoZSByaWdodCB0b29sIGZvciB0aGF0IGpvYiBhbnl3YXkuCgpNeSB0aGlua2luZyBpcyB0byBob3N0IGEgd2FrZSBvbiByZXF1ZXN0IGdob3N0IGluc3RhbmNlIG9uIGZseS4gIFRoZSBkb2NrZXIgaW1hZ2UgbG9va3MgcHJldHR5IHN0cmFpZ2h0IGZvcndhcmQuICBXZSdsbCBzZWUgaG93IGl0IGdvZXMuIA==" | base64 --decode > "../output/posts/2625470-moving-to-ghost/index.mdx"
echo "Ci0tLQp0aXRsZTogCmNyZWF0ZWQ6IDIwMjQtMDItMDRUMDI6MzU6MTguMTkyWgpwdWlibGlzaGVkOiB0cnVlCmFyY2hpdmVkOiBmYWxzZQp0YWdzOiBbInByb2dyYW1taW5nIiwianMiLCJmcm9tOmNvaG9zdCJdCnNsdWc6IDQzNjk4MDYta2luZC1vZi1zdXJwcmlzZWQtaG8KLS0tCgoKS2luZCBvZiBzdXJwcmlzZWQgaG93IG9mdGVuIEkgdXNlIGxhYmVscyBpbiBqcyBub3cu" | base64 --decode > "../output/posts/4369806-kind-of-surprised-ho/index.mdx"

Let’s run it:

.shell bash ../output/write-content-to-disk.sh

It works! Here is a sample of the outputted directories:

And here is the generated file content in the index.mdx file referencing the correct attachments on disk.

Conclusion

This has been a long post! I hope it has been an interesting one. It’s cool to see the total code to do all of this in one screen is only 104 lines.

create or replace view vw_post_frontmatter as
-- ...
;

create or replace view vw_post_content as
-- ...
;

.mode list
.header off
.once ../output/download-attachment.sh

with xs as (
    select 
        headline, unnest(blocks, recursive:= true) as block, * exclude (blocks)
    from read_json('./cohost-export/**/*/*/post.json', union_by_name=true)
)
, ys as (
    select 
        (singlePostpageURL).parse_filename() as slug
        , (fileURL).parse_path()[-2:-1].array_to_string('-') as filename
        , fileURL as src
        , printf('../output/posts/%s/attachments', slug) as parent_dir
        , printf('%s/%s', parent_dir, filename) as dest
        , printf('mkdir -p "%s"; wget "%s" -O "%s"', parent_dir, src, dest) as cmd
    from xs
    where type <> 'markdown'
)
select cmd from ys
;

.mode list
.header off
.once ../output/write-content-to-disk.sh

with xs as (
    select 
        json_contains(blocks, { kind: 'audio' }) as import_audio
        ,json_contains(blocks, { kind: 'image' }) as import_image
        ,FM.headline
        ,postId
        ,slug
        ,frontmatter 
        || E'\n' 
        || case when import_audio then E'\nimport Audio from "../../../components/Audio.astro"' else '' end
        || case when import_image then E'\nimport Image from "../../../components/Image.astro"' else '' end
        || E'\n\n'
        || content as content
    from read_json('./cohost-export/**/*/*/post.json', union_by_name=true) as data
    inner join vw_post_content as C using(postId)
    inner join vw_post_frontmatter as FM using(postId)
)
select 
    printf('mkdir -p ../output/posts/%s; echo "%s" | base64 --decode > "../output/posts/%s/index.mdx"', slug, to_base64(encode(content)), slug) as cmd
from xs
;

.shell bash ../output/download-attachment.sh
.shell bash ../output/write-content-to-disk.sh

After a bit of time I’ll publish these Cohost posts on this site. First I need to think of a nice way to segment them from actual articles as when I render them all the home page looks a little busy. Additionally, some of the Cohost posts are close to being “real” articles I’d post here, so I may just upgrade them and republish in their own right.

Looking forward I’ll probably have a micro blog section that includes these processed cohosts and any other posts from around the web (e.g. Mastodon, BlueSky).

If you have a Cohost export and you’d like to convert it to a format like Markdown or MDX, I would recommend waiting around for someone to build a more robust tool.
But if you are interested in learning DuckDB I hope this has been a helpful practical demonstrattion of the power of DuckDB for general purpose data slicing and dicing.

Next time you’ve got to inspect some data and you’re reaching for tools like JQ maybe use it as an opportunity to try DuckDB out. I think it is so nice we can bring SQL to a domain where we’d normally have to learn some new and arcane DSL. Mastering SQL is non trivial, but most technical (and even semi-technical) people can write even a simple SQL query. That is very powerful and should be exploited if we want simpler systems to maintain.

---

Thank you for reading. If you’d like to reach out you can get in touch on Mastodon, BlueSky or Twitter.