英文:
How to create a SQL statement to produce nested JSON from variables?
问题
{
"fulfillment": {
"notify_customer": true,
"tracking_info": {
"company": "",
"number": 0
},
"line_items_by_fulfillment_order": [
{
"fulfillment_order_id": 0
}
]
}
}
英文:
I have managed to create SQL for the individual nodes, but cannot deduce how to refine the statement to include or merge both outputs.
DECLARE
@FulfillmentOrderId BIGINT = 0,
@NotifyCustomer BIT = 1,
@TrackingCompany NVARCHAR(100) = '',
@TrackingNo NVARCHAR(100) = '';
INPUT
SELECT
@NotifyCustomer AS [notify_customer],
@TrackingCompany AS [tracking_info.company],
@TrackingNo AS [tracking_info.number]
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
OUTPUT
{
"line_items_by_fulfillment_order": [
{
"fulfillment_order_id": 0
}
]
}
INPUT
SELECT (
SELECT
@FulfillmentOrderId AS [fulfillment_order_id]
FOR JSON PATH
) AS [line_items_by_fulfillment_order]
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
OUTPUT
{
"notify_customer": true,
"tracking_info": {
"company": "",
"number": ""
}
}
DESIRED OUTPUT
{
"fulfillment": {
"notify_customer": true,
"tracking_info": {
"company": "",
"number": 0
},
"line_items_by_fulfillment_order": [
{
"fulfillment_order_id": 0
}
]
}
}
答案1
得分: 1
以下是翻译好的部分,代码部分保持原样:
You need to build the nested "line_items_by_fulfillment_order" JSON array and change the paths:
SELECT
@NotifyCustomer AS [fulfillment.notify_customer],
@TrackingCompany AS [fulfillment.tracking_info.company],
@TrackingNo AS [fulfillment.tracking_info.number],
(SELECT @FulfillmentOrderId AS fulfillment_order_id FOR JSON PATH) AS [fulfillment.line_items_by_fulfillment_order]
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
SQL Server 2022 introduced new JSON features, so the following statement is also an option:
SELECT JSON_OBJECT(
'fulfillment': JSON_OBJECT(
'notify_customer': @NotifyCustomer,
'tracking_info': JSON_OBJECT('company': @TrackingCompany, 'number': @TrackingNo),
'line_items_by_fulfillment_order': JSON_ARRAY(JSON_OBJECT('fulfillment_order_id': @FulfillmentOrderId))
)
)
Result:
{
"fulfillment":{
"notify_customer":true,
"tracking_info":{"company":"","number":""},
"line_items_by_fulfillment_order":[{"fulfillment_order_id":0}]
}
}
英文:
You need to build the nested "line_items_by_fulfillment_order"
JSON array and change the paths:
SELECT
@NotifyCustomer AS [fulfillment.notify_customer],
@TrackingCompany AS [fulfillment.tracking_info.company],
@TrackingNo AS [fulfillment.tracking_info.number],
(SELECT @FulfillmentOrderId AS fulfillment_order_id FOR JSON PATH) AS [fulfillment.line_items_by_fulfillment_order]
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
SQL Server 2022 introduced new JSON features, so the following statement is also an option:
SELECT JSON_OBJECT(
'fulfillment': JSON_OBJECT(
'notify_customer': @NotifyCustomer,
'tracking_info': JSON_OBJECT('company': @TrackingCompany, 'number': @TrackingNo),
'line_items_by_fulfillment_order': JSON_ARRAY(JSON_OBJECT('fulfillment_order_id': @FulfillmentOrderId))
)
)
Result:
{
"fulfillment":{
"notify_customer":true,
"tracking_info":{"company":"","number":""},
"line_items_by_fulfillment_order":[{"fulfillment_order_id":0}]
}
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论