anuj_gupta
anuj_gupta

Reputation: 141

In XQuery Marklogic how to sort dynamically?

In XQuery Marklogic how to sort dynamically?

let $sortelement := 'Salary'
for $doc in collection('employee')
order by $doc/$sortelement
return $doc

PS: Sorting will change based on user input, like data, name in place of salary.

Upvotes: 3

Views: 167

Answers (2)

DALDEI
DALDEI

Reputation: 3732

Not recommended because the chances of introducing security issues, runtime crashes and just 'bad results' is much higher and more difficult to control -- BUT available as a last resort.

ALL XQuery can be dynamically created as a string then evaluated using xdmp:eval Much better to follow the guidance of Mads, and use the search apis instead of xquery FLOWR expressions -- note that these APIs actually 'compile down' to a data structure. This is what the 'cts constructors' do : https://docs.marklogic.com/cts/constructors

I find it helps to think of cts searches as a structured search described by data -- which the cts:xxx are simply helper functions to create the data structure. (they dont actually do any searching, they build up a data structure that is used to do the searching)

If you look at the source to the search:xxx apis you can see how this is done.

Upvotes: 0

Mads Hansen
Mads Hansen

Reputation: 66851

If Salary is the name of the element, then you could more generically select any element in the XPath with * and then apply a predicate filter to test whether the local-name() matches the variable for the selected element value $sortelement:

let $sortelement := 'Salary'
for $doc in collection('employee')
order by $doc/*[local-name() eq $sortelement]
return $doc

This manner of sorting all items in the collection may work with smaller number of documents, but if you are working with hundreds of thousands or millions of documents, you may find that pulling back all docs is either slow or blows out the Expanded Tree Cache.

A more efficient solution would be to create range indexes on the elements that you intend to sort on, and could then perform a search with options specified to order the results by cts:index-order with an appropriate reference to the indexed item, such as cts:element-reference(), cts:json-property-reference(), cts:field-reference().

For example:

let $sortelement := 'Salary'
return 
  cts:search(doc(), 
    cts:collection-query("employee"), 
    cts:index-order(cts:element-reference(xs:QName($sortelement)))
  )

Upvotes: 2

Related Questions