Timeless Looping with Json Data

[damisg7]

Hi, i have a json file with the Common Weakness Enumeration Catalog (CWE Catalog - Cybersecurity) and i want to generate a graph, importing this json file. In the last lines of my cypher i think that there is a mistake which leads to timeless execution of the query. Below my json data and my cypher code. I have also changed (due to error) in the db settings file: cypher.lenient_create_relationship = true

JSON Data "cwe.json"

"Weakness_Catalog": {
   "Weaknesses": {.....}
   "Categories": {.....}
   "Views": {.....}
   "External_References": {.....}
}

Cypher Code:

call apoc.load.json(files) yield value
                                unwind value.Weakness_Catalog.Weaknesses.Weakness as weakness
                                merge (i:GeneralInfo_CWE {Name:value.Weakness_Catalog.Name, Version:value.Weakness_Catalog.Version,
                                                        Date:value.Weakness_Catalog.Date, Schema:'https://cwe.mitre.org/data/xsd/cwe_schema_v6.4.xsd'})
                                
                                merge(w:CWE {Name:'CWE-' + weakness.ID})
                                set w.Extended_Name=weakness.Name, w.Abstraction=weakness.Abstraction,
                                w.Structure=weakness.Structure, w.Status=weakness.Status, w.Description=weakness.Description,
                                w.Extended_Description= apoc.convert.toString(weakness.Extended_Description),
                                w.Likelihood_Of_Exploit=weakness.Likelihood_Of_Exploit,
                                w.Background_Details=apoc.convert.toString(weakness.Background_Details.Background_Detail),
                                w.Modes_Of_Introduction=[value in weakness.Modes_Of_Introduction.Introduction | value.Phase],
                                w.Submission_Date=weakness.Content_History.Submission.Submission_Date,
                                w.Submission_Name=weakness.Content_History.Submission.Submission_Name,
                                w.Submission_Organization=weakness.Content_History.Submission.Submission_Organization,
                                w.Modifications=[value in weakness.Content_History.Modification | apoc.convert.toString(value)],
                                w.Alternate_Terms=apoc.convert.toString(weakness.Alternate_Terms),
                                w.Notes=[value in weakness.Notes.Note | apoc.convert.toString(value)],
                                w.Affected_Resources=[value in weakness.Affected_Resources.Affected_Resource | value],
                                w.Functional_Areas=[value in weakness.Functional_Areas.Functional_Area | value]
                                
                                merge (w)-[:belongsTo]->(i)
                                
                                with w, weakness, value
                                unwind weakness.Related_Weaknesses.Related_Weakness as Rel_Weakness
                                match (cwe:CWE) where cwe.Name='CWE-' + Rel_Weakness.CWE_ID
                                merge (w)-[:Related_Weakness{Nature:Rel_Weakness.Nature}]->(cwe)
                                
                                with w, weakness, value
                                unwind weakness.Applicable_Platforms as appPl
                                    foreach (lg in appPl.Language |
                                        merge(ap:Applicable_Platform{Type:'Language', Prevalence:lg.Prevalence,
                                                Name:coalesce(lg.Name, 'NOT SET'), Class:coalesce(lg.Class, 'NOT SET')})
                                        merge(w)-[:Applicable_Platform]->(ap))
                                
                                    with w, weakness, value, appPl
                                    foreach (tch in appPl.Technology |
                                        merge(ap:Applicable_Platform{Type:'Technology', Prevalence:tch.Prevalence,
                                                Name:coalesce(tch.Name, 'NOT SET'), Class:coalesce(tch.Class, 'NOT SET')})
                                        merge(w)-[:Applicable_Platform]->(ap))
                                   
                                    with w, weakness, value, appPl
                                    foreach (arc in appPl.Architecture |
                                        merge(ap:Applicable_Platform{Type:'Architecture', Prevalence:arc.Prevalence,
                                                Name:coalesce(arc.Name, 'NOT SET'), Class:coalesce(arc.Class, 'NOT SET')})
                                        merge(w)-[:Applicable_Platform]->(ap))
                                    
                                    with w, weakness, value, appPl
                                    foreach (os in appPl.Operating_System |
                                        merge(ap:Applicable_Platform{Type:'Operating System', Prevalence:os.Prevalence,
                                                Name:coalesce(os.Name, 'NOT SET'), Class:coalesce(os.Class, 'NOT SET')})
                                        merge(w)-[:Applicable_Platform]->(ap))             
                           
                           
                                with w, weakness, value
                                foreach (example in weakness.Demonstrative_Examples.Demonstrative_Example |
                                    merge(ex:Demonstrative_Example {Intro_Text:apoc.convert.toString(example.Intro_Text)})
                                    set ex.Body_Text=[value in example.Body_Text | apoc.convert.toString(value)],
                                    ex.Example_Code=[value in example.Example_Code | apoc.convert.toString(value)]                                
                                    merge (w)-[:hasExample]->(ex))
                                                                
                                                     
                                with w, weakness, value
                                foreach (consequence in weakness.Common_Consequences.Consequence |
                                    merge (con:Consequence{CWE:w.Name, Scope:[value in consequence.Scope | value]})
                                    set con.Impact=[value in consequence.Impact | value],
                                    con.Note=consequence.Note, con.Likelihood=consequence.Likelihood
                                    merge(w)-[:hasConsequence]->(con))
                                
                                with w, weakness, value
                                foreach (dec in weakness.Detection_Methods.Detection_Method |
                                    merge(d:Detection_Method {Method:dec.Method})
                                    merge(w)-[wd:canBeDetected{Description:apoc.convert.toString(dec.Description)}]->(d)
                                    set wd.Effectiveness=dec.Effectiveness, wd.Effectiveness_Notes=dec.Effectiveness_Notes,
                                    wd.Detection_Method_ID=dec.Detection_Method_ID)                                
                                
                                with w, weakness, value
                                foreach (mit in weakness.Potential_Mitigations.Mitigation |
                                    merge(m:Mitigation {Description:apoc.convert.toString(mit.Description)})
                                    set m.Phase=[value in mit.Phase | value], m.Strategy=mit.Strategy,
                                    m.Effectiveness=mit.Effectiveness, m.Effectiveness_Notes=mit.Effectiveness_Notes,
                                    m.Mitigation_ID=mit.Mitigation_ID
                                    merge(w)-[:hasMitigation]->(m))
                                    
                                with w, weakness, value
                                foreach (rap in weakness.Related_Attack_Patterns.Related_Attack_Pattern |
                                    merge(cp:CAPEC {Name:rap.CAPEC_ID})
                                    merge(w)-[:RelatedAttackPattern]->(cp))
                                
                                with w, weakness, value
                                foreach (reference in value.Weakness_Catalog.External_References.External_Reference |
                                    merge(r:External_Reference{Reference_ID:reference.Reference_ID})
                                    set r.Author=[value in reference.Author | value], r.Title=reference.Title,
                                        r.Edition=reference.Edition, r.URL=reference.URL,
                                        r.Publication_Year=reference.Publication_Year, r.Publisher=reference.Publisher)
                                
                                with w, weakness, value
                                unwind weakness.References.Reference as exReference
                                match (ref:External_Reference) where ref.Reference_ID=exReference.External_Reference_ID
                                merge(w)-[:hasExternal_Reference]->(ref)
                                                 
                                                                
                                with value
                                unwind value.Weakness_Catalog.Views.View as view
                                merge (v:CWE_VIEW{ViewID:view.ID})
                                set v.Name=view.Name, v.Type=view.Type, v.Status=view.Status,
                                v.Objective=apoc.convert.toString(view.Objective), v.Filter=view.Filter,
                                v.Notes=apoc.convert.toString(view.Notes),
                                v.Submission_Name=view.Content_History.Submission.Submission_Name,
                                v.Submission_Date=view.Content_History.Submission.Submission_Date,
                                v.Submission_Organization=view.Content_History.Submission.Submission_Organization,
                                v.Modification=[value in view.Content_History.Modification | apoc.convert.toString(value)]
                                
                                foreach (value in view.Audience.Stakeholder |
                                    merge (st:Stakeholder{Type:value.Type})
                                    merge (v)-[rel:usefulFor]->(st)
                                    set rel.Description=value.Description)
                                
                                with v, view, value
                                unwind (case view.Members.Has_Member when [] then [null] else view.Members.Has_Member end) as members
                                optional match (MemberWeak:CWE{Name:'CWE-' + members.CWE_ID})
                                merge (v)-[:hasMember{ViewID:members.View_ID}]->(MemberWeak)                                
                                
                                                                                                                                
                                with v, view, value
                                unwind (case view.References.Reference when [] then [null] else view.References.Reference end) as viewExReference
                                optional match (viewRef:External_Reference{Reference_ID:viewExReference.External_Reference_ID})
                                merge (v)-[:hasExternal_Reference{ViewID:v.ViewID}]->(viewRef)
                                
                                with value
                                unwind value.Weakness_Catalog.Categories.Category as category
                                merge (c:CWE_Category{CategoryID:category.ID})
                                set c.Name=category.Name, c.Status=category.Status, c.Summary=apoc.convert.toString(category.Summary),
                                c.Notes=apoc.convert.toString(category.Notes), c.Submission_Name=category.Content_History.Submission.Submission_Name,
                                c.Submission_Date=category.Content_History.Submission.Submission_Date,
                                c.Submission_Organization=category.Content_History.Submission.Submission_Organization,
                                c.Modification=[value in category.Content_History.Modification | apoc.convert.toString(value)]
                                
                                with c, category
                                unwind (case category.References.Reference when [] then [null] else category.References.Reference end) as categoryExReference
                                optional match (catRef:External_Reference{Reference_ID:categoryExReference.External_Reference_ID})
                                merge (c)-[:hasExternal_Reference{CategoryID:c.CategoryID}]->(catRef)

So after several executions i think that the problem is in the line below, cause when i delete all this part [with value....(catRef)], the query executes pretty good.

     with value
     unwind value.Weakness_Catalog.Categories.Category as category

[damisg7 · Accepted solution]

Thanks to Neo4j Discord Community i present you the answer.

When i'm using with (after so many unwinds) i'm working in nested loops. The more nested loops the slower the query will be. To speed up my process i created some simplier queries. One for every field (Weakness, Categories, Views, External_References) in .json file. The result was perfect. Query executed almost immediately.