Query a parent child relationship by objectids

💬 Queries and Resources
1

Hi,
i try to build a UI where i can visualize parentChild relation of my data.
i have problems to build the query to filter the child-data by objectId

==my data==

  • datasource is a mongodb (free atlas cluster)
  • i am using MongoDB Extended JSON (v2) - Database Manual - MongoDB Docs
  • there are two mongodb-collections in my mondodb
  • a parentCollection
  • a childCollection
  • a document in parentCollection has a property "child_ids" which stores objectids from documents in childCollection

==Behavior==
for query-testing purpose i hardcoded the objectids in my query as follows:
{"_id": {$in: [{$oid: "687a69d478b4bc14f555f596"}, {$oid:"687a69d478b4bc14f555f597"}] } }
this works as expected.

But when i use instead the array of object-ids from the selectedRow-Property of the table-component as follows:
{"_id": {$in: {{ myTable.selectedRow.child_ids}} } }

I get:
"No result returned"
"Query ran and did not return any results."

In the attached screenhot the naming of the properties is a bit different:
table-component => listProcesses
child_ids == subprocess_ids

image
image391×224 12.1 KB

image

It should be simple .. but I do not get it :frowning:
Thanks for any advice
Gordian

1 Like
2

here is a screenshot of the query:

image
image552×1052 37.9 KB

3

here is a screenhsot with hardcoded objectids which leads to the expected result.

image
image565×810 35.5 KB

4

Hi @Gordian, and welcome to the forum!

Not familiar at all with mongoDB, but this issue is similar also with postgres. I found the below conversation, which is the inverse of what you're trying to achieve.

Which makes me think that you may need to stringify the variable, e.g. {{ JSON.stringify( listProcesses.selectedRow.subprocess_ids ) }} ?

However, this will end up being

{"_id": {$in: [{$oid: "687a69d478b4bc14f555f596","687a69d478b4bc14f555f597"}] } }

rather than

{"_id": {$in: [{$oid: "687a69d478b4bc14f555f596"}, {$oid:"687a69d478b4bc14f555f597"}] } }

So I think the solution may be the below:

{
  "_id": {
    "$in": {{ listProcesses.selectedRow.subprocess_ids.map(id => ({ $oid: id })) }}
  }
}
1 Like
5

Hi @Gordian, just checking in on this thread.

I tend to agree with @MiguelOrtiz - when you are accessing listProcesses.selectedRow.subprocess_ids you are getting an array of ids as strings. Compared to your hardcoded example which had an array of objects, each with a key of $oid, these aren't going to be read the same in MongoDB. In order to match the format of your hardcoded example, it makes sense to iterate over the array and map those ids into objects with a key of $oid.

Let us know if Miguel's solution worked for you!

1 Like