How to identify duplicate records in MarkLogic

user_17596734 0
Viewed 76

I want to identify the documents having duplicate values in MarkLogic. I have an xml like below and BillNo is the primary key for a document, I need to find out is there any documents is having duplicate BillNo ie. Occurrence of the same BillNo multiple times across multiple documents.

<BillData>
  <Date>20211112</Date>
  <BillNo>123</BillNo>
  <Description>ABC</Description>
</BillData>

I am using XQuery for coding

marklogic xquery
1 Answers

If you have a range index on the BillNo, then you could use cts:values() get a distinct list of BillingNo values and filter for the ones that appear more than once using cts:frequency():

for $value in cts:values(cts:element-reference(xs:QName("BillNo")))
where cts:frequency($value) gt 1
return $value

If you wanted to list the URIs of the documents that have the same BillingNo, then you could use cts:element-value-co-occurrences for BillingNo and xdmp:document. If you use the map option to return the results as a map with the BillingNo as the key and the document URI(s) as the value(s), you could remove the entries that have only one corresponding URI, and then would have a map that lists the BillingNo used in multiple documents and their URIs.

let $co-occurrences := cts:element-value-co-occurrences(xs:QName("BillNo"), xs:QName("xdmp:document"), "map")
let $_remove_entries_with_only_one_uri :=
  for $key in map:keys($co-occurrences)
  where map:get($co-occurrences, $key) => tail()
  return map:put($co-occurrences, $key, ())
return
  $co-occurrences

If you don't have an index, then you might be able to do something like this, but run the risk of Expanded Tree Cache errors:

let $dups := map:new()
let $_ :=
  for $value in /BillData/BillNo/string()
  return map:put($dups, $value, head((map:get($dups, $value), 0)) + 1)
let $values-by-count := -$dups  
let $_remove_entries_with_only_one_uri:= map:put($values-by-count, "1", ())
return 
(: flip the map back to values as keys, get BillingNo that have occurred multiple times :)
  map:keys(-$values-by-count)
Mads_Hansen_144190
Related