R - Dataframes to output; Nested JSON-CodePudding

I'm trying to grasp data from datatables and - after some wrangling - get them to a JSON output format, so I can send the updates into the API.

I made a simplified example with a Quotation and several Quotationlines (see below). The thing I can't seem to grasp is how to join and merge the data so i get the data as JSON output format. I get the conceptual idea, but I'm missing out when trying to build it.

Maybe there is a completely different and better solution? Anyone who can give me a slight push towards actual results :)

Thanks already!

Dataframe Quotation

QuoteID    DebtorID
10001      50000
10002      50001
11403      55302
11404      55303

Dataframe Quotationlines

QuoteID    Type    Code     Amount
10001      Art     10000    2
10001      Hrs     510      5
10001      Art     15356    10
10002      Hrs     600      4
10002      Art     10000    2
11403      Hrs     600      5
11403      Art     14356    2
11403      Hrs     500      5

Output format (JSON in R; Run for desired format)

jsonlite::toJSON(

  list(
    "KnQuotation" =
    c(
      list(
        "Element" =
          list(
            "Fields" =
              list(
                "QuId" = 12345,
                "QuDa" = "2022-02-01"),
            

                "Objects" = list(
                    list(
                        "KnQuotationLine" = 
                          list(
                            "Elements" = 
                              list(
                                "Fields" =
                                  list(
                                     "VaId" = "1",
                                      "QuId" = "Twee")
                         )
                    )
                  )                 
                )
            )
        )
    )
), 
    pretty = TRUE,
    auto_unbox = TRUE)

CodePudding user response：

I can not find "QuId" = "Twee") in the example data frame, but you can join tables together and create the JSON using nesting:

library(tidyverse)
library(jsonlite)
#> 
#> Attaching package: 'jsonlite'
#> The following object is masked from 'package:purrr':
#> 
#>     flatten

quotations <- tribble(
  ~QuoteID, ~DebtorID,
  10001,      50000,
  10002,      50001
)

quotationlines <- tribble(
  ~QuoteID, ~Type, ~Code, ~Amount,
  10001 ,     "Art",     10000,    2,
  10001,      "Hrs",     510  ,    5,
  10001,      "Art",     15356,    10,
  10002,      "Hrs",     600  ,    4,
  10002,      "Art",     10000,    2
)

quotations %>%
  full_join(quotationlines) %>%
  nest(-QuoteID) %>%
  toJSON(pretty = TRUE)
#> Warning: All elements of `...` must be named.
#> Did you want `data = -QuoteID`?
#> Joining, by = "QuoteID"
#> [
#>   {
#>     "QuoteID": 10001,
#>     "data": [
#>       {
#>         "DebtorID": 50000,
#>         "Type": "Art",
#>         "Code": 10000,
#>         "Amount": 2
#>       },
#>       {
#>         "DebtorID": 50000,
#>         "Type": "Hrs",
#>         "Code": 510,
#>         "Amount": 5
#>       },
#>       {
#>         "DebtorID": 50000,
#>         "Type": "Art",
#>         "Code": 15356,
#>         "Amount": 10
#>       }
#>     ]
#>   },
#>   {
#>     "QuoteID": 10002,
#>     "data": [
#>       {
#>         "DebtorID": 50001,
#>         "Type": "Hrs",
#>         "Code": 600,
#>         "Amount": 4
#>       },
#>       {
#>         "DebtorID": 50001,
#>         "Type": "Art",
#>         "Code": 10000,
#>         "Amount": 2
#>       }
#>     ]
#>   }
#> ]

^{Created on 2022-04-06 by the reprex package (v2.0.0)}

CodePudding user response：

Thanks danlooo. Appriciate you're help wwith this newwbie. (: Got two questions more; (QuoteID = QuID and DebtorID = DbID.)

Selecting all columns of QuoteID: If I have a lot of columns on the level of 'QuoteID', do I have te specify them all or is there an easier way?

quotation %>%
  full_join(quotationlines) %>%
  nest(-X.QuID, -X.DbID, -X.Column3, -X.Column4, and so on...) %>%
  toJSON(pretty = TRUE)

Structuring hierarchy and naming the data: When I run the code, I get something like this.

[
  {
    "X.QuID": 10001,
    "X.DbID": 50000,
    "data": [
      {
        "X.VaIt": "wst",
        "X.ItCd": 500,
        "X.QuUn": 3
      },
      {
        "X.VaIt": "wst",
        "X.ItCd": 501,
        "X.QuUn": 4
      },
...

Eventual JSON format which is accepted by the API is as follows. It seems like I need to build something with nested lists?

{
  "KnQuotation": {
    "Element": {
      "Fields": {
        "QuId": 12345,
        "QuDa": "2022-02-01"
      },
      "Objects": [
        {
          "KnQuotationLine": {
            "Elements": {
              "Fields": {
                "VaId": "1",
                "QuId": "Twee"
              }
            }
          }
        }
      ]
    }
  }
}