slack-logs-to-pg.md

Slack logs to Postgres

Export the Slack team's public logs. You'll get a directory with a users.json file and a directory for each channel e.g. wheelhouse/.

Use jq to pull the JSON objects out of the root array:

cat users.json | jq -c '.[]'

Use sed to kinda sorta CSV escape the JSON objects:

... | sed 's/"/""/g' | sed 's/^/"/' | sed 's/$/"/'

Use psql to insert these into a table:

... | psql CONN_STR -c '
  drop table if exists users;
  create table users (json_data jsonb);
  copy users from stdin with (format csv);
  '

All together now:

cat users.json |
  jq -c '.[]' |
  sed 's/"/""/g' | sed 's/^/"/' | sed 's/$/"/' |
  psql CONN_STR -c '
    drop table if exists users;
    create table users (json_data jsonb);
    copy users from stdin with (format csv);
    '

Or for all the messages in a channel:

cat wheelhouse/* |
  jq -c '.[]' |
  sed 's/"/""/g' | sed 's/^/"/' | sed 's/$/"/' |
  psql CONN_STR -c '
    drop table if exists wheelhouse;
    create table wheelhouse (json_data jsonb);
    copy wheelhouse from stdin with (format csv);
    '