MarkLogic - Search within each occurrence of an array

1
votes

MarkLogic version : 9.0-6.2

I have an array in a json document as follows. My need is to return this document only if the email is "[email protected]" and EmailOverrideInd is "N" for that particular email.

"Contacts": [
  {
    "FirstName": "FTest1", 
    "LastName": "LTest1", 
    "Email": "[email protected]", 
    "EmailOverrideInd": "Y"
  },
  {
    "FirstName": "Ftest2", 
    "LastName": "Ltest2", 
    "Email": "[email protected]", 
    "EmailOverrideInd": "N"
  }
]

In the example given above, the query should not return the document as the EmailOverrideInd is "N" for email [email protected]

With the regular cts.jsonPropertyValueQuery and the cts.andQuery, I am still getting the document because my search is not limiting the scope to each array occurrence.

cts.search(
  cts.andQuery(
    [
      cts.collectionQuery('testcol'),
      cts.jsonPropertyValueQuery('Email', EmailAddr, ['exact']), 
      cts.jsonPropertyValueQuery('EmailOverrideInd', 'N', ['exact'])
    ]
  ),
  ['unfiltered','score-zero']
)

How can I limit my search to each array occurrence?

2
marklogicmarklogic-9
Targeting anonymous object is always tricky. Would it be possible to restructure your JSON to something like Contacts: [{ Contact: { FirstName: ...,... } }, { Contact: ... } ]. It would greatly simplify your query, as it would allow using jsonPropertyScopeQuery on Contact.. – grtjn

2 Answers

2
votes

If you can count on the structure looking like your example, you could use a cts.nearQuery. 

let emailAddr = "[email protected]";

cts.search(
  cts.andQuery(
    [
      cts.collectionQuery('testcol'),
      cts.nearQuery(
        [
          cts.jsonPropertyValueQuery('Email', EmailAddr, ['exact']), 
          cts.jsonPropertyValueQuery('EmailOverrideInd', 'N', ['exact'])
        ],
        1,
        'ordered'
      ),
    ]
  ),
  ['unfiltered', 'score-zero']
)

For this to run unfiltered successfully, you'll need the "word positions" index turned on.

The 1 parameter for cts.nearQuery means that the two propertyQuery values need to occur within one word of each other. Note that I used the 'ordered' option. That might not be necessary in this case, but I've found it helpful sometimes when I know the order of the data structure.

Caveat: I get how word counting works in XML documents, but haven't really played with that much in JSON. It's possible you'll need to adjust the count, but I think 1 is correct here.

1
votes

One alternative to Dave's good suggestion would be to create a TDE index that projects the array items as rows.

Specify the fragment id when opening the view, constrain using your current query, filter on the columns to the emails of interest, and then join the documents if the other document information is needed (otherwise, just use the rows).

A sketch of the general shape of the code:

const docId = op.fragmentIdCol('docId');

const results = op.fromView(yourEmailsSchema, yourEmailsView, '', docId)
  .where(... your existing cts.query to narrow the candidates ...)
  .where(... boolean expression against the columns to get the exact ...)
  .joinDoc('doc', docId)
  .select('doc')
  .result();

See also:

http://docs.marklogic.com/guide/app-dev/TDE

and

http://docs.marklogic.com/op.fromView

Hoping that helps,