CODEWITHSUNDEEP
xquerymarklogic
Antony
Antony

Reputation: 976

How to using group by function in Xquery

I am using XQuery and I need to convert the (,) sepereated format or using group by in Xquery. 

Final Output
grp|A|#A
grp|A4|#A
grp|A2|#A
grp|A1|#A


Expected Output

grp|A,A4,A2,A1|#A

My Xquery:

let $root := 'grp'
let $uri := for $i in fn:collection('collection')
 return fn:document-uri($i)
let $result := for $each-uri in $uri

             let $title := (doc($each-uri)//grp/title/text())[1] 
             let $id  := (doc($each-uri)//grp/@id)[1]
             let $root :=  'grp'
             return fn:concat($root,'|',$id,'|',$title)

         return fn:distinct-values($result)

Upvotes: 1

Views: 847

Answers (1)

Ghislain Fourny
Ghislain Fourny

Reputation: 7279

I think only a group-by clause and a string-join call were missing.

let $root := 'grp'
let $uri := for $i in fn:collection('collection')
            return fn:document-uri($i)
let $result := for $each-uri in $uri
               let $title := (doc($each-uri)//grp/title/text())[1] 
               let $id  := (doc($each-uri)//grp/@id)[1]
               let $root :=  'grp'
               group by $root, $title
               return fn:concat($root,'|',string-join($id, ","),'|',$title)
return fn:distinct-values($result)

Although distinct-values may need to be moved to the IDs, and concat can be replaced with string-join as well, like so:

let $root := 'grp'
let $uri := for $i in fn:collection('collection')
            return fn:document-uri($i)
let $result := for $each-uri in $uri
               let $title := (doc($each-uri)//grp/title/text())[1] 
               let $id  := (doc($each-uri)//grp/@id)[1]
               let $root :=  'grp'
               group by $root, $title
               return string-join(
                 ($root, string-join(distinct-values($id), ","), $title),
                 '|'
               )
return $result

Upvotes: 1

Related Questions