UNWIND with inline variable, what am i doing wrong?

[mv59_grid]

Hello,

I am generating factorized queries (i do a lot of similar queries in a short period of time).

My code generates these kind of queries :

Example 1 :

WITH [
{"params1_createdTime":"1671525588177","params3_id":"b67dc350da753f4f8fb1cf4de0ff600da47ea7e6b3f01a83951dc0c1a021cde3","properties4":"{nameUser:'',name:'Decision TOULOUSE SO 13 00171',Attr_xmlns:'http://www.xxxx.com/gsr',Attr_createur:'gsradm',Attr_description:'',Attr_dtCreation:'13/10/2022 14:19',Attr_dtModif:'13/10/2022 14:19',Attr_nom:'Decision TOULOUSE SO 13 00171',Attr_type:'151',Attr_xmlns_xsi:'http://www.w3.org/2001/XMLSchema-instance'}","params5_name":"Decision TOULOUSE SO 13 00171"}
] AS array 
UNWIND array as row 
MATCH (nodea1:Import:DOPLER:Maquette:ImportXML{createdTime:row.params1_createdTime}) 
MERGE (nodeb1:HypoComplexe:DOPLER:Maquette:ImportXML{id:row.params3_id})
   ON CREATE SET nodeb1 += row.properties4 
MERGE (nodea1)-[nodea1relnodeb1:GROUP]-(nodeb1) 
MERGE (nodea2:Decision:DOPLER:Maquette:ImportXML{name:row.params5_name})  
MERGE (nodea2)-[nodea2relnodeb1:IS_REFERENCED_BY]-(nodeb1)

Example 2 :

WITH [
{"params1_name":"Decision TOULOUSE SO 13 00171","params2_id":"d42587d57ffeb50233ababd155a2e94dd6ad2b052ae903e96f6be9cb8da336fc","properties3":"{nameUser:'',name:'HypoModifFixeTransfo',description:'',ouvrage:'SSVICY631',supprimerDephaseur:'false',Snominale:'170',lowStep:'1',Attr_type:'53',Attr_xsi_type:'HypoModifFixeTransfo'}","params4_id":"b67dc350da753f4f8fb1cf4de0ff600da47ea7e6b3f01a83951dc0c1a021cde3","properties5":"{nameUser:'',name:'Decision TOULOUSE SO 13 00171',Attr_xmlns:'http://www.xxxxx.com/gsr',Attr_createur:'gsradm',Attr_description:'',Attr_dtCreation:'13/10/2022 14:19',Attr_dtModif:'13/10/2022 14:19',Attr_nom:'Decision TOULOUSE SO 13 00171',Attr_type:'151',Attr_xmlns_xsi:'http://www.w3.org/2001/XMLSchema-instance'}","params6_name":"SSVICY631"},
{"params1_name":"Decision TOULOUSE SO-13-00061","params2_id":"8627a70d54f48c5e8f5e8b5ba9af5ff41b66cdc5ec0dca66093aacf740ce25f8","properties3":"{nameUser:'',name:'HypoModifFixeLiaison',description:'',ouvrage:'SSSULL31VLEMU',seuilsSaisons_seuilsSaison.0.seuils.seuil.Attr_position:'0',seuilsSaisons_seuilsSaison.0.seuils.seuil.Attr_tempo:'60',seuilsSaisons_seuilsSaison.0.seuils.seuil.Attr_valeur:'439.000000',seuilsSaisons_seuilsSaison.0.seuils.Attr_imap:'331.000000',seuilsSaisons_seuilsSaison.0.Attr_saison:'ETE',seuilsSaisons_seuilsSaison.1.seuils.seuil.Attr_position:'0',seuilsSaisons_seuilsSaison.1.seuils.seuil.Attr_tempo:'60',seuilsSaisons_seuilsSaison.1.seuils.seuil.Attr_valeur:'514.000000',seuilsSaisons_seuilsSaison.1.seuils.Attr_imap:'392.000000',seuilsSaisons_seuilsSaison.1.Attr_saison:'INTERSAISON1',seuilsSaisons_seuilsSaison.2.seuils.seuil.Attr_position:'0',seuilsSaisons_seuilsSaison.2.seuils.seuil.Attr_tempo:'60',seuilsSaisons_seuilsSaison.2.seuils.seuil.Attr_valeur:'514.000000',seuilsSaisons_seuilsSaison.2.seuils.Attr_imap:'392.000000',seuilsSaisons_seuilsSaison.2.Attr_saison:'INTERSAISON2',seuilsSaisons_seuilsSaison.3.seuils.seuil.Attr_position:'0',seuilsSaisons_seuilsSaison.3.seuils.seuil.Attr_tempo:'60',seuilsSaisons_seuilsSaison.3.seuils.seuil.Attr_valeur:'564.000000',seuilsSaisons_seuilsSaison.3.seuils.Attr_imap:'451.000000',seuilsSaisons_seuilsSaison.3.Attr_saison:'HIVER1',seuilsSaisons_seuilsSaison.4.seuils.seuil.Attr_position:'0',seuilsSaisons_seuilsSaison.4.seuils.seuil.Attr_tempo:'60',seuilsSaisons_seuilsSaison.4.seuils.seuil.Attr_valeur:'599.000000',seuilsSaisons_seuilsSaison.4.seuils.Attr_imap:'521.000000',seuilsSaisons_seuilsSaison.4.Attr_saison:'HIVER2',supprimerDephaseur:'false',Attr_type:'52',Attr_xsi_type:'HypoModifFixeLiaison'}","params4_id":"16b1905d1b82385151c307a06666462de607acf5f15f278fcad1b3d2b0d5391e","properties5":"{nameUser:'',name:'Decision TOULOUSE SO-13-00061',Attr_xmlns:'http://www.xxxx.com/gsr',Attr_createur:'gsradm',Attr_description:'',Attr_dtCreation:'13/10/2022 14:20',Attr_dtModif:'13/10/2022 14:20',Attr_nom:'Decision TOULOUSE SO-13-00061',Attr_type:'151',Attr_xmlns_xsi:'http://www.w3.org/2001/XMLSchema-instance'}","params6_name":"SSSULL31VLEMU"}
] AS array 

UNWIND array as row  
MERGE (nodea1:Decision:DOPLER:Maquette:ImportXML{name:row.params1_name})
MERGE (nodeb1:Hypothese:DOPLER:Maquette:ImportXML{id:row.params2_id})
   ON CREATE SET nodeb1 += row.properties3 
MERGE (nodea1)-[nodea1relnodeb1:CONTAINS]-(nodeb1) 
MERGE (nodea2:HypoComplexe:DOPLER:Maquette:ImportXML{id:row.params4_id})
   ON CREATE SET nodea2 += row.properties5 
MERGE (nodea2)-[nodea2relnodeb1:CONTAINS]-(nodeb1) 
MERGE (nodea3:Ouvrage:DOPLER:Maquette:ImportXML{name:row.params6_name})
MERGE (nodea3)-[nodea3relnodeb1:CONCERNS]-(nodeb1)

I got the same error : "Invalid input '[': expected "+" or "-" (line 1, column 6 (offset: 5))" for all queries.

Can you tell me what am i doing wrong? I have reviewed my issue for hours without result.

Hint : I haven't succeed to generate a working query. I suspect a synthax problem 🙂 . I am not sure to do it properly.

Thank you for your help.

[mv59_grid]

Finally, I was able to make it work with deep review of NodeAcademy courses 😉 .
However now, i got tis message : The allocation of an extra 117,1 MiB would use more than the limit 2,7 GiB. Currently using 2,7 GiB. dbms.memory.transaction.total.max threshold reached with heavy queries.

How can i configure this threshold value? 
Is there a way to reduce query memory footprint (apart from splitting queries with large list of parameters. In my case it reaches approx 22000 objects with 10 attributes -> 220000 values for the heaviest queries) ?

[glilienfield]

You could batch the updates using a ‘call subquery in transactions’ to reduce the memory requirements. 

https://neo4j.com/docs/cypher-manual/current/clauses/call-subquery/#subquery-call-in-transactions

there is also the apoc.periodic.iterate method, as an alternative

https://neo4j.com/docs/apoc/current/overview/apoc.periodic/apoc.periodic.iterate/

[mv59_grid]

Thank you.

After struggling with CALL IN TRANSACTIONS, i came to the conclusion that this solution applies only to implicit queries. I believe it is not my use case but i may be wrong ?

After that, i have gone to apoc.
After a few nightmares :

• I have seen in logs that i wasn't runnning the good jdk, actually jdk 19.0). So i installed openjdk 17.0 and verified "java -version" running
•  i was told that apoc.periodic.iterate wasn't a known procedure so i manually installed APOC 5.3.0 core
• I am now left with the same error on all queries : " Neo4jError: Failed to invoke procedure `apoc.periodic.iterate`: Caused by: org.neo4j.exception s.ParameterNotFoundException: Expected parameter(s): data". This is surely synthax related but i didn't find where it can be. He re is a faulty query  (FYI: this query was working with a tiny dataset before apoc implementation) :

CALL apoc.periodic.iterate(
  "WITH $data AS data  UNWIND data as row RETURN row",
  " MERGE (nodea1:HypoComplexe:DOPLER:Maquette:ImportXML{id:row.params1_id})   
      ON CREATE SET nodea1 += row.properties2 
    MERGE (nodeb1:Hypothese:DOPLER:Maquette:ImportXML{id:row.params3_id})
      ON CREATE SET nodeb1 += row.properties4 
    MERGE (nodea1)-[nodea1relnodeb1:CONTAINS]->(nodeb1)",
  {batchSize:100, parallel:true}
)
YIELD batch, operations;

 Thank you for your help 

Julien

[glilienfield · Accepted solution]

It doesn’t know $data. You can pass data by adding ‘params:{data:data}’ to the configuration map.  This is assuming ‘data’ is defined before calling the apoc method. 

Yes, ‘call subquery in transactions’ only works with implied transactions.  You need to add ‘:auto’ at the beginning of the query when running in the browser, or execute the cypher using ‘session.run(cypher, params)’ if using a driver. 

[mv59_grid]

Now it works like a charm !
Your help was precious. Thank you so much.

Final query : 

CALL apoc.periodic.iterate(
  "WITH $data AS data  UNWIND data as row RETURN row",
  "MERGE (nodea1:HypoComplexe:DOPLER:Maquette:ImportXML{id:row.params1_id})   
      ON CREATE SET nodea1 += row.properties2 
   MERGE (nodeb1:Hypothese:DOPLER:Maquette:ImportXML{id:row.params3_id})
      ON CREATE SET nodeb1 += row.properties4 
   MERGE (nodea1)-[nodea1relnodeb1:CONTAINS]->(nodeb1)",
   {batchSize:100, parallel:true,params:{data: $data}}
)
YIELD batch, operations;