How we can get all MarkLogic databases document counts in single XQuery Code?

Question

We need to generate a report using XQuery where we need details like database sizes and database document counts(Total documents value).

We had the below XQuery code from which we can get the database names and database size, but we also want to include database document counts which are coming in AdminUI.

for $db-id in xdmp:databases()
let $db-name := xdmp:database-name($db-id)
let $db-size :=
  fn:sum(
    for $f-id in xdmp:database-forests($db-id)
    let $f-status := xdmp:forest-status($f-id)
    let $space := $f-status/forest:device-space
    let $f-name := $f-status/forest:forest-name
    let $f-size :=
      fn:sum(
        for $stand in $f-status/forest:stands/forest:stand
        let $stand-size := $stand/forest:disk-size/fn:data(.)
        return $space
      )
    return $f-size
  )
order by $db-size descending
return $db-name || " = " || $db-size

something like

return $db-name || " = " || $db-size || "=" || $db-count

Using below, we can get document count in a single database(whatever is selected in QC dropdown) but I need to run the below command for all databases in a single script.

xdmp:estimate(doc())

Any help or suggestions on this? Thanks for the help in advance.

Answer 1

You can retrieve the document-count with xdmp:forest-counts() and add them up, like you have done for size:

declare namespace forest = "http://marklogic.com/xdmp/status/forest";
for $db-id in xdmp:databases()
let $db-name := xdmp:database-name($db-id)
let $db-size :=
  fn:sum(
    for $f-id in xdmp:database-forests($db-id)
    let $f-status := xdmp:forest-status($f-id)
    let $space := $f-status/forest:device-space
    let $f-name := $f-status/forest:forest-name
    let $f-size :=
      fn:sum(
        for $stand in $f-status/forest:stands/forest:stand
        let $stand-size := $stand/forest:disk-size/fn:data(.)
        return $space
      )
    return $f-size
  )
let $db-count := 
  fn:sum(
    for $forest-id in xdmp:database-forests($db-id)
    let $forest-counts := xdmp:forest-counts($forest-id)
    return $forest-counts/forest:document-count/fn:data(.)
  )
order by $db-size descending
return $db-name || " = " || $db-size || ", " || $db-count

If you did want to use xdmp:estimate(doc()) then you could use xdmp:invoke-function() and specify the content database to execute against in the options:

declare namespace forest = "http://marklogic.com/xdmp/status/forest";
for $db-id in xdmp:databases()
let $db-name := xdmp:database-name($db-id)
let $db-size :=
  fn:sum(
    for $f-id in xdmp:database-forests($db-id)
    let $f-status := xdmp:forest-status($f-id)
    let $space := $f-status/forest:device-space
    let $f-name := $f-status/forest:forest-name
    let $f-size :=
      fn:sum(
        for $stand in $f-status/forest:stands/forest:stand
        let $stand-size := $stand/forest:disk-size/fn:data(.)
        return $space
      )
    return $f-size
  )
let $db-count := 
  xdmp:invoke-function(
    function(){ xdmp:estimate(doc())}, 
    <options xmlns="xdmp:eval">
      <database>{$db-id}</database>
    </options>)
order by $db-size descending
return $db-name || " = " || $db-size || ", " || $db-count