Array objects donot satisfy the specific condition should get ignored in the result. How?

SQL++
,
1

Way my doc looks like (Sample) -

Blockquote {
“_class”: “com.ibm.maas360.cloud.notif.services.model.DeviceMailBoxDocument”,
“billingId”: “12345”,
“mailboxes”: [
{
“mailboxId”: “mailbox1”,
“notifFailedAttemptCount”: 0,
“notifTime”: 1547128809771,
“pendingNotifCount”: 1,
“subscriptionId”: “id1”,
“subscriptionTime”: 1547127901328
}
]
}

What I tried -

  1. UNNEST - doesnot give appropriate result (maybe I was implementing it in wrong way)
  2. ANY - in this if any of the array objects satisfies the condition, it will take whole array in result
  3. EVERY - in this if even a single object did not satisfy, it will skip the whole doc

Assuming, I have around 100’s or 1k’s of doc as above. I want my query should result all the doc which has array object(s) satisfy some condition subject to if array is empty it should ignored…

Condition - All the docs with mailbox pendingNotifCount > 0

Stored documents in DB -

Blockquote ##doc-1
{
“_class”: “com.ibm.maas360.cloud.notif.services.model.DeviceMailBoxDocument”,
“billingId”: “12345”,
“mailboxes”: [
{
“mailboxId”: “mailbox1”,
“notifFailedAttemptCount”: 0,
“notifTime”: 1547128809771,
“pendingNotifCount”: 4,
“subscriptionId”: “id1”,
“subscriptionTime”: 1547127901328
},
{
“mailboxId”: “mailbox2”,
“notifFailedAttemptCount”: 0,
“notifTime”: 1547128809779,
“pendingNotifCount”: 0,
“subscriptionId”: “id2”,
“subscriptionTime”: 1547127901328
}
]
}
##doc-2
{
“_class”: “com.ibm.maas360.cloud.notif.services.model.DeviceMailBoxDocument”,
“billingId”: “12345”,
“mailboxes”: [
{
“mailboxId”: “mailbox11”,
“notifFailedAttemptCount”: 0,
“notifTime”: 1547128809771,
“pendingNotifCount”: 1,
“subscriptionId”: “id11”,
“subscriptionTime”: 1547127901328
}
]
}
##doc-3
{
“_class”: “com.ibm.maas360.cloud.notif.services.model.DeviceMailBoxDocument”,
“billingId”: “12345”,
“mailboxes”: [
{
“mailboxId”: “mailbox21”,
“notifFailedAttemptCount”: 0,
“notifTime”: null,
“pendingNotifCount”: 0,
“subscriptionId”: “id21”,
“subscriptionTime”: 1547127901328
},
{
“mailboxId”: “mailbox12”,
“notifFailedAttemptCount”: 0,
“notifTime”: null,
“pendingNotifCount”: 0,
“subscriptionId”: “id2”,
“subscriptionTime”: 1547127901328
}
]
}

Expected Result -

Blockquote [
{
“_class”: “com.ibm.maas360.cloud.notif.services.model.DeviceMailBoxDocument”,
“billingId”: “12345”,
“mailboxes”: [
{
“mailboxId”: “mailbox1”,
“notifFailedAttemptCount”: 0,
“notifTime”: 1547128809771,
“pendingNotifCount”: 4,
“subscriptionId”: “id1”,
“subscriptionTime”: 1547127901328
}
]
},
{
“_class”: “com.ibm.maas360.cloud.notif.services.model.DeviceMailBoxDocument”,
“billingId”: “12345”,
“mailboxes”: [
{
“mailboxId”: “mailbox11”,
“notifFailedAttemptCount”: 0,
“notifTime”: 1547128809771,
“pendingNotifCount”: 1,
“subscriptionId”: “id11”,
“subscriptionTime”: 1547127901328
}
]
}
]

2 
SELECT d.*, ARRAY v FOR v IN d.mailboxes WHEN v.pendingNotifCount > 0 END AS mailboxes
FROM default AS d
WHERE d._class = "com.ibm.maas360.cloud.notif.services.model.DeviceMailBoxDocument"
                    AND ANY v IN d.mailboxes SATISFIES v.pendingNotifCount > 0 END;
1 Like
3

Thanks @vsr1 for quick response.

4

Important point to remember here is that the concept of array is new to Relational. N1QL provides support for array predicate in both query, as well as the projection list. The syntax is a slightly different for each area.