Sorting dynamic fields in array of object

Working with Data
1

I currently have some documents of items for dynamic forms.
Below are 3 samples.
The dataValues property stores the array of dynamic fields and its value.

When I want to list all the documents, how can I do sorting based on the dynamic fields?

For example, from UI side we can sort by First Name, or Middle Name dynamically but the sorting is applied on the related value’s field.
(API pass in ‘First Name’, but actual sorting is on dataValues.value of the same dataValues.label = ‘First Name’).

{
  _id: ObjectId("640add3aa23672f23560460d"),
  name: 'Irene',
  slug: 'irene',
  dataValues: [
    {
      label: 'First Name',
      value: 'Irene',
      sysName: 'content'
    }
  ]
},

{
  _id: ObjectId("640add31a23672f23560460b"),
  name: 'Zola',
  slug: 'zola',
  dataValues: [
    {
      label: 'First Name',
      value: 'Zola',
      sysName: 'content'
    },
    {
      label: 'Middle Name',
      value: 'Li',
      sysName: 'content'
    }
  ]
},

{
  _id: ObjectId("640add37a23672f23560460c"),
  name: 'Henry',
  slug: 'henry',
  dataValues: [
    {
      label: 'First Name',
      value: 'Henry',
      sysName: 'content'
    },
    {
      label: 'Rich',
      value: 'Rich',
      sysName: 'content'
    }
  ]
}
1 Like
3

Hi :wave: @HS-Law,

Welcome to the MongoDB Community forums :sparkles:

As per your shared sample documents, I’ve written a MongoDB aggregation pipeline that can sort the documents based on dynamic fields using the $filter, and $sort. Here is the aggregation pipeline for your reference:

db.test.aggregate([
  {
    $set: {
      dv: {
        $filter: {
          input: "$dataValues",
          cond: {
            $eq: ["$$this.label", "First Name"],
          },
        },
      },
    },
  },

  // Sort by the computed `dv.value` field
  {
    $sort: {
      "dv.value": 1,
    },
  },

  // Unsetting the `dv` field
  { $unset: "dv" },
])

It will return the following output:

[{
  "_id": {
    "$oid": "64250d46a1a9970c3457f771"
  },
  "name": "Henry",
  "slug": "henry",
  "dataValues": [
    {
      "label": "First Name",
      "value": "Henry",
      "sysName": "content"
    },
    {
      "label": "Rich",
      "value": "Rich",
      "sysName": "content"
    }
  ]
},
{
  "_id": {
    "$oid": "64250d26a1a9970c3457f76d"
  },
  "name": "Irene",
  "slug": "irene",
  "dataValues": [
    {
      "label": "First Name",
      "value": "Irene",
      "sysName": "content"
    }
  ]
},
{
  "_id": {
    "$oid": "64250d46a1a9970c3457f770"
  },
  "name": "Zola",
  "slug": "zola",
  "dataValues": [
    {
      "label": "First Name",
      "value": "Zola",
      "sysName": "content"
    },
    {
      "label": "Middle Name",
      "value": "Li",
      "sysName": "content"
    }
  ]
}]

Please note: Since the sort field is autogenerated it won’t be fast for large collections because no index will be used.

I hope it helps!

Best,
Kushagra

1 Like
4

Thanks, @Kushagra_Kesav.

If I sort by “Middle Name”, the items that do not have this field are listed first.
How can I change it so that the items without the sorted field will be listed last ?

5

Here is what I tried, sort first using the array’s size,
not sure if it is the best way:


db.Content.Item.aggregate([
    {
    $match: {
      collectionId: ObjectId("640add0fa23672f235604609")
    }
  },
  {
    $set: {
      dv: {
        $filter: {
          input: "$dataValues",
          cond: {
            $eq: ["$$this.label", "Middle Name"],
          },
        },
      },
    },
  },
  {
        $set: { dvCount: {$size: "$dv" } }
    },
  {
    $sort: {
      dvCount:-1,
      "dv.value": 1,
    },
  },
   { $unset: "dv" }
   ,
   { $unset: "dvCount" }
])
1 Like