how can i create array of datas in Stored procedure-CodePudding

Ill share my Stored Procedure code and i want to make some changes and im struggle to do it.

Select  
  hb.Id,
  hb.PatientId,
  hb.CategoryId,
  hdc.Categoryname,
  hb.ItemTitle,
  hb.ItemValue,
  hb.ItemUnit,
  hb.IsTestDone,
  isNull([dbo].[GetClinicianHubXSign] (hb.patientId,hb.HTAID),0) ClinicianHubXSign,
  isnull([dbo].[GetHubXApproveStatus] (hb.patientId,hb.HTAID,hb.CategoryID),0) isAprrovedStatus,
  htap.IsTestApproved,
  set Names = Array[
  hb.categoryname ],
  hb.isActive,
  hb.isDeleted,
  hb.Createdby,
  hb.CreatedDate,
  hb.UpdatedBy,
  hb.UpdatedDate,
  hb.DeletedBy,
  hb.DeletedDate,
  htas.createddate as PhySignDate

 
     
  from HubxDataItems hb  

  left join HubxDataCategory hdc on hdc.Id=hb.categoryId 
  left join HubxTestApprovedForPatient htap on htap.HTAID=hb.HTAID
  left join HubxTestApprovedSignature htas on htas.HTAID=htap.HTAID
 
  where hb.PatientId=(@PatientID) and hb.isActive=1 and hb.IsDeleted=0   
  
     
END

Below ill show the data sample that i want to show.Here HuxDataItems is an array holding multiple datas. Can i implement the same at the above stored procedure code to print the same data as i shown below.

{
  "expires_in": 0,
  "data": [
    {
      "categoryId": 2,
      "categoryName": "Lipid Profile",
      "displayOrder": 2,
      "hubxDataItems": [
        {
          "categoryId": 2,
          "itemTitle": "Total Cholesterol",
          "itemValue": "158",
          "itemUnit": "mg/dl",
          "displayOrder": 2,
          "isActive": true,
          "isDeleted": false,
          "createdDate": "2022-06-30T22:50:49.7333498",
          "createdBy": 1,
          "patientId": 40,
          "categoryName": "Lipid Profile",
          "normalRange": "3.6-6.5",
          "itemColor": "Green"
        },

CodePudding user response：

Your desired results are unclear. But it seems you could use something like a nested FOR JSON clause.

Select  
  hdc.Categoryname,
  hb.hubxDataItems
FROM HubxDataCategory hdc
OUTER APPLY (
 SELECT
  hb.Id,
  hb.PatientId,
  hb.CategoryId,
  hb.ItemTitle,
  hb.ItemValue,
  hb.ItemUnit,
  hb.IsTestDone,
  isNull([dbo].[GetClinicianHubXSign] (hb.patientId,hb.HTAID),0) ClinicianHubXSign,
  isnull([dbo].[GetHubXApproveStatus] (hb.patientId,hb.HTAID,hb.CategoryID),0) isAprrovedStatus,
  hb.categoryname,
  hb.isActive,
  hb.isDeleted,
  hb.Createdby,
  hb.CreatedDate,
  hb.UpdatedBy,
  hb.UpdatedDate,
  hb.DeletedBy,
  hb.DeletedDate,
  htap.IsTestApproved,
  htas.createddate as PhySignDate,
 FROM HubxDataItems hb  
 left join HubxTestApprovedForPatient htap on htap.HTAID = hb.HTAID
 left join HubxTestApprovedSignature htas on htas.HTAID = htap.HTAID
 WHERE hdc.Id = hb.categoryId
   AND hb.isActive = 1
   AND hb.IsDeleted = 0   
 FOR JSON PATH
) hb(hubxDataItems)
FOR JSON PATH;