How to retrieve the Comments in Pages and Blog posts via SQL
Platform Notice: Data Center and Cloud By Request - This article was written for the Atlassian data center platform but may also be useful for Atlassian Cloud customers. If completing instructions in this article would help you, please contact Atlassian Support and mention it.
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
Confluence considers Comment on pages and blog posts as content and it holds the metadata of the comments in the CONTENT table and the data itself in the BODYCONTENT table.
Solution
If you want to retrieve all the comments in your Confluence instance, you can use the following SELECT statement:
SELECT *
FROM BODYCONTENT
WHERE CONTENTID IN (
SELECT CONTENTID
FROM CONTENT
WHERE CONTENTTYPE = 'COMMENT')To fetch the comments of an individual page, you can use the following statement (replacing the <pageID> tags):
SELECT *
FROM CONTENT C
INNER JOIN BODYCONTENT B ON C.CONTENTID = B.CONTENTID
WHERE
C.CONTENTTYPE='COMMENT'
AND PREVVER is null
AND CONTENT_STATUS = 'current' and pageid=<pageID>