MongoDB查找和合并嵌套数组

goqiplq2  于 2023-10-16  发布在  Go
关注(0)|答案(1)|浏览(142)

我在很长一段时间后开始使用MongoDB,但我无法编写代码来执行以下操作。
这里我想做的是从suspectslawyers文档中执行查找,并将它们与suspects字段中的字段合并。
现在,我只执行了一个查找操作,但无法将它与现有对象合并结合起来。

  1. [
  2. {
  3. $unwind: "$suspects",
  4. },
  5. {
  6. $lookup: {
  7. from: "suspects",
  8. let: {
  9. suspectObjId: {
  10. $toObjectId: "$suspects.id",
  11. },
  12. },
  13. pipeline: [
  14. {
  15. $match: {
  16. $expr: {
  17. $eq: ["$_id", "$$suspectObjId"],
  18. },
  19. },
  20. },
  21. ],
  22. as: "suspects",
  23. },
  24. }
  25. ]

可疑文件:

  1. [
  2. {
  3. "_id": ObjectId("64f8d65249432809cdc3cb37"),
  4. "fullName": "John Doe -1",
  5. "phoneNumber": "XXXXXXXXX",
  6. "totalCriminalRecord": 3
  7. },
  8. {
  9. "_id": ObjectId("64f8d6cd49432809cdc3cb38"),
  10. "fullName": "John Doe -2",
  11. "phoneNumber": "XXXXXXXXX",
  12. "totalCriminalRecord": 5
  13. }
  14. ],

律师文件:

  1. [
  2. {
  3. "_id": ObjectId("64f8d58849432809cdc3cb34"),
  4. "fullName": "John Doe -3",
  5. "phoneNumber": "XXXXXXXXX",
  6. "workAddress": "Address of .."
  7. },
  8. {
  9. "_id": ObjectId("64f8d5f349432809cdc3cb35"),
  10. "fullName": "John Doe -4",
  11. "phoneNumber": "XXXXXXXXX",
  12. "workAddress": "Address of .."
  13. }
  14. ],

案例文档:

  1. [
  2. {
  3. "_id": ObjectId("64f8d9f549432809cdc3cb3d"),
  4. "crimeNumber": 12345,
  5. "eventDate": "01/01/2023",
  6. "suspects": [
  7. {
  8. "id": "64f8d65249432809cdc3cb37",
  9. "note": "-- additional note --",
  10. "lawyer": {
  11. "id": "64f8d58849432809cdc3cb34",
  12. "note": "--additional note --"
  13. }
  14. }
  15. ]
  16. }
  17. ]

我希望它的结果如下:

  1. {
  2. "_id": ObjectId("64f8d9f549432809cdc3cb3d"),
  3. "crimeNumber": 12345,
  4. "eventDate": "01/01/2023",
  5. "suspects": [
  6. {
  7. "id": "64f8d65249432809cdc3cb37",
  8. "fullName": "John Doe -1",
  9. "phoneNumber": "XXXXXXXXX",
  10. "totalCriminalRecord": 3,
  11. "note": "-- additional note --",
  12. "lawyer": {
  13. "id": "64f8d58849432809cdc3cb34",
  14. "fullName": "John Doe -3",
  15. "phoneNumber": "XXXXXXXXX",
  16. "workAddress": "Address of ..",
  17. "note": "--additional note --"
  18. }
  19. }
  20. ]
  21. }

MongoPlayground

l2osamch

l2osamch1#

你的方向是对的。只需执行$lookup来获取您需要的所有内容,然后使用$mergeObjects来处理它们。

  1. db.cases.aggregate([
  2. {
  3. "$unwind": "$suspects"
  4. },
  5. {
  6. "$lookup": {
  7. "from": "suspects",
  8. "let": {
  9. "suspectsIds": {
  10. "$toObjectId": "$suspects.id"
  11. }
  12. },
  13. "pipeline": [
  14. {
  15. "$match": {
  16. $expr: {
  17. "$eq": [
  18. "$_id",
  19. "$$suspectsIds"
  20. ]
  21. }
  22. }
  23. },
  24. {
  25. "$unset": "_id"
  26. }
  27. ],
  28. "as": "suspectsLookup"
  29. }
  30. },
  31. {
  32. "$unwind": "$suspectsLookup"
  33. },
  34. {
  35. "$lookup": {
  36. "from": "lawyers",
  37. "let": {
  38. "lawyersIds": {
  39. "$toObjectId": "$suspects.lawyer.id"
  40. }
  41. },
  42. "pipeline": [
  43. {
  44. "$match": {
  45. $expr: {
  46. "$eq": [
  47. "$_id",
  48. "$$lawyersIds"
  49. ]
  50. }
  51. }
  52. },
  53. {
  54. "$unset": "_id"
  55. }
  56. ],
  57. "as": "lawyersLookup"
  58. }
  59. },
  60. {
  61. "$unwind": "$lawyersLookup"
  62. },
  63. {
  64. "$set": {
  65. "suspects.lawyer": {
  66. "$mergeObjects": [
  67. "$suspects.lawyer",
  68. "$lawyersLookup"
  69. ]
  70. }
  71. }
  72. },
  73. {
  74. "$set": {
  75. "suspects": [
  76. {
  77. "$mergeObjects": [
  78. "$suspects",
  79. "$suspectsLookup"
  80. ]
  81. }
  82. ]
  83. }
  84. },
  85. {
  86. "$unset": [
  87. "lawyersLookup",
  88. "suspectsLookup"
  89. ]
  90. }
  91. ])

Mongo Playground

展开查看全部

相关问题