how can i create array of datas in Stored procedure

Question

And this Response is from the Answer suggested below. Here i need to get hubxdataitem array for every categoryName

[{"Categoryname":"General","DisplayOrder":1,
"hubxDataItems":[{"Id":3,"PatientId":40,"CategoryId":1,"ItemTitle":"Height","ItemValue":"158","ItemUnit":"cms","IsTestDone":true,"ClinicianHubXSign":0,"isAprrovedStatus":false,"isActive":true,"isDeleted":false,"Createdby":1,"CreatedDate":"2022-06-30T22:59:18.7448072","IsTestApproved":true,"PhySignDate":"2022-08-29T05:40:32.2733333"}]},
{"Categoryname":"Mental","DisplayOrder":27},
   i expected "hubxDataItems" array here too
{"Categoryname":"Hemoglobin","DisplayOrder":28},
   i expected "hubxDataItems" array here too
{"Categoryname":"SP02","DisplayOrder":3}]
   i expected "hubxDataItems" array here too

Your code (.. [dbo].[GetClinicianHubXSign] ..) seems to be specific for MS SQL, not for MySQL... — Akina
– Akina, Commented Sep 5, 2022 at 11:55
I don't think that sql server knows arrays, maybe this can help you — GuidoG
– GuidoG, Commented Sep 5, 2022 at 13:00

Charlieface · Accepted Answer · 2022-09-05 14:51:23Z

0

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;

answered Sep 5, 2022 at 14:51

Charlieface

78.8k8 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

KingCk Over a year ago

i want to add hubxdataitem array and pass it the above data that i shown. My question to your question is will the code after Outer apply will act as an array or not??

Charlieface Over a year ago

Have you tried it? Yes it will return a JSON array. SQL Server does not have a native Array type

KingCk Over a year ago

I have one question after the new array starts it is not printing hubxdataitems array data again

Charlieface Over a year ago

I'm not sure what you mean, and I can't repro it because you haven't given sample data. Please give a minimal reproducible example, maybe use dbfiddle.uk to construct your data and query

KingCk Over a year ago

I have updated the question.Under my expected result i have shared a json response data. In that response data "hubxDataItems": array is printing for every categoryId. But the answer you gave it is printing hubxDataItems array only once.Ill share the response i get from the answer you gave for better understanding

|

Collectives™ on Stack Overflow

how can i create array of datas in Stored procedure

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related