Search code examples
mongodbprestotrino

Querying an array of MongoDB Embedded/Nested Documents with Presto

Say I have the following documents:

db.inventory.insertMany( [
   { item: "journal", instock: [ { warehouse: "A", qty: 5 }, { warehouse: "C", qty: 15 } ] },
   { item: "notebook", instock: [ { warehouse: "C", qty: 5 } ] },
   { item: "paper", instock: [ { warehouse: "A", qty: 60 }, { warehouse: "B", qty: 15 } ] },
   { item: "planner", instock: [ { warehouse: "A", qty: 40 }, { warehouse: "B", qty: 5 } ] },
   { item: "postcard", instock: [ { warehouse: "B", qty: 15 }, { warehouse: "C", qty: 35 } ] }
]);

I want to query on an instock document based on both warehouse and qty. In mongo shell I would do:

db.inventory.find( { "instock": { $elemMatch: { qty: 5, warehouse: "A" } } } )

How would I do this in Presto?

Solution

  • instock will be mapped to array(row(warehouse varchar, qty double)) type and $elemMatch is equal to any_match array function in Trino (formerly Presto SQL).

    select * from inventory where any_match(instock, x -> x.qty = 5 and x.warehouse = 'A');
  item   |                      instock
---------+---------------------------------------------------
 journal | [{warehouse=A, qty=5.0}, {warehouse=C, qty=15.0}]