Grouping and counting in Xquery

Question

Hear is XML. I am trying to get Number of titles published by an author in a date range 15/02/2012 to 24/02/2012 order by highest to lowest (number of titles).

<entries>
<entry>
    <id>1</id>
    <published>23/02/2012</published>
    <title>Title 1</title>
    <content type="html">This is title one</content>
    <author>
        <name>Pankaj</name>
    </author>
</entry>
<entry>
    <id>2</id>
    <published>22/02/2012</published>
    <title>Title 2</title>
    <content type="html">This is title two</content>
    <author>
        <name>Pankaj</name>
    </author>
</entry>
<entry>
    <id>3</id>
    <published>21/02/2012</published>
    <title>Title 3</title>
    <content type="html">This is title three</content>
    <author>
        <name>Rob</name>
    </author>
</entry>
<entry>
    <id>4</id>
    <published>20/02/2012</published>
    <title>Title 4</title>
    <content type="html">This is title four</content>
    <author>
        <name>Bob</name>
    </author>
</entry>
<entry>
    <id>5</id>
    <published>19/02/2012</published>
    <title>Title 1</title>
    <content type="html">This is title five</content>
    <author>
        <name>Pankaj</name>
    </author>
</entry>

I am trying to get output from xquery:

<?xml version="1.0" encoding="UTF-8"?>
<results>
<result>
    <author>
        <name>Pankaj</name>
    </author>
    <numberOfTitles>3</numberOfTitles>
</result>
<result>
    <author>
        <name>Rob</name>
    </author>
    <numberOfTitles>1</numberOfTitles>
</result>
<result>
    <author>
        <name>Bob</name>
    </author>
    <numberOfTitles>1</numberOfTitles>
</result>

Please help me..

It may depend on XQuery version you use. What XQuery processor/database is supposed to run that query? — Shcheklein, Feb 22 '12 at 19:06
Im using oxygen (Saxon-PE Xquery9.2.0.6) for developement. Finally i have to run this query through XCC api on Marklogic. — Pankaj, Feb 22 '12 at 19:22

Leo Wörteler · Answer 1 · 2012-02-23T00:20:17.413

Here's my go at a solution:

<results>{
  for $entry in //entry
  let $date := xs:date(string-join(reverse(tokenize($entry/published, '/')), '-')),
      $author := $entry/author/string()
  where xs:date('2012-02-15') le $date and $date le xs:date('2012-02-24')
  group by $author
  order by count($entry) descending
  return <result>{
    <author>
      <name>{$author}</name>
    </author>,
    <numberOfTitles>{count($entry)}</numberOfTitles>
  }</result>
}</results>

When executed with BaseX, it yields the correct result.

It uses XQuery 3.0 features like group by, otherwise it would be more complicated. I don't know if MarkLogic supports that.

+1 you can try the solution live at http://www.zorba-xquery.com/html/demo#1zS5NQ3DYQ1USnlxWN8c9+67KJA= — wcandillon, Feb 23 '12 at 09:08

Dimitre Novatchev · Accepted Answer · 2012-02-22T20:26:00.230

This XQuery 1.0 solution is executable by any compliant XQuery 1.0 processor:

Note: No group by and no distinct-values() are used.

<results> 
 {
 let $entries := 
    /*/entry
           [for $d in 
                    xs:date(string-join(reverse(tokenize(published, '/')), '-'))
                return
                   xs:date('2012-02-15') le $d and $d le xs:date('2012-02-24')
             ],

  $vals := $entries/author/name
      return
         for $a in  $vals[index-of($vals, .)[1]],
                $cnt in count(index-of($vals, $a)) 
           order by $cnt descending
             return
              <result>
                <author>
                  {$a}
                 </author>
                 <numberOfTitles>
                   {count(index-of($vals, $a))}
                 </numberOfTitles>
              </result>
    }
</results>

when applied on the provided XML document:

<entries>
    <entry>
        <id>1</id>
        <published>23/02/2012</published>
        <title>Title 1</title>
        <content type="html">This is title one</content>
        <author>
            <name>Pankaj</name>
        </author>
    </entry>
    <entry>
        <id>2</id>
        <published>22/02/2012</published>
        <title>Title 2</title>
        <content type="html">This is title two</content>
        <author>
            <name>Pankaj</name>
        </author>
    </entry>
    <entry>
        <id>3</id>
        <published>21/02/2012</published>
        <title>Title 3</title>
        <content type="html">This is title three</content>
        <author>
            <name>Rob</name>
        </author>
    </entry>
    <entry>
        <id>4</id>
        <published>20/02/2012</published>
        <title>Title 4</title>
        <content type="html">This is title four</content>
        <author>
            <name>Bob</name>
        </author>
    </entry>
    <entry>
        <id>5</id>
        <published>19/02/2012</published>
        <title>Title 1</title>
        <content type="html">This is title five</content>
        <author>
            <name>Pankaj</name>
        </author>
    </entry>
</entries>

produces the wanted, correct result:

<?xml version="1.0" encoding="UTF-8"?>
<results>
   <result>
      <author>
         <name>Pankaj</name>
      </author>
      <numberOfTitles>3</numberOfTitles>
   </result>
   <result>
      <author>
         <name>Rob</name>
      </author>
      <numberOfTitles>1</numberOfTitles>
   </result>
   <result>
      <author>
         <name>Bob</name>
      </author>
      <numberOfTitles>1</numberOfTitles>
   </result>
</results>

score 4 · Answer 3 · answered Feb 22 '12 at 23:22

Here is a solution specific to MarkLogic, using maps to implement grouping efficiently. The input XML has been declared as $INPUT, but you could replace that with a call to doc() or any other accessor.

I also explored this topic in a blog post last year: http://blakeley.com/blogofile/archives/560/

element results {
  let $m := map:map()
  let $start := xs:date('2012-02-15')
  let $stop := xs:date('2012-02-24')
  let $group :=
    for $entry in $INPUT/entry
    let $key := $entry/author/name/string()
    let $date := xs:date(xdmp:parse-yymmdd("dd/MM/yyyy", $entry/published))
    where $date ge $start and $date le $stop
    return map:put($m, $key, 1 + (map:get($m, $key), 0)[1])
  for $key in map:keys($m)
  let $count := map:get($m, $key)
  order by $count
  return element result {
    element author { element name { $key }},
    element numberOfTitles { $count } } }

score 2 · Answer 4 · answered Feb 22 '12 at 19:26

The following should work in most processors. There are likely more efficient queries in MarkLogic you could make, but this will get you started.

let $doc := <entries>
<entry>
    <id>1</id>
    <published>23/02/2012</published>
    <title>Title 1</title>
    <content type="html">This is title one</content>
    <author>
        <name>Pankaj</name>
    </author>
</entry>
<entry>
    <id>2</id>
    <published>22/02/2012</published>
    <title>Title 2</title>
    <content type="html">This is title two</content>
    <author>
        <name>Pankaj</name>
    </author>
</entry>
<entry>
    <id>3</id>
    <published>21/02/2012</published>
    <title>Title 3</title>
    <content type="html">This is title three</content>
    <author>
        <name>Rob</name>
    </author>
</entry>
<entry>
    <id>4</id>
    <published>20/02/2012</published>
    <title>Title 4</title>
    <content type="html">This is title four</content>
    <author>
        <name>Bob</name>
    </author>
</entry>
<entry>
    <id>5</id>
    <published>19/02/2012</published>
    <title>Title 1</title>
    <content type="html">This is title five</content>
    <author>
        <name>Pankaj</name>
    </author>
</entry>
</entries>

return
 <results>
    {
        for $author in distinct-values($doc/entry/author/name/string())
        return
        <result><author>
            <name>{$author}</name>
            <numberOfTitles>{count($doc/entry[author/name/string() eq $author])} </numberOfTitles>
        </author></result>
    }
 </results>

You could add a date constraint to the predicate on entries like $doc/entry[author/name/string() eq $author and XXXX ] ; replace XXX with logic that parses the date format you have and does the requisite comparisons. — Eric Bloch, Feb 22 '12 at 19:31
Nah, I was lazy, but I'd do it by doing something similar to your answer. Adding another bit to the predicate to filter to the date range and then adding an order by count($doc/entry[author/name/string() eq $author]) to sort. — Eric Bloch, Feb 22 '12 at 19:55

Daniel Haley · Answer 5 · 2012-02-22T20:09:02.903

Here's another solution that is similar to Leo Wörteler:

declare function local:FormatDate($origDate as xs:string) as xs:date 
  {
      xs:date(string-join(reverse(tokenize($origDate, '/')), '-'))
  };

<results>
  {
  for $author in distinct-values(/entries/entry/author/name)
  let $startDate := xs:date('2012-02-15')
  let $endDate := xs:date('2012-02-24')
  order by count(/entries/entry[author/name=$author][$startDate <= local:FormatDate(published) and local:FormatDate(published) <= $endDate]) descending
  return
    <result>
      <author>
        <name>{$author}</name>
      </author>
      <numberOfTitles>{count(/entries/entry[author/name=$author][$startDate <= local:FormatDate(published) and local:FormatDate(published) <= $endDate])}</numberOfTitles>
    </result>
  }
</results>

Grouping and counting in Xquery

5 Answers5