apoc.convert.fromJsonMap is giving error

shsamardar · April 11, 2024, 5:46pm

Hi, I have a json file want to process. i am starting with

call apoc.load.json ( $filelocation, '') yield value unwind value.documents as doc with doc.content as dc return dc

it is returning me a json like this

i want to create a node from it. I want to use
call apoc.merge.node

i want to use value of "common:IdentityContext-type" as my label.
as identifer of node use "capital-harness:Design-baseId"
and rest of key/values as the attributes of node

{
"capital-harness:Design-baseId": "UID2e4b5f-18e4182b5cb",
"common:ProductSystemContext-partNumber": "A06-23456-000",
"capital-harness:Design-designAbstraction": "Harness Drawing",
"common:IdentityContext-type": "capital-harness:Design"
}

i know i have to convert the json to map and then i can do some filtering based on what keys i want

if i call

RETURN apoc.convert.fromJsonMap(dc) AS output;

i am getting error

Can't coerce Map{)} to String

is it something you help me .

glilienfield · April 11, 2024, 8:28pm

This gives you an example:

with '{"capital-harness:Design-baseId": "UID2e4b5f-18e4182b5cb","common:ProductSystemContext-partNumber": "A06-23456-000","capital-harness:Design-designAbstraction": "Harness Drawing","common:IdentityContext-type": "capital-harness:Design"}' as json
with apoc.convert.fromJsonMap(json) AS map
call apoc.create.node([map["common:IdentityContext-type"]], map{id:map["capital-harness:Design-baseId"], designAbstraction: map["capital-harness:Design-designAbstraction"], partNumber: map["common:ProductSystemContext-partNumber"]}) yield node
return node

Result:

{
  "identity": 42,
  "labels": [
    "capital-harness:Design"
  ],
  "properties": {
    "partNumber": "A06-23456-000",
    "id": "UID2e4b5f-18e4182b5cb",
    "designAbstraction": "Harness Drawing"
  },
  "elementId": "4:a78ea0f5-f0d7-41ba-947a-7b578538f53a:42"
}

shsamardar · April 11, 2024, 9:10pm

Thank you Gary for your quick response. I appreciate it.

you didn't answer my original question. I am attaching what I am experiencing.
I am calling JsonMap on a json output from but i am getting error.

also as you see in the image,

My actual json has a lot of key-values , more than what I showed.

if i want
1- key names become attribute names automatically instead ( instead of what you hardcoded )
2- except type and baseId that you used for label and Id to match, if i want rest of key:value's all become properties without hardcoding.

can you do your magic for this.

glilienfield · April 11, 2024, 9:30pm

Frankly, I think the colons in your strings that represent your json keys are the cause. It can’t convert those to a Neo4j map key, as those are not quoted.

glilienfield · April 11, 2024, 9:35pm

You could replace those colons with a valid character before converting to a neo4j map.

This apoc method will allow you to use a regex expression.

apoc.text.replace(text :: STRING, regex :: STRING, replacement :: STRING) :: STRING

shsamardar · April 12, 2024, 6:54pm

Hi Gary
if i have a list of list like this that i want to replace - or : into _ ,

how can i do it ?

[["capital-harness:Design-baseId", "UID2e4b5f-18e4182b5cb-28de33b5199547c2f026595b7175a940"], ["capital-harness:Design-designAbstraction", "Harness Drawing"]]

glilienfield · April 12, 2024, 9:11pm

What is the resulting structure you want?

I believe the cause of your errors is trying to convert your json keys (which are quoted strings) into neo4j keys (which are not a quoted string). The colon makes these json keys invalid neo4j keys. Will the keys be acceptable if the colons are replace with a different character, like an underscore?

Can you provide the file or one json object from it so I can test to get your result?

shsamardar · April 12, 2024, 9:26pm

That will be great Gary. I put json and instruction in the file .

I sincerely appreciate your help

neo4jticket.txt (2.4 KB)

more clarity beside what I put in the file , i want all of attributes in the json become node attributes.

glilienfield · April 13, 2024, 12:08am

Looks like I was wrong. You can have a colon in a Neo4j map as long as it is enclosed in a back tick. It looks like the apoc.load.json procedure returns json objects as Neo4j maps, and is ok with the colons in the key values.

Even so, I don't think it is wise to have colons in your key values.

Script to read and create nodes with the raw values:

CALL apoc.load.json("file:///SampleJsonFile.txt") YIELD value
unwind value.documents as document
with document.content as json
create(n:Test) set n=json
return n

Resulting node:

{
  "identity": 46,
  "labels": [
    "Test"
  ],
  "properties": {
    "capital-harness:Design-designAbstraction": "Harness Drawing",
    "common:ProductSystemContext-partNumber": "A06-23456-000",
    "common:SecurityContext-govtClassification": "",
    "common:IdentityContext-dataSourceURL": "UID2e4b5f-18e41820b0c-28de33b5199547c2f026595b7175a940/UID2e4b5f-18e4182b5cb-28de33b5199547c2f026595b7175a940/?objectId=",
    "common:GeneralBusinessContext-owningUser": "Rodney McCLean",
    "common:LifecycleContext-lastModifiedDate": "1621285254051",
    "common:SecurityContext-ipClassification": "",
    "common:IdentityContext-domain": "",
    "capital-harness:Design-baseId": "UID2e4b5f-18e4182b5cb-28de33b5199547c2f026595b7175a940",
    "capital-harness:Design-mcadid": "",
    "common:SecurityContext-itar": "",
    "common:GeneralBusinessContext-owningProject": "ELA-Aero Demo -3",
    "common:IdentityContext-revision": "A",
    "common:LifecycleContext-modifiedBy": "superuser",
    "common:LifecycleContext-releaseDate": "1621285254051",
    "common:IdentityContext-type": "capital-harness:Design",
    "capital-harness:Design-harnessGroup": "",
    "capital-harness:Design-folderPath": "Harnesses",
    "common:LifecycleContext-createdDate": "1536614738883",
    "common:IdentityContext-dataSource": "Capital",
    "common:IdentityContext-description": "EXT",
    "common:IdentityContext-name": "A06-23456-000",
    "common:LifecycleContext-disposition": "Draft",
    "common:IdentityContext-dataSourceUID": "UID2e4b5f-18e4182b5cb-28de33b5199547c2f026595b7175a940"
  },
  "elementId": "4:a78ea0f5-f0d7-41ba-947a-7b578538f53a:46"
}

Next step is to extract the properties you want and to create the node with the correct labels and properties from the extracted Neo4j map.

NOTE: Now I think the exception you got with the apoc.convert.fromJsonMap function was due to the input value ('json') being an actual Neo4j map already and not a string representing a json object to convert.

glilienfield · April 13, 2024, 4:07am

ok, this is getting a little messy. Can you make the format of the keys simpler before creating the file?

Anyway, I came up with something:

CALL apoc.load.json("file:///SampleJsonFile.txt") YIELD value
UNWIND value.documents as document
WITH document.content as json
WITH json, json["common:IdentityContext-type"] as labelText
WITH 
    json, 
    toLower(apoc.text.regexGroups(labelText, '\w+(?=-)')[0][0]) as segment1, 
    toLower(apoc.text.regexGroups(labelText, '(?<=-)\w+(?=:)')[0][0]) as segment2,
    toLower(apoc.text.regexGroups(labelText, '(?<=:)\w+')[0][0]) as segment3
WITH 
    json, 
    segment1+'_'+segment2 as label1, 
    segment3 as label2,
    segment1+'_'+segment2+'_'+segment3 as label3
CALL apoc.create.node([label1, label2, label3], json) YIELD node
return node

Result:

{
  "identity": 47,
  "labels": [
    "capital_harness",
    "design",
    "capital_harness_design"
  ],
  "properties": {
    "capital-harness:Design-designAbstraction": "Harness Drawing",
    "common:ProductSystemContext-partNumber": "A06-23456-000",
    "common:SecurityContext-govtClassification": "",
    "common:IdentityContext-dataSourceURL": "UID2e4b5f-18e41820b0c-28de33b5199547c2f026595b7175a940/UID2e4b5f-18e4182b5cb-28de33b5199547c2f026595b7175a940/?objectId=",
    "common:SecurityContext-ipClassification": "",
    "common:IdentityContext-domain": "",
    "common:SecurityContext-itar": "",
    "common:GeneralBusinessContext-owningProject": "ELA-Aero Demo -3",
    "common:IdentityContext-type": "capital-harness:Design",
    "common:IdentityContext-dataSource": "Capital",
    "common:IdentityContext-name": "A06-23456-000",
    "common:IdentityContext-dataSourceUID": "UID2e4b5f-18e4182b5cb-28de33b5199547c2f026595b7175a940",
    "common:GeneralBusinessContext-owningUser": "Rodney McCLean",
    "common:LifecycleContext-lastModifiedDate": "1621285254051",
    "capital-harness:Design-baseId": "UID2e4b5f-18e4182b5cb-28de33b5199547c2f026595b7175a940",
    "capital-harness:Design-mcadid": "",
    "common:IdentityContext-revision": "A",
    "common:LifecycleContext-modifiedBy": "superuser",
    "common:LifecycleContext-releaseDate": "1621285254051",
    "capital-harness:Design-harnessGroup": "",
    "capital-harness:Design-folderPath": "Harnesses",
    "common:LifecycleContext-createdDate": "1536614738883",
    "common:IdentityContext-description": "EXT",
    "common:LifecycleContext-disposition": "Draft"
  },
  "elementId": "4:a78ea0f5-f0d7-41ba-947a-7b578538f53a:47"
}

glilienfield · April 13, 2024, 4:19am

Here are two examples of transforming the list of lists of elements:

list comprehension. I converted each string to upper case as an example. You can replace the "toUpper' transformation with your own transformation.

with [["capital-harness:Design-baseId", "UID2e4b5f-18e4182b5cb-28de33b5199547c2f026595b7175a940"], ["capital-harness:Design-designAbstraction", "Harness Drawing"]] as data
with [i in data | [j in i | toUpper(j) ]] as transformed_lists
return transformed_lists

Result:

[["CAPITAL-HARNESS:DESIGN-BASEID", "UID2E4B5F-18E4182B5CB-28DE33B5199547C2F026595B7175A940"], ["CAPITAL-HARNESS:DESIGN-DESIGNABSTRACTION", "HARNESS DRAWING"]]

Using unwind to create rows of transformed data

with [["capital-harness:Design-baseId", "UID2e4b5f-18e4182b5cb-28de33b5199547c2f026595b7175a940"], ["capital-harness:Design-designAbstraction", "Harness Drawing"]] as data
unwind data as lists
unwind lists as element
return toUpper(element)

Result:

This should give you some ideas.

shsamardar · April 15, 2024, 5:33pm

Wonderful Gary, thank you for this . it is a joy of education reading your coding. it is advanced Cypher.

shsamardar · April 23, 2024, 4:10pm

Hi Greg

regarding the code you suggested in above, i want to convert it into apoc.merge.node .

I have written like this
:params {
projectIdAttr: "capital-harness:Design-baseId"
}

call apoc.load.json ( $filelocation, '') yield value
UNWIND value.documents as document
WITH document.content as json
WITH json, json["common:IdentityContext-type"] as labelText, json[$projectIdAttr] as id
WITH
json, id ,
toLower(apoc.text.regexGroups(labelText, '\w+(?=-)')[0][0]) as segment1,
toLower(apoc.text.regexGroups(labelText, '(?<=-)\w+(?=:)')[0][0]) as segment2,
toLower(apoc.text.regexGroups(labelText, '(?<=:)\w+')[0][0]) as segment3
WITH
json,
segment1+''+segment2 as label1,
segment3 as label2,
segment1+''+segment2+'_'+segment3 as label3,
id
CALL apoc.merge.node ( [label1, label2,label3] , { $projectIdAttr : id}, json, json ) yield node
return node

it is giving me error for building map { $projectIdAttr : id } . is it possible to use parameters for building maps instead f hardcoding directly in the map?

glilienfield · April 23, 2024, 5:06pm

You can't use a variable for a map key. In your example you have $projectIdAttr as a map key. You can use an apoc method to create a map dynamically, then pass the map.

Try this:

call apoc.load.json ( $filelocation, '') yield value
UNWIND value.documents as document
WITH document.content as json
WITH json, json["common:IdentityContext-type"] as labelText, json[$projectIdAttr] as id
WITH
json, id ,
toLower(apoc.text.regexGroups(labelText, '\w+(?=-)')[0][0]) as segment1,
toLower(apoc.text.regexGroups(labelText, '(?<=-)\w+(?=:)')[0][0]) as segment2,
toLower(apoc.text.regexGroups(labelText, '(?<=:)\w+')[0][0]) as segment3
WITH
json,
segment1+''+segment2 as label1,
segment3 as label2,
segment1+''+segment2+'_'+segment3 as label3,
apoc.map.fromPairs([[$projectIdAttr, id]]) as identifierMap
CALL apoc.merge.node ( [label1, label2, label3], identifierMap, json, json ) yield node
return node

Topic		Replies	Views
Help creating graph from json file Neo4j Graph Platform apoc , cypher , import	5	3869	February 10, 2021
Creating nodes from nested JSON using apoc.load.json() procedure Cypher	1	4815	October 24, 2018
Load Json on the fly Procedures & APOC apoc , cypher , import	2	605	December 4, 2019
apoc.convert.fromJsonMap read JSON string Procedures & APOC	2	2688	September 27, 2019
Creating a graph from json using apoc.graph.fromDocument - children's children do not appear in graph Neo4j Graph Platform apoc	1	776	August 4, 2020

apoc.convert.fromJsonMap is giving error

Related topics