Building JSON Structure in ESQL IIB/ACE

Hi Everyone I am trying to build a JSON with following structure in ESQL-

{
    "programId": 0,
    "sku": "abc",
    "productTypeId": 0,
    "brand": "string",
    "year_no": 0,
    "characteristics": [
        {
            "characteristicId": 1,
            "value_no": "a"
        },
        {
            "characteristicId": 2,
            "value_no": "b"
        },
        {
            "characteristicId": 3,
            "value_no": "c"
        }
    ]
}

This is my db looks like

Can anyone help me in the ESQL code structure for the particular use case.

Problems I am facing- When I am using Outputroot.JSON.Data - It is printing Data as root object. How Should I loop through DB to get this result. I am fetching every record from DB. Thanks for help!

I am able to get result for one record but when I am looping it for second it is overwriting the previous values.

Also, I have to match and compare first five fields I have to keep them common and build characteristics array.

EDIT: Based on your comments, you want one message on the output terminal when one of the first 5 elements changes the value. The algorithm assumes that your database fields are sorted by the first 5 elements.

Then the code will look like this, see Creating a JSON message to understand the details:

CREATE COMPUTE MODULE DbToJson_Compute
    CREATE FUNCTION Main() RETURNS BOOLEAN
    BEGIN
        DECLARE recordJsonItem REFERENCE TO OutputRoot;
        DECLARE characteristicsJsonItem REFERENCE TO OutputRoot;
        DECLARE previousRecordKey CHAR '';
        FOR record AS InputRoot.XMLNSC.doc.record[] DO
            DECLARE programId INT record.programId;
            DECLARE sku CHAR record.sku;
            DECLARE productTypeId INT record.productTypeId;
            DECLARE brand CHAR record.brand;
            DECLARE year_no INT record.year_no;
            DECLARE currentRecordKey CHAR BuildRecordKey(programId, sku, productTypeId, brand, year_no);
            IF currentRecordKey <> previousRecordKey THEN
                IF previousRecordKey <> '' THEN
                    PROPAGATE;
                END IF;
                CREATE LASTCHILD OF OutputRoot.JSON AS recordJsonItem NAME 'Data';
                SET recordJsonItem.programId = programId;
                SET recordJsonItem.sku = sku;
                SET recordJsonItem.productTypeId = productTypeId;
                SET recordJsonItem.brand = brand;
                SET recordJsonItem.year_no = year_no;
                CREATE FIELD recordJsonItem.characteristics IDENTITY(JSON.Array)characteristics;
                SET previousRecordKey = currentRecordKey;
            END IF;
            CREATE LASTCHILD OF recordJsonItem.characteristics AS characteristicsJsonItem NAME 'Item';
            SET characteristicsJsonItem.characteristicId = record.characteristicId;
            SET characteristicsJsonItem.valueNo = record.valueNo;
        END FOR;
        RETURN TRUE;
    END;

    CREATE FUNCTION BuildRecordKey(programId INT, sku CHAR, productTypeId INT, brand CHAR, year_no INT) RETURNS CHAR
    BEGIN
        RETURN CAST(programId AS CHAR) || sku || CAST(productTypeId AS CHAR) || brand || CAST(year_no AS CHAR);
    END;
END MODULE;

I tested this code with following input:

<?xml version="1.0"?>
<doc>
  <record>
    <programId>0</programId>
    <sku>abc</sku>
    <productTypeId>0</productTypeId>
    <brand>string</brand>
    <year_no>0</year_no>
    <characteristicId>1</characteristicId>
    <valueNo>a</valueNo>
  </record>
  <record>
    <programId>0</programId>
    <sku>abc</sku>
    <productTypeId>0</productTypeId>
    <brand>string</brand>
    <year_no>0</year_no>
    <characteristicId>2</characteristicId>
    <valueNo>b</valueNo>
  </record>
  <record>
    <programId>0</programId>
    <sku>abc</sku>
    <productTypeId>0</productTypeId>
    <brand>string</brand>
    <year_no>0</year_no>
    <characteristicId>3</characteristicId>
    <valueNo>c</valueNo>
  </record>
  <!-- second group starts here -->
  <record>
    <programId>0</programId>
    <sku>xyz</sku>
    <productTypeId>0</productTypeId>
    <brand>string</brand>
    <year_no>0</year_no>
    <characteristicId>11</characteristicId>
    <valueNo>aa</valueNo>
  </record>
  <record>
    <programId>0</programId>
    <sku>xyz</sku>
    <productTypeId>0</productTypeId>
    <brand>string</brand>
    <year_no>0</year_no>
    <characteristicId>22</characteristicId>
    <valueNo>bb</valueNo>
  </record>
</doc>

This is the first output message:

{
  "programId": 0,
  "sku": "abc",
  "productTypeId": 0,
  "brand": "string",
  "year_no": 0,
  "characteristics": [
    {
      "characteristicId": "1",
      "valueNo": "a"
    },
    {
      "characteristicId": "2",
      "valueNo": "b"
    },
    {
      "characteristicId": "3",
      "valueNo": "c"
    }
  ]
}

And this is the second output message:

{
  "programId": 0,
  "sku": "xyz",
  "productTypeId": 0,
  "brand": "string",
  "year_no": 0,
  "characteristics": [
    {
      "characteristicId": "11",
      "valueNo": "aa"
    },
    {
      "characteristicId": "22",
      "valueNo": "bb"
    }
  ]
}