Qfyilyi

Initial situation

I’ve written a working Cypher query, which returns four distinct quantities.

MATCH

  <complex satement>

WITH

  count(DISTINCT typeA) AS amountA,

  count(DISTINCT typeB) AS amountB,

  count(DISTINCT typeC) AS amountC,

  count(DISTINCT typeD) AS amountD

RETURN

  amountA, amountB, amountC, amountD;

Target solution

Instead of a four-column table I want to return a single text string now, where all four quantities are concatenated including a descriptive label. However the quantity must only be part of the string, if its amount is greater than zero.

╒════════════════════════════════════════════════════╕

│"formattedQuantities"                               │

╞════════════════════════════════════════════════════╡

│"amountA: 123456, amountC: 9876543, amountD: 2018"  │

└────────────────────────────────────────────────────┘

(Because the value of amountB is 0, it is omitted in the result.)

I use this Cyper query for several millions of rows. Because of a performance impact apprehension I don’t want to create and call a custom plugin.

So, how can I return the quantities as string with Cypher and Neo4j? Can you please give me an advice how to solve this challenge? Many thanks in advance for pointing me into the right direction!

approach to the problem / preliminary result

Cypher statement:

MATCH

<complex satement>

WITH

  count(DISTINCT typeA) AS amountA,

  count(DISTINCT typeB) AS amountB,

  count(DISTINCT typeC) AS amountC,

  count(DISTINCT typeD) AS amountD

WITH

  ['amountA: ', amountA, ', amountB: ', amountB, ', amountC: ', amountC, ', amountD: ', amountD] AS quantities

RETURN

  reduce(result = toString(head(quantities)), n IN tail(quantities) | result + n) AS formattedQuantities;

Result:

╒═════════════════════════════════════════════════════════════════╕

│"formattedQuantities"                                            │

╞═════════════════════════════════════════════════════════════════╡

│"amountA: 123456: 1, amountB: 0, amountC: 9876543, amountD: 2018"│

└─────────────────────────────────────────────────────────────────┘

Still open:

• filtering of amountB because of value 0

You want to use the FILTER function

MATCH

<complex satement>

WITH

  count(DISTINCT typeA) AS amountA,

  count(DISTINCT typeB) AS amountB,

  count(DISTINCT typeC) AS amountC,

  count(DISTINCT typeD) AS amountD

// Reformat to list

WITH

  [{name:'amountA', value:amountA}, {name:'amountB', value:amountB}, {name:'amountC', value:amountC}, {name:'amountD', value:amountD}] AS quantities

// Filter out 0's

WITH filter(x IN quantities WHERE x.value > 0) AS quantities

// Convert list to string

RETURN

  reduce(result = quantities[0].name + ": " + quantities[0].value, n IN tail(quantities) | result + ", " + n.name + ": " + n.value) AS formattedQuantities; AS formattedQuantities;

Note that this returns null if all values are 0 (null + string = null)