我试图从数据表中获取数据,并在经过一些争论后将它们转换为JSON输出格式,这样我就可以将更新发送到API中。
我用一个报价单和几个报价行做了一个简化的例子(见下文(。我似乎无法理解的是如何连接和合并数据,这样我就可以获得JSON输出格式的数据。我有概念性的想法,但在尝试构建它时我错过了。
也许有一个完全不同的更好的解决方案?任何能给我一点实际结果的人:(
已经谢谢了!
数据帧报价
QuoteID DebtorID
10001 50000
10002 50001
11403 55302
11404 55303
数据帧报价行
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
输出格式(R中的JSON;运行所需格式(
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)
我在示例数据帧中找不到"QuId" = "Twee")
,但您可以将表连接在一起,并使用嵌套创建JSON:
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
#> }
#> ]
#> }
#> ]
创建于2022-04-06由reprex包(v2.0.0(
谢谢danlooo。估计你在帮我对付这个新手。(:还有两个问题;(QuoteID=QuID和DebtorID=DbID。(
选择QuoteID的所有列:如果我在"QuoteID"级别上有很多列,我必须全部指定它们吗?或者有更简单的方法吗?
quotation %>%
full_join(quotationlines) %>%
nest(-X.QuID, -X.DbID, -X.Column3, -X.Column4, and so on...) %>%
toJSON(pretty = TRUE)
构建层次结构并命名数据:当我运行代码时,我会得到这样的结果。
[
{
"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
},
...
API接受的最终JSON格式如下。似乎我需要用嵌套列表构建一些东西?
{
"KnQuotation": {
"Element": {
"Fields": {
"QuId": 12345,
"QuDa": "2022-02-01"
},
"Objects": [
{
"KnQuotationLine": {
"Elements": {
"Fields": {
"VaId": "1",
"QuId": "Twee"
}
}
}
}
]
}
}
}