Non Null values in Marklogic - Search JSON documents with attributes in array with not null values in Marklogic

Question

Structure of JSON documents required to be searched in Marklogic

{  
   "MESSAGEID":"18878285",
   "ORDERNUMBER":["2295796"],
   "CATEGORY":"F3702200000"
}

I wanted to search the URIs of all JSON documents in Marklogic that comprised of not null ORDERNUMBER using Javascript

I am using the following query but it is still showing the URIs for the documents comprising of "ORDERNUMBER":[]

cts.uris("",null,cts.andQuery
    ([
        cts.jsonPropertyValueQuery("ORDERNUMBER", "*", "wildcarded"),
        cts.notQuery(cts.jsonPropertyValueQuery("ORDERNUMBER",""))
    ])
);

score 4 · Answer 1 · answered Feb 06 '19 at 12:51

You normally have a few options, but the empty array case is particularly hard to distinguish. This is because it is neither an empty string value, nor a null, yet the property is truly present.

cts.jsonPropertyScopeQuery("ORDERNUMBER", cts.trueQuery()) will match any doc that has that property.

cts.jsonPropertyValueQuery("ORDERNUMBER", "") matches ORDERNUMBER: "" and ORDERNUMBER: [""].

cts.jsonPropertyValueQuery("ORDERNUMBER", null) matches ORDERNUMBER: null.

cts.jsonPropertyValueQuery("ORDERNUMBER", "?*", "wildcarded") matches ORDERNUMBER: null (not sure why), ORDERNUMBER: "xx", and ORDERNUMBER: ["xx"], but only if you enable filtering (which requires cts.search), or if you are able to find the appropriate wildcard settings.

To be honest, the simplest solution to my opinion is to just put a range index on ORDERNUMBER, and use a rangeQuery:

cts.rangeQuery(cts.pathReference('ORDERNUMBER'), '>', '')

HTH!

Non Null values in Marklogic - Search JSON documents with attributes in array with not null values in Marklogic

1 Answers1