How to Get a Listing or Count of Child Pages Under a Parent Page
Platform Notice: Data Center Only - This article only applies to Atlassian apps on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. 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
For auditing purposes or to organize content more effectively, Confluence administrators may find it useful to see the number and kind of pages nested under a given parent page. This can be helpful in examining a large space in order to determine which page trees can be moved to their own space.
Solution
The following are two options that can help you identify pages with large child page trees.
Per Page ID
1. Get a page’s Page ID:
Via the UI: click on '…' > Page Information to get the "pageId" value from the resulting URL address bar.
Via the database, execute the following query:
SELECT contentid FROM CONTENT WHERE prevver IS NULL AND title = 'Your Page Title';2. Run the queries below, depending on what you want to do:
To get a listing of all pages (pageId and title) under the parent:
SELECT c.contentid, c.title FROM CONFANCESTORS a JOIN CONTENT c ON a.descendentid = c.contentid WHERE a.ancestorid = <parent_page_id>;To get a total count of pages under the parent page:
SELECT COUNT(*) FROM CONFANCESTORS WHERE ancestorid = <parent_page_id>;Per Space Key ID
Get the space key via the UI by either clicking on the space and checking for the key in the URL path or clicking on Space tools > Overview and then locating the key on the resulting page.
Next, enter the space key (or list of space keys) in query below and execute against the database:
SELECT COUNT(a.ancestorid), a.ancestorid as contentid, s.spacekey, (SELECT c2.title FROM content c2 WHERE contentid = a.ancestorid) AS PageTitle FROM CONFANCESTORS a JOIN CONTENT c ON a.descendentid = c.contentid JOIN spaces s on s.spaceid=c.spaceid WHERE a.ancestorid in (SELECT distinct(c1.parentid) FROM content c1 WHERE parentid in (SELECT c1.contentid FROM CONTENT c1)) and c.contenttype='PAGE' and c.content_status='current' and c.prevver is null and s.spacekey IN ('enter-the-space-key') GROUP BY (a.ancestorid,s.spaceid) ORDER BY COUNT(a.ancestorid) DESC;Alternatively, if you only want to see those page that exceed a certain count, something like the following can be used where you can modify the HAVING COUNT(*) clause to return page trees over a certain size:
SELECT COUNT(a.ancestorid), a.ancestorid as contentid, s.spacekey, (SELECT c2.title FROM content c2 WHERE contentid = a.ancestorid) AS PageTitle FROM CONFANCESTORS a JOIN CONTENT c ON a.descendentid = c.contentid JOIN spaces s on s.spaceid=c.spaceid WHERE a.ancestorid in (SELECT distinct(c1.parentid) FROM content c1 WHERE parentid in (SELECT c1.contentid FROM CONTENT c1)) and c.contenttype='PAGE' and c.content_status='current' and c.prevver is null and s.spacekey in ('enter-the-space-key') GROUP BY (a.ancestorid,s.spaceid) HAVING COUNT(*) > 100 ORDER BY COUNT(a.ancestorid) DESC;Please note that these queries were written for Postgres. For other DBMSs, the queries may need to be modified.
Related pages
Was this helpful?