“This is the third day of my participation in the First Challenge 2022. For details: First Challenge 2022”

1, the preface

In the last share, I introduced the SpringBoot project integration mongo, this time combined with specific needs for you to share the complex query in Mongo.

2. MongoDB complex queries

First, the data structure is as follows. You can see that each article/video and user form a document. Then, the specific user behaviors, such as reading/watching, liking, and disliking, are included in the Array UserBehaviorItems. Now, the number of videos played per day is the number of statistics. In a relational database, SQL is easy to write: group the user’s actions by the time they were recorded, then filter out the video playback behavior and count(). However, since we need to store grouped data in an array for each document, processing with the Mongo shell is a bit cumbersome. Now expand to say, according to the data in the sub-document group query specific processing.

3. Group query according to sub-document data in Mongo

First, we’re going to “expand” the data in the subdocument into multiple pieces of data, each of which contains a value from the array, and we’re going to need to do this: $unwind.

Such as:

{" _id ": 1, the" item ":" ABC ", a list: [" 1 ", "2", "3"]} : db. The test. The aggregate ([{$unwind: "$list"}]) are: {" _id ": 1, "item" : "ABC", "list" : "1" } { "_id" : 1, "item" : "ABC", "list" : "2" } { "_id" : 1, "item" : "ABC", "list" : "3"}Copy the code

Once you’ve expanded the data, you can group it. In particular, the concept of pipes in Mongo. Pipeline representation: consists of one or more stages that process the document, in this case the $unwind operations. The processing result of the previous pipe will be passed to the next pipe for processing. Then, we run into a tricky problem here. We want to group the user by the date on which the action is recorded, but the date is recorded in the form of a timestamp. And the timestamp is passed from the APP side, the ios side is passed from the 10 bit timestamp, the Android side is passed from the 13 bit timestamp, which is very painful. Here I used two pipes to deal with, first judge the number of timestamp bits, if it is 10 bits then multiply it by 1000, adjust it to 13 bits and then convert it to the date, and then conduct group sorting operation. Finally, the implementation command is as follows:

Db.getcollection ('user_behavior'). Aggregate ([{# Behavior_object_Type ": {$ne: 1},}}, {$unwind: "$UserBehaviorItems"}, {$unwind: "$UserBehaviorItems"}, {$unwind: "$UserBehaviorItems"}, {$project: {resDate: {$cond: {if: {$gte: [{$strLenCP: {$toString: "$UserBehaviorItems.start_time"}}, 12 ] }, then: {$multiply:["$UserBehaviorItems.start_time", 1]}, else: $multiply:["$userbehaviorItems. start_time", 1000]}}}}}, {# format the 13-bit timestamp bit YYYY-MM-DD form $project: {convertedDate: {$dateToString: {format: "% % Y - m - % d", the date: {$toDate: "$resDate"}}}}}, {# grouping by date $group: {_id: $sort: {"_id": -1}}]); $sort: {"_id": -1}}]);Copy the code

Execution Result:

Well, that’s it for this issue. Next time, I’ll introduce you to the development of timed tasks using Quartz. If you need to communicate, please feel free to send me a message. 😊