Infer ABL schemas from JSON data

The READ-JSON( ) method has to infer a schema when its target ABL data object does not have a schema. Unlike XML, JSON does not have a standard schema language. Because the format of each JSON value indicates its data type, the AVM can infer a schema from the source, whether it is a JSON string, a Progress.Json.ObjectModel.JsonArray, or a Progress.Json.ObjectModel.JsonObject.

When the AVM has to infer schema for the data object, the AVM makes two passes through the JSON data: one to build the schema and one to fill in the data. On the first pass, the AVM reads all the records before finalizing the schema, which has the following effects:

• When the AVM parses a JSON null, it provisionally assigns a CHARACTER data type to the column. If a subsequent record includes a non-null value for that column, the AVM assigns that data type to the column. In either case, the AVM equates the JSON null value to the Unknown value (?).
• If different rows contain different fields, the final schema includes all the fields.

The AVM infers ABL schema from JSON data using the following guidelines:

• Any JSON object containing an array of objects is a temp-table. The temp-table's name is the array's name.
• The entries in an array of objects are the rows of a single temp-table.
• Each name/value pair in a row's object is a column in the temp-table. The column's name is the JSON value's name.
• If a value in a row object is a JSON array, it is an array column. The AVM infers the data type of the array column from the first value in the inner array.
• Any JSON object that is not an array of objects, but that contains at least one object from which the AVM infers a temp-table, is a ProDataSet. The ProDataSet's name is the JSON object's name.
• If the AVM encounters an array of objects within another array of objects, the AVM infers it to be a nested temp-table inside the ProDataSet.
• If the AVM infers a temp-table nested within another inferred temp-table, the AVM attempts to create a relationship between the two tables. If there is only one pair of fields with matching names in the parent and child tables, the AVM creates a data-relation between the parent table and nested child table using the matching fields for the pairs-list. If there are no matching fields, the AVM creates a parent-id-relation between the parent and nested child and adds a RECID field to the child table to maintain the relationship. If there is more than one pair of matching fields between the tables, the AVM generates an error message and the READ-JSON( ) method returns FALSE.

The following table shows how the AVM maps JSON data types to ABL data types when inferring the schema of a temp-table. By comparing the following table with Table 7, you can see that the differences in the data type mapping make it unlikely that an inferred temp-table matches the original object from which the data was read.

As an example for the inferring process, take the following JSON object:

{"ttCust": 
  [
    {"Name": ["L", "Frank", "Baum"], "CustNum": 1, "GoldStatus": null},
    {"Name": ["Alfred", "E", "Newman"], "CustNum": 2, "GoldStatus": false},
    {"Name": ["Bullwinkle", "J", "Moose"], "CustNum": 3, "GoldStatus": true}
  ]
}

The JSON object contains an array name, "ttCust". The AVM sees that it is an array of objects and creates a temp-table named ttCust to hold the data. The first name/value pair in the row's object is an array of JSON string values named "Name". The AVM creates a CHARACTER field, Name, of EXTENT 3 as the temp-table's first column. The next name/value pair is a JSON number named "CustNum". The AVM creates a DECIMAL field, CustNum, as the second column. The final name/value pair contains a JSON null named "GoldStatus". Because the value is a null, the AVM temporarily chooses CHARACTER as the final column's data type. The AVM then reads the next record and determines that it contains a JSON boolean for the final pair and creates a LOGICAL field, GoldStatus, as the final column.

The following procedure reads the output from write-json-pds2.p into a dynamic ProDataSet, inferring the ProDataSet's schema from the JSON data. It then outputs the schema and data to another file, so you can examine the results:

/*  read-json-infer-pds2.p */
DEFINE VARIABLE hDataset AS HANDLE  NO-UNDO.
DEFINE VARIABLE lRetOK   AS LOGICAL NO-UNDO.
DEFINE VARIABLE hQuery   AS HANDLE  NO-UNDO.
DEFINE VARIABLE hBuffer  AS HANDLE  NO-UNDO.
DEFINE VARIABLE hField   AS HANDLE  NO-UNDO.
DEFINE VARIABLE idx1     AS INTEGER NO-UNDO.
DEFINE VARIABLE idx2     AS INTEGER NO-UNDO.
DEFINE VARIABLE idx3     AS INTEGER NO-UNDO.

CREATE DATASET hDataset.

OUTPUT TO InferPDS2.out APPEND.
lRetOK = hDataset:READ-JSON("file", "dsOrderLog2.json", "empty").
RUN displayResults.
DELETE OBJECT hDataset NO-ERROR.
OUTPUT CLOSE.

PROCEDURE displayResults:
  MESSAGE "READ-JSON return value: " lRetOK SKIP.
  MESSAGE SKIP "** hDataset schema info **" SKIP.
  MESSAGE "ProDataSet name: " hDataset:NAME 
          "Num-buffers " hDataset:NUM-BUFFERS.
    DO idx1 = 1 TO hDataset:NUM-BUFFERS:
      hBuffer = hDataset:GET-BUFFER-HANDLE(idx1).
      MESSAGE SKIP "Buffer " idx1 "Buffer name: " hBuffer:NAME.
      MESSAGE "Buffer Field info".
      DO idx2 = 1 TO hBuffer:NUM-FIELDS:
        hField = hBuffer:BUFFER-FIELD(idx2).
        MESSAGE "Field name: " hField:NAME "Data type: " hField:DATA-TYPE 
                " Extent: " hField:EXTENT.
      END. /* idx2 loop */
    END. /* idx1 loop */
  MESSAGE SKIP "** hDataset data **".
    DO idx1 = 1 TO hDataset:NUM-BUFFERS:
      hBuffer = hDataset:GET-BUFFER-HANDLE(idx1).
      MESSAGE "*** Buffer " hBuffer:NAME " Data: ***".
      CREATE QUERY hQuery.
      hQuery:SET-BUFFERS(hBuffer).
      hQuery:QUERY-PREPARE("for each " + hBuffer:NAME).
      hQuery:QUERY-OPEN.
      hQuery:GET-NEXT() NO-ERROR.
      DO WHILE NOT hQuery:QUERY-OFF-END: 
        MESSAGE SKIP.
          DO idx2 = 1 TO hBuffer:NUM-FIELDS:
            hField = hBuffer:BUFFER-FIELD(idx2).
            IF hField:EXTENT = 0 THEN
              MESSAGE hField:NAME ": " hField:BUFFER-VALUE.
            ELSE
              MESSAGE hField:NAME.
              DO idx3 = 1 TO hField:EXTENT:
                MESSAGE hField:NAME ": " hField:BUFFER-VALUE(idx3).
              END. /* idx3 loop */
            END. /* idx2 loop */
            hQuery:GET-NEXT() NO-ERROR.
      END. /* hQuery loop */
        MESSAGE SKIP.
        DELETE OBJECT hQuery NO-ERROR.
    END. /* idx1 loop */
END PROCEDURE.

The output from write-json-pds2.p is designed with the READ-JSON( ) method's inferring feature in mind. The ProDataSet's tables have only one possible foreign key between each pair of nested tables. If you ran write-json-pds2.p substituting the following ProDataSet definition for the include file and then ran read-json-infer-pds2.p on the output, the procedure would generate several errors because there are several fields in each table that match fields in the outer tables.

DEFINE TEMP-TABLE ttCustomer NO-UNDO LIKE Customer.
DEFINE TEMP-TABLE ttOrder    NO-UNDO LIKE Order.
DEFINE TEMP-TABLE ttInvoice  NO-UNDO LIKE Invoice.

DEFINE DATASET dsOrderLog FOR ttCustomer, ttOrder, ttInvoice
  DATA-RELATION CustOrd FOR ttCustomer,
    ttOrder   RELATION-FIELDS(CustNum,CustNum) NESTED 
  DATA-RELATION OrdInv  FOR ttOrder,
    ttInvoice RELATION-FIELDS(OrderNum,OrderNum) NESTED.

DEFINE DATA-SOURCE dsCustomer FOR Customer.
DEFINE DATA-SOURCE dsOrder    FOR Order.
DEFINE DATA-SOURCE dsInvoice  FOR Invoice.
BUFFER ttCustomer:HANDLE:ATTACH-DATA-SOURCE(DATA-SOURCE dsCustomer:HANDLE).
BUFFER ttOrder:HANDLE:ATTACH-DATA-SOURCE(DATA-SOURCE dsOrder:HANDLE).
BUFFER ttInvoice:HANDLE:ATTACH-DATA-SOURCE(DATA-SOURCE dsInvoice:HANDLE).