MarkLogic search query multiple duplicates, grab latest results based off timestamp field

Question

I've got a dataset that looks like this:

    {
     "id": 1234,
     "name": "name-1",
     "timestamp: "2022-02-10T13:48:32.195344Z",
    }

There are several thousand documents with unique id's in the database I'm trying to grab. However the issue is the "name" property can change with each id, so there can be multiple documents that have the same id but different name.

For my result set I'm trying to return a full list of all the documents of each unique id-name pair, but only one document for each pair that is the document with the latest timestamp.

For example with four documents with the id of "1234" and "name-2", I'd only want to return one of those documents that has the latest timestamp.

There are millions of documents in this dataset, so performance is a concern here as well.

Is there any way to build this query with cts functions or similar?

score 0 · Answer 1 · answered May 20 '22 at 18:49

If you are able to process all of the documents in one module execution, then you could do something like this, to use a map with a composite key using the id and name as the key, and then test whether there is a timestamp and if greater or less than the current docs timestamp:

let $map := map:new()
let $_ :=
  for $doc in cts:search(doc(), $query)
  let $id := $doc/id
  let $name := $doc/name
  let $timestamp := $doc/timestamp/xs:dateTime(.)
  let $key := $id || $name
  let $old_value := map:get($map, $key)/timestamp/xs:dateTime(.)
  where not(exists($old_value) and $old_value gt $timestamp)
  return map:put($map, $key, $doc)
return 
  for $key in map:keys($map)
  return map:get($map, $key)

However, that might be slow and runs the risk of blowing the Expanded Tree Cache. If you can index those fields, then you could use cts:value-tuples() with similar logic:

let $map := map:new()
let $_ :=
  for $tuple in cts:value-tuples(
    ( 
      cts:element-reference(xs:QName("id")),
      cts:element-reference(xs:QName("name")),
      cts:element-reference(xs:QName("timestamp")),
      cts:uri-reference()
    ),
    (), 
    $query
  )
  let $id := $tuple[1]
  let $name := $tuple[2]
  let $timestamp := $tuple[3]
  let $key := $id || $name
  let $old_value := map:get($map, $key)[3]
  where not(exists($old_value) and $old_value gt $timestamp)
  return map:put($map, $key, $tuple)
return 
  for $key in map:keys($map)
  return (: this returns the URI, you could also wrap with doc() to fetch them :)
    map:get($map, $key)[4]

MarkLogic search query multiple duplicates, grab latest results based off timestamp field

1 Answers1