Json property container query on array of object is not working as expected

Question

{
   "name": "Bill Gates",
   "Jobs":[
           {"isPrimary": true, "jobTitle":"software engineer"}, 
           {"isPrimary": false, "jobTitle":"senior software engineer"}
         ]
}

On execution of the below query, I am getting the above document as the search result as both of the criteria will be true because it is there in either one of the object in 'Jobs' array. But in my case, I need that in result if both of the criteria is satisfied in a single object of 'Jobs' array.

queryBuilder.containerQuery(
          queryBuilder.jsonProperty("Jobs"),
          queryBuilder.and(
              queryBuilder.value(                  
                  queryBuilder.jsonProperty("jobTitle"),
                  "senior software engineer"),
              queryBuilder.value(
                  queryBuilder.jsonProperty("isPrimary"),
                  true)));

Can anyone please help on this?

Answer 1

Your structure does not support what you are trying to do. jobs property contains an array. The scope at that point is a list. Some of these search functions stem from XML structure which would have looked like this:

  <jobs>
    <job>
      <isPrimary>true</isPrimary>
      <jobTitle>llama herder</jobTitle>
    </job>
    <job>
      <isPrimary>false</isPrimary>
      <jobTitle>llama stylist</jobTitle>
    </job>
  </jobs>

In XML, there would have been a need for a containing element per child of jobs.

If you model the JSON to follow that pattern, then you can get things to work.

"Jobs":[
   {job:{"isPrimary": true, "jobTitle":"software engineer"}},
   {job:{"isPrimary": false, "jobTitle":"senior software engineer"}}
]

Example: (Note: I do examples in the underlying languages and not the builders so that they are more transportable. The annotation for QueryBuilder is different, but close enough to see the point)

  let jsonObject = {
     "name": "Bill Gates",
     "Jobs":[
             {job:{"isPrimary": true, "jobTitle":"software engineer"}},
             {job:{"isPrimary": false, "jobTitle":"senior software engineer"}}
           ]
  }

  var q1 = cts.jsonPropertyScopeQuery("job", cts.andQuery([cts.jsonPropertyValueQuery("jobTitle", "senior software engineer"), cts.jsonPropertyValueQuery("isPrimary", true)]))
  var q2 = cts.jsonPropertyScopeQuery("job", cts.andQuery([cts.jsonPropertyValueQuery("jobTitle", "senior software engineer"), cts.jsonPropertyValueQuery("isPrimary", false)]))

  var result = [cts.contains(jsonObject, q1), cts.contains(jsonObject, q2)]
  result

result: [false, true]

Rule of thumb for MarkLogic for me is to always have the pattern of named objects with the wrapper pattern. I even to this on the root level.

If, however, you are not able to change your data, then I urge you to embrace TDE templates for this purpose and query via Optic. With that approach, you are able to project your data at rest into a model that matches your business use-case. Then you could query against each projected record.

name	primary	title
Billy	true	software engineer
Billy	false	senior software engineer