Getting the JSON output in an object format for a single record instead of an array using XQuery

Question

I am getting JSON output from an API in an array format(for multiple records) and in object format for a single record. Instead consumer wants to maintain one format - as an array for single record also. Please let me know if there is a way to display the JSON output in array format irrespective of single/multiple records using XQuery

I have tried the below XQuery:

<Response>
 <totalSize>{totalSize/number()}</totalSize>
 <done>{done/text()}</done>
 <nextRecordsUrl>{nextRecordsUrl/text()}</nextRecordsUrl>
 {
 let $input:=/root/records
 for $i in $input
return
<records>
       <Product1>{$i/Product_Lookup_BI__c/text()}</Product1>
       <EventLastModifiedDate>{$i/LastModifiedDate/text()}</EventLastModifiedDate>
       <Venue>{$i/Venue_vod__r/(fn:concat(fn:concat(fn:concat(fn:concat(fn:concat(fn:concat(BI_Country_Code__c/text(),'-'),State_Province_vod__c/text()),'-'),City_vod__c/text()),'-'),Address_vod__c/text()))}</Venue>
    {
 let $a:=$i/EM_Event_Team_Member_vod__r/records
 for $y in $a
return
 <User_records>
       <AttendeeLastModifiedDate>{$y/LastModifiedDate/text()}</AttendeeLastModifiedDate>
      <EmployeeName>{$y/Team_Member_vod__r/Name/text()}</EmployeeName>
      <EmployeeID>{$y/Team_Member_vod__r/BIDS_ID_BI__c/text()}</EmployeeID>
  </User_records>
     }
  </records>
  }
  </Response>

Actual Output from the above XQuery:

{
  "Response": {
    "totalSize": 1,
    "done": true,
    "nextRecordsUrl": "",
    "records": {
      "Product1": "12345",
      "EventLastModifiedDate": "2021-11-10T01:30:55.000+0000",
      "Venue": "UK",
      "User_records": {
        "AttendeeLastModifiedDate": "2021-11-08T02:55:03.000+0000",
        "EmployeeName": "Ish",
        "EmployeeID": "00002113152"
      }
    }
  }
}

Expected Output: The Output should be in an array format for "records" & "user_records"

{
   "Response":{
      "totalSize":1,
      "done":true,
      "nextRecordsUrl":"",
      "records":[
         {
            "Product1":"12345",
            "EventLastModifiedDate":"2021-11-10T01:30:55.000+0000",
            "Venue":"UK",
            "User_records":[
               {
                  "AttendeeLastModifiedDate":"2021-11-08T02:55:03.000+0000",
                  "EmployeeName":"Ish",
                  "EmployeeID":"00002113152"
               }
            ]
         }
      ]
   }
}

Answer 1

Try:

<User_records xmlns:json="http://www.json.org" json:array="true">
  <AttendeeLastModifiedDate>{$y/LastModifiedDate/text()}</AttendeeLastModifiedDate>
  <EmployeeName>{$y/Team_Member_vod__r/Name/text()}</EmployeeName>
  <EmployeeID>{$y/Team_Member_vod__r/BIDS_ID_BI__c/text()}</EmployeeID>
</User_records>

I would do the same for <records> as well. This example works in eXist-db. The JSON namespace may be different in your environment.

Here is what I ran in eXide:

xquery version "3.1";
declare option exist:serialize "method=json indent=yes media-type=application/json";

<Response>
  <totalSize>5</totalSize>
  <done>yes</done>
  <nextRecordsUrl>abc</nextRecordsUrl>
  <User_records xmlns:json="http://www.json.org" json:array="true">
      <AttendeeLastModifiedDate>123</AttendeeLastModifiedDate>
      <EmployeeName>456</EmployeeName>
      <EmployeeID>789</EmployeeID>
  </User_records>
</Response>