pre-processing JSON

[dmoroney]

set up the Data Adapter to do a GET JSON from a URI. works fine. Dataset and Query Dialog WebServiceQuery works fine. the problem is the web server returns

{
    "objects": {
        "Server::672": {
            "code": 0,
            "message": "",
            "class": "Server",
            "key": "672",
            "fields": {
                "name": "some_server_name",
                "description": "some_server_description",
            }
        },
        "Server::1719": {
            "code": 0,
            "message": "",
            "class": "Server",
            "key": "1719",
            "fields": {
                "name": "some_server_name",
                "description": "some_server_description",
            }
        },
    },
    "code": 0,
    "message": "Found: 2"
}

the problem here is that the 'string::integer" format in the response makes it impossible to set the root path under objects. is there a way in JSS to remove the '::integer' pattern so Server can be used for the root path?

[narcism]

What is your desired output? Are you tied to using the Webservice Datasource?

[dmoroney]

the preferred result would be as follows. the "::key" expression would be filtered out somehow. Server would be set as the root path for the report loop

{
    "objects": {
        "Server": {
            "code": 0,
            "message": "",
            "class": "Server",
            "key": "672",
            "fields": {
                "name": "some_server_name",
                "description": "some_server_description",
            }
        },
        "Server": {
            "code": 0,
            "message": "",
            "class": "Server",
            "key": "1719",
            "fields": {
                "name": "some_server_name",
                "description": "some_server_description",
            }
        },
    },
    "code": 0,
    "message": "Found: 2"
}

the application doing this is iTop v2.x from Combodo. Hopefully, I don't have to be tied to the Webservice Datasource for that application. Other datasource suggestions are welcome.

[narcism]

I understand that you want to remove the "::key" part, but is the output of the report that you want to achieve?

[dmoroney]

here's the process in mind

Get Server Response currently recieves in the Fields tab

v objects

> Server::672

> Server::1719

code

message

 

I'd like for Get Server Response to receive or for some pre-processing thing to clean the JSON into

v objects

> Server

> Server

code

message

then I can right click on Server and Set as Root Path to appear as objects.Server.
I'd click on the Data Preview tab to confirm the data I need before moving on to composing the report

[narcism]

Try using JSONQL instead of Webservice Query. Create a JSON file Data Adapter where you specify your JSON URL and check "Use the report JSON expression when filling the report". Then in your report Dataset and Query Dialog pick the adapter and chose "jsonql" as language. Then use this expression to query for fields: objects["Server::.*"]

[dmoroney]

It works.

Create the JSON File adapter

- File/URL: the web service URI including the trailing ?

- Left URL Parameters tab empty

- Entered the needed POST Parameters. A big thing here was to overtly set the desired output fields in the json_data payload

- Entered the needed Http Headers

- Select Use the report JSON expression when filling the report

- Tested the Connection and got Successful

- Save the adapter

Create a New Report. Data Adaper is the JSON File adapter created earlier. Click Next and click OK to continue, and Finish

- Open the DataSet and Query Editor. Select Language jsonql

- Entered this expression 'objects["Server::.*"]fields' (without the ' quotes) as the Set record node (generate query)

- Clicked on Read Fields and the Fields tab filled in

- Clicked on the Data Preview tab and Refresh Preview Data. It's all there

Thanks for the suggestion.

[dmoroney]

found the JSONQL language guide here...

http://jasperreports.sourceforge.net/sample.reference/jsonqldatasource/index.html#jsonql