How can i get only a doc in array as well as update

SQL++
1

I have a Activity Doc which keeps track of users activity’s and goals etc and I want to be able to get only the name and value of a key and date. So in my query I want to provide the date, the key and the userid. The blow gets me the correct Data.

SELECT d.date,
k.`key`,
       k.name,
       k.`value`
FROM Contacts c
UNNEST c.Data.daily AS d
UNNEST d.metrics AS k
WHERE c._type = "activity_dashboard"
    AND d.date = "2021-11-01T00:00:00Z"
    AND c.userid = "8D6D24A5-D669-45DC-99AC-F257BDA133A4"
    AND k.`key` = 'c28f7ead-d87b-4ad5-b6b3-1f204b013b50'

The question is, is this the most effective way to do so or is there a better way. Also how would i go about if i want to update the value of this key ? Since UNNEST does not work in updates.

Below is some sample DATA

{
	"_id" : "1fb898be-6438-4e8c-8eeb-c74745a73683",
	"_type" : "activity_dashboard",
	"userid" : "8D6D24A5-D669-45DC-99AC-F257BDA133A4",
	"Data": {
		"daily" : [
			{ "date" : "2021-11-01T00:00:00Z",
			"metrics" : [
				{"key" : "8646ec5d-7a72-49bd-9a68-cf326d1c4a14", "name" : "Calls Made", "value" : 0},
				{"key" : "c28f7ead-d87b-4ad5-b6b3-1f204b013b50", "name" : "Notes Written", "value" : 0},
				{"key" : "d0181c74-22a9-4f99-9cc9-df3467c51805", "name" : "Pop-Bys Delivered", "value" : 0},
				{"key" : "90d142ea-6748-4781-b2b9-4f05aab12956", "name" : "Database Additions", "value" : 0},
				{"key" : "723e95dd-8c47-48ed-b9c3-1b010b092a1b", "name" : "Referrals Given", "value" : 0},
				{"key" : "0f054686-ef13-4993-ac5b-f640ceeaaa8d", "name" : "Referrals Received", "value" : 0}
				]
			},
			{ "date" : "2021-10-31T00:00:00Z",
			"metrics" : [
				{"key" : "8646ec5d-7a72-49bd-9a68-cf326d1c4a14", "name" : "Calls Made", "value" : 0},
				{"key" : "c28f7ead-d87b-4ad5-b6b3-1f204b013b50", "name" : "Notes Written", "value" : 0},
				{"key" : "d0181c74-22a9-4f99-9cc9-df3467c51805", "name" : "Pop-Bys Delivered", "value" : 0},
				{"key" : "90d142ea-6748-4781-b2b9-4f05aab12956", "name" : "Database Additions", "value" : 0},
				{"key" : "723e95dd-8c47-48ed-b9c3-1b010b092a1b", "name" : "Referrals Given", "value" : 0},
				{"key" : "0f054686-ef13-4993-ac5b-f640ceeaaa8d", "name" : "Referrals Received", "value" : 0}
				]
			}
		]}
}
2

That is more effective way to do.

If you want do Covering index it is much complex due to multiple fields form ARRAY are used. Also Array Index can ballon. If you want try (check query is covered)

CREATE INDEX ix1 ON Contacts(ALL ARRAY (ALL ARRAY [userid, k.`key`, d.date, k.name, k.`value`]
                                         FOR k IN d.metrics END)
                             FOR d IN Data.daily END)
WHERE _type = "activity_dashboard";

SELECT fltr[1] AS key, fltr[2] AS date, fltr[3] AS name, fltr[4] AS val
FROM Contacts AS c
UNNEST c.Data.daily AS d
UNNEST d.metrics AS k
LET fltr = [c.userid, k.`key`, d.date, k.name, k.`value`]
WHERE c._type = "activity_dashboard"
    AND fltr >= ["8D6D24A5-D669-45DC-99AC-F257BDA133A4", "c28f7ead-d87b-4ad5-b6b3-1f204b013b50","2021-11-01T00:00:00Z"]
   AND fltr < ["8D6D24A5-D669-45DC-99AC-F257BDA133A4", "c28f7ead-d87b-4ad5-b6b3-1f204b013b50",SUCCESSOR("2021-11-01T00:00:00Z")];

UPDATE Contacts AS c
SET k.`value` = 5
    FOR k IN d.metrics
         FOR d IN c.Data.daily WHEN d.date = "2021-11-01T00:00:00Z" AND k.`key` = "c28f7ead-d87b-4ad5-b6b3-1f204b013b50" END
WHERE c._type = "activity_dashboard"
    AND [c.userid, k.`key`, d.date, k.name, k.`value`] >= ["8D6D24A5-D669-45DC-99AC-F257BDA133A4", "c28f7ead-d87b-4ad5-b6b3-1f204b013b50","2021-11-01T00:00:00Z"]
   AND [c.userid, k.`key`, d.date, k.name, k.`value`] < ["8D6D24A5-D669-45DC-99AC-F257BDA133A4", "c28f7ead-d87b-4ad5-b6b3-1f204b013b50",SUCCESSOR("2021-11-01T00:00:00Z")];