How do I create a cypher query to retrieve nested array in JSON data file?

[bvm]

Hi, I have a nested array in JSON called "my_links" which has another array called "Items". I am unable to create a relationship between the elements of "Items". Can someone please help me with that? Here's the JSON file and the desired output.

[{
    "my_code": "alpha",
    "my_name": "name1",
    "my_links":[{
                            "Items": [
                                            {
                                            "itemid": 1,
                                            "my_code": "beta",
                                            "my_name": "name2"
                                            },
                                            {
                                                "itemid": 1,
                                                "my_code": "gamma",
                                                "my_name": "name3"
                                            }
                                            ]
                                },
                                {
                            "Items": [    
                                        {
                                        "itemid": 2,
                                        "my_code": "delta",
                                        "my_name": "name4"
                                        },
                                        {
                                        "itemid": 2,
                                        "my_code": "echo",
                                        "my_name": "name5"
                                        }
                                        
                                        ]
                                }
                             ]
    },
    {
        "my_code": "beta",
        "my_name": "name2"
       
     },
     {
        "my_code": "gamma",
        "my_name": "name3"
       
     },
     {
        "my_code": "delta",
        "my_name": "name4"
    
     },
     {
        "my_code": "echo",
        "my_name": "name5"
        
     }
    ]

It means alpha - [is_linked_with] -> {"beta" OR "gamma"} AND {"delta" OR "echo"}

[tfontanella011]

Hi,

You can load a json from a file using apoc.load.json APOC procedure (make sure your file was imported previously on the DBMS) and assign the result into a variable, For example, in this case it can be:

CALL apoc.load.json("file:///json_example.json")
YIELD value

After doing that, you can use UNWIND sentence to split different array values into different rows, In this case, you can UNWIND my_links and Items arrays as following

UNWIND value.my_links as l
UNWIND l.Items as item

Consequently, you can do a query to select the entities (or whatever label you have) and create a relationship as usual. Like for example:

So, finally, the solution for the linked_in relationship creation from the JSON file, it can be the following:

MATCH (e:Entity), (a:Entity {my_code:value.my_code})
WHERE e.my_code = item.my_code
CREATE (a)-[r:IS_LINKED_WITH]->(e)

So, just to summarize, the full query to read and create the relationship from json array it would be the following (you can adapt it more according to your logic):

CALL apoc.load.json("file:///json_example.json")
YIELD value
UNWIND value.my_links as l
UNWIND l.Items as item
MATCH (e:Entity), (a:Entity {my_code:value.my_code})
WHERE e.my_code = item.my_code
CREATE (a)-[r:IS_LINKED_WITH]->(e)
RETURN a, r, e

Hope this helps

[bvm]

Thank you for your response. Although the logic seems correct, when I executed this query, it did not fetch any results. The output was : (no changes, no records). It gave a warning:

This query builds a cartesian product between disconnected patterns.

If a part of a query contains multiple disconnected patterns, this will build a cartesian product between all those parts. This may produce a large amount of data and slow down query processing. While occasionally intended, it may often be possible to reformulate the query that avoids the use of this cross product, perhaps by adding a relationship between the different parts or by using OPTIONAL MATCH (identifier is: (a))

Could you please try executing it on the dataset provided?

[tfontanella011]

Hi,

I used the following dataset (according to what you specified on the diagram) with the following Cypher create Query:

CREATE (:Entity {my_code: 'alpha', my_name:'name1'}), (:Entity {my_code: 'beta',my_name:'name2'}),  (:Entity {my_code: 'gamma',my_name:'name3'}),  (:Entity {my_code: 'delta',my_name:'name4'}),  (:Entity {my_code: 'echo',my_name:'name5'})

After that, and keeping in mind the file was in json_example.json in the DBMS import folder), executing the following query will create all IS_LINKED_WITH relationships as required.

CALL apoc.load.json("file:///json_example.json")
YIELD value
UNWIND value.my_links as l
UNWIND l.Items as item
MATCH (e:Entity), (a:Entity {my_code:value.my_code})
WHERE e.my_code = item.my_code
CREATE (a)-[r:IS_LINKED_WITH]->(e)
RETURN a, r, e

So I supposed your Dataset has an label called :Entity (you can update it according to the label you defined for your nodes).

Hope it helps.
Thanks

[bvm]

Thank you. But we still haven't created the relationship between :
beta-[r: OR]->gamma
&
echo-[r:OR]->delta
This nested part is what I'm struggling with. I was wondering if we could make use of "itemid" to do that ? Could you please help achieve this part?

[bvm]

Basically, for all the elements in one array of "Items", I would like to create a relationship between them.

[tfontanella011]

Hi,
You can do it in the following way (suposing the OR Items array will only have two elements

CALL apoc.load.json("file:///json_example.json")
YIELD value
UNWIND value.my_links as l
MATCH (e1:Entity), (e2:Entity)
WHERE e1.my_code = l.Items[0].my_code and e2.my_code = l.Items[1].my_code
CREATE (e1)<-[r:OR]-(e2)
RETURN e1, r, e2

In this case we are not using the itemId, but you can use it if you want. That depends on your logic and what do you need to achieve on your queries specifically

Hope this helps

[bvm]

Hello,
Thanks for the solution. I am sorry to stretch it, but the OR Items array are not restricted to have only have two elements. It can be something like :
beta-[r: OR]->gamma -[r:OR]->delta

Like my_links can have this variation:

"my_links":[{
                            "Items": [
                                            {
                                            "itemid": 1,
                                            "my_code": "beta",
                                            "my_name": "name2"
                                            },
                                            {
                                                "itemid": 1,
                                                "my_code": "gamma",
                                                "my_name": "name3"
                                            },
                                           {
                                                 "itemid": 1,
                                                 "my_code": "delta",
                                                 "my_name": "name4"
                                        },
                                            ]
                                },
                                {
                            "Items": [    
                                       
                                        {
                                        "itemid": 2,
                                        "my_code": "echo",
                                        "my_name": "name5"
                                        }
                                        
                                        ]
                                }
                             ]

Leading to output:

Also, the number of elements in the array Items can vary.

[bvm]

Is there a possibility that we can:

1. Traverse through one array (Items) at a time and create a relationship for all the elements in it and
2. Repeat the same procedure for other arrays (Items) if more than two elements exist in it?

[tfontanella011 · Accepted solution]

Hi,

As we are using UNWIND, we can join different items and create relationships accordingly. So with the following updated Cypher query, we can traverse through the items we previously UNWIND:

CALL apoc.load.json("file:///json_example.json")
YIELD value
UNWIND value.my_links as l
UNWIND l.Items as item1
UNWIND l.Items as item2
MATCH (e1:Entity), (e2:Entity)
WHERE item1.my_code > item2.my_code AND e1.my_code = item1.my_code AND e2.my_code = item2.my_code
CREATE (e1)<-[r:OR]-(e2)
RETURN e1, e2, r 

On the other side, annswering to your latest questions, you can also use FOREACH if you need to iterate per some elements.

Hope it helps.

[bvm]

It is working now! Thank you so much. Your quick response is highly appreciated