MongoDB自交查询 or MongoDB Aggregation Self Join

[lanlin]

场景

有一个 demo 集合的关系表，形如

// demo collection
{_id: 1, userid: "A", owner_id: "B", owner_type: "user" }
{_id: 1, userid: "B", owner_id: "A", owner_type: "user" }
{_id: 1, userid: "C", owner_id: "B", owner_type: "user" }
{_id: 1, userid: "D", owner_id: "A", owner_type: "user" }
{_id: 1, userid: "C", owner_id: "D", owner_type: "user" }

要求统计出 A,C,D 用户分别与 B 用户产生交集的用户个数。

实现

// $match 支持 $expr 表达式，可用来处理包含自定义变量的查询
$match =
[
  '$expr' =>
  [
    '$and' =>
    [
      ['$in' => ['$owner_id', ['A', 'C', 'D']]],
      ['$eq' => ['$userid', '$$demouid']]
    ]
  ]
];

// $lookup 支持 let 自定义变量，以及 pipeline 管道查询
$lookup =
[
  'from' => 'demo',
  'let'  => ['demouid' => '$userid'],
  'as'   => 'demo_tmp',
  'pipeline' =>
  [
    ['$match'   => $match],
    ['$project' => ['owner_id' => '$owner_id', 'userid' => '$userid']],
  ]
];

// 最终 aggregation 的情形
$aggregate =
[
  ['$match'   => ['owner_id' => 'B', 'owner_type' => 'user']],
  ['$lookup'  => $lookup],
  ['$project' => ['_id' => false, 'demo_tmp' => true]],
  ['$unwind'  => '$demo_tmp'],
  ['$group'   => ['_id' => '$demo_tmp.owner_id', 'count' => ['$sum' => 1]]]
];

// demo lookup demo 自交查询
db.demo.aggregate($aggregate);

后记

经常忘记 MongoDB aggregation 管道的各个层（stage）的用法， 写个复杂一点的栗子🌰log 一下。