JSON - multiple aggregations extremely slow

Question

Select col1
,sum(json_value(json_field,’$.col2’))
,sum(json_value(json_field,’$.col3’))
,sum(json_value(json_field,’$.col4’))
,sum(json_value(json_field,’$.col5’))
,avg(json_value(json_field,’$.col6’))
,max(json_value(json_field,’$.col7’))
From table1
Group by col1

The table has 78000 rows. Json_field has a json structure with 600 attributes. When I perform just one aggregation it takes less than 1 sec. The performance degrades with addition of each aggregation. The query takes 17 seconds which is not acceptable. When I perform each aggregation on its own the six queries finish in 6 sec. This tells me there is repeated parsing happening with each additional json_value.

I am new to json in sql server. Any help here would be of great value.

Thanks!

Answer 1

Try two other possibilities:

select col1,
   sum(json_value(j, '$.col2')),
   sum(json_value(j, '$.col3')),
   . . .
from Table1
cross apply (values (json_query(json_field, '$'))) t(j)
group by col1

and

select col1,
   sum(col2),
   sum(col3),
   . . .
from Table1
cross apply openjson(json_field) with 
    ( 
        col2 int '$.col2', 
        col3 int '$.col3',
        . . .
    )
group by col1