How to find and count unique values in nested JSON objects

How to find and count unique values in nested JSON objects?

Source: StackOverflow.com

Question

There is the following JavaScript object:

    {
      "business": [
    {
      "order_contents": [
        {
          "id": 83,
          "name": "product 1",
          "price": "1.99",
          "quantity": 1,
          "total": "1.99",
          "ingredients": [],
          "extras": []
        },
        {
          "id": 83,
          "name": "product 1",
          "price": "1.99",
          "quantity": 1,
          "total": "1.99",
          "ingredients": [],
          "extras": []
        },
        {
          "id": 83,
          "name": "product 1",
          "price": "1.99",
          "quantity": 1,
          "total": "1.99",
          "ingredients": [],
          "extras": []
        },
        {
          "id": 85,
          "name": "product 3",
          "price": "1.99",
          "quantity": 1,
          "total": "1.99",
          "ingredients": [],
          "extras": []
        },
        {
          "id": 83,
          "name": "product 1",
          "price": "1.99",
          "quantity": 1,
          "total": "1.99",
          "ingredients": [],
          "extras": []
        },
        {
          "id": 84,
          "name": "product 2",
          "price": "1.99",
          "quantity": 1,
          "total": "1.99",
          "ingredients": [],
          "extras": []
        },
        {
          "id": 83,
          "name": "product 1",
          "price": "1.99",
          "quantity": 1,
          "total": "1.99",
          "ingredients": [],
          "extras": []
        },
        {
          "id": 83,
          "name": "product 1",
          "price": "1.99",
          "quantity": 1,
          "total": "1.99",
          "ingredients": [],
          "extras": []
        },
        {
          "id": 83,
          "name": "product 1",
          "price": "1.99",
          "quantity": 1,
          "total": "1.99",
          "ingredients": [],
          "extras": []
        },
        {
          "id": 83,
          "name": "product 1",
          "price": "1.99",
          "quantity": 1,
          "total": "1.99",
          "ingredients": [],
          "extras": []
        },
        {
          "id": 83,
          "name": "product 1",
          "price": "1.99",
          "quantity": 1,
          "total": "1.99",
          "ingredients": [],
          "extras": []
        },
        {
          "id": 83,
          "name": "product 1",
          "price": "1.99",
          "quantity": 1,
          "total": "1.99",
          "ingredients": [],
          "extras": []
        },
        {
          "id": 84,
          "name": "product 2",
          "price": "1.99",
          "quantity": 1,
          "total": "1.99",
          "ingredients": [],
          "extras": []
        }
       ]
      }
     ]
    }

What i am trying to accomplish is when the order comes through a function scans the JSON and creates an array with each unique product name and adds 1 to the quantity each time.

Answer

You can group and aggregate arrays with the following code:

    var data = {"business": [...]};

    var res1 = alasql('SELECT id, FIRST(name) AS name, COUNT(*) AS cnt FROM ? GROUP BY id',
                     [data.business[0].order_contents]);

Try the example in jsFiddle.

Or for output like: {"product 1":10, "product 2": 23} you can use the following query:

    var res2 = alasql('SELECT INDEX name, COUNT(*) AS cnt FROM ? GROUP BY name'.
                      [data.business[0].order_contents]);