JoinAcross on nested association by nested key

I would like to join two associations on nested keys. Is this possible?

first = {<|"away" -> <|"name" -> "bob", "money" -> 10 |>, "home" -> <|"name" -> "Sue", "money" -> 15 |>|>, <|"away" -> <|"name" -> "Joe", "money" -> 20 |>, "home" -> <|"name" -> "Jane", "money" -> 25 |>|>}

second = {<|"away" -> <|"name" -> "bob", "Sex" -> "male" |>,  "home" -> <|"name" -> "Sue", "sex" -> "female"|>|>, <|"away" -> <|"name" -> "Joe", "Sex" -> "Male" |>, "home" -> <|"name" -> "Jane", "Sex" -> "Female" |>|>}

result=JoinAcross[first, second, ?? ?]

What I would like is:

result = {<|"away" -> <|"name" -> "bob", "money" -> 10, "Sex" -> "male"|>, "home" -> <|"name" -> "Sue", "money" -> 15, "sex" -> "female"|>|>, <|"away" -> <|"name" -> "Joe", "money" -> 20, "Sex" -> "Male"|>, "home" -> <|"name" -> "Jane", "money" -> 25, "Sex" -> "Female"|>|>}

It does not work on just the "away" key due to the "away" key in first and second not being exactly the same and JoinAcross[first,second,"Outer"] just yields a Join.

Is it possible to join across a nested key?

I have tried

JoinAcross[first, second, "away"]

and several variations with zero success.

Thank you.

Answer

JoinAcross cannot reference subparts. The desired result can be obtained using the following expression:

GatherBy[Join[first, second], #[[All, "name"]] &] // Map[Merge[Association]]


% === result
(* True *)

This is basically equivalent to using JoinAcross with full-outer-join semantics.

Explanation

The ultimate goal is to relationally join associations by a composite key consisting of the away and home name values. That is, for each association a we wish to use the key a[[All, "name"]], e.g.:

First[first][[All, "name"]]

(* <| "away" -> "bob", "home" -> "Sue" |>

We could been more explicit about the outer key names by using {"away", "home"} instead of All, but we will stick with the shorter form for now.

Unfortunately, JoinAcross only permits Key syntax, and Key does not presently support subpart references. We have little option but to implement the join directly ourselves.

In order to merge related associations from the two lists first and second, we need to gather those associations together using the desired composite key:

GatherBy[Join[first, second], #[[All, "name"]] &]

(*
{ { <| "away" -> <|"name"->"bob","money"->10|>
     , "home" -> <|"name"->"Sue","money"->15|>
     |>
  , <| "away" -> <|"name"->"bob","Sex"->"male"|>

     , "home" -> <|"name"->"Sue","sex"->"female"|>
     |>
  }
, { <| "away" -> <|"name"->"Joe","money"->20|>
     , "home" -> <|"name"->"Jane","money"->25|>
     |>
  , <| "away" -> <|"name"->"Joe","Sex"->"Male"|>
     , "home" -> <|"name"->"Jane","Sex"->"Female"|>
     |>
  }

}
*)

The result is a list of sublists, each sublist containing a two-level nested association from first and another such nested assocation from second. We wish to produce a single merged association from each sublist:

Assocation will bring together two associations, discarding duplicate keys from the second:

Association[{<|"name"->"bob","money"->10|>, <|"name"->"bob","Sex"->"male"|>}]

(* <| "name"->"bob", "money"->10, "Sex"->"male" |> *)

Merge will bring together two associations, but instead of discarding values duplicate keys will use a function to compute a merged value. Thus, we can use Merge in conjunction with Association from above to merge our away/home pairs:

{ <| "away" -> <|"name"->"bob","money"->10|>
   , "home" -> <|"name"->"Sue","money"->15|>
   |>
, <| "away" -> <|"name"->"bob","Sex"->"male"|>
   , "home" -> <|"name"->"Sue","sex"->"female"|>
   |>
} // Merge[Association]

(*
<| "away" -> <|"name"->"bob","money"->10,"Sex"->"male"|>

 , "home"-> <|"name"->"Sue","money"->15,"sex"->"female"|>
 |>
*)

We Map to perform this merge upon all sublists. Putting it all together, we obtain our final expression:

GatherBy[Join[first, second], #[[All, "name"]] &] // Map[Merge[Association]]

This is essentially equivalent to JoinAcross with full-outer-join semantics. If inner-join semantics are required, we need to add a stage that filters out unmatched elements:

GatherBy[Join[first, second], #[[All, "name"]] &] //
  Select[Length@# > 1 &] //

  Map[Merge[Association]]

Left- or right-outer-join semantics would require more elaborate filtering and is left as an exercise for the reader.