How can I query for multiple values after a wildcard?

Question

I have a json object like so:

{ 
   _id: "12345",
   identifier: [
      { 
         value: "1",
         system: "system1",
         text: "text!"
      },
      { 
         value: "2",
         system: "system1"
      }
   ]
}

How can I use the XDevAPI SearchConditionStr to look for the specific combination of value + system in the identifier array? Something like this, but this doesn't seem to work...

collection.find("'${identifier.value}' IN identifier[*].value && '${identifier.system} IN identifier[*].system")

Answer 1

By using the IN operator, what happens underneath the covers is basically a call to JSON_CONTAINS().

So, if you call:

collection.find(":v IN identifier[*].value && :s IN identifier[*].system")
  .bind('v', '1')
  .bind('s', 'system1')
  .execute()

What gets executed, in the end, is (simplified):

JSON_CONTAINS('["1", "2"]', '"2"') AND JSON_CONTAINS('["system1", "system1"]', '"system1"')

In this case, both those conditions are true, and the document will be returned.

The atomic unit is the document (not a slice of that document). So, in your case, regardless of the value of value and/or system, you are still looking for the same document (the one whose _id is '12345'). Using such a statement, the document is either returned if all search values are part of it, and it is not returned if one is not.

For instance, the following would not yield any results:

collection.find(":v IN identifier[*].value && :s IN identifier[*].system")
  .bind('v', '1')
  .bind('s', 'system2')
  .execute()

EDIT: Potential workaround

I don't think using the CRUD API will allow to perform this kind of "cherry-picking", but you can always use SQL. In that case, one strategy that comes to mind is to use JSON_SEARCH() for retrieving an array of paths corresponding to each value in the scope of identifier[*].value and identifier[*].system i.e. the array indexes and use JSON_OVERLAPS() to ensure they are equal.

session.sql(`select * from collection WHERE json_overlaps(json_search(json_extract(doc, '$.identifier[*].value'), 'all', ?), json_search(json_extract(doc, '$.identifier[*].system'), 'all', ?))`)
  .bind('2', 'system1')
  .execute()

In this case, the result set will only include documents where the identifier array contains at least one JSON object element where value is equal to '2' and system is equal to system1. The filter is effectively applied over individual array items and not in aggregate, like on a basic IN operation.

Disclaimer: I'm the lead developer of the MySQL X DevAPI Connector for Node.js