Create one to many nested JSON object in R

Question

I have some data that looks like this:

  flyFrom flyTo price
1     AAE   ALG    48
2     AAE   ORN    84
3     AAE   MRS   158
4     AAE   LYS   186
5     AAE   ORY   198
6     AAE   IST   374
...

and I want to transform it into something like this:

{"AAE": [
    "ALG", 48,
    "ORN", 84,
    "MRS", 158,
    "LYS", 186,
    "ORY", 198,
    "IST", 374
...
],
"YYE": [
    "YXY", 48,
    "YDQ", 78,
    "YXJ", 200
]}

I spent about an hour trying different stuff but I'm not sure how to get it into the structure that I want. I'm using jsonlite right now but I can use whatever you recommend

edit: I want the price column also included but I'm also not sure what the cleanest implementation would be. the relationship between flyTo and price will always be 1:1

edit2: I was able to pull this out but it's not quite there

> test<-head(price,10)[c(1,2,3)]
> test2<-list(test$flyFrom,list(test$flyTo,test$price))
> jsonlite::toJSON(test2, pretty = TRUE,auto_unbox = TRUE)
[
["BUF", "CAK", "JAN", "PHF", "CAE", "FCA", "PHL", "CHS", "AVL", "AVL"],
[
    ["RIC", "PHF", "MEM", "LGA", "LEX", "JAC", "CHO", "DHN", "RDU", "CHS"],
    [7064, 6686, 6134, 6112, 5667, 5616, 5528, 5462, 5115, 5069]
]
]

score 1 · Accepted Answer · answered Nov 17 '18 at 11:19

As your attempt demonstrates, the result can be achieved by converting your data frame into a certain list. In particular, using

split(price[, -1], price$flyFrom)
# $AAE
#   flyTo price
# 1   ALG    48
# 2   ORN    84
# 3   MRS   158
# 4   LYS   186
# 5   ORY   198
# 6   IST   374

# $YYE
#   flyTo price
# 7   YXY    48
# 8   YDQ    78
# 9   YXJ   200

gives

toJSON(split(price[, -1], price$flyFrom), pretty = TRUE)
{
  "AAE": [
    {
      "flyTo": "ALG",
      "price": 48
    },
    {
      "flyTo": "ORN",
      "price": 84
    },
    {
      "flyTo": "MRS",
      "price": 158
    },
    {
      "flyTo": "LYS",
      "price": 186
    },
    {
      "flyTo": "ORY",
      "price": 198
    },
    {
      "flyTo": "IST",
      "price": 374
    }
  ],
  "YYE": [
    {
      "flyTo": "YXY",
      "price": 48
    },
    {
      "flyTo": "YDQ",
      "price": 78
    },
    {
      "flyTo": "YXJ",
      "price": 200
    }
  ]
}

@jaksco, now I'm thinking that perhaps that's not the format that you were after, or is it? — Julius Vainora, Nov 17 '18 at 19:23
I think this will work perfectly for my use case. Thank you! :D — jaksco, Nov 17 '18 at 20:00
do you know how I could merge the elements of this list with an existing dataframe? https://stackoverflow.com/questions/53577356/write-json-with-multiple-inputs-in-r — jaksco, Dec 02 '18 at 18:39

Create one to many nested JSON object in R

1 Answers1