UNWIND with inline variable, what am i doing wrong?

mv59_grid · December 20, 2022, 9:06am

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 · December 20, 2022, 12:24pm

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 · December 20, 2022, 2:10pm

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 · December 20, 2022, 4:03pm

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 · December 20, 2022, 4:30pm

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 · December 20, 2022, 4:58pm

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;

Topic		Replies	Views
Merging data : Deadlocks and performance balance in a heavily connected graph Neo4j Graph Platform performance , deadlock , migrated , cypher-tagged	0	262	December 21, 2022
Setting the stack size doesn't seems to work Installation configuration	11	2331	December 12, 2018
Query seems to not execute properly. Is it an issue? Neo4j Graph Platform migrated	4	180	January 24, 2023
Double unwinds in a cypher script Cypher cypher	10	1301	September 14, 2020
Passing unwind parameter issue Javascript	5	1916	March 4, 2020

Take the Course Then Join The Aura Agent Hackathon

UNWIND with inline variable, what am i doing wrong?

Related topics

Take the Course Then Join
The Aura Agent Hackathon