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")

ruiquelhas · Accepted Answer · 2022-07-04T19:48:12.587

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

Thanks @ruiquelhas. Looks like I got the right person on it! However, it's not exactly what I'm asking. Imagine if the second identifier in my JSON had 'system2' instead of 'system 1'. Based on what you're telling me, if I query for a value of '1' and a system of 'system 2', I will return my document, despite the matching value being in the first identifier and the matching system being in the second identifier. My requirement is that I match both value and system on the same identifier. Does that make sense? — Beau Berger, Jul 01 '22 at 16:36
No, what I'm saying is that you operate at the document level, not at the field level. Thus, using `identifier[*]` always leads to losing the field context. Unless you iterate through the array fields and compare them individually, you won't be able to make that distinction. — ruiquelhas, Jul 01 '22 at 18:13
I gave this some thought and I think you will not be able to achieve that outcome with the CRUD API. However, I updated my answer with a potential workaround in SQL. Hope it helps. — ruiquelhas, Jul 04 '22 at 19:43

How can I query for multiple values after a wildcard?

1 Answers1