Help : Need a Query to update a document value with result of other collection document

SQL++
1

Hi,
I’ve label and ticket collection in DB.
Each new ticket will be tagged with any of a label from label collection.

Each ticket will have label id and some of details about label.
And Each ticket has expiryTime as well.

label collection

// Sample label collection
[
    {
        id : 'label111',
        name : 'jan',
        taggedLabelcount : 0,
        type="label"

    },
    {
        id : 'label222',
        name : 'feb',
        taggedLabelcount : 0,
        type="label"
    }
]

ticket collection

// sample ticket collection
[
    {
        id : 'ticket111',
        label : {
            id : 'label111',
            name : 'jan',
            type="label"
        },
        expiryTime : '179800000223232232',
        type= "ticket"
    },
    {
        id : 'ticket222',
        label : {
            id : 'label111',
            name : 'jan',
            type="label"
        },
        expiryTime : '179800000223232232',
        type= "ticket"
    }
    },
    {
        id : 'ticket333',
        label : {
            id : 'label111',
            name : 'feb',
            type="label"
        },
        expiryTime : '179800000223232232',
        type= "ticket"
    }
]

My requirement is :

When querying list of labels from DB,
I need to get the count of a label used in tickets (should not account the ticket if its past time from now)
and to update it in taggedLabelcount property in each label.

I tired with join query and unable to produce expected response

Thanks

2 
MERGE INTO label AS l 
USING  (SELECT  t.label.id, COUNT(1) AS cnt
                    FROM ticket AS t WHERE  TO_NUMBER(t.expiryTime) > NOW_MILLIS() AND t.type = "ticket"
                    GROUP BY t.label.id) AS s
ON l.type = "label" AND l.id = s.id
WHEH MATCHED THEN UPDATE SET l.taggedLabelcount = s.cnt;

CREATE INDEX ix1 ON ticket(TO_NUMBER(expiryTime) , label.id) WHERE type = "ticket";
CREATE INDEX ix2 ON label(id) WHERE type = "label";
3

Aweasome & Thanks @vsr1, It working as expected.

Could you help me to tweak this QUERY with additional check before updating actual count.

little bit update on above Ticket documents ( folderPathAsId property added newly)

 [
    {
        id : 'ticket111',
        folderPathAsId : '../march/monday',  
        label : {
            id : 'label111',
            name : 'jan',
            type="label"
        },
        expiryTime : '179800000223232232',
        type= "ticket"
    },
    {
        id : 'ticket222',
        folderPathAsId : '../march/monday',
        label : {
            id : 'label111',
            name : 'jan',
            type="label"
        },
        expiryTime : '179800000223232232',
        type= "ticket"
    },
    {
        id : 'ticket333',
        folderPathAsId : '../april/sunday',
        label : {
            id : 'label111',
            name : 'feb',
            type="label"
        },
        expiryTime : '179800000223232232',
        type= "ticket"
    }
]

Another collection called folder which has below format

 [
    {
        id: 'folder111',
        folderPathAsId : '../march/monday',
        enabled : true,
        type="folder"
    },
    {
        id: 'folder222',
        folderPathAsId : '../april/sunday',
        enabled : false,
        type="folder"
    },
]

Each Ticket is mapped with respective folder in folder collection. Ticket has folderPathAsId property which will have the same value from folderPathAsId in each folder records (acting like secondary key).

Current Above Given query will update the count

 1. label ids are available in Tickets.

 2. ticket should not be expired.

Along with this check, we have to account ticket only if assigned folder is enabled

 3.  ticket assigned to some folder which should be enabled as true
 4.  if folder is disabled (enabled : false), will not account the count at the  end

I tried by modifying your query to add this new check. But i was facing “Indexing” issue

As i am new to sql, i could’t able to resolve this.

Thanks in advance

4 
MERGE INTO label AS l 
USING  (SELECT  t.label.id, COUNT(1) AS cnt
                    FROM ticket AS t  JOIN floder AS f  ON f.folderPathAsId  = t.folderPathAsId AND f.type = "floder" AND f.enabled = true
                    WHERE  TO_NUMBER(t.expiryTime) > NOW_MILLIS() AND t.type = "ticket" 
                    GROUP BY t.label.id) AS s
ON l.type = "label" AND l.id = s.id
WHEH MATCHED THEN UPDATE SET l.taggedLabelcount = s.cnt;

CREATE INDEX ix1 ON ticket(TO_NUMBER(expiryTime) , label.id, folderPathAsId) WHERE type = "ticket";
CREATE INDEX ix2 ON label(id) WHERE type = "label";
CREATE INDEX ix1 ON ticket(enabled, folderPathAsId) WHERE type = "floder";