How to merge array using UPSERT?

Question

I want to merge result from 3 collections into single collection but I can't merge finishedStatus attribute of final result.

The final result collection:

contains only unique status
each document can keep the machine that make this status
merge finishedStatus and then sum finishedStatus.count
(Optional) sort finishedStatus[*].status

Given

Collection A

 {"status": ["1","2","3"], "machine": "A", "finishedStatus": [{"status": "4", "count": 10}, {"status": "5", "count": 1}]}
 {"status": ["4","5","6"], "machine": "A", "finishedStatus": [{"status": "3", "count": 5}, {"status": "5", "count": 11}]}

Collection B

{"status": ["1","2","3"], "machine": "B", "finishedStatus": [{"status": "1", "count": 3}, {"status": "5", "count": 14}]}
{"status": ["2","5","2"], "machine": "B", "finishedStatus": [{"status": "5", "count": 5}, {"status": "3", "count": 5}]}

Collection C

{"status": ["2","5","2"], "machine": "C", "finishedStatus": [{"status": "5", "count": 2}, {"status": "3", "count": 5}]}
{"status": ["3","2","1"], "machine": "C", "finishedStatus": [{"status": "2", "count": 6}, {"status": "4", "count": 7}]}

Collection result

{"status": ["1","2","3"], "machine": ["A", "B"] , "finishedStatus": [{"status": "1", "count": 3}, {"status": "4", "count": 10}, {"status": "5", "count": 15}]}
{"status": ["2","5","2"], "machine": ["B", "C"], "finishedStatus": [{"status": "5", "count": 7}, {"status": "3", "count": 10}]}
{"status": ["3","2","1"], "machine": ["C"], "finishedStatus": [{"status": "2", "count": 6}, {"status": "4", "count": 7}]}
{"status": ["4","5","6"], "machine": ["A"], "finishedStatus": [{"status": "3", "count": 5}, {"status": "5", "count": 11}]}

How to write AQL INSERT/UPDATE/UPSERT to make the final result?

This is my AQL

FOR doc IN A
UPSERT {"status": doc.status}
INSERT {"status": doc.status, "machine": [doc.machine], "finishedStatus": doc.finishedStatus}
UPDATE {
  "machine": APPEND(OLD.machine, doc.machine, true),
  "finishedStatus": <-- I cannot write AQL to update finishedStatus
     How to write AQL to update this property?
}
IN result

Thank you

David Thomas David Thomas · Accepted Answer · 2017-11-20T03:06:35

This this inline if/else format that AQL supports:

FOR doc IN A
UPSERT {"status": doc.status}
INSERT {"status": doc.status, "machine": [doc.machine], "finishedStatus": doc.finishedStatus}
UPDATE {
  "machine": APPEND(OLD.machine, doc.machine, true),
  "finishedStatus": (
    OLD.finishedStatus[i].status == doc.finishedStatus[i].status ?
    SUM([OLD.finishedStatus[i].count, doc.finishedStatus[i].count]) : 
    APPEND(OLD.finishedStatus, doc.finishedStatus[i])
  )
}
IN result

I can't test this without your dataset, but notice the inline if-then-else format.

( comparator ? is_true : is_false )

(a == 1 ? 'one' : 'not one')

AQL will evaluate the if-then-else and then substitute it with either the 'true' or 'false' value you provided.

How to merge array using UPSERT?

1 Answers