2

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.

Mads Hansen
  • 63,927
  • 12
  • 112
  • 147
anuj_gupta
  • 131
  • 4

2 Answers2

2

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)))
  )
Mads Hansen
  • 63,927
  • 12
  • 112
  • 147
  • 1st did help, but somehow 2nd option throws an error – anuj_gupta Jul 01 '22 at 05:54
  • XDMP-ARGTYPE: (err:XPTY0004) cts:and-query((cts:collection-query("test"), cts:index-order(cts:element-reference(xs:QName("r:id"),("type=string","collation=http://marklogic.com/collation/")), "ascending"))) -- arg1 is not of type cts:query* – anuj_gupta Jul 01 '22 at 06:06
  • The cts:index-order() is a third parameter to cts:search(). Do not add it inside of an and-query() with other query criteria in the second param. – Mads Hansen Jul 01 '22 at 12:23
  • let $sortelement := 'Salary' for $doc in collection('employee') order by $doc/*[local-name() eq $sortelement] return $doc – anuj_gupta Jul 07 '22 at 06:24
  • cts:uris((),(), cts:collection-query("employee"), cts:index-order(cts:element-reference(xs:QName($sortelement))) ) Can we use this?? – anuj_gupta Jul 20 '22 at 08:45
  • No, cts:uris() doesn't have the same parameters. The 4th parameter of cts:uris() is quality-weight, not an order operation. – Mads Hansen Jul 20 '22 at 11:26
  • If you just want the URI from the sorted search results, you could use `cts:search(doc(), cts:collection-query("employee"), cts:index-order(cts:element-reference(xs:QName($sortelement))) )/base-uri()` – Mads Hansen Jul 20 '22 at 11:29
  • Thanks, can we use path range index as well in this query? – anuj_gupta Aug 11 '22 at 06:40
  • Yes, you can change the parameter for `cts:index-order()` to specify the appropriate cts:*-reference. For a path-range index, https://docs.marklogic.com/cts:path-reference – Mads Hansen Aug 11 '22 at 16:13
0

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.

DALDEI
  • 3,722
  • 13
  • 9