Apoc.load.json loads only the first record

Hello again guys.
I modified the settings to enable json to load:
"apoc.import.file.enabled=true"

I have downloaded a bibliography result in json format and have stored it in the correct import directory.
this is my code:
//load json table into NEO4j
CALL apoc.load.json('file:///all_works.json') yield value
unwind value.0 as insti
Merge(a:Author) on create
set a.Firstname =insti.author[0].given,
a.Lastname =insti.author[0].family,
a.Sequence =insti.author[0].sequence

MERGE (i:Institution) on CREATE
set i.Name =insti.institution.name

MERGE (p:Publication) on CREATE
set p.ReferenceCount=insti.is-referenced-by-count,
p.IndexDate = insti.indexed.date-time,
p.IndexYear= insti.indexed.date-parts[0][0],
p.IndexMonth =insti.indexed.date-parts[0][1],
p.IndexDay = insti.indexed.date-parts[0][2],
p.Prefix= insti.prefix,
p.DateDeposited= insti.deposited.date-time,
p.Type=insti.type,
p.Title=insti.title,
p.URL=insti.URL,
p.Score=insti.score,
p.ContainerTitle=insti.container-title,
p.Restrictions=insti.content-domain.crossmark-restriction,
p.Member=insti.member ,
p.DOI=insti.DOI

MERGE (a)-[r:BelongsTo]->(i)
MERGE (a)-[s:Authored]->(p)

The weird thing is that it only created nodes and links for the first record.
What am i missing here? It did not create the nodes for the other 400K records.

Update: Apparently, the first item in the element is the record number (row)
So I mistook the 0 as the main field.

So my question is how to you reference a json array without a header/title?
The keys(insti) becomes "0","1","2","3" etc...

So this line won't work:
unwind value. 0 as insti

Since 0 is record number.... not the array....
Right now the input json file looks like this:
{0:{},1:{},2:{}....

What is the structure of the json file? Is it an array of objects?

We were able to resolve this by asking the extractor to change the format to something like this:
{items:
[ {"tag1":value},
{"tag2": [
(tag21:value),
(tag22:value),
(tag23:value)
]
}
]

I find that to
However, now im having difficulty referencing nested json elements like tag21 to tag23
I find this works but i have to iterate.

CALL apoc.load.json('file:///all_works.json') yield value
unwind value.items as insti
WITH insti.tag2[0].tag22 as x1, insti.tag2[0].tag23 as x2, inti.tag2[0].tag21 as x0
(some processing code here)

So if tag2 has several occurences (like in authors of a paper)
i need to increment it (ie tag2[1] and so forth...
I tried FOREACH but it doesnt work with WITH.

First, solving any problem starts with clearly defining the problem, and cleanly structuring your desired start and end states.

Source data:

{ "items":[
      {"tag1":"value1" },
      {  "tag2":[
            {"tag21":"value21"},
            {"tag22":"value22"},
            {"tag23":"value23"}
         ]
      }
   ]
}

There is not a clean way to conditionally expand a property. Honestly, I think the first extractor format may have been better, but without a clean sample, I can only guess.

Your best bet is a combination of apoc.meta.type, and apoc.do.when.

...
CALL apoc.load.json('file:///all_works.json') YIELD value
UNWIND value.items as item
UNWIND keys(item) as tagN
WITH item, tagN, apoc.meta.isType(item.tagN,"LIST OF ANY") as isList
CALL apoc.when(
  isList,
  ' UNWIND item[tagN] as tagNlist
    UNWIND keys(tagNlist) as tagNN
    // you now have all the data in the list
    // ex: tagN = "tag2" and item[tagN][tagNN] will contain all "value2x" entries
  ',
  ' // single entry, not a list',
  {item: item, tagN: tagN, isList: isList})
YIELD value
RETURN value
1 Like

Thanks Tony, I learned a lot from your reply. I never even knew about apoc.meta.isType and apoc.when.

I am trying to understand the code, and i tried to run it, but got this error:
"Query cannot conclude with WITH (must be RETURN or an update clause) (line 1, column 2 (offset: 1))
" WITH `item` as `item` , tagN as tagN , $isList as isList // single entry, not a list"

The code I provided was a sample. You'll have to adjust it to your needs.

Content in apoc.when cannot contain comments.

1 Like

Tony, big thanks, you have given me enough to work on !

1 Like

Thanks again tony.
I also wanted to share back:
the use of apoc.periodic.iterate command

wilson

1 Like