CODEWITHSUNDEEP
marklogic
Fan Li
Fan Li

Reputation: 1077

"Scoped" Range Query in MarkLogic?

Given the following document structure, I need to compose a query to find, for example, all accounts with > 500 USD.

{
  "account": 1, 
  "assets": [
    {
      "currency": "USD", 
      "amount": 600
    }, 
    {
      "currency": "CAD", 
      "amount": 1000
    }, 
    {
      "currency": "EUR", 
      "amount": 200
    }
  ]
}

Using Xpath predicate, it would be something like:

fn:collection()[//(currency='USD' and amount>500)]

However I am having trouble to create a cts query for the same purpose, leveraging range index. Naively I can create a separate path range index for each currency type but that seems to be too much.

Any suggestions? Thank you!

Upvotes: 1

Views: 82

Answers (2)

ehennum
ehennum

Reputation: 7335

Fiona's solution works when eliminating the false positives by running a filtered search or by indexing positions.

An alternative would be to model the data in a different way:

{
  "account": 1, 
  "assetsByCurrency": {
    "USD": {"amount": 600}, 
    "CAD": {"amount": 1000}, 
    "EUR": {"amount": 200}
  }
}

With that model, it should be possible to eliminate false positives without filtering or positions:

cts.search(cts.jsonPropertyScopeQuery('assetsByCurrency', 
       cts.jsonPropertyScopeQuery('USD',
                     cts.jsonPropertyRangeQuery('amount', '>', 599.99)
                     ))
      ))

Another possibility would be to index the assets objects with TDE and retrieve the data using the Optic API, which allows comparison filters on any column and eliminates false positives.

Hoping that helps,

Upvotes: 1

Fiona Chen
Fiona Chen

Reputation: 1368

cts.search(cts.jsonPropertyScopeQuery('assets', 
           cts.andQuery([cts.jsonPropertyValueQuery('currency', 'USD'),
                         cts.jsonPropertyRangeQuery('amount', '>', 599.99)
                         ])
          ))

Upvotes: 3

Related Questions