CODEWITHSUNDEEP
sqljsonsql-serverfor-json
DreamTeK
DreamTeK

Reputation: 34177

How to create a SQL statement to produce nested JSON from variables?

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

Upvotes: 0

Views: 136

Answers (1)

Zhorov
Zhorov

Reputation: 29943

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

Upvotes: 2

Related Questions