CODEWITHSUNDEEP
xquerymarklogic
Prashant Chauhan
Prashant Chauhan

Reputation: 11

How to sort string date in marklogic?

we have to sort year and month combination in marklogic Ex. "2018 April", "2018 Dec", "2018 Feb", "2018 Nov"

its sorting according to alphabetic order, but i want to sort on the basis of month and year.

Upvotes: 1

Views: 528

Answers (2)

grtjn
grtjn

Reputation: 20414

You'll have to normalize the values, that for sure. You could normalize to:

  • xs:date (as Martin suggested as well)
  • or xs:gYearMonth

You could do so either on ingest, or at runtime.

Doing so on ingest allows leveraging MarkLogic range indexes to support the sorting, using for instance cts:index-order in combination with cts:search.

At runtime you could cast to xs:date or xs:gYearMonth too (after normalizing on the fly), but you can also just order on the normalized strings directly, without casting. Sorting and normalizing at runtime will perform worse though, and not scale well.

Regarding normalizing itself, you can use the string manipulation described by Martin, but you can also make use of the MarkLogic function xdmp:parse-dateTime, for instance something like this:

xs:gYearMonth(xdmp:parse-dateTime("[Y] [Mn]", "2018 Jan"))

It takes additional parameters to indicate language and such too.

HTH!

Upvotes: 1

Martin Honnen
Martin Honnen

Reputation: 167591

You can certainly try to convert the format to an xs:date and use order by e.g.

let $seq := ("2018 Apr", "2018 Dec", "2018 Feb", "2018 Nov"),
  $month-order := ('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec')
for $date in $seq
order by xs:date(
          substring($date, 1, 4) 
          || '-' 
          ||format-number(index-of($month-order, substring($date, 6, 3)), '00') 
          || '-01')
return $date

gives

"2018 Feb"
"2018 Apr"
"2018 Nov"
"2018 Dec"

Note that I simplified the task by assuming a consistent three letter month name. And I am not familiar with the particular XQuery processor Marklogic you use whether it has some custom date parsing functions that make the task easier, I simply used XQuery 3.1 functions and expressions. Working demo at https://xqueryfiddle.liberty-development.net/948Fn59

Upvotes: 0

Related Questions