Perform Aggregations using JSON1 and SQLite3 in Json Objects

Question

I just started using SQLite 3 with JSON1 support.

I already have created a database that consists of several attributes. One of these attributes is a json object.

What I want to do is perform aggregations within this object.

Running the following query:

select json_extract(report, '$.some_attribute')
from table1
group by var1
having date == max(date);

returns the following:

[{"count":55,"label":"A"},{"count":99,"label":"B"}, {"count":1,"label":"C"}]
[{"count":29,"label":"A"},{"count":285,"label":"B"},{"count":461,"label":"C"}]
[{"count":6642,"label":"A"},{"count":24859,"label":"B"},{"count":3031,"label":"C"}]
[{"count":489,"label":"A"},{"count":250,"label":"B"},{"count":74,"label":"C"}]

Now, what I want to do is to group by the label key and for example, sum the count key.

The output should be something like this:

[{"label": A, 'count': 7215},
 {"label": B, 'count': 25493},
 {"label": C, 'count': 3567}]

OR this:

  A,       B,     C
7215,    25493,  3567

I've tried to implement the latter one like this:

select sum(A) as A, sum(B) as B, sum(C) as C
from (
select json_extract(report,
                '$.some_attribute[0].count') as A,
       json_extract(report,
                '$.some_attribute[1].count') as B,
       json_extract(report,
                '$.some_attribute[0].count') as C
from table1
group by var1
having date == max(date));

The thing is, how can you be sure that all the objects in the array will be sorted the same way. So this may cause problems.

Any solutions? thanks!

Answer 1

If you "un-nest" the json strings returned from the first json_extract,as with json_each, it becomes trivial. This worked in my repro:

WITH result as (select jsonString from jsonPlay)
select json_extract(value,'$.label') label,SUM(json_extract(value,'$.count'))
from result,json_each(jsonString) 
group by label

Giving this result:

 A| 7215
 B| 25493
 C| 3567

Basically, your select json_extract(report, '$.some_attribute') block replaces select jsonString from jsonPlay

You could use this to "columnize" it, as in your OR option.

WITH result as (select jsonString from jsonPlay) 
select SUM(CASE WHEN json_extract(value,'$.label')='A' then json_extract(value,'$.count') END) 'A', 
SUM(CASE WHEN json_extract(value,'$.label')='B' then json_extract(value,'$.count') END) 'B', 
SUM(CASE WHEN json_extract(value,'$.label')='C' then json_extract(value,'$.count') END) 'C'
from result,json_each(jsonString)