This is a snippet of the JSON document:<\/p>\n
{\r\n \"categories\": [\r\n {\r\n \"name\": \"Index Crime\", \r\n \"sub_categories\": [\r\n {\r\n \"code\": \"01A\", \r\n \"description\": \"Homicide 1st & 2nd Degree\"\r\n }\r\n ]\r\n }, \r\n {\r\n \"name\": \"Non-Index Crime\", \r\n \"sub_categories\": [\r\n {\r\n \"code\": \"01B\", \r\n \"description\": \"Involuntary Manslaughter\"\r\n }\r\n ]\r\n }, \r\n {\r\n \"name\": \"Violent Crime\", \r\n \"sub_categories\": [\r\n {\r\n \"code\": \"01A\", \r\n \"description\": \"Homicide 1st & 2nd Degree\"\r\n }\r\n ]\r\n }\r\n ]\r\n}<\/pre>\nWe want to create the following graph structure from this document:<\/p>\n
![\"2015-07-23_06-46-50\"](\"http:\/\/www.webcodegeeks.com\/wp-content\/uploads\/2015\/07\/2015-07-23_06-46-50-300x127.png\")
<\/a><\/p>\nWe can then connect the crimes to the appropriate sub category and write aggregation queries that drill down from the category.<\/p>\n
To do this we\u2019re going to have to pass the JSON document to Neo4j via its HTTP API rather than through the browser. Luckily there are drivers available for {insert your favourite language here}<\/a> so we should still be good.<\/p>\nPython is my current goto language so I\u2019m going to use py2neo<\/a> to load the data in.<\/p>\nLet\u2019s start by writing a simple query which passes our JSON document in and gets it straight back. Note that I\u2019ve updated my Neo4j password to be \u2018foobar\u2019 \u2013 replace that with your equivalent if you\u2019re following along:<\/p>\n
import json\r\nfrom py2neo import Graph, authenticate\r\n\u00a0\r\n# replace 'foobar' with your password\r\nauthenticate(\"localhost:7474\", \"neo4j\", \"foobar\")\r\ngraph = Graph()\r\n\u00a0\r\nwith open('categories.json') as data_file:\r\n json = json.load(data_file)\r\n\u00a0\r\nquery = \"\"\"\r\nRETURN {json}\r\n\"\"\"\r\n\u00a0\r\n# Send Cypher query.\r\nprint graph.cypher.execute(query, json = json)<\/pre>\n$ python import_categories.py\r\n | document\r\n---+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\n 1 | {u'categories': [{u'name': u'Index Crime', u'sub_categories': [{u'code': u'01A', u'description': u'Homicide 1st & 2nd Degree'}, {u'code': u'02', u'description': u'Criminal Sexual Assault'}, {u'code': u'03', u'description': u'Robbery'}, {u'code': u'04A', u'description': u'Aggravated Assault'}, {u'code': u'04B', u'description': u'Aggravated Battery'}, {u'code': u'05', u'description': u'Burglary'}, {u'code': u'06', u'description': u'Larceny'}, {u'code': u'07', u'description': u'Motor Vehicle Theft'}, {u'code': u'09', u'description': u'Arson'}]}, {u'name': u'Non-Index Crime', u'sub_categories': [{u'code': u'01B', u'description': u'Involuntary Manslaughter'}, {u'code': u'08A', u'description': u'Simple Assault'}, {u'code': u'08B', u'description': u'Simple Battery'}, {u'code': u'10', u'description': u'Forgery & Counterfeiting'}, {u'code': u'11', u'description': u'Fraud'}, {u'code': u'12', u'description': u'Embezzlement'}, {u'code': u'13', u'description': u'Stolen Property'}, {u'code': u'14', u'description': u'Vandalism'}, {u'code': u'15', u'description': u'Weapons Violation'}, {u'code': u'16', u'description': u'Prostitution'}, {u'code': u'17', u'description': u'Criminal Sexual Abuse'}, {u'code': u'18', u'description': u'Drug Abuse'}, {u'code': u'19', u'description': u'Gambling'}, {u'code': u'20', u'description': u'Offenses Against Family'}, {u'code': u'22', u'description': u'Liquor License'}, {u'code': u'24', u'description': u'Disorderly Conduct'}, {u'code': u'26', u'description': u'Misc Non-Index Offense'}]}, {u'name': u'Violent Crime', u'sub_categories': [{u'code': u'01A', u'description': u'Homicide 1st & 2nd Degree'}, {u'code': u'02', u'description': u'Criminal Sexual Assault'}, {u'code': u'03', u'description': u'Robbery'}, {u'code': u'04A', u'description': u'Aggravated Assault'}, {u'code': u'04B', u'description': u'Aggravated Battery'}]}]}<\/pre>\nIt\u2019s a bit ugly but we can see that everything\u2019s there! Our next step is to extract each category into its own row. We can do this by accessing the \u2018categories\u2019 key in our JSON document and then calling the UNWIND<\/a><\/cite> function which allows us to expand a collection into a sequence of rows:<\/p>\nquery = \"\"\"\r\nWITH {json} AS document\r\nUNWIND document.categories AS category\r\nRETURN category.name\r\n\"\"\"<\/pre>\n$ python import_categories.py\r\n | category.name\r\n---+-----------------\r\n 1 | Index Crime\r\n 2 | Non-Index Crime\r\n 3 | Violent Crime<\/pre>\nNow we can create a node for each of those categories. We\u2019ll use the MERGE<\/cite> command so that we can run this script multiple times without ending up with repeat categories:<\/p>\nquery = \"\"\"\r\nWITH {json} AS document\r\nUNWIND document.categories AS category\r\nMERGE (:CrimeCategory {name: category.name}) \r\n\"\"\"<\/pre>\nLet\u2019s quickly check those categories were correctly imported:<\/p>\n
match (category:CrimeCategory)\r\nreturn category<\/pre>\n![\"graph-23\"](\"http:\/\/www.webcodegeeks.com\/wp-content\/uploads\/2015\/07\/graph-23.png\")
<\/a><\/p>\nLooking good so far \u2013 now for the sub categories. We\u2019re going to use the UNWIND<\/cite> function to help us out here as well:<\/p>\nquery = \"\"\"\r\nWITH {json} AS document\r\nUNWIND document.categories AS category\r\nUNWIND category.sub_categories AS subCategory\r\nRETURN category.name, subCategory.code, subCategory.description\r\n\"\"\"<\/pre>\n$ python import_categories.py\r\n | category.name | subCategory.code | subCategory.description\r\n----+-----------------+------------------+---------------------------\r\n 1 | Index Crime | 01A | Homicide 1st & 2nd Degree\r\n 2 | Index Crime | 02 | Criminal Sexual Assault\r\n 3 | Index Crime | 03 | Robbery\r\n 4 | Index Crime | 04A | Aggravated Assault\r\n 5 | Index Crime | 04B | Aggravated Battery\r\n 6 | Index Crime | 05 | Burglary\r\n 7 | Index Crime | 06 | Larceny\r\n 8 | Index Crime | 07 | Motor Vehicle Theft\r\n 9 | Index Crime | 09 | Arson\r\n 10 | Non-Index Crime | 01B | Involuntary Manslaughter\r\n 11 | Non-Index Crime | 08A | Simple Assault\r\n 12 | Non-Index Crime | 08B | Simple Battery\r\n 13 | Non-Index Crime | 10 | Forgery & Counterfeiting\r\n 14 | Non-Index Crime | 11 | Fraud\r\n 15 | Non-Index Crime | 12 | Embezzlement\r\n 16 | Non-Index Crime | 13 | Stolen Property\r\n 17 | Non-Index Crime | 14 | Vandalism\r\n 18 | Non-Index Crime | 15 | Weapons Violation\r\n 19 | Non-Index Crime | 16 | Prostitution\r\n 20 | Non-Index Crime | 17 | Criminal Sexual Abuse\r\n 21 | Non-Index Crime | 18 | Drug Abuse\r\n 22 | Non-Index Crime | 19 | Gambling\r\n 23 | Non-Index Crime | 20 | Offenses Against Family\r\n 24 | Non-Index Crime | 22 | Liquor License\r\n 25 | Non-Index Crime | 24 | Disorderly Conduct\r\n 26 | Non-Index Crime | 26 | Misc Non-Index Offense\r\n 27 | Violent Crime | 01A | Homicide 1st & 2nd Degree\r\n 28 | Violent Crime | 02 | Criminal Sexual Assault\r\n 29 | Violent Crime | 03 | Robbery\r\n 30 | Violent Crime | 04A | Aggravated Assault\r\n 31 | Violent Crime | 04B | Aggravated Battery<\/pre>\nLet\u2019s give sub categories the MERGE<\/cite> treatment too:<\/p>\nquery = \"\"\"\r\nWITH {json} AS document\r\nUNWIND document.categories AS category\r\nUNWIND category.sub_categories AS subCategory\r\nMERGE (c:CrimeCategory {name: category.name})\r\nMERGE (sc:SubCategory {code: subCategory.code})\r\nON CREATE SET sc.description = subCategory.description\r\nMERGE (c)-[:CHILD]->(sc)\r\n\"\"\"<\/pre>\nAnd finally let\u2019s write a query to check what we\u2019ve imported:<\/p>\n
match (category:CrimeCategory)-[:CHILD]->(subCategory)\r\nreturn *<\/pre>\n![\"graph-24\"](\"http:\/\/www.webcodegeeks.com\/wp-content\/uploads\/2015\/07\/graph-24.png\")
<\/a><\/p>\nI hadn\u2019t realised before running this query is that some sub categories sit under multiple categories so that\u2019s quite an interesting insight. The final Python script<\/a> is available on github \u2013 any questions let me know.<\/p>\n\n