MongoDB: Transform array of objects to array of arrays

user_1727392 0
Viewed 557

I have a collection named "records" that contains documents in the following form:

{
  "name": "a"
  "items": [
    {
      "a": "5", 
      "b": "1", 
      "c": "2"
    }, 
    {
      "a": "6", 
      "b": "3", 
      "c": "7"
    }
  ]
}

I want to keep the data just as it is in the database (to make the data easy to read and interpret). But I'd like to run a query that returns the data in the following form:

{
  "name": "a"
  "items": [
    ["5", "1", "2"],
    ["6", "3", "7"],
  ]
}

Is this possible with pymongo? I know I can run a query and translate the documents using Python, but I'd like to avoid iterating over the query result if possible.

pymongo mongodb
2 Answers

I have a table named "records"

Collection

Is this possible with pymongo?

Yes

Any pointers on how to approach this would be super helpful!

I'd suggest you to use a view to transform your data during a query in MongoDB.
In this way, you can get transformed data and apply find to already transformed data if you need.

db.createCollection(
    "view_name",
    {"viewOn": "original_collection_name",
     "pipeline": [{$unwind: "$items"},
                  {$project: {name: 1, items: {$objectToArray: "$items"}}},
                  {$project: {name: 1, items: {$concatArrays: ["$items.v"]}}}, 
                  {$group: {_id: "$_id", name: {$first: "$name"},
                            items: {$push: "$items"}}}]
    }
)

> db.view_name.find({name: "a"})
{ "_id" : ObjectId("5fc3dbb69cb76f866582620f"), "name" : "a", "items" : [ [ "5", "1", "2" ], [ "6", "3", "7" ] ] }

> db.view_name.find({"items": {$in: [["5", "1", "2"]]}})
{ "_id" : ObjectId("5fc3dbb69cb76f866582620f"), "name" : "a", "items" : [ [ "5", "1", "2" ], [ "6", "3", "7" ] ] }

> db.view_name.find()
{ "_id" : ObjectId("5fc3dbb69cb76f866582620f"), "name" : "a", "items" : [ [ "5", "1", "2" ], [ "6", "3", "7" ] ] }

Query:

db.original_collection_name.aggregate([
    {$unwind: "$items"},
    {$project: {name: 1, items: {$objectToArray: "$items"}}}, 
    {$project: {name: 1, items: {$concatArrays: ["$items.v"]}}}, 
    {$group: {_id: "$_id", name: {$first: "$name"}, items: {$push: "$items"}}}])
user_26998060

Using $objectToArray and $map transformations:

// { name: "a", items: [ { a: "5", b: "1", c: "2" }, { a: "6", b: "3", c: "7" } ] }
db.collection.aggregate([

  { $set: { items: { $map: { input: "$items", as: "x", in: { $objectToArray: "$$x" } } } } },
  // {
  //   name: "a",
  //   items: [
  //     [ { k: "a", v: "5" }, { k: "b", v: "1" }, { k: "c", v: "2" } ],
  //     [ { k: "a", v: "6" }, { k: "b", v: "3" }, { k: "c", v: "7" } ]
  //   ]
  // }

  { $set: { items: { $map: { input: "$items", as: "x", in: "$$x.v" } } } }
])
// { name: "a", items: [["5", "1", "2"], ["6", "3", "7"]] }

This maps items' elements as key/value arrays such that { field: "value" } becomes [ { k: "field", v: "value" } ]. This way whatever the field name, we can easily access the value using v, which is the role of the second $set stage: "$$x.v".

This has the benefit of avoiding heavy stages such as unwind/group.

Note that you can also imbricate the second $map within the first; but that's probably less readable.

user_92971440
Related