Reporting Web Services :REST JSON Responses

This is a follow-up to the Reporting Web Services: Using the REST Web Services to run a Query for PeopleTools 8.55. I am assuming you have read that article as this post builds on that one. In the 8.55 Release notes, there is a small reference that JSON is now supported in the QAS web service responses. Basically, a third party can run a query over a web service and get JSON back instead of XML. Here is what the release notes say.

PeopleTools 8.55 introduces the ability for QAS REST Services to return a JSON-formatted response, rather than the standard XML response. – PeopleTools 8.55 Release Notes

That is the only sentence included in the release notes which is not too helpful. The QAS Executing the Query - 8.55 PeopleBooks has some mention of this JSON support but the section is not complete and the JSON example is not clear unless you really have a detailed understanding of these web services which most people don’t. I had to do a little digging and debugging to get a working example.

What is new for JSON output Support in QAS Web Services?

• There is a new Query String parameter called json_resp that takes either “true” or “false”
• There is a new undocumented OutResultType value of JSON that is part of the URL Path.
  • In my previous posts about QAS services, you will see the use of the XMLP value.
  • The 8.55 PeopleBooks (as of Aug 26, 2016) does not include the JSON value as a valid value for REST document template in the “Request Message: QAS_EXECUTEQRY_TEMPL” section. However, later in the example section there is an opaque reference to the new value.

Not to worry. We will give a full example here.

First I created a public query called UNLOCKED_USERS with the following SQL.

SELECT A.OPRID, A.USERIDALIAS, TO_CHAR(CAST((A.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), A.LASTUPDOPRID, A.PTALLOWSWITCHUSER 
  FROM PSOPRDEFN A 
  WHERE ( A.ACCTLOCK = 0)</pre>

Now let’s call the QAS web service to execute the query and return JSON. Here is the HTTP Syntax.

GET /PSIGW/RESTListeningConnector/PSFT_CHGDEM/ExecuteQuery.v1/PUBLIC/UNLOCKED_USERS/JSON/NONFILE?isconnectedquery=n&maxrows=3&json_resp=true HTTP/1.1
Host: demo.cedarhillsgroup.com
Authorization: Basic UkVTVFVTRVI6JGVjcmV0a25vY2sxMjM

The JSON response is header has a Content-Type: application/json; charset=UTF-8. The response JSON was:

{
    "status": "success",
    "data": {
        "query": {
            "numrows": 2,
            "queryname=": "UNLOCKED_USERS",
            "rows": [
                {
                    "attr:rownumber": 1,
                    "A.OPRID": "PS",
                    "A.USERIDALIAS": "",
                    "A.LASTUPDDTTM": "2016-05-24T12:17:29-0700",
                    "A.LASTUPDOPRID": "PPLSOFT",
                    "A.PTALLOWSWITCHUSER": 0
                },
                {
                    "attr:rownumber": 2,
                    "A.OPRID": "PTWEBSERVER",
                    "A.USERIDALIAS": "",
                    "A.LASTUPDDTTM": "2016-07-05T09:34:22-0700",
                    "A.LASTUPDOPRID": "PPLSOFT",
                    "A.PTALLOWSWITCHUSER": 0
                }
            ]
        }
    }
}

FILE Example

You can also request a FILE as opposed to the NONFILE above.

GET /PSIGW/RESTListeningConnector/PSFT_CHGDEM/ExecuteQuery.v1/PUBLIC/UNLOCKED_USERS/JSON/FILE?isconnectedquery=n&maxrows=3&json_resp=true HTTP/1.1
Host: demo.cedarhillsgroup.com
Authorization: Basic UkVTVFVTRVI6JGVjcmV0a25vY2sxMjM

The reponse body looks like this:

{
  "status": "success",
  "data": {
    "fileurl": "http://demo.cedarhillsgroup.com/psreports/ps/2630/UNLOCKED_USERS.json",
    "status": "posted"
  }
}

Your client would have to pull out the fileurl URL and then do a GET on that. Here is the HTTP Syntax.

GET /psreports/ps/2630/UNLOCKED_USERS.json HTTP/1.1
Host: demo.cedarhillsgroup.com
Authorization: Basic UkVTVFVTRVI6JGVjcmV0a25vY2sxMjM

Here is the response body:

{"query": {
"numrows": 2,
"queryname=": "UNLOCKED_USERS",
"rows": [
{"attr:rownumber":1,
"A.OPRID":"PS",
"A.USERIDALIAS":"",
"A.LASTUPDDTTM":"2016-05-24T12:17:29-0700",
"A.LASTUPDOPRID":"PPLSOFT",
"A.PTALLOWSWITCHUSER":0
},
{"attr:rownumber":2,
"A.OPRID":"PTWEBSERVER",
"A.USERIDALIAS":"",
"A.LASTUPDDTTM":"2016-07-05T09:34:22-0700",
"A.LASTUPDOPRID":"PPLSOFT",
"A.PTALLOWSWITCHUSER":0
}]
}}

To be honest, I don’t see a lot of value in using the “FILE” method. Most client will want the response right away.

How is the JSON Generated?

The state of JSON generation in PeopleSoft at the time of writing has major limitations and is very painful. Additionally, there is no delivered way to generate dynamic JSON. So I thought maybe there was a new undocumented way of generating JSON. I dug into the handler code to determine how the JSON was being generated.

What I found was that PeopleTools is handing the JSON generation off to the Query Class. The RunToString method has a new undocumented output parameter called %Query_JSON.

From QAS handler code here is the appropriate snippets of PeopleCode.

&aRunQry = %Session.GetQuery();
&resultString = &aRunQry.RunToString(&aQryPromptRec, 0, %Query_JSON, &oExecuteQueryParam.MaxRows);

The Query class is not implemented in Peoplecode class so they must be using some JAVA class to dynamically turn the dynamic query output into JSON which you would not be able to do with the “Document Technology”.

So this could be useful in some situations where you need to generate dynamic JSON from some data. I have a previous post Create a PDF file in PeopleCode using the Query API. You could take that same premise and do some bulk processing, populate a result table, then run a query on that result table using the query class and the new %Query_JSON value to generate JSON dynamically. Of course, you don’t really have any control over the JSON. So there is limited use for this.

Additional Reading

• Reporting Web Services: Using the REST Web Services to run a Query
• QAS Executing the Query - 8.55 PeopleBooks