Aggregation by Group By

[balbreaker75]

Hi,
I'm new to neo4j and I'm learning. my question may seem very basic and simple for some people but after extensive research, I did not find a solution to my problem.
I'm trying to import data from JSON file and creating the nodes but i don't know how we can do the aggregation when we are trying to create the nodes. I have 3 fields as follows in my source:

id_station id_pdc nb_place
id1 id11 1
id1 id12 1
id1 id13 1
id2 id21 1
id2 id22 1
id3 id31 1

And I would like to aggregate the number of places (nb_place) per station, and create the 3 "Station" nodes with the "Nb place" property as follows:

id_station nb_place
id1 3
id2 2
id3 1

Thank you for your help and suggestion.

[koji]

This code can be used when the number of records is small.

WITH "file:///place.json" AS url
CALL apoc.load.json(url) YIELD value
UNWIND value.items AS item
MERGE (n:Pdc {id_station:item.id_station})
  ON CREATE SET n.nb_place = item.nb_place
  ON MATCH SET n.nb_place = n.nb_place + item.nb_place

Result text

{"nb_place":3,"id_station":"id1"}
{"nb_place":2,"id_station":"id2"}
{"nb_place":1,"id_station":"id3"}

<NEO4J_HOME>/import/place.json

{
    "items": [
        {
            "id_station": "id1",
            "id_pdc": "id11",
            "nb_place": 1
        },
        {
            "id_station": "id1",
            "id_pdc": "id12",
            "nb_place": 1
        },
        {
            "id_station": "id1",
            "id_pdc": "id13",
            "nb_place": 1
        },
        {
            "id_station": "id2",
            "id_pdc": "id21",
            "nb_place": 1
        },
        {
            "id_station": "id2",
            "id_pdc": "id22",
            "nb_place": 1
        },
        {
            "id_station": "id3",
            "id_pdc": "id31",
            "nb_place": 1
        }
    ]
}

[balbreaker75]

Hi, Koji,
Thank you for your reply.

The structure of my json file is an array like this:

[
{
"datasetid": "bornes-de-recharge-pour-vehicules-electriques",
"recordid": "fd4c520f950a9560d52996b164da52dcd0810501",
"fields": {
"id_station": "FRV75EBELI11",
"id_pdc": "FRV75EBELI1112",
"nb_place": "1"
}
},
{
.....
},
]

and UNWIND value.items AS item doesn't work in my case
i need to select the pairs of the fields part

[koji · Accepted solution]

Hi,

I modified the data.
Is this data structure correct?
<NEO4J_HOME>/import/place2.json

[
    {
        "datasetid": "datasetid1",
        "recordid": "recordid1",
        "fields": {
            "id_station": "id1",
            "id_pdc": "id11",
            "nb_place": "1"
        }
    },
    {
        "datasetid": "datasetid2",
        "recordid": "recordid2",
        "fields": {
            "id_station": "id1",
            "id_pdc": "id12",
            "nb_place": "1"
        }
    },
    {
        "datasetid": "datasetid3",
        "recordid": "recordid3",
        "fields": {
            "id_station": "id1",
            "id_pdc": "id13",
            "nb_place": "1"
        }
    },
    {
        "datasetid": "datasetid4",
        "recordid": "recordid4",
        "fields": {
            "id_station": "id2",
            "id_pdc": "id21",
            "nb_place": "1"
        }
    },
    {
        "datasetid": "datasetid5",
        "recordid": "recordid5",
        "fields": {
            "id_station": "id2",
            "id_pdc": "id22",
            "nb_place": "1"
        }
    },
    {
        "datasetid": "datasetid6",
        "recordid": "recordid6",
        "fields": {
            "id_station": "id3",
            "id_pdc": "id31",
            "nb_place": "1"
        }
    }
]

I modified the Cypher a little.

WITH "file:///place2.json" AS url
CALL apoc.load.json(url) YIELD value
UNWIND value.fields AS item
MERGE (n:Pdc {id_station:item.id_station})
  ON CREATE SET n.nb_place = toInteger(item.nb_place)
  ON MATCH SET n.nb_place = n.nb_place + toInteger(item.nb_place)

This is the result.

{"nb_place":3,"id_station":"id1"}
{"nb_place":2,"id_station":"id2"}
{"nb_place":1,"id_station":"id3"}

I don't understand that "nb_place" is a number or just a string.
And please use this when you have a small number of records.

[balbreaker75]

Thank you again Koji for your help
yes the date structure is correct and your transformation to Interger too because nb_place isn't a number.
have a nice day.