rjsoncsvtidyverse

How can I create a JSON in R to match this schema?


I need to create a JSON file that matches the schema at the end of the post in R. I need this to originate from a CSV file that I will generate and it can be crafted to whichever format will make it easiest to achieve the result. The CSV file will be dynamic in nature, in the below example, the only variables that will change per given file are:

  1. day
  2. cityName - Can be multiple cityName
  3. Type - Each cityName can have multiple types
  4. price - Each type will have one price
  5. order - Each type will have one order

My CSV in it's first iteration is formatted as such and it can have multiple rows:

day cityName Type price order
2019-03-14 City1 1 5 50
2019-03-14 City1 2 10 75
2019-03-14 City2 1 10 50
2019-03-14 City2 2 15 75
2019-03-14 City2 3 20 100
{
  "day": "2019-03-14",
  "city": [
    {
      "cityName": "city1",
      "FreightA": [
        {
          "Type": 1,
          "FreightSegments": [
            {
              "price": 10,
              "order": 50
            }
          ]
          
          "Type": 2,
          "FreightSegments": [
            {
              "price": 10,
              "order": 75
            }
          ]
        }
      ]
      "cityName": "city2",
      "FreightA": [
        {
          "Type": 1,
          "FreightSegments": [
            {
              "price": 10,
              "order": 50
            }
          ]
          
          "Type": 2,
          "FreightSegments": [
            {
              "price": 15,
              "order": 20
            }
            
            "Type": 3,
            "FreightSegments": [
              {
                "price": 20,
                "order": 100
              }
            ]
          ]
        }
      ]
    }
  ]
}

I've gone through relevant JSON posts and a number of trial and errors and am not able to create it. Any guidance/tips/workflow recommendations would be greatly appreciated.

Thank you,


Solution

  • library(jsonlite)
    library(tidyr)
    
    nest(df, FreightSegments = c(price, order)) |>
      nest(FreightA = c(Type, FreightSegments)) |>
      nest(city = c(cityName, FreightA)) |> 
      toJSON(pretty = TRUE)
    # [
    #   {
    #     "day": "2019-03-14",
    #     "city": [
    #       {
    #         "cityName": "City1",
    #         "FreightA": [
    #           {
    #             "Type": 1,
    #             "FreightSegments": [
    #               {
    #                 "price": 5,
    #                 "order": 50
    #               }
    #             ]
    #           },
    #           {
    #             "Type": 2,
    #             "FreightSegments": [
    #               {
    #                 "price": 10,
    #                 "order": 75
    #               }
    #             ]
    #           }
    #         ]
    #       },
    #       {
    #         "cityName": "City2",
    #         "FreightA": [
    #           {
    #             "Type": 1,
    #             "FreightSegments": [
    #               {
    #                 "price": 10,
    #                 "order": 50
    #               }
    #             ]
    #           },
    #           {
    #             "Type": 2,
    #             "FreightSegments": [
    #               {
    #                 "price": 15,
    #                 "order": 75
    #               }
    #             ]
    #           },
    #           {
    #             "Type": 3,
    #             "FreightSegments": [
    #               {
    #                 "price": 20,
    #                 "order": 100
    #               }
    #             ]
    #           }
    #         ]
    #       }
    #     ]
    #   }
    # ] 
    

    Using this input:

    df = read.table(text = 'day     cityName    Type    price   order
    2019-03-14  City1   1   5   50
    2019-03-14  City1   2   10  75
    2019-03-14  City2   1   10  50
    2019-03-14  City2   2   15  75
    2019-03-14  City2   3   20  100', header =T)