MongoDb 聚合数据操作 - 对象到数组 [英] MongoDb Aggregation Data manipulation - Objects to Arrays
问题描述
我有以下示例数据集
[{_id":{$oid":60f83d3cd66842301905aa77"},id":527438,名称":CryptoPunk #4050",资产合同":{名称":CryptoPunks",地址":0xb47e3cd837ddf8e4c57f05d70ab865de6e193bbb"},特性":[{trait_type":类型",值":男",显示类型":空,max_value":空,trait_count":6039,订单":空},{trait_type":附件",价值":莫霍克",显示类型":空,max_value":空,trait_count":441,订单":空},{trait_type":附件",价值":耳环",显示类型":空,max_value":空,trait_count":2459,订单":空},{trait_type":附件",价值":皱眉",显示类型":空,max_value":空,trait_count":261,订单":空}],token_id":4050",永久链接":https://opensea.io/assets/0xb47e3cd837ddf8e4c57f05d70ab865de6e193bbb/4050",background_color":空,image_url":https://lh3.googleusercontent.com/sO18rDQYhC5yIcj12RVsv31pbbsZo_2muQQbTJMQHn47EKGhnirs8mxzohm58HAZ7taBoe4pU6x1qntlExkquot;TtJimage_preview_url":https://lh3.googleusercontent.com/sO18rDQYhC5yIcj12RVsv31pbbsZo_2muQQbTJMQHn47EKGhnirs8mxzohm58HAZ7taBoe4pU6x1qntlJExk"Tanimation_url":空,vault_contract":0x269616d549d7e8eaa82dfb17028d0b212d11232a"},{_id":{$oid":60f83d3cbc3f0161da2141f7"},id":17736625,姓名":OJ辛普森",资产合同":{名称":哈希掩码",地址":0xc2c747e0f7004f9e8817db2ca4997657a7746928"},特性":[{trait_type":字符",值":男",显示类型":空,max_value":空,trait_count":8659,订单":空},{trait_type":面具",价值":涂鸦",显示类型":空,max_value":空,trait_count":2187,订单":空},{trait_type":眼睛颜色",价值":黑暗",显示类型":空,max_value":空,trait_count":7419,订单":空},{trait_type":物品",价值":无项目",显示类型":空,max_value":空,trait_count":14533,订单":空},{trait_type":肤色",价值":黑暗",显示类型":空,max_value":空,trait_count":3784,订单":空},{trait_type":令牌ID",值":3535,display_type":数字",max_value":空,trait_count":0,订单":空},{trait_type":背景",价值":涂鸦",显示类型":空,max_value":空,trait_count":5538,订单":空}],token_id":3535",永久链接":https://opensea.io/assets/0xc2c747e0f7004f9e8817db2ca4997657a7746928/3535",background_color":空,"image_url":"https://lh3.googleusercontent.com/NZQu7CNjgJ_1uhbUVwEb-14rZPJmPCaqaXy0qnUpgm5Qll0BvmmF7tPMjBhFH6ZZp_qzOPxHi0NFmRkOjDcLuBuQ0"image_preview_url":"https://lh3.googleusercontent.com/NZQu7CNjgJ_1uhbUVwEb-14rZPJmPCaqaXy0qnUpgm5Qll0BvmmF7tPMjBhFH6ZZp_qzOPxHi0NFmRk0sBvmmF7tPMjBhFH6ZZp_qzOPxHi0NFmRk8sB0DcOxLuBJ_1uhbUVwEb-14rZPJmPanimation_url":空,vault_contract":0xc7a8b45e184138114e6085c82936a8db93dd156a"}]
我想更新为
[{_id":{$oid":60f83d3cd66842301905aa77"},id":527438,名称":CryptoPunk #4050",资产合同":{名称":CryptoPunks",地址":0xb47e3cd837ddf8e4c57f05d70ab865de6e193bbb"},特质":{类型":男性",配件":[莫霍克"、耳环"、皱眉"]、},token_id":4050",永久链接":https://opensea.io/assets/0xb47e3cd837ddf8e4c57f05d70ab865de6e193bbb/4050",background_color":空,image_url":https://lh3.googleusercontent.com/sO18rDQYhC5yIcj12RVsv31pbbsZo_2muQQbTJMQHn47EKGhnirs8mxzohm58HAZ7taBoe4pU6x1qntlExkquot;TtJimage_preview_url":https://lh3.googleusercontent.com/sO18rDQYhC5yIcj12RVsv31pbbsZo_2muQQbTJMQHn47EKGhnirs8mxzohm58HAZ7taBoe4pU6x1qntlJExk"Tanimation_url":空,vault_contract":0x269616d549d7e8eaa82dfb17028d0b212d11232a"},{_id":{$oid":60f83d3cbc3f0161da2141f7"},id":17736625,姓名":OJ辛普森",资产合同":{名称":哈希掩码",地址":0xc2c747e0f7004f9e8817db2ca4997657a7746928"},特性":{人物":男",面具":涂鸦",eye_color":深色",项目":无项目",skin_color":深色",token_id":3535,背景":涂鸦",},token_id":3535",永久链接":https://opensea.io/assets/0xc2c747e0f7004f9e8817db2ca4997657a7746928/3535",background_color":空,"image_url":"https://lh3.googleusercontent.com/NZQu7CNjgJ_1uhbUVwEb-14rZPJmPCaqaXy0qnUpgm5Qll0BvmmF7tPMjBhFH6ZZp_qzOPxHi0NFmRkOjDcLuBuQ0"image_preview_url":"https://lh3.googleusercontent.com/NZQu7CNjgJ_1uhbUVwEb-14rZPJmPCaqaXy0qnUpgm5Qll0BvmmF7tPMjBhFH6ZZp_qzOPxHi0NFmRk0sBvmmF7tPMjBhFH6ZZp_qzOPxHi0NFmRk8sB0DcOxLuBJ_1uhbUVwEb-14rZPJmPanimation_url":空,vault_contract":0xc7a8b45e184138114e6085c82936a8db93dd156a"}]
背后的逻辑是
- 查看 Traits 数组对象
- 获取 trait_type 值并使用小写名称(空格下划线)创建一个新键
- 将新键的值设置为
value"
的值
所以,
"trait_type": "type",值":男",//变成类型":男性"
- 如果有多个相同特征类型的实例,则创建一个值数组.
所以,
{trait_type":附件",价值":莫霍克",显示类型":空,max_value":空,trait_count":441,订单":空},{trait_type":附件",价值":耳环",显示类型":空,max_value":空,trait_count":2459,订单":空},//变成配饰":[莫霍克"、耳环"]
查询
即使管道不允许我们使用这里使用的组查找等阶段,它也是一个聚合更新.(您可以使用
$out
并在之后替换集合或$merge
来替换文档(类似于更新))第一张地图
- 对于每个特征(特征的文档成员),它都将其放入数组
[[trait_type":type"] [value":Male"] [display_type":null] ...]
- 减少该数组以仅从它们构建 1 个文档
<代码>{类型"类型",值":"Male"}(小写和_"也是如此)
- 对于每个特征(特征的文档成员),它都将其放入数组
现在的特点是一样的
特性":[{类型":类型",价值":男性"},{类型":附件",价值":莫霍克"},{类型":附件",价值":耳环"},{类型":附件",价值":皱眉"}]
使用虚拟集合
[{}]
查找(我们这样做是为了在该数组中创建一个组)它就像一个技巧,允许我们在 1 个文档中使用阶段运算符>- 查找管道按类型展开和分组
特性":[{值":[莫霍克",耳环",皱眉"],类型":附件";},{值":[男"],类型":类型"}]
- 然后它是一个替换根来获取类型的值,使其成为字段名和值作为值
(如果 size=1 删除数组)
查找后我们有
特质":[{附件":[莫霍克",耳环",皱眉"]},{类型":男性"}]
所以我们要做的就是减少特征并合并对象(无论如何,键都是唯一的,因为我们按它们分组)
我们得到了预期的输出(至少我认为没问题)
db.collection.aggregate([{$set":{特性":{$地图":{输入":$traits",作为":t",在":{$减少":{输入":{$地图":{输入":{$objectToArray":$$t"},作为":m",在":[$$m.k",$$m.v"]}},初始值":{},在":{$let":{变量":{type_value":$$value",ta":$$this"},在":{$let":{变量":{键":{$arrayElemAt":[$$ta",0]},价值":{$arrayElemAt":[$$ta",1]}},在":{$开关":{分支机构":[{案例":{$eq":[$$key",价值"]},然后":{$mergeObjects":[$$type_value",{价值":$$价值"}]}},{案例":{$eq":[$$key",trait_type"]},然后":{$mergeObjects":[$$type_value",{类型":{$replaceAll":{输入":{$toLower":$$value"},发现":"",替换":_"}}}]}}],默认":$$type_value";}}}}}}}}}}}},{$查找":{来自":假人",让":{特质":$特质";},管道":[{$set":{特质":$$特质"}},{$解开":{路径":$traits"}},{$replaceRoot":{newRoot":$traits"}},{$组":{_id":$type",值":{$push":$value"}}},{$set":{类型":$_id"}},{$项目":{_id":0}},{$replaceRoot":{新根":{$cond":[{$eq":[{$size":$values"},1]},{$arrayToObject":{$let":{变量":{对":[[$type",{$arrayElemAt":[$values",0]}]]},输入":$$pair"}}},{$arrayToObject":{$let":{变量":{对":[[$type",$values"]]},输入":$$pair"}}}]}}}],作为":特性";}},{$set":{特性":{$mergeObjects":$traits"}}}])
I have the following example set of data
[{
"_id": {
"$oid": "60f83d3cd66842301905aa77"
},
"id": 527438,
"name": "CryptoPunk #4050",
"asset_contract": {
"name": "CryptoPunks",
"address": "0xb47e3cd837ddf8e4c57f05d70ab865de6e193bbb"
},
"traits": [
{
"trait_type": "type",
"value": "Male",
"display_type": null,
"max_value": null,
"trait_count": 6039,
"order": null
},
{
"trait_type": "accessory",
"value": "Mohawk",
"display_type": null,
"max_value": null,
"trait_count": 441,
"order": null
},
{
"trait_type": "accessory",
"value": "Earring",
"display_type": null,
"max_value": null,
"trait_count": 2459,
"order": null
},
{
"trait_type": "accessory",
"value": "Frown",
"display_type": null,
"max_value": null,
"trait_count": 261,
"order": null
}
],
"token_id": "4050",
"permalink": "https://opensea.io/assets/0xb47e3cd837ddf8e4c57f05d70ab865de6e193bbb/4050",
"background_color": null,
"image_url": "https://lh3.googleusercontent.com/sO18rDQYhC5yIcj12RVsv31pbbsZo_2muQQbTJMQHn47EKGhnirs8mxzohm58HAZ7taBoe4pU6x1qntlExk_TtJ-",
"image_preview_url": "https://lh3.googleusercontent.com/sO18rDQYhC5yIcj12RVsv31pbbsZo_2muQQbTJMQHn47EKGhnirs8mxzohm58HAZ7taBoe4pU6x1qntlExk_TtJ-=s250",
"animation_url": null,
"vault_contract": "0x269616d549d7e8eaa82dfb17028d0b212d11232a"
},{
"_id": { "$oid": "60f83d3cbc3f0161da2141f7" },
"id": 17736625,
"name": "OJ Simpson",
"asset_contract": {
"name": "Hashmasks",
"address": "0xc2c747e0f7004f9e8817db2ca4997657a7746928"
},
"traits": [
{
"trait_type": "Character",
"value": "Male",
"display_type": null,
"max_value": null,
"trait_count": 8659,
"order": null
},
{
"trait_type": "Mask",
"value": "Doodle",
"display_type": null,
"max_value": null,
"trait_count": 2187,
"order": null
},
{
"trait_type": "Eye Color",
"value": "Dark",
"display_type": null,
"max_value": null,
"trait_count": 7419,
"order": null
},
{
"trait_type": "Item",
"value": "No Item",
"display_type": null,
"max_value": null,
"trait_count": 14533,
"order": null
},
{
"trait_type": "Skin Color",
"value": "Dark",
"display_type": null,
"max_value": null,
"trait_count": 3784,
"order": null
},
{
"trait_type": "Token ID",
"value": 3535,
"display_type": "number",
"max_value": null,
"trait_count": 0,
"order": null
},
{
"trait_type": "Background",
"value": "Doodle",
"display_type": null,
"max_value": null,
"trait_count": 5538,
"order": null
}
],
"token_id": "3535",
"permalink": "https://opensea.io/assets/0xc2c747e0f7004f9e8817db2ca4997657a7746928/3535",
"background_color": null,
"image_url": "https://lh3.googleusercontent.com/NZQu7CNjgJ_1uhbUVwEb-14rZPJmPCaqaXy0qnUpgm5Qll0BvmmF7tPMjBhFH6ZZp_qzOPxHi0NFmRkOjHoBQ0BODcWI8NlyBXLu",
"image_preview_url": "https://lh3.googleusercontent.com/NZQu7CNjgJ_1uhbUVwEb-14rZPJmPCaqaXy0qnUpgm5Qll0BvmmF7tPMjBhFH6ZZp_qzOPxHi0NFmRkOjHoBQ0BODcWI8NlyBXLu=s250",
"animation_url": null,
"vault_contract": "0xc7a8b45e184138114e6085c82936a8db93dd156a"
}]
which I would like to be updated to
[{
"_id": {
"$oid": "60f83d3cd66842301905aa77"
},
"id": 527438,
"name": "CryptoPunk #4050",
"asset_contract": {
"name": "CryptoPunks",
"address": "0xb47e3cd837ddf8e4c57f05d70ab865de6e193bbb"
},
"traits":
{
"type": "Male",
"accessory": ["Mohawk", "Earing", "Frown"],
},
"token_id": "4050",
"permalink": "https://opensea.io/assets/0xb47e3cd837ddf8e4c57f05d70ab865de6e193bbb/4050",
"background_color": null,
"image_url": "https://lh3.googleusercontent.com/sO18rDQYhC5yIcj12RVsv31pbbsZo_2muQQbTJMQHn47EKGhnirs8mxzohm58HAZ7taBoe4pU6x1qntlExk_TtJ-",
"image_preview_url": "https://lh3.googleusercontent.com/sO18rDQYhC5yIcj12RVsv31pbbsZo_2muQQbTJMQHn47EKGhnirs8mxzohm58HAZ7taBoe4pU6x1qntlExk_TtJ-=s250",
"animation_url": null,
"vault_contract": "0x269616d549d7e8eaa82dfb17028d0b212d11232a"
},{
"_id": { "$oid": "60f83d3cbc3f0161da2141f7" },
"id": 17736625,
"name": "OJ Simpson",
"asset_contract": {
"name": "Hashmasks",
"address": "0xc2c747e0f7004f9e8817db2ca4997657a7746928"
},
"traits": {
"character": "Male",
"mask": "Doodle",
"eye_color": "Dark",
"item": "No Item",
"skin_color": "Dark",
"token_id": 3535,
"background": "Doodle",
},
"token_id": "3535",
"permalink": "https://opensea.io/assets/0xc2c747e0f7004f9e8817db2ca4997657a7746928/3535",
"background_color": null,
"image_url": "https://lh3.googleusercontent.com/NZQu7CNjgJ_1uhbUVwEb-14rZPJmPCaqaXy0qnUpgm5Qll0BvmmF7tPMjBhFH6ZZp_qzOPxHi0NFmRkOjHoBQ0BODcWI8NlyBXLu",
"image_preview_url": "https://lh3.googleusercontent.com/NZQu7CNjgJ_1uhbUVwEb-14rZPJmPCaqaXy0qnUpgm5Qll0BvmmF7tPMjBhFH6ZZp_qzOPxHi0NFmRkOjHoBQ0BODcWI8NlyBXLu=s250",
"animation_url": null,
"vault_contract": "0xc7a8b45e184138114e6085c82936a8db93dd156a"
}]
The logic behind it would be
- Look at the Traits array objects
- get the trait_type value and create a new key using the lower case name (underscores for spaces)
- Set the value of the new key to be the value of
"value"
So,
"trait_type": "type",
"value": "Male",
//becomes
"type": "Male"
- Where there are multiple instances of the same trait type, create an array of values.
So,
{
"trait_type": "accessory",
"value": "Mohawk",
"display_type": null,
"max_value": null,
"trait_count": 441,
"order": null
},
{
"trait_type": "accessory",
"value": "Earring",
"display_type": null,
"max_value": null,
"trait_count": 2459,
"order": null
},
// becomes
"accessory": ["Mohawk", "Earring"]
Query
its an aggregation update even if pipeline doesn't allow us to use stages like group lookup etc that here is used. (you can use
$out
and replace collection after or$merge
to replace documents(similar to update))first map
- for each trait(document member of traits), it makes it into array
[["trait_type": "type"] ["value": "Male"] ["display_type": null] ...]
- reduce on that array to contruct from them 1 document only
{"type" "type","value" :"Male"}
(does also that lowercase and "_")
- for each trait(document member of traits), it makes it into array
Now traits its like
"traits": [ { "type": "type", "value": "Male" }, { "type": "accessory", "value": "Mohawk" }, { "type": "accessory", "value": "Earring" }, { "type": "accessory", "value": "Frown" } ]
lookup with the dummy collection
[{}]
(we do that to make a group inside that array) its like a trick that allows us to use stage operators inside 1 document- lookup pipeline unwinds and groups by type
"traits": [ { "values": [ "Mohawk", "Earring", "Frown" ], "type": "accessory" }, { "values": [ "Male" ], "type": "type" } ]
- then its a replace root to do take the value of type, make it the field-name and the values as value
(if size=1 removes the array)
After lookup we have
"traits": [ { "accessory": [ "Mohawk", "Earring", "Frown" ] }, { "type": "Male" } ]
so all we have to do is to reduce that traits and merge the objects (keys are unique anyways because we grouped by them)
and we get the expected output (at least i think its ok)
db.collection.aggregate([
{
"$set": {
"traits": {
"$map": {
"input": "$traits",
"as": "t",
"in": {
"$reduce": {
"input": {
"$map": {
"input": {
"$objectToArray": "$$t"
},
"as": "m",
"in": [
"$$m.k",
"$$m.v"
]
}
},
"initialValue": {},
"in": {
"$let": {
"vars": {
"type_value": "$$value",
"ta": "$$this"
},
"in": {
"$let": {
"vars": {
"key": {
"$arrayElemAt": [
"$$ta",
0
]
},
"value": {
"$arrayElemAt": [
"$$ta",
1
]
}
},
"in": {
"$switch": {
"branches": [
{
"case": {
"$eq": [
"$$key",
"value"
]
},
"then": {
"$mergeObjects": [
"$$type_value",
{
"value": "$$value"
}
]
}
},
{
"case": {
"$eq": [
"$$key",
"trait_type"
]
},
"then": {
"$mergeObjects": [
"$$type_value",
{
"type": {
"$replaceAll": {
"input": {
"$toLower": "$$value"
},
"find": " ",
"replacement": "_"
}
}
}
]
}
}
],
"default": "$$type_value"
}
}
}
}
}
}
}
}
}
}
}
},
{
"$lookup": {
"from": "dummy",
"let": {
"traits": "$traits"
},
"pipeline": [
{
"$set": {
"traits": "$$traits"
}
},
{
"$unwind": {
"path": "$traits"
}
},
{
"$replaceRoot": {
"newRoot": "$traits"
}
},
{
"$group": {
"_id": "$type",
"values": {
"$push": "$value"
}
}
},
{
"$set": {
"type": "$_id"
}
},
{
"$project": {
"_id": 0
}
},
{
"$replaceRoot": {
"newRoot": {
"$cond": [
{
"$eq": [
{
"$size": "$values"
},
1
]
},
{
"$arrayToObject": {
"$let": {
"vars": {
"pair": [
[
"$type",
{
"$arrayElemAt": [
"$values",
0
]
}
]
]
},
"in": "$$pair"
}
}
},
{
"$arrayToObject": {
"$let": {
"vars": {
"pair": [
[
"$type",
"$values"
]
]
},
"in": "$$pair"
}
}
}
]
}
}
}
],
"as": "traits"
}
},
{
"$set": {
"traits": {
"$mergeObjects": "$traits"
}
}
}
])
这篇关于MongoDb 聚合数据操作 - 对象到数组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!