Create a relationship from 2 JSON files

[runtonky]

These are the two JSON-files which are used to create a relationship:

1. Event-Dataset on historical exhibitions: (Entries with id, title and attributte)
   categorized by subjects (0..1) and referenced by an attributte-table (ex: entries of id )

An excerpt from the data set:

 {
    "id": 19669,
    "title": {
      "en": "Peter Brüning, Paris, Galerie Arnaud"
    },
    "attrs": {
      "by_category": {
        "0": [
          9280
        ],
        "1": [
          15814,
          13569
        ]
      }
    }
 },
 {
    "id": 19812,
    "title": {
      "en": "14 mal 14 - Junge deutsche Künstler, Baden-Baden, Staatliche Kunsthalle"
    },

    "attrs": {
      "by_category": {
        "0": [
          11044
        ],
        "1": [
          15814
        ]
      }
    }
 }

2. A table with entries of cities in which an entry is constructed as follows:

 {
    "id": 9280,
    "kind_id": 43,
    "klass_id": 6,
    "category_id": 0,
    "name": {
      "en": "Paris"
    }
 }

The idea behind this is to create a relationship between the data set and the table of cities,

(event)-[r:located_IN]->(city)

More precisely it should create a relationship between an event and a city, if a specific ID from the table of cities correlates with a specific ID of the event data set.

z.B. iD: 9280 (Paris) -> attrs.by_category["0"]

My assumption is, that this could be solved by using a WHERE-Clause. At the moment, I do not know how to implement this idea, though. However, I have started working on the following query:
Many Thanks!

CALL apoc.load.json("file:///test_1.json") YIELD value
	UNWIND value AS r
	WITH r, r.attrs.by_category["0"] as item
	MATCH (e:event {id: r.id,
      name: r.title.en);
	WITH *
	CALL apoc.load.json("file:///Cities.json") YIELD value
	UNWIND value as r
	WITH item, r
	MATCH (c:city {id:r.id, name:r.name});	  
	WHERE id IN item
	MERGE (e)-[r:located_IN]->(c)

[tony_chiboucas]

Use event.category.0 and city.id as the main identity for a city, and load them separately.

CALL apoc.load.json("file:///test_1.json") YIELD value
UNWIND value AS event
WITH event
WHERE EXISTS(event.attrs.by_category.`0`) AND size(event.attrs.by_category.`0`) > 0
MERGE (e:Event {id: event.id})
SET e.title = event.title.en
WITH e, event
UNWIND event.attrs.by_category.`0` AS cat0
MERGE (c:City {id: cat0})
MERGE (e)-[:LOCATION]->(c)
;

CALL apoc.load.json("file:///Cities.json") YIELD value
UNWIND value AS city
MERGE (c:City {id:city.id})
SET c.name = city.name
;

[runtonky]

tony.chiboucas:

CALL apoc.load.json("file:///Cities.json") YIELD value
UNWIND value AS city
MERGE (c:City {id:city.id})
SET c.name = city.name
;

Many thanks for your help, but unfortunately when I run it, I get this error:

Property values can only be of primitive types or arrays thereof

[tony_chiboucas]

...then... troubleshoot and correct the problem. Please don't use the community to write your cypher for you.

SET c.name = city.name.en