Select query with named slot lookups?

Question

Given this Dataset fragment (copy-paste friendly version at bottom)

Can this query be simplified by referencing named slots directly in the Select logic?

data[All, Values /* Thread][All, All, Apply@Join][All, Select[#VisitStartTime <= #"WEBCAM_TIME" <= #VisitEndTime &]]

Although functional, limitations include: (1) Creates intermediate Datasets; (2) threads VisitStartTime and VisitEndTime, which must then be dropped; (3) Values removes the Key "Morae/..." which must then be reinstered in each row; (4) overall complexity makes it difficult to modify to similar datasets.

An approach based on named slot syntax shown here by WReach looks applicable, but the naive version fails:

data[All, {"Morae/Manual/Emr/Coder1Data" -> 
   Select[#["Master", "VisitStartTime"] <=  #"WEBCAM_TIME" <= #[
       "Master", "VisitEndTime"] &]}]

Even though it is possible to reference the slots in the other branch like this:

data[All, {"Morae/Manual/Emr/Coder1Data" -> {#["Master", 
      "VisitStartTime"], #["Master", "VisitEndTime"]} &}]

Is it possible to combine these path-lookup methods?

INPUT DATA

data = <|"Q001" -> <|"Morae/Manual/Emr/Coder1Data" -> {<|"WEBCAM_TIME" -> 
       TimeObject[{0, 12, 9.4}], 
      "TASK" -> "F1"|>, <|"WEBCAM_TIME" -> TimeObject[{0, 12, 10.1}], 
      "TASK" -> "F1"|>, <|"WEBCAM_TIME" -> TimeObject[{0, 12, 13.1}], 
      "TASK" -> "F1"|>, <|"WEBCAM_TIME" -> TimeObject[{0, 12, 27.9}], 
      "TASK" -> "F1"|>}, 
   "Master" -> <|"VisitStartTime" -> TimeObject[{0, 12, 15.}], 
     "VisitEndTime" -> TimeObject[{0, 38, 49.}]|>|>, 
 "Q002" -> <|"Morae/Manual/Emr/Coder1Data" -> {<|"WEBCAM_TIME" -> 
       TimeObject[{0, 0, 1.9}], 
      "TASK" -> "F25"|>, <|"WEBCAM_TIME" -> TimeObject[{0, 0, 3.5}], 
      "TASK" -> "F25"|>, <|"WEBCAM_TIME" -> TimeObject[{0, 0, 9.7}], 
      "TASK" -> "F25"|>, <|"WEBCAM_TIME" -> TimeObject[{0, 0, 10.8}], 
      "TASK" -> "F25"|>}, 
   "Master" -> <|"VisitStartTime" -> TimeObject[{0, 13, 0.}], 
     "VisitEndTime" -> TimeObject[{0, 22, 27.}]|>|>|> //Dataset

Answer 1

There is a complication that prevents us from using a single Select and named slots: we need to form a cross-product of the data and master elements. Even so, the query can be simplified somewhat:

data[All
, Join @@@ Tuples[{#"Morae/Manual/Emr/Coder1Data", {#Master}}] & /*
    Select[#VisitStartTime <= #"WEBCAM_TIME" <= #VisitEndTime &]
]

The Join@@@Tuples construction forms the cross-product and collapses each pair of associations into a single association. That leaves us in a position to apply the Select.

---

Side note: A limitation in JoinAcross

One might think to use JoinAcross to form the cross-product. Unfortunately, it does not handle a vacuous join condition properly:

JoinAcross[{<|a->1|>, <|a->2|>}, {<|b->10|>}, {}]
(* {} *)

Since JoinAcross is advertised as the Mathematica equivalent of a relational join, I would have expected the result to be the same as this:

Join @@@ Tuples[{{<|a -> 1|>, <|a -> 2|>}, {<|b -> 10|>}}]
(* {<|a -> 1, b -> 10|>, <|a -> 2, b -> 10|>} *)

But it is not. I am sorely tempted to call this a bug, but let's go with "limitation" for now :)

---

Update

If the master data is not required in the output (as mentioned in a comment), things can made simpler because we can eliminate the explicit cross-product. We start by defining a helper function that encapsulates the filtering condition:

filter[data_, master_] :=
  Select[data, master["VisitStartTime"] <= #"WEBCAM_TIME" <= master["VisitEndTime"]&]

This enables us to use a much simpler query:

data[All
, <| "Morae/Manual/Emr/Coder1Data" -> (filter[#"Morae/Manual/Emr/Coder1Data", #Master]&) |>
]

If desired, the master data can also be retained as a separate key like this:

data[All
, <| "Morae/Manual/Emr/Coder1Data" -> (filter[#"Morae/Manual/Emr/Coder1Data", #Master]&)
   , "Master" -> "Master"
  |>
]