some of the operations like $elemMatch

[anandpatil]

I am working in migrating from mongo to oracle SODA, some of the operations that are supported in mongo are not available in Oracle ,so struggling to find a workaround. I tried $exists for $elematch but the query did not return similar result.
Please suggest if there is fix for this or any other strategy
A sample query from mongo
{ "$and" : [{ "$and" : [{ "$and" : [{ "$or" : [{ "somefields" : { "$all" : [{ "$elemMatch" : { "rawid" : "57ff7d764343rerer333", "value" : { "$ne" : "676dfeererefdfdfd" } } }, { "$elemMatch" : { "rawid" : "5e01e27c61450e2eab223a4a", "macrovalue" : { "$regex" : "^xyz$", "$options" : "i" } } }] } }] }, { "isdeleted" : false }] }, { "someflowid" : "57f782c4346eerer343" }] }, { "someinstanceid" : { "$in" : [] } }] }

Thanks,Anand

[morgiyan]

Could you please give me an example of a query (just in English) you're trying to write and a JSON document or two that would be expected to match this query (ideally as short as possible for both the query and the documents). I can then suggest how to write it in SODA QBEs.

In general, are you trying to enforce multiple conditions on a particular array element? If so, you'd use a "nested condition", which has the form:

{"somePath[*]" : { nested condition here }}

somePath[*] drills down to the array elements under some path, and then the nested condition is evaluated on each one of these array elements.

For example, say you have these two documents:

{"wine" : [ {"country" : "france", "type" : "merlot"}]}
{"wine" : [ {"country" : "italy", "type" : "merlot"}, {"country" : "france", "type" : "riesling"} ] }

Then the QBE {"wine[*]" : {"country" : "france", "type" : "merlot"}} will select only the first document (and not the second). This query is returning documents that have both the country set to france and the type set to merlot, within the same array element.

Let me know if that's what you're looking for, or if you're looking for something else.

[anandpatil]

Hi,
We are migrating the application from mongo to Oracle, the complex queries that are working with Mongo(production) & need to get the same results with Oracle.
Mongo provides a way to check if the elements exists with And/Or condition https://docs.mongodb.com/manual/reference/operator/query/elemMatch/ , we are considering to rewrite the queries but it may require more efforts.
I will try to find a simple example to explain the requirement.

Thanks
Anand