Parsing JSON Data with jBASE, QM, and U2

jBase

There are many different ways to parse JSON, but if you are working with jBASE 5 or above, then there are tools built directly into the BASIC language.

jDO

jBASE BASIC allows developers to use the Dynamic Object (jDO) feature to parse JSON into in-memory objects. While the Dynamic Objects has many uses beyond simple JSON name/value pairs, it simplifies parse and interacting with JSON strings.

Figure 1 shows the example JSON that we will be parsing.

{ "orderID": 12345,
"shopper": {
    "Name": "John Smith",
    "Email": johnsmith@example.com
},
"contents": [ 
{
"productID": 34,
"productName": "SuperWidget",
"quantity": 1
},
{
"productID": 56,
"productName": "WonderWidget",
"quantity": 3
}
],
"orderCompleted": true}

Figure 1

For those who don't read JSON, the equivalent in XML can be found in Figure 2.

<order>
  <orderID>1234</orderID>
  <shopper>
    <Name>John Smith</Name>
    <Email>John Smith</Email>
  </shopper>
  <contents>
    <product>
      <productID>34</productID>
      <productName>SuperWidget</productName>
      <quantity>1</quantity>
    </product>
    <product>
      <productID>56</productID>
      <productName>WonderWidget</productName>
      <quantity>3</quantity>
    </product>
  </contents>
  <orderCompleted>true</orderCompleted>
</order>

Figure 2

Parsing JSON into Memory

The first thing that needs to be done is load the JSON string into memory. This will create an in-memory structure that make accessing JSON easier. In order to enable Dynamic Objects "$OPTION jabba" must be included at the top of your program.

JSON.DOCUMENT = JSON.STRING->$fromjson()

$fromjson() converts the JSON string into a dynamic object. Dynamic Objects are part of jBASE's Object Oriented Programing features for BASIC.

Retrieving a Name/Value Pair

Once you have your data collection variable, you will need to access the JSON data in it. Let's say you need to access the "orderID" information. To do this, you would do the following:

CRT JSON.DOCUMENT->orderID

In jDO notation, the "->" indicates that orderID is a property of the JSON.DOCUMENT variable.

NOTE: Just a reminder to people new to JSON… JSON is case-sensitive. In this case, "OrderID", "orderID", and "orderid" are all different property values according to the rules of JSON.

Retrieving a nested Name/Value Pair

The JSON data you are working with will rarely be a simple Name/Value Pair. Generally, it will be nested data. Shopper Name is an example. If you needed to access the Shopper Name, you would use the following JSON Dot notation (not supported in MV) to reference it:

echo shopper.Name; \\ output: "John Smith"

Using a Dynamic Object, the code is very similar to the JSON Dot Notation:

SHOPPER.NAME = JSON.DOCUMENT->shopper->Name
CRT SHOPPER.NAME

Retrieve Data from Arrays

Some JSON strings will have data found in arrays instead of just nested JSON objects. In the example, this would the 'contents' data. If you were accessing this information using JSON Dot Notation, then you would do something like the following:

echo contents[0].productID; \\ output: "34"
echo contents[1].productID; \\ output: "56"

Using a Dynamic Object, there we first need to figure out the size of the array in order to get the information from each product:

PRODUCT.NUM = JSON.DOCUMENT?contents?$size() -1
FOR PCNT = 0 TO PRODUCT.NUM
PRODUCT.ITEM = JSON.DOCUMENT->contents->@p
CRT PRODUCT.ITEM->productID
NEXT PCNT

Formatting JSON

As a byproduct of the jDO functions, there is a cool feature that allows a developer to format a JSON string as human readable indented format. Many time JSON data will be provided in a compressed format. That is, no spaces and line feed, which make debugging JSON issues harder.

JSON.DOCUMENT = JSON.STRING->$fromjson()
CRT JSON.DOCUMENT->$tojson(1)

Additional Information

You can find out more about the jBASE Dynamic objects in the jBASE manual under "Introduction to Dynamic Objects".

QM

If you are working with QM 3.3 or above, then there are tools built directly into the BASIC language.

QM BASIC allows developers to use the Data Collection feature to parse JSON into in memory objects. While the Data Collection feature has many uses beyond simple JSON name/value pairs, it simplifies parse and interacting with JSON strings.

We will be using the same example that we used for jBase as seen in Figure 1 and 2.

Parsing JSON into Memory

The first thing that needs to be done is load the JSON string into memory. This will create an in-memory structure that make accessing JSON easier.

JSON.DOCUMENT = JPARSE(JSON.DATA)

JPARSE() converts the JSON string into a Data Collection variable. Data Collection variables in QM can be pass around inside subroutines and COMMON blocks the same way that File handler and dynamic arrays can.

Retrieving a Name/Value Pair

Once you have your data collection variable, you will need to access the JSON data in it. Let's say you need to access the "orderID" information. To do this, you would do the following:

CRT JSON.DOCUMENT{'orderID'}

NOTE: Just a reminder to people new to JSON: Everything is case-sensitive. In JSON, "OrderID", "orderID", and "orderid" are all different property values.

Retrieving a nested Name/Value Pair

The JSON data you are working with will rarely be in a simple Name/Value Pair without the nested data. For example, if you needed to access the Shopper Name, you would use the following JSON Dot notation (not supported in MV) to reference it:

echo shopper.Name; \\ output: "John Smith"

Using a Data Collection variable, it is very similar to the JSON Dot Notation:

SHOPPER.NAME = JSON.DOCUMENT{'shopper','name'}
CRT SHOPPER.NAME

Retrieve Data from Arrays

Some JSON strings will have data found in arrays instead of just nested JSON objects. In the example, this would the contents data. If you were accessing this information using JSON Dot Notation, then you would do something like the following:

echo contents[0].productID; \\ output: "34"
echo contents[1].productID; \\ output: "56"

Using a Data Collection variable, it is very similar to the JSON Dot Notation:

PRODUCT.ID = JSON.DOCUMENT{'contents',1,'productID'}

While the above code shows you how to access a specific array position, if you needed to process each item in the array you need to get the array size:

PRODUCT.NUM = INMAT(JSON.DOCUMENT{'contents'})
FOR PCNT = 1 TO PRODUCT.NUM
CRT JSON.DOCUMENT{'contents',P,'productID'}
NEXT PCNT

Formatting JSON

As a byproduct of the UDO function, there is a cool feature that allows a developer to format a JSON string as human readable indented format. Many time JSON data will be provided in a compress format. That is, no spaces and line feed, which make debugging JSON issues harder.

JSON.DOCUMENT = JPARSE(JSON.DATA)
CRT JBUILD(JSON.DOCUMENT)

Additional Information

You can find out more about the Data Collections in the QM manual.

U2 (UniData or UniVerse)

If you are working with U2 (UniData or UniVerse), then there are several built-in functions that simplify the processes.

Starting in Universe 11.1 and UniData 7.3, the U2 Dynamic Objects (UDO) functions were added. The UDO commands are designed for developers to parse Name/Value pair data from both JSON, and in some cases, XML strings. U2 dynamic Objects will load the information into memory, so be aware of how large the data is.

We will be using the example of JSON and XML found in Figure 1 and 2

Parsing JSON into Memory

The first thing that needs to be done is load the JSON string into memory. This will create an in-memory structure that the rest of the UDO function will use to access the data.

There is one last thing you will need for the UDO functions to work. You have to include "$INCLUDE UNIVERSE.INCLUDE UDO.H" into your program [Figure 3].

IF NOT (UDORead(JSON.DATA,UDOFORMAT_JSON,JSON.DOCUMENT.HANDLE) = UDO_SUCCES) THEN
  * JSON is not formatted correctly.
END
…
…
UDOFree(JSON.DOCUMENT.HANDLE)

Figure 3

The UDOFree() statement is very important. This will clear the JSON document from memory, which is important for security and to avoid Memory Leaks. It is NOT recommended you store the JSON.DOCUMENT.HANDLE in COMMON statements for this reason.

Retrieving a Name/Value Pair

Once you have your data in-memory, you will need to access it. Let's say you need to access the "orderID" information. To do this, you would use the UDOGetProperty [Figure 4].

IF NOT(UDOGetProperty(JSON.DOCUMEHT.HANDLE,"orderID",ORDER.ID,DATA.TYPE) = UDO_SUCCES) THEN
  * Unable to find OrderID
  UDOFree(JSON.DOCUMENT.HANDLE)
  RETURN
END

Figure 4

UDOGetProperty will return the value of 'orderID' in the ORDER.ID variable, as well as, tell you what kind of information it is in DATA.TYPE. The data type information for function will return if the data is a String, Boolean, Array, or another JSON object. This will be important to know when trying to retrieve the Product data.

NOTE: Just a reminder to people new to JSON… JSON is case-sensitive. In this case, "OrderID", "orderID", and "orderid" are all different property values according to the rules of JSON.

Retrieving a nested Name/Value Pair

Very rarely will the JSON data you are working with be clear Name/Value Pair without the nested data. For example, if you needed to access the Shopper Name, you would use the following JSON Dot notation to reference it:

echo shopper.Name; \\ output: "John Smith"

Using the UDO functions, you will still access the data with UDOGetProperty, but you need to use the following steps [Figure 5].

* Retrieve a handle to the shopper Object
IF NOT(UDOGetProperty(JSON.DOCUMEHT.HANDLE,"shopper",SHOPPER.HANDLE,DATA.TYPE) = UDO_SUCCES) THEN
 * Unable to find Shipper in the JSON Document
 RETURN
END

* Retrieve the Name value from Shopper Object
IF NOT(UDOGetProperty(SHOPPER.HANDLE,"Name",SHOPPER.NAME,DATA.TYPE) = UDO_SUCCES) THEN
 * Unable to find then Name property in Shopper
 RETURN
END
CRT SHOPPER.NAME

Figure 5

You will notice that you first access the 'shipper' property from the document. In this case, the value will be a JSON object. Since it is a JSON object, the value returned is a handle which allows you to access the properties specific to that JSON object.

Once you have SHIPPER.HANDLE defined, you use that variable instead of the JSON.DOCUMENT.HANDLE to reference the properties associated with the 'shopper' data.

Retrieve Data from Arrays

Some JSON strings will have data found in arrays instead of just nested JSON objects. In the example, this would the 'contents' data. If you were accessing this information using JSON Dot Notation, then you would do something like the following:

echo contents[0].productID; \\ output: "34"
echo contents[1].productID; \\ output: "56"

To retrieve these values using the UDO function, there are several steps to go through [Figure 6].

* Retrieve a handle to the contentsarray
IF NOT(UDOGetProperty(JSON.DOCUMEHT.HANDLE,"contents",CONTENTS.HANDLE,DATA.TYPE) = UDO_SUCCES) THEN
 * Unable to find contents in the JSON Document
 RETURN
END

* Process each item in the array
LOOP
 SUCCESS = UDOArrayGetNextItem(CONTENTS.HANDLE,PRODUCT.HANDLE,DATA.TYPE)
WHILE (SUCCESS = UDO_SUCCES) DO
 * Retrieve the Product ID
 IF NOT(UDOGetProperty(PRODUCT.HANDLE,"productID",PRODUCT.ID,DATA.TYPE) = UDO_SUCCES) THEN
  * Unable to find then Name property in Shopper
 END
 CRT PRODUCT.ID
REPEAT

Figure 6

When you do a UDOGetProperty on a JSON property that is an array, then the DATA.TYPE variable will return the value UDO_ARRAY. It would be smart to code your program to test for this before trying to do a UDOArrayGetNextItem to help avoid any assumptions in the JSON data.

Formatting JSON

As a byproduct of the UDO function, there is a cool feature that allows a developer to format a JSON string as human readable indented format. Many time JSON data will be provided in a compress format. That is, no spaces and line feed, which make debugging JSON issues harder [Figure 7].

* Tell UDO to output as formated
IF NOT(UDOSetOption(UDOOPTION_OUTPUTMODE, UDO_OUTPUT_FORMATTED) = UDO_SUCCESS) THEN
 * Unable to set Options
END
IF UDOWrite(JSON.DOCUMEHT.HANDLE, UDOFORMAT_JSON, JSON.OUT) = UDO_SUCCESS THEN
 CRT JSON.OUT
END

Figure 7

Additional Information

You can find out more about the U2 Dynamic Objects in the UniBASIC Extensions manual.

Featured:

Jan/Feb 2020