• Adi Jaradat

Maximo JSON Data

Updated: Feb 18

What is JSON


JSON stands for JavaScript Object Notation. It a lightweight format for storing and transporting data. JSON has gained popularity and became the defacto standard for data transmission.


There are many way to expose data from Maximo (or any other system) to the world. Maximo has out-of-the-box built in capabilities to expose data using XML or JSON but we will focus on a different approach today.


Maximo JSON Capabilities


You can check Maximo JSON API here and here. I wont spend much time on this topic because I believe it deserves a dedicated post.


Other Options to Expose JSON Data


If you decided that going through the different layers of exposing JSON data through Maximo is not the desired options and you probably have a certain format that you need to comply with, there are other options out there.


Those options are data-focused and do not interact (mostly) with Maximo. Hence, you will need to take extra care to maintain the data integrity and relevancy.

Knowledge of Maximo Business Rules is a must to be able to not only expose a well-formatted JSON data but also a data that is correct and makes sense.


Oracle Support for JSON


One of the most convenient ways to generate JSON file and optionally offer this data as a Web Service is using Oracle. Yes, Oracle Database! Oracle 12c offers the ability to construct XML or JSON file from a query result set. An amazing capability added profoundly in 12c release. It gives you full control over the XML or JSON schema.


If you are using any older version of Oracle Database, then you will have to use “concatenation” to generate JSON output. Not so pretty neither easy! but works never the less.


There are four main functions in 12c to construct a JSON output:

  1. JSON_OBJECT

  2. JSON_OBJECTAGG

  3. JSON_ARRAY

  4. JSON_ARRAYAGG

In the example in this post we will focus mainly on JSON_OBJECT and JSON_ARRAYAGG because they allow us to achieve the required schema.

The use of JSON_ARRAYAGG will be demonstrated in the code to show how details can be attached to an object.


The Query


The following query uses Oracle 12c JSON capabilities to generate Public Tender information in JSON based on RFQ object in Maximo. This information will then be offered as a Web Service (I will talk about this in future post) and consumed by a public portal where participates have been registered and qualified.


Of course, the JSON schema is decided by the public portal, but I am just showing a made-up schema for illustration only.

SELECT JSON_OBJECT
(
'Tender Header' VALUE
(
      SELECT JSON_ARRAYAGG
      (
            JSON_OBJECT
            (
                  'Reference'       VALUE RFQ.RFQNUM
                  ,'Description'    VALUE RFQ.DESCRIPTION
                  ,'Reply Date'     VALUE REPLYDATE
                  ,'Strategy'       VALUE RFQ.AWARDSTRATEGY
                  ,'Stages'         VALUE RFQ.STAGES
                  ,'Inspection'     VALUE RFQ.INSPECTIONREQ
                  ,'Par Delivery' VALUE RFQ.PARTIALDELIVERY
                  ,'Change Ind'     VALUE 'N'
                  ,'Tender Details' VALUE
                  (
                        SELECT JSON_ARRAYAGG
                        (
                              JSON_OBJECT
                              (
                                    'Tender Line'     VALUE RFQL.RFQLINENUM
                                    ,'Description'    VALUE RFQL.DESCRIPTION
                                    ,'Quantity'       VALUE RFQL.ORDERQTY
                                    ,'Unit'           VALUE RFQL.ORDERUNIT
                                    ,'Change Ind'     VALUE 'N'
                              )
                        )
                        FROM RFQLINE RFQL
                        WHERE RFQL.RFQNUM=RFQ.RFQNUM
                  )
            )
      )
      FROM RFQ
)
)
FROM DUAL 

And the Output

{
       "Tender Header": [{
              "Reference": "ABC66899",
              "Description": "1225 - Gardner Denver Nash ",
              "Reply Date": "2020-03-04T00:00:00",
              "Strategy": "Competitive",
              "Stages": "One Stage",
              "Inspection": "No",
              "Par Delivery": "Not Acceptable",
              "Change Ind": "N",
              "Tender Details": [{
                     "Tender Line": 5,
                     "Description": "Power Grip, Gt2/1890-14mgt-40, 14/1890-14mgt-40, For Xl-500 ",
                     "Quantity": 5,
                     "Unit": "PC",
                     "Change Ind": "N"
              }, {
                     "Tender Line": 4,
                     "Description": "Power Grip, Gt2/1120-8mgt-30, 14/1120-8mgt-30, For Xl-350 ",
                     "Quantity": 4,
                     "Unit": "PC",
                     "Change Ind": "N"
              }, {
                     "Tender Line": 3,
                     "Description": "Plug Gasket, 1 3/4 In X T 1.5 X Dia 40.5, 8-2, For Xl-350 ",
                     "Quantity": 20,
                     "Unit": "PC",
                     "Change Ind": "N"
              }, {
                     "Tender Line": 2,
                     "Description": "Power Grip, Gt2/2100-8mgt-30, 14/2100-8mgt-30, For Xl-500 ",
                     "Quantity": 2,
                     "Unit": "PC",
                     "Change Ind": "N"
              }, {
                     "Tender Line": 1,
                     "Description": "Plug Bolt, W/Compressor 1.3/4in X 15l X 40.5,  1.3/4 In-12unf X 45l, 8-1,3, For Xl-350 ",
                     "Quantity": 20,
                     "Unit": "PC",
                     "Change Ind": "N"
              }]
       }]
} 


What's next?


With a straight forward query we were able to generate a JSON data that matches a required schema, unless you plan to copy/paste this information or save it to a file, the next step would be creating a Web Service and having external systems consume this service.

Freeing data from a specific system has advantages if used properly, check out my other post about exporting Maximo Workflow into BPMN.

Links

https://www.w3schools.com/js/js_json_intro.asp

https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM%20Maximo%20Asset%20Management/page/Maximo%20JSON%20API

https://www.ibm.com/developerworks/community/wikis/form/anonymous/api/wiki/02db2a84-fc66-4667-b760-54e495526ec1/page/87348f89-b8b4-4c4a-94bd-ecbe1e4e8857/attachment/4221dcd1-6015-41a2-ad28-11b0007ad208/media/Maximo%20JSON%20API%20Overview.pdf

https://docs.oracle.com/database/121/ADXDB/json.htm#ADXDB6246

39 views0 comments

Recent Posts

See All