Using SQL to access key in a nested MongoDB object with a variable array name

[amesery]

Hey Retool community,

I'm attempting to access a key in a nested array from MongoDB, normally easy peasy. I've pulled data using an aggregate query and am further cleaning that data using Query JSON with SQL.

While I would normally access it via SELECT blah.object_name -> array_name -> key FROM. {{mongoQuery.data}} blah, this particular object has a user defined array_name.

How might I go about accessing ...price -> value without specifying misc_adj -> Beer... or misc_adj -> Wine.... The format of the misc_adj object is always either empty or as shown in the image.

In this case, "Wine" and "Beer" should really be values to a "category" key, buts thats just not how whomever designed this database set it up.

Thanks in advance for any input!

[joeBumbaca]

Hey amesery, in this case with the data being structured in a less consistent way you may want to use a JS query to first return the keys of the misc_adj object that point to the arrays. You could then iterate through those keys and trigger your Query JSON with SQL query once for each key with it passed in as additional scope.
https://docs.retool.com/docs/scripting-retool#triggering-a-query