How to convert a list of strings as an array by cypher?

[lingvisa]

I have an array of strings as property value, and what's the cypher command to convert it as an array?

string_property = ["a", "b", "c"]

This is. a string with array format. I want to change it into this format when exporting the graph:
string_property = "a|b|c"

I need to use it in the export procedure:

MATCH (m:Sports)
WITH collect(DISTINCT m) AS M
CALL apoc.export.csv.data( M, [], null,  {stream:true, batchSize:2000000}) YIELD data
WITH apoc.text.replace(data, '(?:,"_start","_end","_type"|,,,)(?=\n)', '') AS mdata
WITH REDUCE(reduced="", term IN mdata | reduced+term+'|') as reducedStr
WITH LEFT(reducedStr,SIZE(reducedStr)-1) as mdata
RETURN mdata

The REDUCE isn't quite right, since mdata is a string produced by apoc.text.replace(), not an array. My goal is to export the array of property from the original:

["a", "b", "c"]

to
"a|b|c"

So I need to manipulate the string created by apoc.export.csv.data. The REDUCE shouldn't work, since mdata is a big string with everything in it. I still need to use something like 'replace' to change the format of the string.

If that's hard, I can do it the other way: in loading csv, I can convert the string to an array:
string_property = ["a", "b", "c"]

The split function can not do this.

[koji]

Hi @lingvisa

Is this Cypher your answer?

WITH ["a","b","c"] AS string_property
RETURN apoc.text.join(string_property, '|') as string

[lingvisa]

koji:

WITH ["a","b","c"] AS string_property
RETURN apoc.text.join(string_property, '|') as string

Hi, @koji, my problem is how to convert an array-like string into an array. In your example, ["a","b","c"] is already an array. In my question, ["a","b","c"] is a string which load from a csv column.

[koji · Accepted solution]

Hi @lingvisa

How about this one.

WITH '["a","b","c"]' AS string_property
WITH replace(replace(replace(string_property,'[',''),']',''),'"','') AS string_property
WITH split(string_property,',') AS string_property
RETURN apoc.text.join(string_property, '|') as string

This is the shortest one.

WITH '["a","b","c"]' AS string_property
RETURN apoc.text.join(split(replace(replace(replace(string_property,'[',''),']',''),'"',''),','), '|') as string

[lingvisa]

Hi, Koji:

That would work, and I used a regular express to put '[' and ']' and '"' together and used one replace function to do that. This would work. However, my actual case is a little more complex. I used it in a apoc.csv.load() block and I also embed it into this:

CALL apoc.periodic.iterate

function. And this always reports a syntax error, no matter how I edit the query statement. So I ended up with dropping this external function, and just used the 'CALL apoc.load.csv' function, and it works. If my data is big enough that needs me to use CALL apoc.periodic.iterate, I will come back to re-visit this.

[andrew_bowman]

There's an APOC function for doing this kind of string-to-list converstion:

WITH '["a","b","c"]' AS string_property
RETURN apoc.convert.fromJsonList(string_property) as listOfStrings

[koji]

@lingvisa

If it's the Json string, @andrew.bowman 's suggestion is a good one.

WITH '["a","b","c"]' AS string_property
RETURN apoc.text.join(split(replace(replace(replace(string_property,'[',''),']',''),'"',''),','), '|') as string

This Cypher has the same return value as below.

WITH '["a","b","c"]' AS string_property
RETURN apoc.text.join(apoc.convert.fromJsonList(string_property), '|') as string

[lingvisa]

koji:

["a","b","c"]

["a","b","c"]: This is the apoc.csv.export's output format for array property, and the question I am asking here is regards how to load the data in this format.

[ameyasoft]

Here is a solution:

I created a simple csv file with one column:
In these situations, we need to have a different delimiter other than ','. 
c1
["a","b","c"]

LOAD CSV WITH HEADERS FROM "file:///c1.csv" AS row FIELDTERMINATOR '|'
WITH row
with row.c1 as a1
with replace(a1, '[', '') as a2
with replace(a2, ']', '') as a22
with replace(a22, '"', '') as a23
return replace(a23, ',', '|')

Result:
"a|b|c"