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

The XQuery code you have shown does not produce JSON at all but rather XML so it is not clear how or with which tool you convert the XML result from XQuery to JSON. I don't think anyone can guess that from your code and tags that only mention XQuery and XQuery 3.1, but if you used XQuery 3.1 to construct JSON output you would have some very different XQuery code creating Xdm maps/arrays or making use of `xml-to-json` and its input format. So please add more details to your question about the XQuery processor/environment. — Martin Honnen, Nov 11 '21 at 13:40
Sorry I should have mentioned. We are using Informatica cloud as a middleware, and they have function "util:tojson(output)" function to change from xml to json format. — iswariya Kumaravelu, Nov 12 '21 at 08:02

score 2 · Accepted Answer · answered Nov 11 '21 at 13:49

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>

Thanks for your inputs,i t worked. I used and referred in . – iswariya Kumaravelu Nov 12 '21 at 08:16 — iswariya Kumaravelu, Nov 12 '21 at 08:16

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

1 Answers1