测试数据
导入
mongoimport -d grids -c schemas --file schemas.json
schemas.json:
{
"_id" : ObjectId("5b9882a1629317fe30e4fd78"),
"schema" : "attachment",
"fields" : [{
"interactive" : false,
"name" : "name",
"data" : ["attachment", "action"]
}, {
"interactive" : false,
"name" : "1532921881691.jpg",
"data" : ["<img src=\"http://localhost/photos/1532921881691.jpg\"/>", {
"action" : true,
"visible" : true,
"type" : "button",
"innerText" : "view",
"value" : {
"url" : "http://localhost/photos/1532921881691.jpg"
}
}]
}]
}
{
"_id" : ObjectId("5b9882a1629317fe30e4fd79"),
"schema" : "report",
"fields" : [{
"interactive" : false,
"name" : "name",
"data" : ["statistic", "arts", "science"]
}, {
"interactive" : false,
"name" : "subject",
"data" : [{
"innerText" : "total"
}, ["history", "physical", "painting"], ["math", "chemistry", "geography"]]
}, {
"interactive" : true,
"name" : "Wang",
"data" : [545.0, [85.0, 100.0, 85.0], [92.0, 91.0, 92.0]]
}, {
"interactive" : true,
"name" : "Liu",
"data" : [527.0, [88.0, 99.0, 88.0], [66.0, 98.0, 88.0]]
}]
}
{
"_id" : ObjectId("5b9882a1629317fe30e4fd7a"),
"schema" : "graph",
"fields" : [{
"interactive" : false,
"name" : "shape",
"data" : ["type"]
}, {
"interactive" : true,
"name" : "tree",
"data" : [{
"innerText" : "draw",
"value" : ["subjects", ["arts", ["history", "geography", "painting"]], ["science", ["math", "physical", "chemistry"]]]
}]
}, {
"interactive" : true,
"name" : "circle",
"data" : [{
"innerText" : "draw",
"value" : {
"point" : {
"x" : 0.0,
"y" : 0.0
},
"radius" : 666.0
}
}]
}]
}
一、增
插入通用模版：
$push:{
<field>:{
$position:<num>, // 索引>=0
[$slice]:<num>, // 可选，>0 从头部开始截取（尾插） ; <0 从尾部向上截取（头插） ; 0 删除全部
[$sort]:{<field>:<num>}, // 可选，-1 降序 ; 1 升序
$each:[{<field1>:<value1> , ...},{<field1>:<value1> , ...} , ...]
}
}
在$position位置拼接$each数组，并且将拼接得出的数组长度截断为$slice。
令数组拼接后长度为length，$slice>0截取[0,$slice-1]，$slice<0截取[length-|$slice|,length)，当length-|$slice|<=0时，数组清空。
$sort排序在截断$slice操作前执行，即先排序再截断。
为方便记叙，以下，直接在文档根部定义的数组约定为外层数组，不管是否带键值，外层数组内部的数组全部约定为内嵌数组，不管是否带键值。
1.1 外层数组插入
Visual Schema Attachment（可视化）
基本操作，往
attachment模型插入一行数据，即往fields字段数组添加一个对象。
db.schemas.update(
{"schema" : "attachment"},
{
$push:{
fields:{
"interactive" : false,
"name" : "1565489.xlsx",
"data" : ["-", {
"action" : true,
"visible" : true,
"type" : "button",
"innerText" : "view",
"value" : {
"url" : "http://localhost/files/1565489.xlsx"
}
}]
}
}
});
结果：
{
"_id" : ObjectId("5b9882a1629317fe30e4fd78"),
"schema" : "attachment",
"fields" : [{
"interactive" : false,
"name" : "name",
"data" : ["attachment", "action"]
}, {
"interactive" : false,
"name" : "1532921881691.jpg",
"data" : ["<img src=\"http://localhost/photos/1532921881691.jpg\"/>", {
"action" : true,
"visible" : true,
"type" : "button",
"innerText" : "view",
"value" : {
"url" : "http://localhost/photos/1532921881691.jpg"
}
}]
}, {
"interactive" : false,
"name" : "1565489.xlsx",
"data" : ["-", {
"action" : true,
"visible" : true,
"type" : "button",
"innerText" : "view",
"value" : {
"url" : "http://localhost/files/1565489.xlsx"
}
}]
}]
}
1.2 带键值内嵌数组插入
这里要用到占位符，MongoDB 3.6+的特性，能解决旧版本无解的内嵌元素定位问题，在旧版只能修改外层数组，修改内嵌数组需要将外层数组元素整个替换。
已弃坑项目 Robotmongo / Robot3T 下不能运行以下查询，仅支持到MongoDB 3.4，应使用自带Shell环境或换用NoSQL Manager。
往attachment模型添加一列，即往所有的内嵌字段data添加一个对象，且仅当data字段存在且类型为数组时生效。
假如内嵌数组data的实际类型为String，那么查询就会报错，出于严谨性考虑，因此需要在arrayFilters中为data字段及其元素判断设置过滤条件，不符合条件的data字段不被执行插入操作。
注意，arrayFilters中的占位符i指代数组data的元素本体，并非数组元素索引，arrayFilters每个元素绑定一个占位符的子查询。
i的类型为Object时，可使用"Element Query Operators"进行过滤，即可以使用$exists和$type，当i的类型为数字或字符串时，可使用值域判断相关的操作符$in、$eq等，当i类型为数组时，可以使用"Array Query Operators"。
db.schemas.update(
{
"schema" : "attachment"
},
{
$push:{
"fields.$[i].data":{
innerText:"new column"
}
}
},{
arrayFilters:[
{
i:{
$type:"object"
},
"i.data":{
$exists:true // 若不设置该条件，当data字段不存在时，会自动创建data并插入元素
},
"i.data":{
$type:"array" // 校验类型，实际上也起到了{$exists:true}的作用
}
}
]
});
结果：
{
"_id" : ObjectId("5b9882a1629317fe30e4fd78"),
"schema" : "attachment",
"fields" : [{
"interactive" : false,
"name" : "name",
"data" : ["attachment", "action", {
"innerText" : "new column"
}]
}, {
"interactive" : false,
"name" : "1532921881691.jpg",
"data" : ["<img src=\"http://localhost/photos/1532921881691.jpg\"/>", {
"action" : true,
"visible" : true,
"type" : "button",
"innerText" : "view",
"value" : {
"url" : "http://localhost/photos/1532921881691.jpg"
}
}, {
"innerText" : "new column"
}]
}]
}
1.3 无键值内嵌元素插入
Visual Schema Graph
往
graph模型的tree图表绑定数据的叶子元素添加一个节点，表现为["history","geography","painting"]里面多了个字符串。
db.schemas.update({schema:"graph"},{
$push:{
"fields.$[i].data.$[j].value.$[k].$[l]":"leaf"
}
},{
arrayFilters:[
{
i:{
$type:"object"
},
"i.name":{
$eq:"tree"
},
"i.data":{
$type:"array"
}
},
{
j:{
$type:"object"
},
"j.value":{
$type:"array"
}
},
{
k:{
$type:"array"
}
},
{
l:{
$type:"array"
}
}
]
});
结果：
{
"_id" : ObjectId("5b9882a1629317fe30e4fd7a"),
"schema" : "graph",
"fields" : [{
"interactive" : false,
"name" : "shape",
"data" : ["type"]
}, {
"interactive" : true,
"name" : "tree",
"data" : [{
"innerText" : "draw",
"value" : ["subjects", ["arts", ["history", "geography", "painting", "leaf"]], ["science", ["math", "physical", "chemistry", "leaf"]]]
}]
}, {
"interactive" : true,
"name" : "circle",
"data" : [{
"innerText" : "draw",
"value" : {
"point" : {
"x" : 0,
"y" : 0
},
"radius" : 666
}
}]
}]
}
由于使用k指定过滤为数组，可以使用$elemMatch数组操作符进行具体匹配，定向在"arts"类别下添加节点，也可以使用$all:["arts"]。
如何细化匹配视具体数据而言，如果所有元素数据都是相同的，那就无法做到单一定位修改。
db.schemas.update({schema:"graph"},{
$push:{
"fields.$[i].data.$[j].value.$[k].$[l]":"leaf"
}
},{
arrayFilters:[
{
i:{
$type:"object"
},
"i.name":{
$eq:"tree"
},
"i.data":{
$type:"array"
}
},
{
j:{
$type:"object"
},
"j.value":{
$type:"array"
}
},
{
k:{
$type:"array",
$elemMatch:{
$eq:"arts" // $all:["arts"]
}
}
},
{
l:{
$type:"array"
}
}
]
});
结果：
{
"_id" : ObjectId("5b9882a1629317fe30e4fd7a"),
"schema" : "graph",
"fields" : [{
"interactive" : false,
"name" : "shape",
"data" : ["type"]
}, {
"interactive" : true,
"name" : "tree",
"data" : [{
"innerText" : "draw",
"value" : ["subjects", ["arts", ["history", "geography", "painting", "leaf"]], ["science", ["math", "physical", "chemistry"]]]
}]
}, {
"interactive" : true,
"name" : "circle",
"data" : [{
"innerText" : "draw",
"value" : {
"point" : {
"x" : 0,
"y" : 0
},
"radius" : 666
}
}]
}]
}
二、删
2.1 无键值内嵌数组的删除
Visual Schema Report
删除模型
report中的physical和geography科目分数，即裁掉一列。更新和删除是原子操作，暂没有类似
removeAt(index)根据索引删除的操作符，需要先置空再删除。
db.schemas.update({"schema" : "report"},
{
$pull:{
"fields.$[i].data.$[j]":{
$in:["physical","geography"]
}
},
$unset:{
"fields.$[i2].data.1.1":{}
},
$pop:{
"fields.$[i2].data.2":1,
}
},{
arrayFilters:[
{
i:
{
$type:"object",
},
"i.name":{$eq:"subject"}
},
{
j:
{
$type:"array"
}
},
{
"i2":
{
$type:"object"
},
"i2.name":{$not:{$in:["name","subject"]}}
}
]
});
db.schemas.update({"schema" : "report"},
{
$pull:{
"fields.$[i].data.1":null
}
},{
arrayFilters:[
{
"i":
{
$type:"object"
},
"i.name":{$not:{$in:["name","subject"]}}
}
]
});
结果：
{
"_id" : ObjectId("5b9882a1629317fe30e4fd79"),
"schema" : "report",
"fields" : [{
"interactive" : false,
"name" : "name",
"data" : ["statistic", "arts", " science"]
}, {
"interactive" : false,
"name" : "subject",
"data" : [{
"innerText" : "total"
}, ["history", "painting"], ["math", "chemistry"]]
}, {
"interactive" : true,
"name" : "Wang",
"data" : [545, [85, 85], [92, 91]]
}, {
"interactive" : true,
"name" : "Liu",
"data" : [527, [88, 88], [66, 98]]
}]
}
三、查
3.1 常用聚集操作
重新统计2.1的的总分
聚集操作符$in、$not、$type单独一套，与同名的查询操作符用法不一样，尤其是$type，聚集操作符{$type:value}识别数据类型，返回字符串"string"、"object"等，与查询操作符{$type:type}刚好反过来。
db.schemas.aggregate([
{
$match:{"schema" : "report"}
},
{
$project:{
_id:false,
scores:{
$filter:{
input:"$fields",
as:"field",
cond:{
$not:{
$in:["$$field.name",["name","subject"]]
}
}
}
}
}
},
{
$unwind:{
path:"$scores",
preserveNullAndEmptyArrays:false
}
},
{
$project:
{
name:"$scores.name",
scores:{
$filter:{
input:"$scores.data",
as:"score",
cond:{
$eq:[{$type:"$$score"},"array"]
}
}
}
}
},
{
$project:{
name:"$name",
arts: { $arrayElemAt: [ "$scores", 0 ] },
science: { $arrayElemAt: [ "$scores", 1 ] }
}
},
{
$project:{
name:"$name",
history:{ $arrayElemAt: [ "$arts", 0 ] },
painting:{ $arrayElemAt: [ "$arts", 1 ] },
math:{ $arrayElemAt: [ "$science", 0 ] },
chemistry:{ $arrayElemAt: [ "$science", 1 ] },
atrs: { $sum:"$arts" },
science: { $sum:"$science" }
}
},
{
$addFields:{
total: { $sum:["$atrs","$science"] }
}
},
{
$out:"scores"
}
]);
结果：
db.scores.find()
/* 1 */
{
"_id" : ObjectId("5b9f97db927db7e834715b04"),
"name" : "Wang",
"history" : 85,
"painting" : 85,
"math" : 92,
"chemistry" : 91,
"atrs" : 170,
"science" : 183,
"total" : 353
}
/* 2 */
{
"_id" : ObjectId("5b9f97db927db7e834715b05"),
"name" : "Liu",
"history" : 88,
"painting" : 88,
"math" : 66,
"chemistry" : 98,
"atrs" : 176,
"science" : 164,
"total" : 340
}
四、改
4.1 存储过程
更新 2.1总分
var atrs_Wang=db.scores.findOne({name:"Wang"}).atrs
var atrs_Liu=db.scores.findOne({name:"Liu"}).atrs
var science_Wang=db.scores.findOne({name:"Wang"}).science
var science_Liu=db.scores.findOne({name:"Liu"}).science
db.schemas.update(
{"schema" : "report"},
{
$set:{
"fields.$[i].data.0":(atrs_Wang+science_Wang),
"fields.$[i2].data.0":(atrs_Liu+science_Liu)
}
},{
arrayFilters:[
{
i:
{
$type:"object"
},
"i.name":{$eq:"Wang"}
},
{
i2:
{
$type:"object"
},
"i2.name":{$eq:"Liu"}
}
]
});
结果：
{
"_id" : ObjectId("5b9882a1629317fe30e4fd79"),
"schema" : "report",
"fields" : [{
"interactive" : false,
"name" : "name",
"data" : ["statistic", "arts", "science"]
}, {
"interactive" : false,
"name" : "subject",
"data" : [{
"innerText" : "total"
}, ["history", "painting"], ["math", "chemistry"]]
}, {
"interactive" : true,
"name" : "Wang",
"data" : [353, [85, 85], [92, 91]]
}, {
"interactive" : true,
"name" : "Liu",
"data" : [340, [88, 88], [66, 98]]
}]
}
