複数のコレクションにわたって Atlas 検索クエリを実行する方法

1
using MongoDB.Bson;
2
using MongoDB.Bson.Serialization.Attributes;
3
using MongoDB.Bson.Serialization.Conventions;
4
using MongoDB.Driver;
5
using MongoDB.Driver.Core;
6
using MongoDB.Driver.Search;
7
8
public class LookupWithSearch{
9
10
  static void Main(string[] args) {// allow automapping of the camelCase database fields to our MovieDocument
11
    var camelCaseConvention = new ConventionPack { new CamelCaseElementNameConvention() };
12
    ConventionRegistry.Register("CamelCase", camelCaseConvention, type => true);
13
14
    // connect to your Atlas cluster
15
    var mongoClient = new MongoClient("<connection-string>");
16
    
17
    // define namespace
18
    var analyticsDatabase = mongoClient.GetDatabase("sample_analytics");
19
    var accountsCollection = analyticsDatabase.GetCollection<AccountDocument>("accounts");
20
    var customersCollection = analyticsDatabase.GetCollection<CustomerDocument>("customers");
21
22
    // define pipeline stages
23
    var lookupStage = new BsonDocument("$lookup", new BsonDocument{
24
      { "from", "accounts" }, { "localField", "accounts" }, { "foreignField", "account_id" }, 
25
      { "as", "purchases" }, { "pipeline", new BsonArray{
26
        new BsonDocument("$search", new BsonDocument{
27
          { "index", "lookup-with-search-tutorial" }, { "compound", new BsonDocument{
28
            { "must", new BsonArray{
29
              new BsonDocument("queryString", new BsonDocument{
30
                { "defaultPath", "products" }, { "query", "products: (CurrencyService AND InvestmentStock)" }
31
              })
32
            }}, 
33
            { "should", new BsonArray{
34
              new BsonDocument("range", new BsonDocument{
35
                { "path", "limit" }, { "gte", 5000 }, { "lte", 10000 }
36
              })
37
            }}
38
          }}
39
        })
40
      }}
41
    });
42
    var projectStage1 = new BsonDocument("$project", new BsonDocument("_id", 0));
43
    var limitStage = new BsonDocument("$limit", 5);
44
    var projectStage2 = new BsonDocument("$project", new BsonDocument{
45
      { "_id", 0 }, { "address", 0 }, { "birthdate", 0 }, { "username", 0 }, { "tier_and_details", 0 }
46
    });
47
    var aggregationPipeline = new List<BsonDocument> {lookupStage, projectStage1, limitStage, projectStage2};
48
49
    // run pipeline
50
    var results = customersCollection.Aggregate<BsonDocument>(aggregationPipeline).ToList();
51
52
    // print results
53
    foreach (var acct in results) {
54
        Console.WriteLine(acct.ToJson());
55
    }
56
  }            
57
}
58
59
// define fields in the accounts collection
60
[BsonIgnoreExtraElements]
61
public class AccountDocument {
62
    [BsonId]
63
    [BsonRepresentation(BsonType.ObjectId)]
64
    [BsonElement("_id")]
65
    public string Id { get; set; }
66
67
    [BsonElement("account_id")]
68
    public int AccountId { get; set; }
69
70
    [BsonElement("limit")]
71
    public int Limit { get; set; }
72
}
73
74
// define fields in the customers collection
75
[BsonIgnoreExtraElements]
76
public class CustomerDocument {
77
    [BsonId]
78
    [BsonRepresentation(BsonType.ObjectId)]
79
    [BsonElement("_id")]
80
    public ObjectId Id { get; set; }
81
82
    [BsonElement("name")]
83
    public string Name { get; set; }
84
85
    [BsonElement("email")]
86
    public string Email { get; set; }
87
88
    [BsonElement("active")]
89
    public bool Active { get; set; }
90
91
    [BsonElement("accounts")]
92
    public List<int> Accounts { get; set; }
93
}
94
95
// define new array field for matching documents
96
public class CustomerLookedUp: CustomerDocument{
97
    public List<CustomerDocument> Purchases { get; set; }
98
}

1
package main
2
3
import (
4
	"context"
5
	"fmt"
6
	"time"
7
8
	"go.mongodb.org/mongo-driver/v2/bson"
9
	"go.mongodb.org/mongo-driver/v2/mongo"
10
	"go.mongodb.org/mongo-driver/v2/mongo/options"
11
)
12
13
func main() {
14
	var err error
15
	// connect to the Atlas cluster
16
	ctx := context.Background()
17
	clOpts := options.Client().ApplyURI("<connection-string>").SetTimeout(5 * time.Second)
18
	client, err := mongo.Connect(clOpts)
19
	if err != nil {
20
		panic(err)
21
	}
22
	defer client.Disconnect(ctx)
23
	// set namespace
24
	collection := client.Database("sample_analytics").Collection("customers")
25
	// define pipeline
26
	lookupStage := bson.D{{Key: "$lookup", Value: bson.D{
27
		{Key: "from", Value: "accounts"},
28
		{Key: "localField", Value: "accounts"},
29
		{Key: "foreignField", Value: "account_id"},
30
		{Key: "as", Value: "purchases"},
31
		{Key: "pipeline", Value: bson.A{
32
			bson.D{
33
				{Key: "$search", Value: bson.D{
34
					{Key: "index", Value: "lookup-with-search-tutorial"},
35
					{Key: "compound", Value: bson.D{
36
						{Key: "must", Value: bson.A{
37
							bson.D{{Key: "queryString", Value: bson.D{
38
								{Key: "defaultPath", Value: "products"},
39
								{Key: "query", Value: "products: (CurrencyService AND InvestmentStock)"},
40
							}}},
41
						}},
42
						{Key: "should", Value: bson.A{
43
							bson.D{{Key: "range", Value: bson.D{
44
								{Key: "path", Value: "limit"},
45
								{Key: "gte", Value: 5000},
46
								{Key: "lte", Value: 10000},
47
							}}},
48
						}},
49
					}},
50
				}},
51
			},
52
			bson.D{{Key: "$project", Value: bson.D{
53
				{Key: "_id", Value: 0},
54
				{Key: "address", Value: 0},
55
				{Key: "birthdate", Value: 0},
56
				{Key: "username", Value: 0},
57
				{Key: "tier_and_details", Value: 0},
58
			}}},
59
		}},
60
	}}}
61
	limitStage := bson.D{{Key: "$limit", Value: 5}}
62
	projectStage := bson.D{{Key: "$project", Value: bson.D{
63
		{Key: "name", Value: 1},
64
		{Key: "email", Value: 1},
65
		{Key: "active", Value: 1},
66
		{Key: "accounts", Value: 1},
67
		{Key: "purchases", Value: 1},
68
	}}}
69
70
	// run pipeline
71
	cursor, err := collection.Aggregate(ctx, mongo.Pipeline{lookupStage, limitStage, projectStage})
72
	if err != nil {
73
		panic(err)
74
	}
75
	// print results
76
	var results []bson.D
77
	if err = cursor.All(context.TODO(), &results); err != nil {
78
		panic(err)
79
	}
80
	for _, result := range results {
81
		fmt.Println(result)
82
	}
83
}

1
import static com.mongodb.client.model.Aggregates.limit;
2
import static com.mongodb.client.model.Aggregates.project;
3
import static com.mongodb.client.model.Projections.*;
4
import java.util.Arrays;
5
import com.mongodb.client.MongoClient;
6
import com.mongodb.client.MongoClients;
7
import com.mongodb.client.MongoCollection;
8
import com.mongodb.client.MongoDatabase;
9
import org.bson.Document;
10
11
public class LookupWithSearchQuery {
12
13
  public static void main(String[] args) {
14
    // connect to your Atlas cluster
15
    String uri = "<connection-string>";
16
        
17
    try (MongoClient mongoClient = MongoClients.create(uri)) {
18
      // set namespace
19
      MongoDatabase database = mongoClient.getDatabase("sample_analytics");
20
      MongoCollection<Document> collection = database.getCollection("customers");
21
            
22
      // define pipeline
23
      Document agg = new Document("$lookup", 
24
        new Document("from", "accounts")
25
          .append("localField", "accounts")
26
          .append("foreignField", "account_id")
27
          .append("as", "purchases")
28
          .append("pipeline", Arrays.asList(new Document("$search", 
29
            new Document("index", "lookup-with-search-tutorial")
30
              .append("compound", 
31
              new Document("must", Arrays.asList(new Document("queryString", 
32
                new Document("defaultPath", "products")
33
                  .append("query", "products: (CurrencyService AND InvestmentStock)"))))
34
                  .append("should", Arrays.asList(new Document("range", 
35
                    new Document("path", "limit")
36
                      .append("gte", 5000L)
37
                      .append("lte", 10000L)
38
                  )))
39
              )
40
          ), 
41
          new Document("$limit", 5L),
42
          new Document("$project", 
43
          new Document("_id", 0L)
44
            .append("address", 0L)
45
            .append("birthdate", 0L)
46
            .append("username", 0L)
47
            .append("tier_and_details", 0L)
48
        )))
49
      );
50
      // run pipeline and print results
51
      collection.aggregate(Arrays.asList(agg,
52
        limit(5),
53
        project(fields(excludeId(), include("name", "email", "active", "accounts", "purchases")))
54
      ))
55
      .forEach(doc -> System.out.println(doc.toJson()));
56
    }
57
  }
58
}

1
import com.mongodb.client.model.Aggregates.limit
2
import com.mongodb.client.model.Aggregates.project
3
import com.mongodb.client.model.Projections.*
4
import com.mongodb.kotlin.client.coroutine.MongoClient
5
import kotlinx.coroutines.runBlocking
6
import org.bson.Document
7
import java.util.*
8
9
fun main() {
10
    // connect to your Atlas cluster
11
    val uri = "<connection-string>"
12
    val mongoClient = MongoClient.create(uri)
13
14
    // set namespace
15
    val database = mongoClient.getDatabase("sample_analytics")
16
    val collection = database.getCollection<Document>("customers")
17
18
    runBlocking {
19
        // define pipeline
20
        val agg = Document(
21
            "\$lookup",
22
            Document("from", "accounts")
23
                .append("localField", "accounts")
24
                .append("foreignField", "account_id")
25
                .append("as", "purchases")
26
                .append(
27
                    "pipeline", Arrays.asList(
28
                        Document(
29
                            "\$search",
30
                            Document("index", "lookup-with-search-tutorial")
31
                                .append(
32
                                    "compound",
33
                                    Document(
34
                                        "must", Arrays.asList(
35
                                            Document(
36
                                                "queryString",
37
                                                Document("defaultPath", "products")
38
                                                    .append("query", "products: (CurrencyService AND InvestmentStock)")
39
                                            )
40
                                        )
41
                                    )
42
                                        .append(
43
                                            "should", Arrays.asList(
44
                                                Document(
45
                                                    "range",
46
                                                    Document("path", "limit")
47
                                                        .append("gte", 5000)
48
                                                        .append("lte", 10000)
49
                                                )
50
                                            )
51
                                        )
52
                                )
53
                        ),
54
                        Document("\$limit", 5),
55
                        Document(
56
                            "\$project",
57
                            Document("_id", 0)
58
                                .append("address", 0)
59
                                .append("birthdate", 0)
60
                                .append("username", 0)
61
                                .append("tier_and_details", 0)
62
                        )
63
                    )
64
                )
65
        )
66
67
        // run pipeline and print results
68
        val resultsFlow = collection.aggregate<Document>(
69
            listOf(
70
                agg,
71
                limit(5),
72
                project(fields(excludeId(), include("name", "email", "active", "accounts", "purchases")))
73
            )
74
        )
75
        resultsFlow.collect { println(it) }
76
    }
77
    mongoClient.close()
78
}

1
const MongoClient = require("mongodb").MongoClient;
2
const assert = require("assert");
3
4
const agg = [
5
    {
6
      '$lookup': {
7
        'from': 'accounts', 
8
        'localField': 'accounts', 
9
        'foreignField': 'account_id', 
10
        'as': 'purchases', 
11
        'pipeline': [
12
          {
13
            '$search': {
14
              'index': 'lookup-with-search-tutorial', 
15
              'compound': {
16
                'must': [
17
                  {
18
                    'queryString': {
19
                      'defaultPath': 'products', 
20
                      'query': 'products: (CurrencyService AND InvestmentStock)'
21
                    }
22
                  }
23
                ], 
24
                'should': [
25
                  {
26
                    'range': {
27
                      'path': 'limit', 
28
                      'gte': 5000, 
29
                      'lte': 10000
30
                    }
31
                  }
32
                ]
33
              }
34
            }
35
          }, {
36
            '$project': {
37
              '_id': 0
38
            }
39
          }
40
        ]
41
      }
42
    }, {
43
      '$limit': 5
44
    }, {
45
      '$project': {
46
        '_id': 0, 
47
        'address': 0, 
48
        'birthdate': 0, 
49
        'username': 0, 
50
        'tier_and_details': 0
51
      }
52
    }
53
  ];
54
55
MongoClient.connect(
56
  "<connection-string>",
57
  { useNewUrlParser: true, useUnifiedTopology: true },
58
  async function (connectErr, client) {
59
    assert.equal(null, connectErr);
60
    const coll = client.db("sample_analytics").collection("customers");
61
    let cursor = await coll.aggregate(agg);
62
    await cursor.forEach((doc) => console.log(doc));
63
    client.close();
64
  }
65
);

1
import pymongo
2
3
# connect to your Atlas cluster
4
client = pymongo.MongoClient('<connection-string>')
5
6
# define pipeline
7
pipeline = [
8
  {
9
    '$lookup': {
10
      'from': 'accounts', 
11
      'localField': 'accounts', 
12
      'foreignField': 'account_id', 
13
      'as': 'purchases', 
14
      'pipeline': [
15
        {
16
          '$search': {
17
            'index': 'lookup-with-search-tutorial', 
18
            'compound': {
19
              'must': [
20
                {
21
                  'queryString': {
22
                    'defaultPath': 'products', 
23
                    'query': 'products: (CurrencyService AND InvestmentStock)'
24
                  }
25
                }
26
              ], 
27
              'should': [
28
                {
29
                  'range': {
30
                    'path': 'limit', 
31
                    'gte': 5000, 
32
                    'lte': 10000
33
                  }
34
                }
35
              ]
36
            }
37
          }
38
        }, 
39
        { '$project': { '_id': 0 }  }
40
      ]
41
    }
42
  }, 
43
  { '$limit': 5 }, 
44
  {
45
    '$project': {
46
      '_id': 0, 
47
      'address': 0, 
48
      'birthdate': 0, 
49
      'username': 0, 
50
      'tier_and_details': 0
51
    }
52
  }
53
]
54
55
# run pipeline
56
result = client['sample_analytics']['customers'].aggregate(pipeline)
57
58
# print results
59
for i in result:
60
    print(i)