I want to query the entire tree structure in mongodb, and I found a piece of code here.
aggregations shell.
[
{
$match: { pid: 0 }
},
{
$graphLookup: {
from: "administrative_divisions",
startWith: "$_id",
connectFromField: "_id",
connectToField: "pid",
depthField: "level",
as: "children"
}
},
{
$unwind: {
path: "$children",
preserveNullAndEmptyArrays: true
}
},
{
$sort: {
"children.level": -1
}
},
{
$group: {
_id: "$_id",
name: {
$first: "$name"
},
path: {
$first: "$path"
},
pid: {
$first: "$pid"
},
children: {
$push: "$children"
}
}
},
{
$addFields: {
children: {
$reduce: {
input: "$children",
initialValue: {
level: -1,
presentChild: [],
prevChild: []
},
in: {
$let: {
vars: {
prev: {
$cond: [
{
$eq: [
"$$value.level",
"$$this.level"
]
},
"$$value.prevChild",
"$$value.presentChild"
]
},
current: {
$cond: [
{
$eq: [
"$$value.level",
"$$this.level"
]
},
"$$value.presentChild",
[]
]
}
},
in: {
level: "$$this.level",
prevChild: "$$prev",
presentChild: {
$concatArrays: [
"$$current",
[
{
$mergeObjects: [
"$$this",
{
children: {
$filter: {
input: "$$prev",
as: "e",
cond: {
$eq: [
"$$e.pid",
"$$this._id"
]
}
}
}
}
]
}
]
]
}
}
}
}
}
}
}
},
{
$addFields: {
children: "$children.presentChild"
}
}
]
But there is no sorting based on fields in this code.
If there is a field sort per node, Java type is BigDecimal, mongoDB type is string.
Data used for example:
[
{
"_id": 1,
"name": "New York State",
"pid": 0,
"level": 1,
"sort": 2.00223000,
"leaf": false,
"path": "1"
},
{
"_id": 2,
"name": "New York City",
"pid": 1,
"level": 2,
"sort": 2.0000000,
"leaf": false,
"path": "1, 2"
},
{
"_id": 4,
"name": "The Bronx",
"pid": 2,
"level": 3,
"sort": 1.0000000,
"leaf": true,
"path": "1, 2, 4"
},
{
"_id": 5,
"name": "Brooklyn",
"pid": 2,
"level": 3,
"sort": 4.0000000,
"leaf": true,
"path": "1, 2, 5"
},
{
"_id": 6,
"name": "Manhattan",
"pid": 2,
"level": 3,
"sort": 5.0000000,
"leaf": true,
"path": "1, 2, 6"
},
{
"_id": 7,
"name": "Queens",
"pid": 2,
"level": 3,
"sort": 6.0000000,
"leaf": true,
"path": "1, 2, 7"
},
{
"_id": 8,
"name": "Staten Island",
"pid": 2,
"level": 3,
"sort": 12.0002220,
"leaf": true,
"path": "1, 2, 8"
},
{
"_id": 3,
"name": "Albany",
"pid": 1,
"level": 2,
"sort": 1.0023400,
"leaf": true,
"path": "1, 3"
}
]
Nodes of the same level are sorted in ascending order according to the sort number.
I expect the final result:
{
"_id": 1,
"name": "New York State",
"path": "1",
"pid": 0,
"children": [
{
"_id": 3,
"name": "Albany",
"pid": 1,
"level": {
"$numberLong": "0"
},
"sort": 1.00234,
"leaf": true,
"path": "1, 3",
"children": []
},
{
"_id": 2,
"name": "New York City",
"pid": 1,
"level": {
"$numberLong": "0"
},
"sort": 2,
"leaf": false,
"path": "1, 2",
"children": [
{
"_id": 4,
"name": "The Bronx",
"pid": 2,
"level": {
"$numberLong": "1"
},
"sort": 1,
"leaf": true,
"path": "1, 2, 4",
"children": []
},
{
"_id": 5,
"name": "Brooklyn",
"pid": 2,
"level": {
"$numberLong": "1"
},
"sort": 4,
"leaf": true,
"path": "1, 2, 5",
"children": []
},
{
"_id": 6,
"name": "Manhattan",
"pid": 2,
"level": {
"$numberLong": "1"
},
"sort": 5,
"leaf": true,
"path": "1, 2, 6",
"children": []
},
{
"_id": 7,
"name": "Queens",
"pid": 2,
"level": {
"$numberLong": "1"
},
"sort": 6,
"leaf": true,
"path": "1, 2, 7",
"children": []
},
{
"_id": 8,
"name": "Staten Island",
"pid": 2,
"level": {
"$numberLong": "1"
},
"sort": 12.000222,
"leaf": true,
"path": "1, 2, 8",
"children": []
}
]
}
]
}
How should I get the query results to sort based on the sort field?
Reference, not necessarily correct, because of the switched data.
I tried replacing
{ $sort: { "children.level": -1 } }with{ $sort: { "children.sort": 1 } }, which broke the final result.Adding
{ $sort: { "children.sort": 1 } }or{ $sort: { "sort": 1 } }to the final pipe doesn't work either.
I think you just need to add 1 more level of sorting at children.sort: 1 after children.level in the $sort stage.
db.administrative_divisions.aggregate([
{
"$match": {
pid: 0
}
},
{
$graphLookup: {
from: "administrative_divisions",
startWith: "$_id",
connectFromField: "_id",
connectToField: "pid",
depthField: "level",
as: "children"
}
},
{
$unwind: {
path: "$children",
preserveNullAndEmptyArrays: true
}
},
{
$sort: {
"children.level": -1,
"children.sort": 1
}
},
{
$group: {
_id: "$_id",
name: {
$first: "$name"
},
path: {
$first: "$path"
},
pid: {
$first: "$pid"
},
children: {
$push: "$children"
}
}
},
{
$addFields: {
children: {
$reduce: {
input: "$children",
initialValue: {
level: -1,
presentChild: [],
prevChild: []
},
"in": {
$let: {
vars: {
prev: {
$cond: [
{
$eq: [
"$$value.level",
"$$this.level"
]
},
"$$value.prevChild",
"$$value.presentChild"
]
},
current: {
$cond: [
{
$eq: [
"$$value.level",
"$$this.level"
]
},
"$$value.presentChild",
[]
]
}
},
"in": {
level: "$$this.level",
prevChild: "$$prev",
presentChild: {
$concatArrays: [
"$$current",
[
{
$mergeObjects: [
"$$this",
{
children: {
$filter: {
input: "$$prev",
as: "e",
cond: {
$eq: [
"$$e.pid",
"$$this._id"
]
}
}
}
}
]
}
]
]
}
}
}
}
}
}
}
},
{
$addFields: {
children: "$children.presentChild"
}
}
])