如何创建一个SQL语句以从变量生成嵌套的JSON?

huangapple go评论100阅读模式
英文:

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.

  1. DECLARE
  2. @FulfillmentOrderId BIGINT = 0,
  3. @NotifyCustomer BIT = 1,
  4. @TrackingCompany NVARCHAR(100) = '',
  5. @TrackingNo NVARCHAR(100) = '';

INPUT

  1. SELECT
  2. @NotifyCustomer AS [notify_customer],
  3. @TrackingCompany AS [tracking_info.company],
  4. @TrackingNo AS [tracking_info.number]
  5. FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

OUTPUT

  1. {
  2. "line_items_by_fulfillment_order": [
  3. {
  4. "fulfillment_order_id": 0
  5. }
  6. ]
  7. }

INPUT

  1. SELECT (
  2. SELECT
  3. @FulfillmentOrderId AS [fulfillment_order_id]
  4. FOR JSON PATH
  5. ) AS [line_items_by_fulfillment_order]
  6. FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

OUTPUT

  1. {
  2. "notify_customer": true,
  3. "tracking_info": {
  4. "company": "",
  5. "number": ""
  6. }
  7. }

DESIRED OUTPUT

  1. {
  2. "fulfillment": {
  3. "notify_customer": true,
  4. "tracking_info": {
  5. "company": "",
  6. "number": 0
  7. },
  8. "line_items_by_fulfillment_order": [
  9. {
  10. "fulfillment_order_id": 0
  11. }
  12. ]
  13. }
  14. }

答案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:

  1. SELECT
  2. @NotifyCustomer AS [fulfillment.notify_customer],
  3. @TrackingCompany AS [fulfillment.tracking_info.company],
  4. @TrackingNo AS [fulfillment.tracking_info.number],
  5. (SELECT @FulfillmentOrderId AS fulfillment_order_id FOR JSON PATH) AS [fulfillment.line_items_by_fulfillment_order]
  6. FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

SQL Server 2022 introduced new JSON features, so the following statement is also an option:

  1. SELECT JSON_OBJECT(
  2. 'fulfillment': JSON_OBJECT(
  3. 'notify_customer': @NotifyCustomer,
  4. 'tracking_info': JSON_OBJECT('company': @TrackingCompany, 'number': @TrackingNo),
  5. 'line_items_by_fulfillment_order': JSON_ARRAY(JSON_OBJECT('fulfillment_order_id': @FulfillmentOrderId))
  6. )
  7. )

Result:

  1. {
  2. "fulfillment":{
  3. "notify_customer":true,
  4. "tracking_info":{"company":"","number":""},
  5. "line_items_by_fulfillment_order":[{"fulfillment_order_id":0}]
  6. }
  7. }

huangapple
  • 本文由 发表于 2023年2月14日 19:53:25
  • 转载请务必保留本文链接:https://go.coder-hub.com/75447456.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定