How to create node for nested query inside an array?

[kelvin159]

Hey, i'm new with Neo4j and I got a problem on how to create a node for nested query that is inside an array from a csv file.
The csv file has this field:
"keywords": "[{'id': 240, 'name': 'underdog'}, {'id': 378, 'name': 'prison'}, {'id': 730, 'name': 'factory worker'}, {'id': 1563, 'name': 'prisoner'}, {'id': 1787, 'name': 'helsinki'}, {'id': 10183, 'name': 'independent film'}, {'id': 13072, 'name': 'falling in love'}]"

This is just one of many rows from the file and there are repetition.

Currently what i got is this code:

LOAD CSV WITH HEADERS FROM "file:///keywords.csv" AS line
WITH line, SPLIT(replace(replace(replace(line.keywords, '"', ''), '[', ''), ']', ''), ',') AS keywords
FOREACH (keyword IN keywords| MERGE (k:Keywords {keyword_id: keyword}))

But the result is that is separate the id and the name into different node. I wanted it to have the ID and name in same node without any repetition. So, anyone please help me in solving this problem.

[julian_n_de_jon]

Hi,

I believe you need to convert the string into a recognised Json-object. Also, you use a comma to separate the json object's properties, as well as separating the Json objects.

The following code worked for me, if you have the APOC library activated:

LOAD CSV WITH HEADERS FROM "file:///testdata.csv" as l
WITH REPLACE(REPLACE(REPLACE(l.keywords,'"',''),'[',''),']','') as x
UNWIND SPLIT(REPLACE(x,'}, {','}|{'),"|") as p
WITH apoc.convert.fromJsonmap(p) AS obj
MERGE (k:Keyword {id: obj.id})
ON CREATE SET k = obj
RETURN k

Hope that helps,
Nick

[kelvin159]

Hey, Thank you for helping me solving this problems.
But, apparently one of the rows from the files make an error.

[{'id': 612, 'name': 'hotel'}, {'id': 613, 'name': "new year's eve"}, {'id': 616, 'name': 'witch'}, {'id': 622, 'name': 'bet'}, {'id': 922, 'name': 'hotel room'}, {'id': 2700, 'name': 'sperm'}, {'id': 12670, 'name': 'los angeles'}, {'id': 160488, 'name': 'hoodlum'}, {'id': 187056, 'name': 'woman director'}, {'id': 198129, 'name': 'episode film'}]

it contains " " instead of ' '. So, hopefully you can help me figure out the code.

Thanks

[kelvin159]

Nevermind, found out the solution. Thanks

[julian_n_de_jon]

For anyone else who's got this issue, I would do a replace (or regex replace) statement that changes all instances of " to ' right at the start, so that you've got consistency.

Nick

[mdfrenchman]

@julian.n.de.jong, except then you have the issue of apostrophe inside of the string potentially being replaced.

I'd use double quote " to indicate the string. That ensures you can use single quote for apostrophe inside of the string. Along with using 2*" to replace double quote inside the string (I've run into this for symbol for inches in data).

I'm not able to look for it atm, but I thought neo4j had a way to import/load from JSON either via apoc or regular cypher? That would be easier than splitting and all that if so.
I had to go look to see if I was imagining things.... you want to use: apoc.load.json if apoc is avialable.