MarkLogic - CTS query to filter based on json path

Question

I have two JSON documents in a collection, as below

Doc 1
-----
"instance": {
    "PolicyInfo": [
       {
         "PolicyNumber": "P1-111", 
         "PolicyStatusCd": "Primary"
       }, 
       {
         "PolicyNumber": "P2-222", 
         "PolicyStatusCd": "Additional"
       }
    ],
    "ClaimInfo" : [
       {
         "PolicyNumber": "P3-333", 
         "PolicyStatusCd": "Additional"
       } 
    ]
  }

Doc 2
-----
"instance": {
    "PolicyInfo": [
       {
         "PolicyNumber": "P2-222", 
         "PolicyStatusCd": "Primary"
       }
    ],
    "ClaimInfo" : [
       {
         "PolicyNumber": "P1-111", 
         "PolicyStatusCd": "Primary"
       } 
    ]
  }

I have an input Policy number as P1-111 and need to return the document only if the PolicystatusCd under PolicyInfo for the policy is 'Primary'. So, I should return only Doc1 but not Doc2, because Doc2 has the policy P1-111 as primary in ClaimInfo but not PolicyInfo

I am trying to use cts.serch (preferably without creating additional indexes other than default universal indexes), but could not find a suitable solution.

Alternatively, I could use something like cts.propertyValueQuery to return both documents and use JavaScipt to filter out Doc2, but checking if I can do all that with MarkLogic functions itself.

Thanks in advance!

Answer 1

You can use cts.jsonPropertyScopeQuery for this purpose. You'd write your query something like this:

cts.jsonPropertyScopeQuery('PolicyInfo',
  cts.jsonPropertyValueQuery('PolicyNumber', 'P1-111')
)

HTH!