CODEWITHSUNDEEP
xquerymarklogic
Rashmita Purkayastha
Rashmita Purkayastha

Reputation: 449

How to get count of a certain record with a specific value in MarkLogic xquery

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))

Upvotes: 1

Views: 174

Answers (1)

David Ennis -CleverLlamas.com
David Ennis -CleverLlamas.com

Reputation: 7770

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 }))

Upvotes: 1

Related Questions