Read json property names in json with CYPHER

Question

I have this kind of input JSON file for my Neo4J database :

    {"list":[
    {
      "extraction":{"extractorId":"45485","date":"20211201","location":"France"},
      "actors":{"88187":"Toto","74017":"Titi","78901":"Tata","45867":"Tutu"},
      "data":{"2877:-99033:-979":{"4934":[45867],"4935":[88187],"4933":[74017],"4932":[45867],"4931":[78901,45867],"4930":[78901]}}
    },

    {"extraction":{"extractorId":"45485","date":"20211201","location":"England"},
     "actors":{"42815":"Lala","45867":"Tutu"},
     "data":{"2877:-99033:-979":{"4939":[86970],"4934":[74975,45867],"4935":[42815,88187],"4933":[74017],"4932":[45867],"4931":[78901,45867],"4930":[78901]}}}
    ]}

As you can see, this is not a standard JSON format, because property names can be values too.

How can I read this with UNWIND, FOREACH and so on ? A one-liner CYPHER request is not mandatory.

For example, if I want to create nodes with (a:Actor {id,name}), if I try this :

WITH "file:///data.json" as json
CALL apoc.load.json(json) YIELD value
UNWIND value.list AS item
UNWIND item.actors AS actors
RETURN actors

I get

row1 :
{
  "45867": "Tutu",
  "74017": "Titi",
  "78901": "Tata",
  "88187": "Toto"
}
row 2 : 
{
  "42815": "Lala",
  "45867": "Tutu",
}

How can I get the id and the name to use them in the MERGE node cypher command ?

stellasia stellasia · Accepted Answer · 2021-02-18T09:24:09

The keys function can help here. Elaborating on your initial query:

WITH "file:///data.json" as json
CALL apoc.load.json(json) YIELD value
UNWIND value.list AS item
UNWIND item.actors AS actors
// Extract map's keys, that will be the ID
WITH keys(actors) as keys, actors
// "loop" over keys
UNWIND keys as key
RETURN key as id, actors[key] as name

Result:

╒═══════╤══════╕
│"id"   │"name"│
╞═══════╪══════╡
│"78901"│"Tata"│
├───────┼──────┤
│"45867"│"Tutu"│
├───────┼──────┤
│"74017"│"Titi"│
├───────┼──────┤
│"88187"│"Toto"│
├───────┼──────┤
│"45867"│"Tutu"│
├───────┼──────┤
│"42815"│"Lala"│
└───────┴──────┘

Read json property names in json with CYPHER

1 Answers