how to get blogs information via sql queries?

[Lakshman]

Hi all,

I need to collect the blog information on Database level. Can you please someone tell me the table name, where blogs content stored in a Confluence.

Thanks,

Lakshmana

[Zak Laughton]

Hi Lakshman,

You'll find the primary key on the CONTENT table with CONTENTTYPE='BLOGPOST', and the content of the blog will be on the BODYCONTENT table. You'll also want the space name to distinguish blogs with the same title. This should do it:

SELECT
  sp.spacename,
  con.title,
  bcon.body
FROM bodycontent bcon
JOIN content con ON con.contentid = bcon.contentid
JOIN spaces sp ON sp.spaceid = con.spaceid
WHERE con.contenttype = 'BLOGPOST';

NOTE: This doesn't take previous versions into account, and will likely return several results for blog posts that have been edited. To avoid this, you may also want to combine with filters listed on How to find the number of pages, blogposts, and attachments in a space.

I hope this helps!
– Zak

[Nic Brough -Adaptavist-]

The blogs are held in bodycontent, but that's just the raw text content and formatting.  For the metadata for the blog, you're going to have to go off to read many other tables.

This is not "fun" - your SQL is going to be slow, painful to build and maintain, and you're going to be reading 20-30 tables in quite a convoluted way.  You will be far better off using the REST API to get the information.

[Gonchik Tsymzhitov]

I hope without joining with content table you can't fetch data :) or fetch not relevant

[Gonchik Tsymzhitov]

Hi! 

Please, have a look that data model page

https://confluence.atlassian.com/doc/confluence-data-model-127369837.html

also, check content table

Cheers,

Gonchik Tsymzhitov