Optionally CREATE/MERGE depending on multiple list parameters

[igorclark]

Hi folks,

I often pass in lists of data dictionaries in parameters and UNWIND them in the query to MERGE or CREATE nodes based on provided information. When I try to do it with two sets of data as separate parameters, if both are filled out with data, no problem; if either has no data in it, then UNWINDing the parameter won't work because UNWIND on an empty list always returns no nodes.

For a really simplified example:

:params product_info => [ {code:"ABC",price:2500}, {code:"DEF",price:3000} ]
:params discount_dates => [ 20190925, 20190926, 20190928 ]
[..other params...]

In this example, there may be no dates where discounts should be applied.
I'd like to do the equivalent of

UNWIND $product_info AS product_detail
MATCH (product:Product{code:product_detail.code})
MERGE [...]

(and then...)

UNWIND $discount_dates AS discount_date
MATCH (discount_day:Day{daystamp:discount_date})
MERGE [...]

This works great when both are populated, or works for the first clause when only the first is populated. But of course as soon as either are empty (e.g. when there aren't any discounts), the UNWIND returns no rows, the query stops, and any nodes & relationships created before the UNWIND clause are left dangling because as the query hasn't failed but just run out of road, they were left in the transaction and committed and now not only do we not have all the data we want, we have inconsistent data.

I've been trying a bunch of other ways to enumerate the data, including REDUCEing and FILTERing the lists into other useful shapes for MERGE and FOREACH and so forth but I can't get past the dreaded 'null' errors like e.g. Expected to find a node at 'product' but found instead: null, or a different equivalent depending on how I've implemented it.

Is there a recommended approach to doing this? I really don't want to have to break it down in client-side logic because really it needs to be done in a single hit as a transaction.

Thanks!

[andrew_bowman · Accepted solution]

Since an UNWIND is the opposite of a collect(), emitting a row for each list element, when you UNWIND on an empty list you get no rows, which effectively wipes out the current row.

You can use a CASE when you UNWIND to check if the list is empty and use [null] instead, but it will require you to either filter out the subsequent rows that now have null, or otherwise apply come conditional logic (via the FOREACH hack or apoc.cypher.run().

(and a note for the future...we are looking at having an OPTIONAL UNWIND that will do this or you, but it may take awhile to get approval and get added in).

That said, you probably just need conditional Cypher execution via APOC. You will need to make sure that the CALL returns at least a single row, however, to again avoid wiping out the row if there's nothing to YIELD.

[igorclark]

Thanks for getting back to me so quickly Andrew!

Aha, yes, OPTIONAL UNWIND would be fantastic - I saw a proposal for it in my searching but it was from 2017 and it didn't seem to be ready. And I often use apoc.cypher.run to rationalize and clarify things (and sometimes to speed them up), it's brilliant, but it doesn't do writing queries (as far as I can tell anyway) - and FOREACH is cool but limited because you can't MATCH inside it. Etc.

But the conditional execution looks like it is exactly what I need, I can't believe I hadn't come across it before, especially as I've been searching "conditional cypher X" a lot for this, and especially as the apoc.do.when version allows writing. (And the apoc.case stuff looks like I'll be able to tangle myself up in it to my heart's content!)

apoc.when worked exactly how I wanted it to on a read-only test I did just now - I'll dig into it more in the morning and try to conditionally write nodes and relationships this way. Will update this thread if it does what I need. Thanks very much indeed @andrew.bowman!

[andrew_bowman]

No problem!

And for future uses when you don't need conditionals, but do need subqueries with write capability, apoc.cypher.doIt() is the writing equivalent of apoc.cypher.run(), another tool for your toolbox!

Also keep an eye out for later Neo4j 4.0 announcements, we have some Cypher goodies coming that may help for some of these kinds of cases.

[igorclark]

Morning Andrew, I just got this doing exactly what I need using apoc.do.when. I still can't believe I never came across this before, it's a game-changer for me. And cypher.doIt will certainly be very useful too. Thanks!

Will definitely keep an eye on the 4.0 stuff, some really interesting stuff happening - though moving to 4.0 is going to mean a lot of work for us because we have a lot of code still using the REST API, so it'll need to be pretty compelling stuff to get us to finally do that work 😉

Anyhow, thanks again!