Get ids of EXCEPTed documents

SQL++
1

I run a query similar to this:

SELECT event.visitId, elementValue.dataOption as vdo FROM bucket event
UNNEST event.elementValues elementValue
WHERE event.`type` = 'EXAM'
AND elementValue.`type` = 'EXAM_TYPE'

EXCEPT

SELECT event.visitId, selectedValue as vdo FROM bucket event
UNNEST event.elementValues elementValue
UNNEST elementValue.selectedValues selectedValue
WHERE event.`type` = 'REQUEST'
AND elementValue.`type`= 'REQUEST_EXAMS'

And I would like to get the ids of the returned values but I can’t add it to the SELECT statement otherwhise the EXCEPT won’t work anymore. Is their any workaround?

Here are some sample documents (they have been handwritten for this use-case):

{
	"_id": "6a90946b-9f54-43e7-ab9a-9c9ac29b4e0e",
	"type": "EXAM",
	"elementValues": [
		{
			"type": "EXAM_VALUE"
			"value": "++"
		},
		{
			"type": "EXAM_TYPE"
			"dataOption": "TDR"
		}
	],
	"visitId": "0aa55d87-5a18-4b0e-b328-913479108bad"
},
{
	"_id": "45f13433-9e35-4ea1-a430-56de6a811a68",
	"type": "EXAM",
	"elementValues": [
		{
			"type": "EXAM_VALUE"
			"value": "O"
		},
		{
			"type": "EXAM_TYPE"
			"dataOption": "GR_SANG"
		}
	],
	"visitId": "0aa55d87-5a18-4b0e-b328-913479108bad"
},
{
	"_id": "72768a3e-d92c-4b65-a1ec-67a7527fc0ba",
	"type": "EXAM",
	"elementValues": [
		{
			"type": "EXAM_VALUE"
			"value": "--"
		},
		{
			"type": "EXAM_TYPE"
			"dataOption": "HEMO"
		}
	],
	"visitId": "0aa55d87-5a18-4b0e-b328-913479108bad"
},
{
	"_id": "965396be-5a4b-4de9-9366-94d9216e31ac",
	"type": "REQUEST",
	"elementValues": [
		{
			"type": "REQUEST_EXAMS"
			"selectedValues": [
				"TDR",
				"GR_SANG"
			]
		}
	],
	"visitId": "0aa55d87-5a18-4b0e-b328-913479108bad"
},
{
	"_id": "e2e9c817-1d8a-46b4-99b6-8f14f208e809",
	"type": "EXAM",
	"elementValues": [
		{
			"type": "EXAM_VALUE"
			"value": "++"
		},
		{
			"type": "EXAM_TYPE"
			"dataOption": "TDR"
		}
	],
	"visitId": "12d6c90e-d0b6-4f1f-a6a8-3a72acda4d82"
}

The document keys I would expect to receive are 72768a3e-d92c-4b65-a1ec-67a7527fc0ba and e2e9c817-1d8a-46b4-99b6-8f14f208e809 because they aren’t any requests with the same visitId and a selectedValue containing the dataOption of the EXAM_TYPE elementValue.

Hope this is clear.

Many thanks.

2

Do you want to return document keys?
Can you post sample documents with fields referenced in queries.
Do you except multiple documents with same visitId

3

Yes when I say “ids of the returned values” I mean document keys.
And yes multiple documents can have the same visitId

You can find sample documents and the expected result in my updated question.

4

Hi @martin.hogge

INSERT INTO default VALUES
(“6a90946b-9f54-43e7-ab9a-9c9ac29b4e0e”, { “_id”: “6a90946b-9f54-43e7-ab9a-9c9ac29b4e0e”, “type”: “EXAM”, “elementValues”: [ { “type”: “EXAM_VALUE”, “value”: “++” }, { “type”: “EXAM_TYPE”, “dataOption”: “TDR” } ], “visitId”: “0aa55d87-5a18-4b0e-b328-913479108bad” }),
(“45f13433-9e35-4ea1-a430-56de6a811a68”, { “_id”: “45f13433-9e35-4ea1-a430-56de6a811a68”, “type”: “EXAM”, “elementValues”: [ { “type”: “EXAM_VALUE”, “value”: “O” }, { “type”: “EXAM_TYPE”, “dataOption”: “GR_SANG” } ], “visitId”: “0aa55d87-5a18-4b0e-b328-913479108bad” }),
(“72768a3e-d92c-4b65-a1ec-67a7527fc0ba”,{ “_id”: “72768a3e-d92c-4b65-a1ec-67a7527fc0ba”, “type”: “EXAM”, “elementValues”: [ { “type”: “EXAM_VALUE”, “value”: “XX” }, { “type”: “EXAM_TYPE”, “dataOption”: “HEMO” } ], “visitId”: “0aa55d87-5a18-4b0e-b328-913479108bad” }),
(“965396be-5a4b-4de9-9366-94d9216e31ac”, { “_id”: “965396be-5a4b-4de9-9366-94d9216e31ac”, “type”: “REQUEST”, “elementValues”: [ { “type”: “REQUEST_EXAMS”, “selectedValues”: [ “TDR”, “GR_SANG” ] } ], “visitId”: “0aa55d87-5a18-4b0e-b328-913479108bad” }),
(“e2e9c817-1d8a-46b4-99b6-8f14f208e809”,{ “_id”: “e2e9c817-1d8a-46b4-99b6-8f14f208e809”, “type”: “EXAM”, “elementValues”: [ { “type”: “EXAM_VALUE”, “value”: “++” }, { “type”: “EXAM_TYPE”, “dataOption”: “TDR” } ], “visitId”: “12d6c90e-d0b6-4f1f-a6a8-3a72acda4d82” });

SELECT e.visitId, ex.k AS id, missedexams FROM (
               SELECT event.visitId,  ex AS exams, exr AS reqexams
               FROM default event
               WHERE ((event.`type` = 'EXAM' AND ANY ev IN event.elementValues SATISFIES ev.`type` = 'EXAM_TYPE' END) OR
                     (event.`type` = 'REQUEST' AND ANY ev IN event.elementValues SATISFIES ev.`type` = 'REQUEST_EXAMS' END))
               GROUP BY event.visitId
               LETTING ex = ARRAY evx FOR evx IN (ARRAY_AGG(CASE WHEN event.`type` = 'EXAM' THEN {"k":META(event).id, "v": ARRAY_FLATTEN(ARRAY v.dataOption FOR v IN event.elementValues WHEN v.`type` = 'EXAM_TYPE' END,2)} END)) WHEN evx IS VALUED END,
                       exr = ARRAY evr FOR evr IN (ARRAY_FLATTEN(ARRAY_AGG(CASE WHEN event.`type` = 'REQUEST' THEN ARRAY vr.selectedValues FOR vr IN event.elementValues WHEN vr.`type` = 'REQUEST_EXAMS' END END),3)) WHEN evr IS VALUED END
              ) AS e UNNEST e.exams AS ex
LET missedexams = ARRAY me FOR me IN ex.v WHEN me NOT IN e.reqexams END
WHERE ARRAY_LENGTH(missedexams) > 0;
  • Filter the EXAM, REQUET documents
  • GROUP the results by visitId
  • Aggregate all the Requested exams, Aggregates all the EXAM documents with document id and exams needs to be taken
  • remove the MISSING/NULL values from aggregates
  • Unnest EXAM documents filetr out exams needs to be taken in requested exams