CODEWITHSUNDEEP
databaseneo4jcypher
Artemiy Vereshchinskiy
Artemiy Vereshchinskiy

Reputation: 131

How to group values from Node's props by its other props in Cypher Neo4j?

I'll describe my data model, and some things that I want to achieve. I have Neo4j as db, data structure is immutable for now, so advices like "just create other Nodes with changed label" is not for my situation, in advance I'm sorry!

In Neo4j I have Node:Category, Node:Goods, Node:Property. Goods could have many relations with Property. For example: (:Goods {name: "Chair"})-[:PROP]-(:Property). This Node:Goods related to two Node:Properties - price and material. As you might have guessed - I have a lot of Node:Goods and each one is related to price and material Node:Property.

I need to build frontend client for this structure with ability to filter Node:Goods by its related Node:Property. For example I need all Node:Goods that have relation to Node:Property with {name:"Price"} and {value: >40, <100}. I've asked about this here and get the great answer by @InverseFalcon:

...
WITH g, size([(g)-[:Prop]-(p:Property) WHERE p.name = "Price" AND 190 < p.value < 320 | p]) > 0 as priceRange 
WHERE priceRange
...

This solution works perfectly when I know what and how to filter. My question is how can I group all Node:Propery values by its names, to find max and min and variants to pass them to frontend filters?

My Node:Property looks like this:

{
    "name":"Price",
    "type":"num", //text, bool
    "value":245
}

I expected smth like this (but no matter if response would be not so pretty): 

{ 
    "Category": "Chair",
    "Goods": 157,
    "Properties": [
        { "Name": "Material", "type":"text", "variants": "Metal", "Oak", "Maple" },
        { "Name": "Price", "type": "num", "min": 25, "max": 490 },
        { "Name": "InStock", "type": "bool" },
        ...
    ]
}

I tried to achieve this by UNWIND and FOREACH, but there is no way I found to make RETURN inside FOREACH. I appreciate any help and tips, thank you!

Upvotes: 1

Views: 387

Answers (2)

stdob--
stdob--

Reputation: 29147

You can use CASE when aggregating:

MATCH (C:Category)
OPTIONAL MATCH (C)-[:Good]->(G:Goods)-[:Prop]->(P:Property)
WITH C, 
     count(DISTINCT G) AS cnt, 
     P.Name AS name, P.type AS type, 
     CASE WHEN P.type = "num" THEN min(P.value) ELSE null END AS min, 
     CASE WHEN P.type = "num" THEN max(P.value) ELSE null END AS max,
     CASE WHEN P.type = "text" THEN collect(P.value) ELSE null END AS variants
WITH C.name AS name, cnt, 
     {name: name, type: type, min: min, max: max, variants: variants } AS prop
RETURN {Category: name, Goods: cnt, Properties: collect(prop)} AS category

Upvotes: 2

InverseFalcon
InverseFalcon

Reputation: 30407

You can just use the min() and max() aggregation functions when you're dealing with num type :Properties.

Tackling these three variants, you might use a query like this:

MATCH (good:Good)
WITH good
OPTIONAL MATCH (good)-[:PROP]-(p:Property {type:'text'})
WITH good, p.name as name, collect(p.value) as variants
WITH good, collect({name:name, type:'text', variants:variants}) as properties
OPTIONAL MATCH (good)-[:PROP]-(p:Property {type:'num'})
WITH good, properties, p.name as name, min(p.value) as min, max(p.value) as max
WITH good, properties + collect({name:name, type:'num', min:min, max:max}) as properties
WITH good, properties + [(good)-[:PROP]-(p:Property {type:'bool'}) | p {.name, type:'bool'}] as properties
RETURN good, properties

Upvotes: 2

Related Questions