sql - 如何编写 SQL 代码以生成 4 级 JSON 输出？
问题描述
我正在尝试创建一个 4 级 json。让我感到困惑的是这里的“skus”级别。我正在尝试创建一个组级别，然后是一个 skus 级别，一个 fieldInfo 级别，并在其中创建给定组中所有 sku 的所有字段值等。
这是我的数据示例：
| 库存单位 | 场组 | 分组排序 | 字段名 | 字段排序 | 组背景颜色 | 标签名称 | 默认值 |
|---|---|---|---|---|---|---|---|
| RB130-9-H-S21D2-PID-OS | 遵守 | 9 | item_volume_unit_of_measure | 246 | CC9999 | item_volume_unit_of_measure | |
| RB130-9-H-S21D2-PID-OS | 产品丰富 | 6 | leg_style | 172 | BBA680 | 腿型 | |
| RB130-9-H-S21D2-PID-OS | 基本的 | 4 | 服装尺寸类 | 56 | F8A45E | 服装尺码等级 | |
| RB130-9-H-S21D2-PID-OS | 基本的 | 4 | shirt_size_class | 51 | F8A45E | 衬衫尺码等级 | |
| RB130-9-H-S21D2-PID-OS | 方面 | 7 | item_height | 198 | 8DB4E2 | 物品高度 | |
| RB130-9-H-S21D2-PID-OS | 变化 | 3 | parent_sku | 43 | FF8080 | 父 SKU | |
| RB130-9-H-S21D2-SSKC-OS | 变化 | 3 | parent_sku | 43 | FF8080 | 父 SKU | |
| RB130-9-H-S21D2-SSKC-OS | 方面 | 7 | item_height | 198 | 8DB4E2 | 物品高度 | |
| RB130-9-H-S21D2-SSKC-OS | 基本的 | 4 | shirt_size_class | 51 | F8A45E | 衬衫尺码等级 | |
| RB130-9-H-S21D2-SSKC-OS | 基本的 | 4 | 服装尺寸类 | 56 | F8A45E | 服装尺码等级 | |
| RB130-9-H-S21D2-SSKC-OS | 产品丰富 | 6 | leg_style | 172 | BBA680 | 腿型 | |
| RB130-9-H-S21D2-SSKC-OS | 遵守 | 9 | item_volume_unit_of_measure | 246 | CC9999 | item_volume_unit_of_measure |
我想要完成的是这样的：
{
"groups": [{
"group": "Group1",
"skus": [{
"sku": "xxx",
"fieldInfo": [{
"fieldName": "field1",
"value": "1"
}, {
"fieldName": "2",
"fieldValue": "2"
}]
}, {
"sku": "yyy",
"fieldInfo": [{
"fieldName": "field1",
"value": "1"
}, {
"fieldName": "2",
"fieldValue": "2"
}]
}]
}, {
"group": "Group2",
"skus": [{
"sku": "xxx",
"fieldInfo": [{
"fieldName": "field1",
"value": "1"
}, {
"fieldName": "2",
"fieldValue": "2"
}]
}, {
"sku": "yyy",
"fieldInfo": [{
"fieldName": "field1",
"value": "1"
}, {
"fieldName": "2",
"fieldValue": "2"
}]
}]
}, {
"group": "Group3",
"skus": [{
"sku": "xxx",
"fieldInfo": [{
"fieldName": "field1",
"value": "1"
}, {
"fieldName": "2",
"fieldValue": "2"
}]
}, {
"sku": "yyy",
"fieldInfo": [{
"fieldName": "field1",
"value": "1"
}, {
"fieldName": "2",
"fieldValue": "2"
}]
}]
}]
}
到目前为止我的代码：
select (
select fieldGroup, max(groupbackgroundcolor) backgroundcolor,JSON_Query(max(skuinfo.skus)) skuinfo
from Amazon.AmazonSpreadsheetData sd
cross apply (
select (select sd2.sku, JSON_Query(max(fieldinfo.fields)) fieldInfo from Amazon.AmazonSpreadsheetData sd2
cross apply (
select (select fieldid, FieldName,LabelName,DefaultValue
from Amazon.AmazonSpreadsheetData sd3
where sd3.sku=sd2.sku
and sd3.fieldgroup=sd2.fieldgroup
and sd3.collectionid=sd2.collectionid
and sd3.deliveryDate=sd2.deliverydate
and sd3. TemplateName=sd2.templatename
and sd3.productType=sd2.producttype
order by sd3.parentchildsort, sd3.fieldSort for JSON path ) fields
) fieldInfo
where sd2.fieldgroup=sd.fieldgroup
and sd2.sku=sd.sku
and sd2.collectionid=sd.collectionid
and sd2.deliveryDate=sd.deliverydate
and sd2.TemplateName=sd.templatename
and sd2.productType=sd.producttype
group by sd2.sku
order by max(sd2.parentchildsort), sd2.sku
for JSON path )skus
--order by parentchildsort,sd.sku for JSON path
) skuInfo
where collectionid=524
and sd.deliveryDate='2021-02-15'
and sd.TemplateName='Boys'
and sd.productType='pants'
and sd.sku like '%RB130-9-%'
group by fieldgroup
--order by max(groupsort)
for JSON path, root('Groups')
) jstring
但所做的只是获取每个组的最大 sku：
{
"Groups": [{
"fieldGroup": "Basic",
"backgroundcolor": "F8A45E",
"skuinfo": [{
"sku": "RB130-9-H-S21D2-SSKC-OS",
"fieldInfo": [{
"fieldid": 220702,
"FieldName": "update_delete",
"LabelName": "Update Delete",
"DefaultValue": "Update"
}, {
"fieldid": 221206,
"FieldName": "product_description",
"LabelName": "Product Description",
"DefaultValue": "descrip"
}]
}]
}, {
"fieldGroup": "Compliance",
"backgroundcolor": "CC9999",
"skuinfo": [{
"sku": "RB130-9-H-S21D2-SSKC-OS",
"fieldInfo": [{
"fieldid": 221390,
"FieldName": "fabric_type",
"LabelName": "Fabric Type",
"DefaultValue": ""
}, {
"fieldid": 220742,
"FieldName": "import_designation",
"LabelName": "Import Designation",
"DefaultValue": ""
}]
}]
}, {
"fieldGroup": "Dimensions",
"backgroundcolor": "8DB4E2",
"skuinfo": [{
"sku": "RB130-9-H-S21D2-SSKC-OS",
"fieldInfo": [{
"fieldid": 221478,
"FieldName": "chest_size",
"LabelName": "Chest Size",
"DefaultValue": ""
}, {
"fieldid": 220930,
"FieldName": "chest_size_unit_of_measure",
"LabelName": "Chest Size Unit Of Measure",
"DefaultValue": ""
}]
}]
}, {
"fieldGroup": "Discovery",
"backgroundcolor": "92D050",
"skuinfo": [{
"sku": "RB130-9-H-S21D2-SSKC-OS",
"fieldInfo": [{
"fieldid": 220842,
"FieldName": "bullet_point1",
"LabelName": "Key Product Features",
"DefaultValue": ""
}, {
"fieldid": 221502,
"FieldName": "bullet_point2",
"LabelName": "Key Product Features",
"DefaultValue": ""
}]
}]
}, {
"fieldGroup": "Fulfillment",
"backgroundcolor": "B7DEE8",
"skuinfo": [{
"sku": "RB130-9-H-S21D2-SSKC-OS",
"fieldInfo": [{
"fieldid": 221190,
"FieldName": "fulfillment_center_id",
"LabelName": "Fulfillment Center ID",
"DefaultValue": "DEFAULT"
}, {
"fieldid": 221422,
"FieldName": "package_height",
"LabelName": "Package Height",
"DefaultValue": ""
}]
}]
}, {
"fieldGroup": "Images",
"backgroundcolor": "FFFF00",
"skuinfo": [{
"sku": "RB130-9-H-S21D2-SSKC-OS",
"fieldInfo": [{
"fieldid": 221358,
"FieldName": "other_image_url1",
"LabelName": "Other Image URL1",
"DefaultValue": ""
}, {
"fieldid": 220858,
"FieldName": "other_image_url2",
"LabelName": "Other Image URL2",
"DefaultValue": ""
}]
}]
}, {
"fieldGroup": "Offer",
"backgroundcolor": "FF0000",
"skuinfo": [{
"sku": "RB130-9-H-S21D2-SSKC-OS",
"fieldInfo": [{
"fieldid": 221014,
"FieldName": "list_price",
"LabelName": "Manufacturer's Suggested Retail Price",
"DefaultValue": ""
}, {
"fieldid": 221414,
"FieldName": "map_price",
"LabelName": "Minimum Advertised Price",
"DefaultValue": ""
}]
}]
}, {
"fieldGroup": "Product Enrichment",
"backgroundcolor": "BBA680",
"skuinfo": [{
"sku": "RB130-9-H-S21D2-SSKC-OS",
"fieldInfo": [{
"fieldid": 221398,
"FieldName": "bottom_style",
"LabelName": "Bottom Style",
"DefaultValue": ""
}, {
"fieldid": 220798,
"FieldName": "subject_character",
"LabelName": "character",
"DefaultValue": ""
}]
}]
}, {
"fieldGroup": "Required",
"backgroundcolor": "FCD5B4",
"skuinfo": [{
"sku": "RB130-9-H-S21D2-SSKC-OS",
"fieldInfo": [{
"fieldid": 221418,
"FieldName": "feed_product_type",
"LabelName": "Product Type",
"DefaultValue": "pants"
}, {
"fieldid": 220758,
"FieldName": "item_sku",
"LabelName": "Seller SKU",
"DefaultValue": "KP-RB130-9-S21D2"
}]
}]
}, {
"fieldGroup": "Variation",
"backgroundcolor": "FF8080",
"skuinfo": [{
"sku": "RB130-9-H-S21D2-SSKC-OS",
"fieldInfo": [{
"fieldid": 220934,
"FieldName": "parent_child",
"LabelName": "Parentage",
"DefaultValue": "Parent"
}, {
"fieldid": 221538,
"FieldName": "parent_sku",
"LabelName": "Parent SKU",
"DefaultValue": ""
}, {
"fieldid": 221234,
"FieldName": "relationship_type",
"LabelName": "Relationship Type",
"DefaultValue": "Variation"
}, {
"fieldid": 221030,
"FieldName": "variation_theme",
"LabelName": "Variation Theme",
"DefaultValue": "SizeColor"
}]
}]
}]
}
我错过了一个步骤或一个组或其他东西。
谢谢你。
解决方案
推荐阅读
- reactjs - 从 React Native 中的 api 拦截器（组件外部）重定向到屏幕
- css - 使 CSS 图像背景变暗
- c# - Unity3D：GetComponent（string）和GetComponent（Type）之间的结果差异？
- linux - rsyslogd-3000：连接（）处的 omazuremds 错误。errno=没有这样的文件或目录
- html - HTML 找不到样式表信息
- node.js - 过滤和更新数据
- java - 打印带有敏感数据的 HTTPClient 请求参数
- node.js - 监听其他应用程序中发生的事件
- java - Rabbitmq 连接被拒绝，在 linux 服务器上的 docker 容器内运行
- python - Python 函数从另一个函数调用变量。但为什么？