如何基于数组数值筛选MongoDB数据?

huangapple go评论83阅读模式
英文:

How to Filter MongoDB Data Based on Array Values?

问题

以下是您要翻译的内容:

我在一个集合中有一个文档,其中有数组中的值。
{
  "name": "ABC",
  "details": [
     {"color": "red", "price": 20000}, 
     {"color": "black", "price": 22000}, 
     {"color": "blue", "price": 21000}
  ]
},
{
  "name": "XYZ",
  "details": [
     {"color": "yellow", "price": 10000}, 
     {"color": "black", "price": 12000}, 
     {"color": "green","price": 11000}
  ]
},
{
  "name": "CBD",
  "details": [
     {"color": "red", "price": 30000}, 
     {"color": "pink", "price": 32000}, 
     {"color": "blue", "price": 31000}
  ]
}

我想要过滤红色和蓝色的数据,并需要输出如下:

{"name": "ABC", "color": "red", "price": 20000},
{"name": "ABC", "color": "blue", "price": 21000},
{"name": "CBD", "color": "red", "price": 30000},
{"name": "CBD", "color": "blue", "price": 31000}

有关此查询的MongoDB查询或JS / TS代码,请告诉我...

英文:

I have a document in a collection which have values in array.

{
  "name": "ABC",
  "details": [
     {"color": "red", "price": 20000}, 
     {"color": "black", "price": 22000}, 
     {"color": "blue", "price": 21000}
  ]
},
{
  "name": "XYZ",
  "details": [
     {"color": "yellow", "price": 10000}, 
     {"color": "black", "price": 12000}, 
     {"color": "green", "price": 11000}
  ]
},
{
  "name": "CBD",
  "details": [
     {"color": "red", "price": 30000}, 
     {"color": "pink", "price": 32000}, 
     {"color": "blue", "price": 31000}
  ]
}

I want to filter data for color red and blue and need output like

{"name": "ABC", "color": "red", "price": 20000},
{"name": "ABC", "color": "blue", "price": 21000},
{"name": "CBD", "color": "red", "price": 30000},
{"name": "CBD", "color": "blue", "price": 31000}

what will the query for this...

I am able to get

{
  "name": "ABC",
  "details": [
     {"color": "red", "price": 20000}, 
     {"color": "blue", "price": 21000}
  ]
},
{
  "name": "CBD",
  "details": [
     {"color": "red", "price": 30000}, 
     {"color": "blue", "price": 31000}
  ]
}

but I want like

{"name": "ABC", "color": "red", "price": 20000},
{"name": "ABC", "color": "blue", "price": 21000},
{"name": "CBD", "color": "red", "price": 30000},
{"name": "CBD", "color": "blue", "price": 31000}

let me know the MongoDB query or JS/TS code to simplify array object...

答案1

得分: 1

您可以使用flatMap获取以下所示的所需格式化数据:

const data = [
  {
    "name": "ABC",
    "details": [
       {"color": "red", "price": 20000}, 
       {"color": "blue", "price": 21000}
    ]
  },
  {
    "name": "CBD",
    "details": [
       {"color": "red", "price": 30000}, 
       {"color": "blue", "price": 31000}
    ]
  }
];

const result = data.flatMap(entry => entry.details.map(detail => ({name: entry.name, ...detail})));

console.log(result);

请注意,上述代码是JavaScript代码示例,用于演示如何使用flatMap获得所需格式化数据。

英文:

You can use flatMap to get desired formatted data like below:

<!-- begin snippet: js hide: false console: true babel: false -->

<!-- language: lang-js -->

const data = [
  {
    &quot;name&quot;: &quot;ABC&quot;,
    &quot;details&quot;: [
       {&quot;color&quot;: &quot;red&quot;, &quot;price&quot;: 20000}, 
       {&quot;color&quot;: &quot;blue&quot;, &quot;price&quot;: 21000}
    ]
  },
  {
    &quot;name&quot;: &quot;CBD&quot;,
    &quot;details&quot;: [
       {&quot;color&quot;: &quot;red&quot;, &quot;price&quot;: 30000}, 
       {&quot;color&quot;: &quot;blue&quot;, &quot;price&quot;: 31000}
    ]
  }
];

const result = data.flatMap(entry =&gt; entry.details.map(detail =&gt; ({name: entry.name, ...detail})));

console.log(result);

<!-- end snippet -->

答案2

得分: 1

使用 Array.flatMap() 可以实现这个功能,下面是参考链接,你可以查看 flattening-an-array-of-complex-objects-with-an-array-attribute

let data = [
    {
        "name": "ABC",
        "details": [
            {"color": "red", "price": 20000},
            {"color": "black", "price": 22000},
            {"color": "blue", "price": 21000}
        ]
    },
    {
        "name": "XYZ",
        "details": [
            {"color": "yellow", "price": 10000},
            {"color": "black", "price": 12000},
            {"color": "green", "price": 11000}
        ]
    },
    {
        "name": "CBD",
        "details": [
            {"color": "red", "price": 30000},
            {"color": "pink", "price": 32000},
            {"color": "blue", "price": 31000}
        ]
    }
]

data = data.filter(d1 => d1.details.some(d2 => d2.color == "red" || d2.color == "blue"))
data = data.flatMap(({ name, details }) => details.map(d => ({ name, ...d })))

console.log(data)
英文:

Using Array.flatMap() can do it ,below is reference for you,you can check at flattening-an-array-of-complex-objects-with-an-array-attribute

<!-- begin snippet: js hide: false console: true babel: false -->

<!-- language: lang-js -->

let data = [
{
  &quot;name&quot;: &quot;ABC&quot;,
  &quot;details&quot;: [
     {&quot;color&quot;: &quot;red&quot;, &quot;price&quot;: 20000}, 
     {&quot;color&quot;: &quot;black&quot;, &quot;price&quot;: 22000}, 
     {&quot;color&quot;: &quot;blue&quot;, &quot;price&quot;: 21000}
  ]
},
{
  &quot;name&quot;: &quot;XYZ&quot;,
  &quot;details&quot;: [
     {&quot;color&quot;: &quot;yellow&quot;, &quot;price&quot;: 10000}, 
     {&quot;color&quot;: &quot;black&quot;, &quot;price&quot;: 12000}, 
     {&quot;color&quot;: &quot;green&quot;, &quot;price&quot;: 11000}
  ]
},
{
  &quot;name&quot;: &quot;CBD&quot;,
  &quot;details&quot;: [
     {&quot;color&quot;: &quot;red&quot;, &quot;price&quot;: 30000}, 
     {&quot;color&quot;: &quot;pink&quot;, &quot;price&quot;: 32000}, 
     {&quot;color&quot;: &quot;blue&quot;, &quot;price&quot;: 31000}
  ]
}
]

data = data.filter(d1 =&gt; d1.details.some(d2 =&gt; d2.color == &quot;red&quot; || d2.color == &quot;blue&quot;))
data = data.flatMap(({ name,details }) =&gt; details.map(d =&gt; ({ name, ...d })))

console.log(data)

<!-- end snippet -->

答案3

得分: 1

我认为,如果你想要获得良好的性能,那么这将是最佳选择:

db.collection.aggregate([
   { $match: { "details.color": { $in: ["red", "blue"] } } },
   {
      $set: {
         details: {
            $filter: {
               input: "$details",
               cond: { $in: ["$$this.color", ["red", "blue"]] }
            }
         }
      }
   },
   { $unwind: "$details" },
   { $replaceWith: { $mergeObjects: [{ name: "$name" }, "$details"] } }
])

Mongo Playground

英文:

I think, if you like to get good performance then this would be the best option:

db.collection.aggregate([
   { $match: { &quot;details.color&quot;: { $in: [&quot;red&quot;, &quot;blue&quot;] } } },
   {
      $set: {
         details: {
            $filter: {
               input: &quot;$details&quot;,
               cond: { $in: [&quot;$$this.color&quot;, [&quot;red&quot;, &quot;blue&quot;]] }
            }
         }
      }
   },
   { $unwind: &quot;$details&quot; },
   { $replaceWith: { $mergeObjects: [{ name: &quot;$name&quot; }, &quot;$details&quot;] } }
])

Mongo Playground

答案4

得分: 0

你可以运行以下的JavaScript代码(如果你不介意性能):

let initialArr = [{
    "name": "ABC",
    "details": [
       {"color": "red", "price": 20000}, 
       {"color": "blue", "price": 21000}
    ]
  },

  {
    "name": "CBD",
    "details": [
       {"color": "red", "price": 30000}, 
       {"color": "blue", "price": 31000}
    ]
  }
]
let resultArr = []
for (let i = 0; i < initialArr.length; i++) {
    for (let k = 0; k < initialArr[i].details.length; k++) {
        resultArr.push({name: initialArr[i].name, ...initialArr[i].details[k]})
    }
}
英文:

You can run the below js code (if you don't mind the performance):

let initialArr = [{
    &quot;name&quot;: &quot;ABC&quot;,
    &quot;details&quot;: [
       {&quot;color&quot;: &quot;red&quot;, &quot;price&quot;: 20000}, 
       {&quot;color&quot;: &quot;blue&quot;, &quot;price&quot;: 21000}
    ]
  },

  {
    &quot;name&quot;: &quot;CBD&quot;,
    &quot;details&quot;: [
       {&quot;color&quot;: &quot;red&quot;, &quot;price&quot;: 30000}, 
       {&quot;color&quot;: &quot;blue&quot;, &quot;price&quot;: 31000}
    ]
  }
]
let resultArr = []
for (let i = 0; i &lt; initialArr.length; i++) {
    for (let k = 0; k &lt; initialArr[i].details.length; k++) {
        resultArr.push({name: initialArr[i].name, ...initialArr[i].details[k]})
    }
}

答案5

得分: 0

MongoDB的$unwind操作符可以帮助 - > 从输入文档中拆分数组字段,以输出每个元素的文档。每个输出文档都是输入文档,其数组字段的值被元素替换。

以下是一个聚合查询 -

db.getCollection('your_collection').aggregate([
  // 为`details`数组中的每个元素创建一个文档
  { $unwind : "$details" },

  // 过滤颜色为红色或蓝色的文档
  { $match: { "details.color": { $in: ["red", "blue"] } } },
  
  // 以所需格式输出
  { $project: { name: 1, color: "$details.color", price: "$details.price" } }
])
  1. 这是拆分后的数据的样子 -
[
  {"name": "ABC", "details": { "color": "red", "price": 20000} },
  {"name": "ABC", "details": { "color": "blue", "price": 22000} },
  {"name": "ABC", "details": { "color": "blue", "price": 21000} },
  {"name": "XYZ", "details": { "color": "yellow", "price": 10000} },
  {"name": "XYZ", "details": { "color": "black", "price": 12000} },
  ...
]
  1. 过滤后的、拆分后的数据(仅红色和蓝色)-
[
  {"name": "ABC", "color": "red", "price": 20000},
  {"name": "ABC", "color": "blue", "price": 21000},
  {"name": "CBD", "color": "red", "price": 30000},
  {"name": "CBD", "color": "blue", "price": 31000}
]
英文:

MongoDB's $unwind operator can help -
> Deconstructs an array field from the input documents to output a document for each element. Each output document is the input document with the value of the array field replaced by the element.

Here's an aggregate query -

db.getCollection(&#39;your_collection&#39;).aggregate([
  // create a document for each element in `details` array
  { $unwind : &quot;$details&quot; },

  // filter document with color red or blue
  { $match: { &quot;details.color&quot;: { $in: [&quot;red&quot;, &quot;blue&quot;] } } },
  
  // output in desired format
  { $project: { name: 1, color: &quot;$details.color&quot;, price: &quot;$details.price&quot; } }
])
  1. This is how unwounded data would look like -
[
  {&quot;name&quot;: &quot;ABC&quot;, &quot;details&quot;: { &quot;color&quot;: &quot;red&quot;, &quot;price&quot;: 20000} },
  {&quot;name&quot;: &quot;ABC&quot;, &quot;details&quot;: { &quot;color&quot;: &quot;blue&quot;, &quot;price&quot;: 22000} },
  {&quot;name&quot;: &quot;ABC&quot;, &quot;details&quot;: { &quot;color&quot;: &quot;blue&quot;, &quot;price&quot;: 21000} },
  {&quot;name&quot;: &quot;XYZ&quot;, &quot;details&quot;: { &quot;color&quot;: &quot;yellow&quot;, &quot;price&quot;: 10000} },
  {&quot;name&quot;: &quot;XYZ&quot;, &quot;details&quot;: { &quot;color&quot;: &quot;black&quot;, &quot;price&quot;: 12000} },
  ...
]
  1. Filtered, unwounded data (only red & blue)-
[
  {&quot;name&quot;: &quot;ABC&quot;, &quot;color&quot;: &quot;red&quot;, &quot;price&quot;: 20000},
  {&quot;name&quot;: &quot;ABC&quot;, &quot;color&quot;: &quot;blue&quot;, &quot;price&quot;: 21000},
  {&quot;name&quot;: &quot;CBD&quot;, &quot;color&quot;: &quot;red&quot;, &quot;price&quot;: 30000},
  {&quot;name&quot;: &quot;CBD&quot;, &quot;color&quot;: &quot;blue&quot;, &quot;price&quot;: 31000}
]

答案6

得分: 0

通过使用聚合查询,以下查询对您非常有帮助。

db.collection.aggregate([
  {
    $unwind: "$details"
  },
  {
    $match: {
      "details.color": { $in: ["red", "blue"] }
    }
  },
  {
    $project: {
      _id: 0,
      name: 1,
      color: "$details.color",
      price: "$details.price"
    }
  }
]);
英文:

By using aggregate the below query helps you alot.

db.collection.aggregate([
  {
    $unwind: &quot;$details&quot;
  },
  {
    $match: {
      &quot;details.color&quot;: { $in: [&quot;red&quot;, &quot;blue&quot;] }
    }
  },
  {
    $project: {
      _id: 0,
      name: 1,
      color: &quot;$details.color&quot;,
      price: &quot;$details.price&quot;
    }
  }
]);

huangapple
  • 本文由 发表于 2023年5月24日 18:20:20
  • 转载请务必保留本文链接:https://go.coder-hub.com/76322459.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定