How to do group by on repeated field in BigQuery

In BigQuery, I have created a table with the below schema

id                  INTEGER NULLABLE    
visits              INTEGER NULLABLE    
dimensions          RECORD  REPEATED    
dimensions.value    STRING  
dimensions.key      STRING  

How to get sum(visits) by grouping device and state values?

Example data:

{"id": 1, visits: 100, "dimensions": [{"key":"device","value":"mobile"}, {"key":"state","value":"CA"}]}
{"id": 1, visits: 500, "dimensions": [{"key":"device","value":"desktop"}, {"key":"state","value":"CA"}]}
{"id": 1, visits: 200, "dimensions": [{"key":"device","value":"mobile"}, {"key":"state","value":"NY"}]}
{"id": 2, visits: 100, "dimensions": [{"key":"device","value":"mobile"}, {"key":"state","value":"CA"}]}
{"id": 2, visits: 500, "dimensions": [{"key":"device","value":"desktop"}, {"key":"state","value":"CA"}]}
{"id": 2, visits: 200, "dimensions": [{"key":"device","value":"mobile"}, {"key":"state","value":"NY"}]}
{"id": 2, visits: 780, "dimensions": [{"key":"device","value":"desktop"}, {"key":"state","value":"NY"}]}

I want id, device, state, sum(visits) in the output.

I could do a group by using a single dimension with below query but do not know how to do it for multiple dimensions.

SELECT id,d.value, sum(visits) FROM dataset.tabe_name,UNNEST(dimensions) as d where d.key = "device" group by id, d.value LIMIT 1000

And also is it possible to write a generic query when key values are not known in advance?