Delete array items by comparing with another document array

SQL++
1

I have several documents with meta().id like lcontainer_6b693f242e14f3ab0d6b95cf59089b52
All of them has same structure like attached.
One of them is master document (say container_e6b693f242e14f3ab0d6b95cf59089b5) This contains large number of parts - many of which are available in other documents as well.
I need to delete all parts in this document which are found in other documents.

The problem is I can identify the uniqueness of a part only by combination of partNumber, lotCode & ticks inside expirationDate.

I tried to convert to excel and do a vlookup of combination and then deleted the common parts and then tried to reconstruct the json, But i am not able to reconstruct the nested document for expirationDate.

Please let me know if its is possible to form a join query using multiple keys I mentioned above and use it remove the common parts.

{
“currentLocation”: {
“displayName”: “JZ MEDICAL INC-704001001”,
“href”: “location_d3210e7a1dec4c6584e90e1079ab8392”
},
“homeLocation”: {
“displayName”: “JZ MEDICAL INC-704001001”,
“href”: “location_d3210e7a1dec4c6584e90e1079ab8392”
},
“name”: “Volume Test 2104 Bin”,
“parts”: [
{
“expirationDate”: {
“ticks”: 1609372800000,
“tz”: “Etc/UTC”
},
“lotCode”: “730551-2104”,
“name”: “SIGMA HP UNI INS SZ3 11MM LMRL”,
“partNumber”: “102453311”
}
],
“reservable”: “false”,
“serialNumber”: “B-VOLUME_2104”
}

UPDATE default cont
SET cont.parts = (SELECT RAW p FROM cont.parts AS p where parts not in (select RAW p from default Where meta().id=‘container_6b693f242e14f3ab0d6b95cf59089b52_two’))
WHERE META(cont).id =‘container_e6b693f242e14f3ab0d6b95cf59089b5_one’

2 
UPDATE  default AS d USE KEYS ["container_e6b693f242e14f3ab0d6b95cf59089b5"]
SET d.cont.parts = ARRAY v FOR v IN d.cont.parts
                   WHEN [v.partNumber,v.lotCode,v.expirationDate.ticks] NOT IN
                             ARRAY_FLATTEN((SELECT RAW ARRAY_AGG([sp.partNumber,sp.lotCode,sp.expirationDate.ticks])
                                            FROM default AS s
                                            UNNEST s.cont.parts AS sp
                                            WHERE META(s).id != "container_e6b693f242e14f3ab0d6b95cf59089b5"
                                            AND s.type = "parts")),1)
;
3

Wrong number of arguments to function ARRAY_FLATTEN

I tried to move the last few braces and the arguments but some syntax keeps appearing.

Also trying to understand the last line: There is no key “type” in this document.

AND s.type = “parts”

4 
UPDATE  default AS d USE KEYS ["container_e6b693f242e14f3ab0d6b95cf59089b5"]
SET d.cont.parts = ARRAY v FOR v IN d.cont.parts
                   WHEN [v.partNumber,v.lotCode,v.expirationDate.ticks] NOT IN
                             ARRAY_FLATTEN((SELECT RAW ARRAY_AGG([sp.partNumber,sp.lotCode,sp.expirationDate.ticks])
                                            FROM default AS s
                                            UNNEST s.cont.parts AS sp
                                            WHERE META(s).id != "container_e6b693f242e14f3ab0d6b95cf59089b5"),1) END ;
5

I have modified the sub query as per my requirement - see below, It is giving me results, However the update statement is not removing the duplicates:

SELECT ARRAY_FLATTEN(( SELECT RAW ARRAY_AGG([sp.partNumber,sp.lotCode,sp.expirationDate.ticks])
FROM default AS s
UNNEST s.parts AS sp
WHERE META(s).id like “container_%” and s.serialNumber like ‘B-264575002_%’),1)

Do I have to get rid of ARRAY_FLATTEN and match directly?

6

This query is not functioning either:

UPDATE default AS d USE KEYS [“container_e6b693f242e14f3ab0d6b95cf59089b5”]
SET d.cont.parts = ARRAY v FOR v IN d.cont.parts
WHEN [to_string(sp.partNumber) || to_string(sp.lotCode) || to_string(sp.expirationDate.ticks)] NOT IN
(SELECT RAW [to_string(sp.partNumber) || to_string(sp.lotCode) || to_string(sp.expirationDate.ticks)]
FROM default AS s
UNNEST s.parts AS sp
WHERE META(s).id like “container_%” and s.serialNumber like ‘B-264575002_%’) END

7

In WHEN clause variable needs to be v not sp.
Also check CONCAT is right operation for your use case. Ex: “12”||“3” is same as “1”|| “23”

UPDATE default AS d USE KEYS ["container_e6b693f242e14f3ab0d6b95cf59089b5"]
SET d.cont.parts = ARRAY v FOR v IN d.cont.parts
WHEN [TO_STRING(v.partNumber), TO_STRING(v.lotCode), TO_STRING(v.expirationDate.ticks)] NOT IN
       (SELECT RAW [TO_STRING(sp.partNumber), TO_STRING(sp.lotCode), TO_STRING(sp.expirationDate.ticks)]
        FROM default AS s UNNEST s.parts AS sp
        WHERE META(s).id like "container_%" and s.serialNumber like "B-264575002_%") END;

UPDATE default AS d USE KEYS ["container_e6b693f242e14f3ab0d6b95cf59089b5"]
SET d.cont.parts = ARRAY v FOR v IN d.cont.parts
WHEN {v.partNumber, v.lotCode, v.expirationDate.ticks} NOT IN
       (SELECT sp.partNumber, sp.lotCode, sp.expirationDate.ticks
        FROM default AS s UNNEST s.parts AS sp
        WHERE META(s).id like "container_%" and s.serialNumber like "B-264575002_%") END
8

Modified slightly and it worked! Thanks