Convert query output into a variable structure

r.kempers · May 25, 2023, 9:16pm

Hi,

I have a query which generates an output structure and like to store it in a variable structure.
What is the easiest way to do this?

Query Output:

location	cableType	cablePullType	Sum of nrOfCables	Sum of lengthOfCables
"A"	       "Main"	       "Pulled"	               1	                                47
"B"	       "Local"	       "Pulled"	               3	                               15
"B"	       "Main"	       "Pulled"	               5	                               168
"C"	       "Local"	       "Pulled"	               37	                               597
"C"	       "Main"	       "Not Pulled"	       1	                               0
"C"	       "Main"	       "Pulled"	               138	                               7291
"D"	       "Local"	       "Not Pulled"	       2	                               0
"D"	       "Local"	       "Pulled"	               41	                              467
"D"	       "Main"	       "Not Pulled"	       2	                              0
"D"	       "Main"	      "Pulled"	              348	                              12776

Example how I like to store the output data into variables.

OUTPUT		
Location	Variable	Result
A	NrMainPulledCables	1
A	LengthMainPulledCables	47
A	NrMainNotPulledCables	0
A	LengthNotMainPulledCables	0
A	NrLocalPulledCables	0
A	LengthLocalPulledCables	0
A	NrLocalNotPulledCables	0
A	LengthNotLocalPulledCables	0
D	NrMainPulledCables	348
D	LengthMainPulledCables	12776
D	NrMainNotPulledCables	2
D	LengthNotMainPulledCables	0
D	NrLocalPulledCables	41
D	LengthLocalPulledCables	467
D	NrLocalNotPulledCables	2
D	LengthNotLocalPulledCables	0

The query which generates the output:

// Collect cable information V0.2
Call { UNWIND ['Main','Local'] as cableIdType
       MATCH (areaDescription:AreaDescription)<-[:HAVE_AD]-(area:Area)<-[:PULLED_TO]-(cables:Cables{cableType:cableIdType})
       WHERE cables.mainCable IN ['Routed', 'Not Routed', 'Local'] OR cables.pulled IN ['Pulled', 'NOT Pulled']
       WITH area.fromLocation AS location,
            cables.pulled AS cablePullType,
            cableIdType,
            count(*) as countOfCables, 
            sum(cables.length) AS sumOfLengths
       WITH {
             AreaLocation: location,
             CableType: cableIdType,
             CablePullType: cablePullType,
             NrOfCables: countOfCables, 
             LengthOfCables: sumOfLengths} as typeData
       RETURN typeData as recordSet
       UNION
       UNWIND ['Main','Local'] as cableIdType
       MATCH (areaDescription:AreaDescription)-[:BELONGS_TO]->(area:Area)-[:PULLED_FROM]->(cables:Cables{cableType:cableIdType})
       WHERE cables.mainCable IN ['Routed', 'Not Routed', 'Local'] OR cables.pulled IN ['Pulled', 'NOT Pulled']
       WITH area.fromLocation AS location,
            cables.pulled AS cablePullType,
            cableIdType,
            count(*) as countOfCables, 
            sum(cables.length) AS sumOfLengths
       WITH {
             AreaLocation: location,
             CableType: cableIdType,
             CablePullType: cablePullType,
             NrOfCables: countOfCables, 
             LengthOfCables: sumOfLengths} as typeData
       RETURN typeData as recordSet
     }
WITH recordSet.AreaLocation as location,
     recordSet.CableType as cableType,
     recordSet.CablePullType as cablePullType,
     sum(recordSet.NrOfCables) as nrOfCables,
     sum(recordSet.LengthOfCables) as lengthOfCables
RETURN location, cableType, cablePullType, nrOfCables, lengthOfCables

Kind regards ?

glilienfield · May 26, 2023, 2:41pm

Since you are aggregating the results from each union query, I believe the following simplification will give you the same results.

Call { 
       UNWIND ['Main','Local'] as cableIdType
       MATCH (areaDescription:AreaDescription)<-[:HAVE_AD]-(area:Area)<-[:PULLED_TO]-(cables:Cables{cableType:cableIdType})
       WHERE cables.mainCable IN ['Routed', 'Not Routed', 'Local'] OR cables.pulled IN ['Pulled', 'NOT Pulled']
       RETURN 
            area.fromLocation AS location,
            cables.pulled AS cablePullType,
            cableIdType AS cableType,
            cables.length as length
       UNION
       UNWIND ['Main','Local'] as cableIdType
       MATCH (areaDescription:AreaDescription)-[:BELONGS_TO]->(area:Area)-[:PULLED_FROM]->(cables:Cables{cableType:cableIdType})
       WHERE cables.mainCable IN ['Routed', 'Not Routed', 'Local'] OR cables.pulled IN ['Pulled', 'NOT Pulled']
       RETURN 
            area.fromLocation AS location,
            cables.pulled AS cablePullType,
            cableIdType AS cableType,
            cables.length as length
}     
RETURN location, cablePullType, cableType, count(*) as countOfCables,  sum(length) AS sumOfLengths

Assuming that is true, I think the following will give you an output similar to what you want:

Call { 
       UNWIND ['Main','Local'] as cableIdType
       MATCH (areaDescription:AreaDescription)<-[:HAVE_AD]-(area:Area)<-[:PULLED_TO]-(cables:Cables{cableType:cableIdType})
       WHERE cables.mainCable IN ['Routed', 'Not Routed', 'Local'] OR cables.pulled IN ['Pulled', 'NOT Pulled']
       RETURN 
            area.fromLocation AS location,
            cables.pulled AS cablePullType,
            cableIdType AS cableType,
            cables.length as length
       UNION
       UNWIND ['Main','Local'] as cableIdType
       MATCH (areaDescription:AreaDescription)-[:BELONGS_TO]->(area:Area)-[:PULLED_FROM]->(cables:Cables{cableType:cableIdType})
       WHERE cables.mainCable IN ['Routed', 'Not Routed', 'Local'] OR cables.pulled IN ['Pulled', 'NOT Pulled']
       RETURN 
            area.fromLocation AS location,
            cables.pulled AS cablePullType,
            cableIdType AS cableType,
            cables.length as length
}     
WITH location, cablePullType, cableType, count(*) as countOfCables,  sum(length) AS sumOfLengths
UNWIND [
{location: location, cablePullType: cablePullType, cableType: cableType, result: countOfCables, prefix: “count”},
{location: location, cablePullType: cablePullType, cableType: cableType, result: sumOfLengths, prefix: “length”}] as data
RETURN data.location as location, data.prefix+"-"+data.cableType+"-"+data.cablePullType as variable, data.result as result

r.kempers · May 26, 2023, 11:50pm

Gary,

Getting closer to to the solution what I like to achieve. Understand what your proposal do but I realize I need to explain more in detail what I want and why I have two datasets of cables (PULLED_TO and PULLED_FROM).

The cable nodes are loaded from a CSV file with the structure below (just a part of the structure):
LOAD CSV WITH HEADERS FROM 'file:///CableList.csv' AS line FIELDTERMINATOR ';'
MERGE (cable:Cables{nummer_ID:line.Nummer_ID})
ON CREATE SET cable.locatie_1 = line.Locatie_1,
              cable.length = toInteger(line.Lengte),
              cable.van_object = line.Van_object,  //means cable.from_object
              cable.naar_object = line.Naar_object, //means cable.to_Object
              cable.connectedFrom = line.Connected_from,
              cable.connectedTo = line.Connected_to,
              cable.mainCable = line.`Main Cable`,
              cable.pulled = line.Pulled,
              cable.systemAbreviation = line.`System Abreviation`;

The relationships which I have made are below (Reminder, the properties cable.van_object and cable.naar_Object (sorry for the DUTCH) are two different properties) :

//07. PULLED_FROM equipment to cable v0.2
MATCH (area:Area) 
WITH area 
MATCH (cable:Cables) 
WHERE area.objectId = cable.van_object
CREATE (area)-[p:PULLED_FROM]->(cable);

//08. PULLED_TO Cable to equipment
MATCH (area:Area) 
WITH area
MATCH (cable:Cables) 
WHERE area.objectId = cable.naar_object
CREATE (cable)-[p:PULLED_TO]->(area);

// 09 Define Local Cables in Cable Nodes V1.0
MATCH (equipmentFrom:Area)-[pf:PULLED_FROM]->(cable)-[pt:PULLED_TO]->(equipmentTo:Area)
WHERE equipmentFrom.fromLocation =  equipmentTo.fromLocation
SET cable.cableType = "Local";

// 10. Define Main Cables in Cable Nodes
MATCH (equipmentFrom:Area)-[pf:PULLED_FROM]->(cable)-[pt:PULLED_TO]->(equipmentTo:Area)
WHERE equipmentFrom.fromLocation <> equipmentTo.fromLocation
SET cable.cableType = "Main";

Try to explain the query below what I like to achieve:

Call { 
       // The cables (Main or Local) which are pulled TO the area can be "Routed", "Not Routed" or 
       //"LOCAL" or be "PULLED" or "NOT PULLED".  **I like to know how many cables (Main, Local) 
       //going to this area with what length and if the are "PULLED" or "NOT PULLED".** Will be 
       //calculated in the RETURN clause. So far so good. The challenge is, the area. The area (name "X") 
       //can be specified in this part of the UNION OR specified in the other part of the UNION.
       UNWIND ['Main','Local'] as cableIdType
       MATCH (areaDescription:AreaDescription)<-[:HAVE_AD]-(area:Area)<-[:PULLED_TO]-(cables:Cables{cableType:cableIdType})
       WHERE cables.mainCable IN ['Routed', 'Not Routed', 'Local'] OR cables.pulled IN ['Pulled', 'NOT Pulled']
       RETURN 
            area.fromLocation AS location,
            cables.pulled AS cablePullType,
            cableIdType AS cableType,
            count(*) as countOfCables, 
            sum(cables.length) AS sumOfLengths
       UNION
       // As specified, in this part of the UNION the area (name "X")  can be located here also. So for this 
       // area I like to know also the number of cables and their length.
       UNWIND ['Main','Local'] as cableIdType
       MATCH (areaDescription:AreaDescription)-[:BELONGS_TO]->(area:Area)-[:PULLED_FROM]->(cables:Cables{cableType:cableIdType})
       WHERE cables.mainCable IN ['Routed', 'Not Routed', 'Local'] OR cables.pulled IN ['Pulled', 'NOT Pulled']
       RETURN 
            area.fromLocation AS location,
            cables.pulled AS cablePullType,
            cableIdType AS cableType,
            count(*) as countOfCables, 
            sum(cables.length) AS sumOfLengths
}
//Now I know for the area (name "X") of both sides the length and number of cables and I just summatrize them to get the complete overview   
WITH location, cablePullType, cableType, sum(countOfCables) AS countOfCables,  sum(sumOfLengths) AS sumOfLengths
UNWIND [
{location: location, cablePullType: cablePullType, cableType: cableType, result: countOfCables, prefix: 'count'},
{location: location, cablePullType: cablePullType, cableType: cableType, result: sumOfLengths, prefix: 'length'}] as data
RETURN data.location as location, data.prefix+"-"+data.cableType+"-"+data.cablePullType as variable, data.result as result

The output of the query is expected, as follows (just a part of it):

location	variable	result				variable occurences :
1000.146	"count-Main-Pulled"	231				"count-Main-Pulled"
1000.146	"length-Main-Pulled"	10002				"length-Main-Pulled"
1000.152	"count-Main-Pulled"	284				"count-Main-Not Pulled"
1000.152	"length-Main-Pulled"	12257				"length-Main-Not Pulled"
1000.234	"count-Main-Pulled"	348				"count-Local-Pulled"
1000.234	"length-Main-Pulled"	12776				"length-Local-Pulled"
1000.149	"count-Main-Pulled"	502				"count-Local-Not Pulled"
1000.149	"length-Main-Pulled"	26043				"length-Local-Not Pulled"
1000.147	"count-Main-Pulled"	116				
1000.147	"length-Main-Pulled"	5890				
1000.263	"count-Main-Pulled"	337				
1000.263	"length-Main-Pulled"	28630				
1000.296	"count-Main-Pulled"	9				
1000.296	"length-Main-Pulled"	241				
1000.44	"count-Main-Pulled"	662				
1000.44	"length-Main-Pulled"	35925				
1000.202	"count-Main-Pulled"	538				
1000.202	"length-Main-Pulled"	20951				
1000.45	"count-Main-Pulled"	130				
1000.45	"length-Main-Pulled"	6895				
1000.165	"count-Main-Pulled"	226				
1000.165	"length-Main-Pulled"	11283				
1000.41	"count-Main-Pulled"	489				
1000.41	"length-Main-Pulled"	24769				
1000.191	"count-Main-Pulled"	256				
1000.191	"length-Main-Pulled"	13327				
1000.187	"count-Main-Pulled"	375				
1000.187	"length-Main-Pulled"	13926

My main goal is to translate the output into the variables below and store them into the Node "AreaDescription":

NrMainPulledCables
LengthMainPulledCables
NrMainNotPulledCables
LengthNotMainPulledCables
NrLocalPulledCables
LengthLocalPulledCables
NrLocalNotPulledCables
LengthNotLocalPulledCables

The translation of the output to these variables is for me another challenge.
The possible solution can be:

Create a temporary node;
Store in the node the output data of the query ;
Use apoc.when to translate the output into the right variable;
Store them in the Node "AreaDescription";
Remove temporary node.

Proposal:
MERGE (cableData:cableDataPerArea);

Above QUERY;

MATCH (cableData:cableDataPerArea)
WITH data, cableData
SET cableData.location = data.location,
cableData.variable = data.prefix+"-"+data.cableType+"-"+data.cablePullType,
cableData.result = data.result

In this way the Node cableDataPerArea is only loaded with the last data of data.
What I need to change in the query that the temporary node is loaded with all the data?

Is the proposed way, the correct way?

Query which I have used.

MERGE (cableData:cableDataPerArea);
Call { 
       UNWIND ['Main','Local'] as cableIdType
       MATCH (areaDescription:AreaDescription)<-[:HAVE_AD]-(area:Area)<-[:PULLED_TO]-(cables:Cables{cableType:cableIdType})
       WHERE cables.mainCable IN ['Routed', 'Not Routed', 'Local'] OR cables.pulled IN ['Pulled', 'NOT Pulled']
       RETURN 
            area.fromLocation AS location,
            cables.pulled AS cablePullType,
            cableIdType AS cableType,
            count(*) as countOfCables, 
            sum(cables.length) AS sumOfLengths
       UNION
       UNWIND ['Main','Local'] as cableIdType
       MATCH (areaDescription:AreaDescription)-[:BELONGS_TO]->(area:Area)-[:PULLED_FROM]->(cables:Cables{cableType:cableIdType})
       WHERE cables.mainCable IN ['Routed', 'Not Routed', 'Local'] OR cables.pulled IN ['Pulled', 'NOT Pulled']
       RETURN 
            area.fromLocation AS location,
            cables.pulled AS cablePullType,
            cableIdType AS cableType,
            count(*) as countOfCables, 
            sum(cables.length) AS sumOfLengths
}   
WITH location, cablePullType, cableType, sum(countOfCables) AS countOfCables,  sum(sumOfLengths) AS sumOfLengths
UNWIND [
{location: location, cablePullType: cablePullType, cableType: cableType, result: countOfCables, prefix: 'count'},
{location: location, cablePullType: cablePullType, cableType: cableType, result: sumOfLengths, prefix: 'length'}] as data
//RETURN data.location as location, data.prefix+"-"+data.cableType+"-"+data.cablePullType as variable, data.result as result
MATCH (cableData:cableDataPerArea)
WITH data, cableData
SET cableData.location = data.location,
    cableData.variable = data.prefix+"-"+data.cableType+"-"+data.cablePullType,
    cableData.result = data.result

glilienfield · May 28, 2023, 12:34pm

Is it true that one areaDescription node is associated with one area node and the location value is unique to one area node? If so, we can pass the areaDescription node through with the location value and the aggregate values should be the same for location. We can then save the metrics to the AreaDesciption node directly.

Unfortunately, we can't use the SET operator with the dynamic properties, i.e., using the brackets to set a property based on a variable. This is the need for the apoc method. The planner does not allow the query to end with a 'call', so I added a 'return' to comply.

I did not fully grasp the cableDataPerArea approach. As you have it written, all the cableDataPerArea nodes will be retrieved and each set with the properties. As such, they all should end up with the last values saved.

Does the following approach make sense?

Call { 
       UNWIND ['Main','Local'] as cableIdType
       MATCH (areaDescription:AreaDescription)<-[:HAVE_AD]-(area:Area)<-[:PULLED_TO]-(cables:Cables{cableType:cableIdType})
       WHERE cables.mainCable IN ['Routed', 'Not Routed', 'Local'] OR cables.pulled IN ['Pulled', 'NOT Pulled']
       RETURN
            areaDescription,
            area.fromLocation AS location,
            cables.pulled AS cablePullType,
            cableIdType AS cableType,
            count(*) as countOfCables, 
            sum(cables.length) AS sumOfLengths
       UNION
       UNWIND ['Main','Local'] as cableIdType
       MATCH (areaDescription:AreaDescription)-[:BELONGS_TO]->(area:Area)-[:PULLED_FROM]->(cables:Cables{cableType:cableIdType})
       WHERE cables.mainCable IN ['Routed', 'Not Routed', 'Local'] OR cables.pulled IN ['Pulled', 'NOT Pulled']
       RETURN 
            areaDescription,
            area.fromLocation AS location,
            cables.pulled AS cablePullType,
            cableIdType AS cableType,
            count(*) as countOfCables, 
            sum(cables.length) AS sumOfLengths
}   
WITH areaDescription, location, cablePullType, cableType, sum(countOfCables) AS countOfCables,  sum(sumOfLengths) AS sumOfLengths
UNWIND [
	{location: location, cablePullType: cablePullType, cableType: cableType, result: countOfCables, prefix: 'count'},
	{location: location, cablePullType: cablePullType, cableType: cableType, result: sumOfLengths, prefix: 'length'}] as data
CALL apoc.create.setProperty(areaDescription, data.prefix+"-"+data.cableType+"-"+data.cablePullType, data.result) yield node
RETURN node

Topic		Replies	Views
Viewing Cypher result anywhere Integrations & Ecosystem migrated	1	143	October 15, 2022
WITH producing different result depending on variables declared Neo4j Graph Platform cypher	4	521	November 11, 2019
Variable not defined in query Neo4j Graph Platform cypher	5	350	May 25, 2023
Is there a way to create a "global" named variable? Neo4j Graph Platform cypher	4	1346	October 7, 2021
Convert neo4j.data.Record to string Python	1	521	February 17, 2022

Get Certified in June!

Convert query output into a variable structure

Related topics