Merge three json lists in R

Question

I have created three different json objects with R. They look like (I put a small subset of each):

JSON 1:

[
 {
  "id": "user_F_1",
  "group": {
    "age": "32"
  },
  "activity": {
    "sport": "football"
  },
  "scores": {
    "2016": "85",
    "2017": "87"
   }
 },
 {
   "id": "user_F_2",
   "group": {
     "age": "32"
   },
   "activity": {
     "sport": "hockey"
   },
   "scores": {
     "2016": "62",
     "2017": "75"
   }
 }
]

JSON 2:

[
 {
  "id": "user_H_1",
  "gender": "male",
  "region": {
    "country": "Finland"
  }
},
{
  "id": "user_H_2",
  "gender": "female",
  "region": {
    "country": "Greece"
  }
 }
]

JSON 3:

[  
 {
  "id": "user_Z_1",
  "gender": "female",
  "age": "35",
  "data": {
    "continent": "Europe",
    "country": "France",
    "teamgroup": 3
  }
},
{
  "id": "user_Z_2",
  "gender": "female",
  "age": "46",
  "data": {
    "continent": "Asia",
    "country": "China",
    "teamgroup": 17
  }
 }
]

I need to merge them into one single json object. Following what was suggested in this question/27524122 I tried to do:

jsonl <- list(fromJSON(json1), fromJSON(json2), fromJSON(json3))
jsonc <- toJSON(jsonl, pretty=TRUE)

But it concatenates the three of them, not merge. I mean, it returns:

[
 {
  "id": "user_F_1",
  "group": {
    "age": "32"
  },
  "activity": {
    "sport": "football"
  },
  "scores": {
    "2016": "85",
    "2017": "87"
   }
 },
 {
   "id": "user_F_2",
   "group": {
     "age": "32"
   },
   "activity": {
     "sport": "hockey"
   },
   "scores": {
     "2016": "62",
     "2017": "75"
   }
 }
],
[
 {
  "id": "user_H_1",
  "gender": "male",
  "region": {
    "country": "Finland"
  }
},
{
  "id": "user_H_2",
  "gender": "female",
  "region": {
    "country": "Greece"
  }
 }
],
[  
 {
  "id": "user_Z_1",
  "gender": "female",
  "age": "35",
  "data": {
    "continent": "Europe",
    "country": "France",
    "teamgroup": 3
  }
},
{
  "id": "user_Z_2",
  "gender": "female",
  "age": "46",
  "data": {
    "continent": "Asia",
    "country": "China",
    "teamgroup": 17
  }
 }
]

and I need:

[
 {
  "id": "user_F_1",
  "group": {
    "age": "32"
  },
  "activity": {
    "sport": "football"
  },
  "scores": {
    "2016": "85",
    "2017": "87"
   }
 },
 {
   "id": "user_F_2",
   "group": {
     "age": "32"
   },
   "activity": {
     "sport": "hockey"
   },
   "scores": {
     "2016": "62",
     "2017": "75"
   }
 },
 {
  "id": "user_H_1",
  "gender": "male",
  "region": {
    "country": "Finland"
  }
},
{
  "id": "user_H_2",
  "gender": "female",
  "region": {
    "country": "Greece"
  }
 },  
 {
  "id": "user_Z_1",
  "gender": "female",
  "age": "35",
  "data": {
    "continent": "Europe",
    "country": "France",
    "teamgroup": 3
  }
},
{
  "id": "user_Z_2",
  "gender": "female",
  "age": "46",
  "data": {
    "continent": "Asia",
    "country": "China",
    "teamgroup": 17
  }
 }
]

i.e. without the square brackets between the different jsons. All the elements inside one block of square brackets: [{...},{...},{...},{...},{...},{...}].

I also tried the paste0 option in this question/47983058, but it's not working either.

How can I achieve this?

Jet · Answer 1 · 2019-04-30T08:31:01.403

0

It seems that fromJSON returns a list. To concatenate lists, you should use the c function.

json_all <- c(fromJSON(json1), fromJSON(json2), fromJSON(json3))
jsonc <- toJSON(json_all, pretty=TRUE)

Example :

json1 <- '
[
{
  "id": "user_H_1",
  "gender": "male"
}
]' 

json2 <- '
[
{
  "id": "user_H_1e",
  "gender": "female"
},
{
  "id": "user_H_2e",
  "age": "62"
}
]'

Merging :

library(jsonlite)
toJSON(c(fromJSON(json1), fromJSON(json2)), pretty=TRUE)

Output :

{
  "id": ["user_H_1"],
  "gender": ["male"],
  "id.1": ["user_H_1e", "user_H_2e"],
  "gender.1": ["female", null],
  "age": [null, "62"]
}

edited Apr 30 '19 at 08:31

answered Apr 30 '19 at 08:18

Jet

650
5
17

Thanks for your answer but, as you show in your example, this doesn't return what I need to have as output. In your solution every element now is a list... I need exactly the same json structure for each json object, and just merge them one below the other inside one square bracket as I said above `[{...},{...},{...},{...},{...},{...}]` – LRD Apr 30 '19 at 08:40
1

Then there mey be no point using json handling functions. You may just strip `[` & `]` brackets at the start and end of the strings, and concatenate them like normal strings using `paste0` – Jet Apr 30 '19 at 08:48
Ok. Well, I followed your advice and got something that works. I posted the answer below. Thanks again. – LRD Apr 30 '19 at 09:43

score 0 · Accepted Answer · answered Apr 30 '19 at 09:40

Following @Jet answer, I solved my problem by removing the brackets first, then concatenating all the jsons, and finally adding brackets again at the beginning and at the end of the resulting json:

cat("[",gsub("\\[|\\]", "",json1), ",", gsub("\\[|\\]", "",json2), ",", gsub("\\[|\\]", "",json3),"]" )

If you want to write this in a json file, then:

fileConn<-file("Output.json")
writeLines(paste0("[", gsub("\\[|\\]", "",json1), ",", gsub("\\[|\\]", "",json2), ",", gsub("\\[|\\]", "",json3),"]"), fileConn)
close(fileConn)

Merge three json lists in R

2 Answers2