How to SUM and GROUP BY of JSON data?

最新推荐文章于 2025-05-02 11:32:49 发布

转载最新推荐文章于 2025-05-02 11:32:49 发布 · 1.7k 阅读

Python 专栏收录该内容

2 篇文章

订阅专栏

本文介绍如何将SQL查询应用于JSON格式的数据，通过JavaScript实现对JSON对象进行分组、求和及排序的操作，以达到类似SQL查询的效果，并提供实际应用案例。

How to SUM and GROUP BY of JSON data?

Source: StackOverflow.com

Question

Some server-side code actually generates a JSON formatted string, which converts to JSON object:

    var dataString='[ 
      { "category" : "Search Engines", "hits" : 5, "bytes" : 50189 },
      { "category" : "Content Server", "hits" : 1, "bytes" : 17308 },
      { "category" : "Content Server", "hits" : 1, "bytes" : 47412 },
      { "category" : "Search Engines", "hits" : 1, "bytes" : 7601 },
      { "category" : "Business", "hits" : 1, "bytes" : 2847 },
      { "category" : "Content Server", "hits" : 1, "bytes" : 24210 },
      { "category" : "Internet Services", "hits" : 1, "bytes" : 3690 },
      { "category" : "Search Engines", "hits" : 6, "bytes" : 613036 },
      { "category" : "Search Engines", "hits" : 1, "bytes" : 2858 } 
    ];

I need to the equivalent of an SQL statement like this:

    SELECT category, sum(hits), sum(bytes) 
    FROM dataObject
    GROUP BY category
    ORDER BY sum(bytes) DESC

The desired output would be an object like this:

    var aggregatedObject='[ 
        { "category" : "Search Engines", "hits" : 13, "bytes" : 673684 },
        { "category" : "Content Server", "hits" : 3, "bytes" : 88930 },
        { "category" : "Internet Services", "hits" : 1, "bytes" : 3690 },
        { "category" : "Business", "hits" : 1, "bytes" : 2847 } 
    ]';

Answer

This is an example with SQL query from your question:

    var data = [ { "category" : "Search Engines", "hits" : 5, "bytes" : 50189 },...;

    var res = alasql('SELECT category, sum(hits) AS hits, sum(bytes) as bytes \
       FROM ? \
       GROUP BY category \
       ORDER BY bytes DESC',[data]);