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

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

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}]
}
}

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:

确定