Export forum to json

Tips & Tricks - Public
1

Here’s a rather silly trick for getting a nice json object from a discourse forum. I don’t know if it will be useful to anyone else, but it took ages to write and produces nice results, so thought it worth sharing. It relies on postgresql-specific json functions.

select 
  jsonb_agg(root) as data 
from 
  (
    select 
      categories.name, 
      categories.slug, 
      categories.id, 
      topics 
    from 
      (
        select 
          category_id, 
          jsonb_agg(category_data) as topics 
        from 
          (
            select 
              category_id, 
              topics.id, 
              slug, 
              title, 
              posts 
            from 
              (
                select 
                  topic_id, 
                  jsonb_agg(posts_subquery) as posts 
                from 
                  (
                    select 
                      topic_id, 
                      name, 
                      username, 
                      post_number, 
                      posts.created_at, 
                      posts.updated_at, 
                      posts.deleted_at, 
                      like_count, 
                      raw as content 
                    from 
                      posts 
                      join users on user_id = users.id
                  ) posts_subquery 
                  join topics on topic_id = topics.id 
                group by 
                  topic_id
              ) topic_data 
              join topics on topics.id = topic_data.topic_id
          ) category_data 
        group by 
          category_data.category_id
      ) outer_data 
      join categories on outer_data.category_id = categories.id
  ) root;

My use case was to get a nice export of forum posts, without resorting to web scraping or the painful json api.

3 Likes