WITH example(data) as
(
VALUES
(json '{"result":[{"name":"Jarret","score":"90"},{"name":"Blanche","score":"95"}]}'),
(json '{"result":[{"name":"Blanche","score":"76"},{"name":"Jarret","score":"88"}]}')
)
SELECT n.name as "Student Name", avg(n.score) as "Average Score"
FROM example
CROSS JOIN
UNNEST (
CAST (JSON_EXTRACT(data, '$.result')
as ARRAY(ROW(name VARCHAR, score INTEGER )))
) as n
--WHERE n.name='Jarret'
GROUP BY n.name;
11-16
2005
![](https://csdnimg.cn/release/blogv2/dist/pc/img/readCountWhite.png)
09-29
3038
![](https://csdnimg.cn/release/blogv2/dist/pc/img/readCountWhite.png)
05-16
476
![](https://csdnimg.cn/release/blogv2/dist/pc/img/readCountWhite.png)