4

I want to replace the value of the 'Amount' key in a map (literal) with the sum of the existing 'Amount' value plus the new 'Amount' value such where both the 'type' and 'Price' match. The structure I have so far is:

WITH [{type:1, Orders:[{Price:10,Amount:100},{Price:11,Amount:200},{Price:12,Amount:300}]},
{type:2, Orders:[{Price:10,Amount:100},{Price:11,Amount:200},{Price:12,Amount:300}]},
{type:3, Orders:[{Price:10,Amount:100},{Price:11,Amount:200},{Price:12,Amount:300}]}] as ExistingOrders,
{type:2, Order:{Price:11,Amount:50}} as NewOrder

(I'm trying to get it to:)

RETURN [{type:1, Orders:[{Price:10,Amount:100},{Price:11,Amount:200},{Price:12,Amount:300}]}, {type:2, Orders:[{Price:10,Amount:100},{Price:11,Amount:250},{Price:12,Amount:300}]}, {type:3, Orders:[{Price:10,Amount:100},{Price:11,Amount:200},{Price:12,Amount:300}]}] as CombinedOrders

If there is no existing NewOrder.type and NewOrder.Price then it should obviously insert the new record rather than add it together.

Sorry, this is possibly really straight forward, but I'm not very good at this yet.

thanks

Edit:

I should add, that I have been able to get this working for a simpler map structure as such:

WITH [{type:1, Amount:100},{type:2, Amount:200},{type:3, Amount:300}] as ExistingOrders,
{type:2, Amount:50} as NewValue
RETURN reduce(map=filter(p in ExistingOrders where not p.type=NewValue.type),x in [(filter(p2 in ExistingOrders where p2.type=NewValue.type)[0])]|CASE x WHEN null THEN NewValue ELSE {type:x.type,Amount:x.Amount+NewValue.Amount} END+map) as CombinedOrders

But I'm struggling I think because of the Orders[array] in my first example.

whitfa
  • 117
  • 8

3 Answers3

2

I believe you are just trying to update the value of the appropriate Amount in ExistingOrders.

The following query is legal Cypher, and should normally work:

WITH ExistingOrders, NewOrder, [x IN ExistingOrders WHERE x.type = NewOrder.type | x.Orders] AS eo
FOREACH (y IN eo |
  SET y.Amount = y.Amount + CASE WHEN y.Price = NewOrder.Order.Price THEN NewOrder.Order.Amount ELSE 0 END
)

However, the above query produces a (somewhat) funny ThisShouldNotHappenError error with the message:

Developer: Stefan claims that: This should be a node or a relationship

What the message is trying to say (in obtuse fashion) is that you are not using the neo4j DB in the right way. Your properties are way too complicated, and should be separated out into nodes and relationships.

So, I will a proposed data model that does just that. Here is how you can create nodes and relationships that represent the same data as ExistingOrders:

CREATE (t1:Type {id:1}), (t2:Type {id:2}), (t3:Type {id:3}), 
  (t1)-[:HAS_ORDER]->(:Order {Price:10,Amount:100}),
  (t1)-[:HAS_ORDER]->(:Order {Price:11,Amount:200}),
  (t1)-[:HAS_ORDER]->(:Order {Price:12,Amount:300}),
  (t2)-[:HAS_ORDER]->(:Order {Price:10,Amount:100}),
  (t2)-[:HAS_ORDER]->(:Order {Price:11,Amount:200}),
  (t2)-[:HAS_ORDER]->(:Order {Price:12,Amount:300}),
  (t3)-[:HAS_ORDER]->(:Order {Price:10,Amount:100}),
  (t3)-[:HAS_ORDER]->(:Order {Price:11,Amount:200}),
  (t3)-[:HAS_ORDER]->(:Order {Price:12,Amount:300});

And here is a query that will update the correct Amount:

WITH {type:2, Order:{Price:11,Amount:50}} as NewOrder
MATCH (t:Type)-[:HAS_ORDER]->(o:Order)
WHERE t.id = NewOrder.type AND o.Price = NewOrder.Order.Price
SET o.Amount = o.Amount + NewOrder.Order.Amount
RETURN t.id, o.Price, o.Amount;
cybersam
  • 63,203
  • 6
  • 53
  • 76
  • Thanks for this suggestion, but I guess what I'm trying to do is keep an in-memory database and avoid the disk read/writes. Once the in-memory database is built my intention is to then write the contents to relationship properties (albeit not in the same nested map structure, due to current limitations). – whitfa Jan 27 '15 at 20:43
  • 1
    That seems to be the wrong approach, as the error produced by my first query indicates. The neo4j DB simply does not let to you use it in an non-graph way. Also, even if you did get my first query to work, it looks nothing like the actual query you will use once you start using nodes and relationships -- so you would have to start from scratch again. Finally, my first query is not efficient, since it has to perform an addition update on *every* `Amount` value, even if alll except one do not need to change. There is no need to fear going to disk -- you will have to do it eventually anyway. – cybersam Jan 27 '15 at 21:04
  • 2
    You could try to reduce to sum maps, like this: `REDUCE(m= { v:0 }, x IN range(1,10)| { v : m.v + x })}` – Michael Hunger Jan 27 '15 at 22:05
  • That looks like it ought to be a potentially elegant solution, Michael but I can't quite make it work for me. Are you able to apply it to the first example please? – whitfa Jan 28 '15 at 11:31
1

There's two parts to your question - one with a simple answer, and a second part that doesn't make sense. Let me take the simple one first!

As far as I can tell, it seems you're asking how to concatenate a new map on to a collection of maps. So, how to add a new item in an array. Just use + like this simple example:

return [{item:1}, {item:2}] + [{item:3}];

Note that the single item we're adding at the end isn't a map, but a collection with only one item.

So for your query:

RETURN [
    {type:1, Orders:[{Price:10,Amount:100},
                     {Price:11,Amount:200},
                     {Price:12,Amount:300}]},
    {type:2, Orders:[{Price:10,Amount:100},
                     {Price:11,Amount:**250**},
                     {Price:12,Amount:300}]}] 
    + 
    [{type:3, Orders:[{Price:10,Amount:100},
                     {Price:11,Amount:200},{Price:12,Amount:300}]}] 
as **CombinedOrders**

Should do the trick.

Or you could maybe do it a bit cleaner, like this:

WITH [{type:1, Orders:[{Price:10,Amount:100},{Price:11,Amount:200},{Price:12,Amount:300}]},
{type:2, Orders:[{Price:10,Amount:100},{Price:11,Amount:200},{Price:12,Amount:300}]},
{type:3, Orders:[{Price:10,Amount:100},{Price:11,Amount:200},{Price:12,Amount:300}]}] as ExistingOrders,
{type:2, Order:{Price:11,Amount:50}} as NewOrder
RETURN ExistingOrders + [NewOrder];

OK now for the part that doesn't make sense. In your example, it looks like you want to modify the map inside of the collection. But you have two {type:2} maps in there, and you're looking to merge them into something with one resulting {type:3} map in the output that you're asking for. If you need to deconflict map entries and change what the map entry ought to be, it might be that cypher isn't your best choice for that kind of query.

FrobberOfBits
  • 17,634
  • 4
  • 52
  • 86
  • Thanks for this, but @cybersam is correct in that (in the case of) this example is adding the type:2 order amount to it's Price:10 order amount. totalling 250. – whitfa Jan 27 '15 at 20:38
0

I figured it out:

WITH [{type:1, Orders:[{Price:10,Amount:100},{Price:11,Amount:200},Price:12,Amount:300}]},{type:2, Orders:[{Price:10,Amount:100},{Price:11,Amount:200},{Price:12,Amount:300}]},{type:3, Orders:[{Price:10,Amount:100},{Price:11,Amount:200},{Price:12,Amount:300}]}] as ExistingOrders,{type:2, Orders:[{Price:11,Amount:50}]} as NewOrder
RETURN 
reduce(map=filter(p in ExistingOrders where not p.type=NewOrder.type),
x in [(filter(p2 in ExistingOrders where p2.type=NewOrder.type)[0])]|
CASE x 
WHEN null THEN NewOrder 
ELSE {type:x.type, Orders:[
    reduce(map2=filter(p3 in x.Orders where not (p3.Price=(NewOrder.Orders[0]).Price)),
    x2 in [filter(p4 in x.Orders where p4.Price=(NewOrder.Orders[0]).Price)[0]]|
    CASE x2 
    WHEN null THEN NewOrder.Orders[0] 
    ELSE {Price:x2.Price, Amount:x2.Amount+(NewOrder.Orders[0]).Amount} 
    END+map2 )]} END+map) as CombinedOrders

...using nested Reduce functions.

So, to start with it combines a list of orders without matching type, with a list of those orders (actually, just one) with a matching type. For those latter ExistingOrders (with type that matches the NewOrder) it does a similar thing with Price in the nested reduce function and combines non-matching Prices with matching Prices, adding the Amount in the latter case.

whitfa
  • 117
  • 8