Serve JSON from PeopleSoft

Last month a reader asked me for an example of serving JSON from PeopleSoft. The following IScript demonstrates how to serve JSON by printing user and role information in JSON format:

Function IScript_GetJSON
   Local SQL &usersCursor = CreateSQL("SELECT OPRID, OPRDEFNDESC, EMAILID FROM PSOPRDEFN WHERE ROWNUM < 6");
   Local SQL &rolesCursor;
   Local string &oprid;
   Local string &oprdefndesc;
   Local string &emailid;
   Local string &rolename;
   
   Local boolean &isFirstUser = True;
   Local boolean &isFirstRole = True;
   
   %Response.Write("[");
   While &usersCursor.Fetch(&oprid, &oprdefndesc, &emailid)
      REM ** comma logic;
      If (&isFirstUser) Then
         &isFirstUser = False;
      Else
         %Response.Write(", ");
      End-If;

      %Response.Write("{""OPRID"": """ | EscapeJavascriptString(&oprid) | """, ""OPRDEFNDESC"": """ | EscapeJavascriptString(&oprdefndesc) | """, ""EMAILID"": """ | EscapeJavascriptString(&emailid) | """, ""ROLES"": [");

      &rolesCursor = CreateSQL("SELECT ROLENAME FROM PSROLEUSER WHERE ROLEUSER = :1 AND ROWNUM < 6", &oprid);
      &isFirstRole = True;
      
      While &rolesCursor.Fetch(&rolename);
         REM ** comma logic;
         If (&isFirstRole) Then
            &isFirstRole = False;
         Else
            %Response.Write(", ");
         End-If;

         %Response.Write("""" | EscapeJavascriptString(&rolename) | """");
      End-While;
      
      &rolesCursor.Close();
      %Response.Write("]}");
   End-While;
   
   %Response.Write("]");
   &usersCursor.Close();
   
End-Function;

The code above uses embedded SQL. In production, be sure to use App Designer SQL definitions. This code listing also embeds JSON formatting strings. As an alternative, I recommend HTML definitions and HTML bind variables. In this manner, HTML definitions serve as templates for structured JSON data.

Formatted, the output from my demo database looks like:

[
    {
        "OPRID": "ADRIESSEN",
        "OPRDEFNDESC": "Anton Driessen",
        "EMAILID": "ADRIESSEN@server.com",
        "ROLES": [
            "All Processes",
            "All Query Access Groups",
            "EPM Scorecard Viewer",
            "Portal User",
            "Query Access - All FSCM"
        ]
    },
    {
        "OPRID": "ADUPOND",
        "OPRDEFNDESC": "Alain Dupond",
        "EMAILID": "ADUPOND@server.com",
        "ROLES": [
            "All Processes",
            "All Query Access Groups",
            "EPM Scorecard Viewer",
            "Portal User",
            "Query Access - All FSCM"
        ]
    },
    {
        "OPRID": "AEGLI",
        "OPRDEFNDESC": "Anna Egli",
        "EMAILID": "AEGLI@server.com",
        "ROLES": [
            "All Processes",
            "All Query Access Groups",
            "EPM Scorecard Viewer",
            "Employee Global Payroll",
            "Portal User"
        ]
    },
    {
        "OPRID": "AERICKSON",
        "OPRDEFNDESC": "Arthur Erickson",
        "EMAILID": "AERICKSON@server.com",
        "ROLES": [
            "Accounts Payable Manager",
            "All Processes",
            "All Query Access Groups",
            "Application Homepages",
            "EP General Options"
        ]
    },
    {
        "OPRID": "AFAIRCHILD",
        "OPRDEFNDESC": "Alison Fairchild",
        "EMAILID": "AFAIRCHILD@server.com",
        "ROLES": [
            "Applicant",
            "All Processes",
            "All Query Access Groups",
            "EPM Scorecard Viewer",
            "Employee ELM"
        ]
    }
]

IScripts provide a secure free-form mechanism for serving data. This makes them perfect for serving JSON in response to a logged in user's AJAX request. If you want to serve PeopleSoft data in JSON format for consumption in a page outside PeopleSoft, then try using an Integration Broker synchronous message handler.

If you need help prototyping your JSON, take a look at the JSON homepage and JSONLint, an online JSON validator. I rely heavily on JSONLint when prototyping JSON.