Arangodb groupby query multiple fields

Question

I have a database of products in an arangodb collection in which a product has multiple sizes. The issue is that for each size, the same product is repeated. But each product has a common group number. Like this:

{"name": "product1", "description": "someDescription", size: 5,price: 12 groupNumber: 12}
{"name": "product1", "description": "someDescription", size: 15, price: 26, groupNumber: 12}
{"name": "product1", "description": "someDescription", size: 25, price: 84, groupNumber: 12}
{"name": "product1", "description": "someDescription", size: 35, price: 106, groupNumber: 12}

{"name": "product2", "description": "someDescription", size: 5, price: 12, groupNumber: 11}
{"name": "product2", "description": "someDescription", size: 15, price: 22, groupNumber: 11}
{"name": "product2", "description": "someDescription", size: 25, price: 32, groupNumber: 11}
{"name": "product2", "description": "someDescription", size: 35, price: 43, groupNumber: 11}

I have to now display the list of products(in a web page) but each product should appear only once with sizes and prices in an array for each product like this:

product1 someDescription sizes: 5,15,25,35, prices: 12,26,84,106
product2 someDescription sizes: 5,15,25,35, prices: 12,22,32,43
...

How do I do it?

Given your reputation, you should know that some kind of interaction would be expected in this community. Did my answer help? Was I wrong? Without confirmation from your side, this Q&A is mostly worthless for other visitors. — Tom Regner
@TomRegner I don't know if your answer was wrong or right. It looks correct though.I did it in a different way which I would put as the answer in some time. Enjoy the upvote in the meanwhile :) — rahulserver
It's not so much the upvote I'm interested in, feel free to retract it. aql/arango is not a very active sub-group here on SO, the quality of the Q/As is of more concern to me -- and if I take the time so setup a testcase and try solve a problem, I appreciate a little time given back to enhance the usefulness of our combined effort. — Tom Regner

Tom Regner Tom Regner · Accepted Answer · 2020-02-20T09:13:07

Ignoring the groupNumber and grouping by name, the query looks like this:

FOR p IN products
  COLLECT description = p.description, name = p.name INTO groups
  RETURN { 
    "name" : name, 
    "description": description,
    "prices" : groups[*].p.price,
    "sizes" : groups[*].p.size
  }

Given your (corrected) example data, the query returns:

[
  {
    "name": "product1",
    "description": "someDescription",
    "prices": [
      12,
      84,
      106,
      26
    ],
    "sizes": [
      5,
      25,
      35,
      15
    ]
  },
  {
    "name": "product2",
    "description": "someDescription",
    "prices": [
      43,
      32,
      22,
      12
    ],
    "sizes": [
      35,
      25,
      15,
      5
    ]
  }
]

The grouped values aren't sorted, but the positions of sizes and prices correspond, you can alleviate this fact to zip the values into a size-price map:

FOR p IN products
  COLLECT description = p.description, name = p.name INTO groups
  RETURN { 
    "name" : name, 
    "description": description,
    "size_price_map" : ZIP(groups[*].p.size, groups[*].p.price)
  }

yielding:

[
  {
    "name": "product1",
    "description": "someDescription",
    "size_price_map": {
      "5": 12,
      "15": 26,
      "25": 84,
      "35": 106
    }
  },
  {
    "name": "product2",
    "description": "someDescription",
    "size_price_map": {
      "5": 12,
      "15": 22,
      "25": 32,
      "35": 43
    }
  }
]

Arangodb groupby query multiple fields

1 Answers