How to create nodes in nested array of JSON

[esakkiammal1036]

Hi,
I am new to Neo4j , I want to import json file of particular cities into neo4j, but it has lots and lots of nested array , I dont know how to access the inner value of field, and searched many things, yet not get any answer,

neo4j version,: 3.5.17
desktop version: 1.3.10
browser version: 4.2.0

Sample json file:
**{"type":"FeatureCollection", **
"features": [
{"type":"Feature","geometry":{"type":"Polygon","coordinates":[[[-75.915125,38.720037999999995],[-75.915359,38.709506],[-75.909801,38.704992],[-75.909369,38.703889],[-75.907619,38.706168999999996],[-75.904116,38.704648],[-75.89867699999999,38.707256],[-75.912858,38.719997],[-75.915125,38.720037999999995]]]},"properties":{"STATEFP":"24","PLACEFP":"63825","PLACENS":"02391372","AFFGEOID":"1600000US2463825","GEOID":"2463825","NAME":"Preston","LSAD":"43","ALAND":1402223,"AWATER":9096}},

{"type":"Feature","geometry":{"type":"Polygon","coordinates":[[[-77.932906,39.657125],[-77.936332,39.653343],[-77.931286,39.653847999999996],[-77.925725,39.657014],[-77.926906,39.659485],[-77.932906,39.657125]]]},"properties":{"STATEFP":"24","PLACEFP":"17600","PLACENS":"02390800","AFFGEOID":"1600000US2417600","GEOID":"2417600","NAME":"Clear Spring","LSAD":"43","ALAND":297503,"AWATER":0}}
]}

I want to create nodes of city under city nodes, need information of geometry (type, coordinates), properties(stateFP,placeFP,Name)

I want this format(below attachment), can anyone help and tell the query for this?

I have tried below query, but its not give the expected output, could you anyone please correct the query

CALL apoc.load.jsonArray("file:///Maryland_Cities_cb_2019_24_place_500k.json")
yield value as file
WITH *
Unwind file.features as features
UNWIND features.geometry AS geometry
Unwind geometry.coordinates as geometryCoordinates
Unwind features.properties as properties
unwind properties.STATEFP as stateId
unwind properties.PLACEFP as placeId
unwind properties.NAME as cityName
MERGE (ct:city{coordinates:geometryCoordinates,stateId:stateId,place:placeId,cityName:cityName})
return *

Thanks lot for Advance

[tarendran_vivek]

Hi @esakkiammal1036,

Neo4j does not allow nested arrays to be stored as properties of a node. Thus, the polygon coordinates will have to be stored somewhere else.

CALL apoc.load.jsonArray("file:///Maryland_Cities_cb_2019_24_place_500k.json")
yield value AS file
WITH file.features AS features
UNWIND features AS citynodes
WITH citynodes.properties AS props
MERGE (ct:city {stateId:props.STATEFP, place:props.PLACEFP, cityName:props.NAME})
RETURN *

[esakkiammal1036]

Thanks for the reply Tarendran,
But my need is to load the polygon coordinates into neo4j, Could you please say how to load those into Neo4j. I need it immediately.. Could you please send the solution to me?

Thanks for great help in advance.

[klaus_blass]

Hi,
I just had the problem of storing a 3-dimensional array as a property. The way I did it is this:

1. flattened the 3-dim array into a single array containing all the values (column-first)
2. wrote a user-function to index into the array like this:

f(x,y,z) = (x-1)ab + (y-1)*b + z

where a and b are the dimensions of y and z.

This, of course, only works if the dimensions are the same for each row.
In your case the innermost dimension is always 2 (lon, lat), but the outer dimension which is a list of points, seems not be the same number of points for all cities?

[clem]

Neo4J allows you to store a "list" (really an "array" where all the members must be of the same scalar type) into a property. Have you tried that?

That means you have to flatten the array:

[[[-75.915125,38.720037999999995],[-75.915359,38.709506],[-75.909801,38.704992],[-75.909369,38.703889],[-75.907619,38.706168999999996],[-75.904116,38.704648],[-75.89867699999999,38.707256],[-75.912858,38.719997],[-75.915125,38.720037999999995]]]

=>

[-75.915125,38.720037999999995,-75.915359,38.709506,-75.909801,38.704992,-75.909369,38.703889,-75.907619,38.706168999999996,-75.904116,38.704648,-75.89867699999999,38.707256,-75.912858,38.719997,-75.915125,38.720037999999995]

Which means whenever you process the polygon, you have to extract the pairs of coordinates out of the list.

See:

Neo4j Graph Database Platform

List functions - Neo4j Cypher Manual

List functions return lists of things -- nodes in a path, and so on.

Me being a newbie, what I am NOT clear on, is if you could make a list of Neo4J's spacial types:

Neo4j Graph Database Platform

Spatial values - Neo4j Cypher Manual

Cypher has built-in support for handling spatial values (points), and the underlying database supports storing these point values as properties on nodes and relationships.

You might experiment that.

[klaus_blass]

I just realized that you have only two dimensions and if you use spatial point it becomes a one-dimensional array of points. So you can store it as the value of a property.

[esakkiammal1036]

Thanks for reply Klaus,

But I have doubt , how to flatten the array either using cypher query or have to write some script to do ...

How to convert into single array? Suppose if we have millions amount of geospatial data means, then it really tools some times? I thought,

Could you please tell us how to convert it? It's more useful to me to understand ...

[klaus_blass]

Hi esakkiammal (which part of this is your first name?),

as I said in my previous post, your case is easier than I thought:
Just save the coordinates as a single list of points.
Here is the cypher to do it (I tested with the two cities you gave in your original post):

CALL apoc.load.json("test.json")
yield value as file
Unwind file.features as features
Unwind features.properties as properties
unwind properties.NAME as cityName
UNWIND features.geometry AS geometry
Unwind geometry.coordinates as geometryCoordinates
UNWIND geometryCoordinates as geoCoord
with cityName, collect(point({
latitude: toFloat(geoCoord[0]),
longitude: toFloat(geoCoord[1])
})) as points
MERGE (ct:city{cityName:cityName})
SET ct.coordinates = points
return *

I only included the city name, you can add the other properties...

Klaus

[esakkiammal1036]

Great Thanks for reply. You can call me Esai. (

I have tried this. But facing this error :
Expected a String or Number, got: List{Double(-7.676886e+01), Double(3.897851e+01)}

I think its file problem, but I could not attached the file here,

Could you please say how to solve the issues?

[klaus_blass]

Esai, for testing I put your two records in a file like this:

{
"features": [
{"type":"Feature","geometry":{"type":"Polygon","coordinates":[[[-75.915125,38.720037999999995],[-75.915359,38.709506],[-75.909801,38.704992],[-75.909369,38.703889],[-75.907619,38.706168999999996],[-75.904116,38.704648],[-75.89867699999999,38.707256],[-75.912858,38.719997],[-75.915125,38.720037999999995]]]},"properties":{"STATEFP":"24","PLACEFP":"63825","PLACENS":"02391372","AFFGEOID":"1600000US2463825","GEOID":"2463825","NAME":"Preston","LSAD":"43","ALAND":1402223,"AWATER":9096}},
{"type":"Feature","geometry":{"type":"Polygon","coordinates":[[[-77.932906,39.657125],[-77.936332,39.653343],[-77.931286,39.653847999999996],[-77.925725,39.657014],[-77.926906,39.659485],[-77.932906,39.657125]]]},"properties":{"STATEFP":"24","PLACEFP":"17600","PLACENS":"02390800","AFFGEOID":"1600000US2417600","GEOID":"2417600","NAME":"Clear Spring","LSAD":"43","ALAND":297503,"AWATER":0}}
]
}

Also note that I used apoc.load.json, not apoc.load.jsonArray!
Could it be that the error comes from some other point in your query?

Klaus

[esakkiammal1036]

Yes, Understood you are using just two records in my file..

I have used apoc.load.json to load the file, but don't know why it throws the error because I have large data in file, couldn't able to check and verify that error.why it occurs?

[klaus_blass]

Hi Esai,

For debugging the problem I would do the following:

1. take my query and my little 2-record file and see if it runs ok on your machine
   (it does on mine).
2. If your file has higher level objects (it looks like you commented out an object
   "type":"FeatureCollection"), put them in this small test file and modify the query.
3. When this runs okay run it on your complete file.
4. I you still get the error, check your file for problems in some record further down.

since I don't have your file I cannot do debug it for you...

Klaus

[clem]

The correct function to use is toFloat()

See: https://neo4j.com/docs/cypher-manual/current/functions/scalar/#functions-tofloat

[esakkiammal1036]

Yes Clem. I have used that function.but it throw me the error.

[ameyasoft]

I reproduced your error, Esai. geometryCoordinates is a nested list and we should unwind. 

Klaus code worked for me. I modified his code to match your code. Here is the code:

CALL apoc.load.json("esai1.json")
yield value as file
Unwind file.features as features
Unwind features.properties as properties
unwind properties.NAME as cityName
UNWIND features.geometry AS geometry
Unwind geometry.coordinates as geometryCoordinates
unwind properties.STATEFP as stateId
unwind properties.PLACEFP as placeId
UNWIND geometryCoordinates as geoCoord
with cityName, stateId, placeId, collect(point({
latitude: toFloat(geoCoord[0]),
longitude: toFloat(geoCoord[1])
})) as points

MERGE (ct:City{coordinates:points,stateId:stateId,place:placeId,cityName:cityName})
return ct.cityName, ct.stateId, ct.place, ct.coordinates

Result ( a sample):