1

I am trying to get count of a record for a given condition.

Count the number of Ancillary Price in all documents in a collection where the Ancillary Price is greater than 1000.

I am trying the below XQuery for that, it gives me this below response:

Returned sequence of 532 items in 9007.0781 ms. (-361.0691 ms. compared to previous run)

And along with it prints value of 1 in each row for all 532 rows.

However my expected result is to get the count as result: 532 in my result page.

Can you please help to identify the XQuery issue here?

xquery version "1.0-ml";
for $x in collection("GTM2_Shipment")
where ($x/*:Shipment/*:Ancillary/*:QuotePrice/text() > 1000) 
return (count($x))
Mads Hansen
  • 63,927
  • 12
  • 112
  • 147
Rashmita Purkayastha
  • 457
  • 1
  • 8

1 Answers1

1

The structure is slightly incorrect.

Ref: https://www.w3schools.com/xml/xquery_flwor.asp

Try:

xquery version "1.0-ml";
fn:count(
   for $x in collection("GTM2_Shipment")
   where ($x/*:Shipment/*:Ancillary/*:QuotePrice/text() > 1000) 
   return $x
)

Or simply:

xquery version "1.0-ml";
fn:count(collection("GTM2_Shipment")[./*:Shipment/*:Ancillary/*:QuotePrice/text() > 1000])

However, eventually these will not scale. Model your data where each shipment is in it's own document and then you can use an estimate such as:

xquery version "1.0-ml";
xdmp:estimate(cts:search(fn:count(collection("GTM2_Shipment"), {a range query to your quotePrice }))
David Ennis -CleverLlamas.com
  • 7,560
  • 12
  • 20
  • Thanks David it works as expected. Can we make a make a Template using this xquery . As I want to put this in a template with different conditions like this above one and put the count names as column names and the values in the column value. – Rashmita Purkayastha Jun 27 '22 at 10:32
  • NO Problem. I will not clutter the answer above with turning the above into what you want because it answers a specific question and keeps StackOverflow clean and useful. However, this should get you started: https://docs.marklogic.com/guide/app-dev/import_modules understand the difference between a main module and function module. You want a function module for what you describe – David Ennis -CleverLlamas.com Jun 27 '22 at 11:54