# Importing a JSON column from a csv **URL:** \ **Category:** Cypher\ **Tags:** apoc, cypher, import\ **Created:** [October 21, 2020, 12:59pm UTC](https://community.neo4j.com/t/importing-a-json-column-from-a-csv/27609 "2020-10-21T12:59:12Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![matthew\_gregory](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/matthew_gregory/32/4909_2.png) [@matthew\_gregory](https://community.neo4j.com/u/matthew_gregory)\ **Post date:** [October 21, 2020, 12:59pm UTC](https://community.neo4j.com/t/importing-a-json-column-from-a-csv/27609/1 "2020-10-21T12:59:12Z")
We are currently enriching our knowledge graph with NLP derived metadata about the content on [GOV.UK](http://GOV.UK). See this [blog post for context](https://insidegovuk.blog.gov.uk/2020/08/07/one-graph-to-rule-them-all/). Our desired outcome is each piece of content (a page on [GOV.UK](http://GOV.UK); labelled a Cid; circa. ~ 500k pages) will have a relationship with the various entities it contains or mentions in its text. This can be Organisations, People, Dates and Services etc.. We use a modified version of BERT (trained on our data; we call it GovNER) to identify entities within each page. The output is a csv that looks like this (I've removed some entities and included only one row for ease of reading): ```auto base_path,entities,updated_at,govner_version /government/news/new-digital-resource-for-charity-trustees-launched,"[{""end"": 144, ""entity"": ""charity"", ""entity_type"": ""ORGANIZATION"", ""start"": 137}, {""end"": 2788, ""entity"": ""Commission"", ""entity_type"": ""ORGANIZATION"", ""start"": 2778}, {""end"": 4155, ""entity"": ""Commission"", ""entity_type"": ""ORGANIZATION"", ""start"": 4145}, {""end"": 4822, ""entity"": ""Commission"", ""entity_type"": ""ORGANIZATION"", ""start"": 4812}, {""end"": 1557, ""entity"": ""questions"", ""entity_type"": ""CONTACT"", ""start"": 1548}, {""end"": 2175, ""entity"": ""email"", ""entity_type"": ""CONTACT"", ""start"": 2166}, {""end"": 2847, ""entity"": ""questions"", ""entity_type"": ""CONTACT"", ""start"": 2838}, {""end"": 2979, ""entity"": ""questions"", ""entity_type"": ""CONTACT"", ""start"": 2970}]",2020-10-15 17:56:30.955360,0.1 ``` The base\_path is unique for each Cid, so we can use this to look up our Cids. We then would like to extract the data from the entities column which is in JSON format. As you can see there are a variety of entity\_types which corresponds to different node labels. We need to iterate through and create a labelled node if it doesn't exist then create a relationship between the Cid and that entity. We would like to store the location of that entity in the text (start, end) as properties of the edge (in 3.5.16 I believe you can only store one property on an edge?). Historically, we've used python to get our data into a simple nodelist and edgelist format, with one csv per nodelist label and one edge csv per relationship. We'd normally do the data wrangling in python then have a simple LOAD CSV in Cypher to load in each of the different node labels as the graph gets built. This will result in quite a few intermediary csvs, is there a cleaner way to do this using just Cypher and / or APOC? We've not reviewed the best way to do this for about a year and are aware that things might have moved on with Cypher and APOC. We are using Community edition 3.5.16. ---
**Author:** ![tony.chiboucas](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/tony.chiboucas/32/5980_2.png) [@tony.chiboucas](https://community.neo4j.com/u/tony.chiboucas)\ **Post date:** [October 21, 2020, 10:21pm UTC](https://community.neo4j.com/t/importing-a-json-column-from-a-csv/27609/2 "2020-10-21T22:21:49Z")
- [apoc.convert.fromJsonMap()](https://neo4j.com/labs/apoc/4.1/overview/apoc.convert/apoc.convert.fromJsonMap/) load a raw JSON string as an object in Cypher. - Labels cannot be created dynamically, but you could use [apoc.do.case](https://neo4j.com/labs/apoc/4.0/overview/apoc.do/apoc.do.case/) to selectively assign a label based on existing data. I'm not really following what you're expecting the graph to look like. Perhaps you can manually create a small sample of your expected graph, at [https://console.neo4j.org](https://console.neo4j.org)? The start and end don't really make sense to me without context. That said, I can probably get you _most_ of the way there: ```auto LOAD CSV WITH HEADERS FROM "https://someurl.gov.uk/yourfile.csv" AS csvLine MERGE (cid :Cid {base_path: csvLine.base_path}) WITH cid, apoc.convert.fromJsonMap(csvLine.entities) as entities UNWIND entities AS entity CREATE (e :Entity) SET e = entity CREATE (cid)-[r :REL]->(e) SET r.start = e.start, r.end = e.end ``` ---
**Author:** ![matthew\_gregory](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/matthew_gregory/32/4909_2.png) [@matthew\_gregory](https://community.neo4j.com/u/matthew_gregory)\ **Post date:** [October 23, 2020, 9:32am UTC](https://community.neo4j.com/t/importing-a-json-column-from-a-csv/27609/3 "2020-10-23T09:32:49Z")
> [@tony.chiboucas](#): > > ```auto > LOAD CSV WITH HEADERS FROM "https://someurl.gov.uk/yourfile.csv" AS csvLine > MERGE (cid :Cid {base_path: csvLine.base_path}) > WITH cid, apoc.convert.fromJsonMap(csvLine.entities) as entities > UNWIND entities AS entity > CREATE (e :Entity) SET e = entity > CREATE (cid)-[r :REL]->(e) SET r.start = e.start, r.end = e.end > > ``` Thanks! My first attempt looked like this ```auto // my first attempt WITH apoc.convert.fromJsonList( ' [ {"end": 144, "entity": "charity", "entity_type": "ORGANIZATION", "start": 137}, {"end": 2788, "entity": "Commission", "entity_type": "ORGANIZATION", "start": 2778}, {"end": 1557, "entity": "questions", "entity_type": "CONTACT", "start": 1548}, {"end": 2175, "entity": "email", "entity_type": "CONTACT", "start": 2166}, {"end": 2847, "entity": "questions", "entity_type": "CONTACT", "start": 2838}, {"end": 2979, "entity": "questions", "entity_type": "CONTACT", "start": 2970} ] ' ) AS batch WITH batch UNWIND batch as row //RETURN row // https://gist.github.com/jexp/caeb53acfe8a649fecade4417fb8876a#lookup-by-id-or-even-list-of-ids // this doesn't do what we want, we want to specify the label if we can MERGE (to:Entity { entity_type: row.entity_type, name: row.entity}) WITH row, to MATCH (from:Cid {name: "/government/news/new-digital-resource-for-charity-trustees-launched"}) MERGE (from)-[rel:CONTAINS_ENTITY { start_end_indices: '[' + row.start + ', ' + row.end + ']'} ]->(to) ``` And didn't do quite what we wanted. So a workaround for this caveat `Labels cannot be created dynamically, but you could use apoc.do.case to selectively assign a label based on existing data.` might be to create a general `Entity` and store the `entity_type` as a property, then use `apoc.do.case` to cycle through and add an additional more specific label as desired? Rather than having the umbrella `Entity` label, we want the entity type to determine the label of the node. So we have a bunch of labels like; Person, Contact, Organisation, Finance, Service etc. Thanks for your help by the well, it's reassured that I'm on the right track ---
**Author:** ![matthew\_gregory](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/matthew_gregory/32/4909_2.png) [@matthew\_gregory](https://community.neo4j.com/u/matthew_gregory)\ **Post date:** [October 23, 2020, 3:03pm UTC](https://community.neo4j.com/t/importing-a-json-column-from-a-csv/27609/4 "2020-10-23T15:03:54Z")
Got the entities in and moved their `entity_type` property to a label as desired. ```auto // This proof of principle in cypher takes some test data from the entities column in entity_report.csv, // the govner output (CAVEAT: had to remove double quotes from things) // TODO: get it reading from the .csv WITH apoc.convert.fromJsonList( ' [ {"end": 144, "entity": "charity", "entity_type": "ORGANIZATION", "start": 137}, {"end": 2788, "entity": "Commission", "entity_type": "ORGANIZATION", "start": 2778}, {"end": 1557, "entity": "questions", "entity_type": "CONTACT", "start": 1548}, {"end": 2175, "entity": "email", "entity_type": "CONTACT", "start": 2166}, {"end": 2847, "entity": "questions", "entity_type": "CONTACT", "start": 2838}, {"end": 2979, "entity": "questions", "entity_type": "CONTACT", "start": 2970} ] ' ) AS entities WITH entities UNWIND entities as entity // this doesn't do what we want, we want to specify the label dynamically if we can MERGE (e:Entity { entity_type: entity.entity_type, name: entity.entity}) WITH e, entity // TODO: replace specific url with cid.name from matching row in csv MATCH (cid:Cid {name: "/government/news/new-digital-resource-for-charity-trustees-launched"}) MERGE (cid)-[r :HAS_ENTITY { start_end_indices: [toInteger(entity.start) , toInteger(entity.end)]} ]->(e) ; // We can't set labels dynamically // Instead we move the entity_type property to a label, we choose not to remove it // https://neo4j.com/labs/apoc/4.1/graph-updates/graph-refactoring/property-value-label/ MATCH (e:Entity) CALL apoc.create.addLabels( id(e), [apoc.text.upperCamelCase(e.entity_type)] ) YIELD node // we don't remove the generic Entity label //REMOVE node.genre RETURN node ; ``` ![Screenshot 2020-10-23 at 16.01.34](https://us1.discourse-cdn.com/flex021/uploads/neo4jcommunity/original/2X/c/cd97346dafcf17202615572f8ca9f1737c6efbbe.png) ---
**Author:** ![tony.chiboucas](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/tony.chiboucas/32/5980_2.png) [@tony.chiboucas](https://community.neo4j.com/u/tony.chiboucas)\ **Post date:** [October 23, 2020, 5:23pm UTC](https://community.neo4j.com/t/importing-a-json-column-from-a-csv/27609/5 "2020-10-23T17:23:36Z")
Honestly, yours may be the best approach. Dynamically defining the labels when creating the node will still require a two-step cypher. The code you have in your last post will be easier to adjust and maintain than a large `apoc.do.case`. I think you've got it. That said, if you _really_ want to explore the `case` approach, here's a snippet to get you started: ```auto MATCH (cid:Cid {name: "/government/news/new-digital-resource-for-charity-trustees-launched"}) WITH cid, apoc.convert.fromJsonList( ' [ {"end": 144, "entity": "charity", "entity_type": "ORGANIZATION", "start": 137}, {"end": 2788, "entity": "Commission", "entity_type": "ORGANIZATION", "start": 2778}, {"end": 1557, "entity": "questions", "entity_type": "CONTACT", "start": 1548}, {"end": 2175, "entity": "email", "entity_type": "CONTACT", "start": 2166}, {"end": 2847, "entity": "questions", "entity_type": "CONTACT", "start": 2838}, {"end": 2979, "entity": "questions", "entity_type": "CONTACT", "start": 2970} ] ' ) AS entities UNWIND entities as entity WITH cid, entity WHERE entity.entity_type IS NOT NULL AND entity.entity IS NOT NULL CALL apoc.do.case([ type = "ORGANIZATION", " MERGE (e:Organization {name: name}) MERGE (cid)-[r :ORG { start_end_indices: [toInteger(entity.start) , toInteger(entity.end)]} ]->(e) ", type = "CONTACT", " MERGE (e:Contact {name: name}) MERGE (cid)-[r :CONTACT { start_end_indices: [toInteger(entity.start) , toInteger(entity.end)]} ]->(e) " ], " MERGE (e:EntityUnhandled { entity_type: type, name: name}) MERGE (cid)-[r :HAS_ENTITY { start_end_indices: [toInteger(entity.start) , toInteger(entity.end)]} ]->(e) ", {cid: cid, entity: entity, type:entity.entity_type, name: entity.entity} ) YIELD value RETURN value ; ``` ---
**Author:** ![matthew\_gregory](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/matthew_gregory/32/4909_2.png) [@matthew\_gregory](https://community.neo4j.com/u/matthew_gregory)\ **Post date:** [November 2, 2020, 5:13pm UTC](https://community.neo4j.com/t/importing-a-json-column-from-a-csv/27609/6 "2020-11-02T17:13:21Z")
For completion, when reading from the file. ```auto // This writes entities from entity_report.csv into the graph // for testing on local USING PERIODIC COMMIT LOAD CSV WITH HEADERS FROM "file:///entity_report.csv" AS csvLine CREATE (cid:Cid {name: csvLine.base_path}) USING PERIODIC COMMIT LOAD CSV WITH HEADERS FROM "file:///entity_report.csv" AS csvLine MATCH (cid:Cid {name: csvLine.base_path}) WITH cid, apoc.convert.fromJsonList(csvLine.entities) AS entities UNWIND entities as entity // this doesn't do what we want, we want to specify the label dynamically if we can MERGE (e:Entity { entity_type: entity.entity_type, name: entity.entity}) WITH e, entity, cid MERGE (cid)-[r :HAS_ENTITY { start_end_indices: [toInteger(entity.start) , toInteger(entity.end)]} ]->(e) ; // SECOND CYPHER // We can't set labels dynamically // Instead we move the entity_type property to a label, we choose not to remove it, as we change it's case // https://neo4j.com/labs/apoc/4.1/graph-updates/graph-refactoring/property-value-label/ // TODO:: Organisation instead of Organization; could fix here or upstream MATCH (e:Entity) CALL apoc.create.addLabels( id(e), [apoc.text.upperCamelCase(e.entity_type)] ) YIELD node REMOVE node.entity_type RETURN node ; ```