Analytical Service map collection query

Analytics
1

I have issue working with map collection from Analytical service
my json schema is :
{
“upsertDate”: “2020-02-27T20:25:25”,
“partyId”: “42”,
“docType”: “detail”,
“details”: {
“Mock1”: {
“accountId”: 123,
“dType”: “Mock1”,
“mandatory”: false,
“status”: “OPEN”
},
“Mock2”: {
“accountId”: 123,
“dType”: “Mock2”,
“mandatory”: false,
“status”: “COMPLETED”
}
}
}

When I run this query in query Service, it is working
SELECT dp.name, SUM( CASE WHEN dp.val.status = “OPEN” THEN 1 ELSE 0 END) AS open_sum,
SUM( CASE WHEN dp.val.status = “COMPLETED” THEN 1 ELSE 0 END) AS completed_sum
FROM bucketcb AS d
UNNEST OBJECT_PAIRS(d.details) AS dp
WHERE d.docType = ‘detail’
GROUP BY dp.name

When I run same it is not working as expected. I’m not getting value for dp.val.status , so it is getting value of open_sum as 0

Can anyone please advise. Thank you!!

2

@rajv
The dp.val.status field should have been dp.`value`.status (value is a keyword that needs to be enclosed in backticks).