JSON Nested Array in ESQL IIB

Question

I am new to IIB and I am struggling to create below JSON data from a nested array in Extended SQL. Kindly suggest me.Thanks a lot in advance.

{
"entities": [
    {
        "entityId": "104477",
        "systemId": "CCCTP1",
        "segmentType": "Company",
        "customerName": "104477",
        "countryCode": "CYP",
        "modelId": "BOCESM",
        "sourceCurrency": "EUR",
        "accountingMethod": "",
        "consolidated": "N",
        "addresses": [
            {
                "entityId": "104477",
                "addressType": "",
                "address1": "104477",
                "address2": "104477",
                "country": "CYP",
                "state": "",
                "city": "104477",
                "postCode": "104477"
            }
        ]
}
]

My ESQL Code

            DECLARE i INTEGER 1;
            CREATE LASTCHILD OF refResponse NAME 'entities';
            SET refResponse.entities TYPE = (JSON.Array);
            CREATE FIELD refResponse.entities.Item IDENTITY (JSON.Object)Item;
            DECLARE refEntities REFERENCE TO OutputRoot.JSON.Data.entities;
            FOR refEntitiesInput AS refInputRoot.entities.[] DO
                SET refEntities.Item[i].entityId=refEntitiesInput.entityId;
                SET refEntities.Item[i].systemId=refEntitiesInput.systemId;
                SET refEntities.Item[i].segmentType=refEntitiesInput.segmentType;
                SET refEntities.Item[i].customerName=refEntitiesInput.customerName;
                SET refEntities.Item[i].countryCode=refEntitiesInput.countryCode;
                SET refEntities.Item[i].modelId=refEntitiesInput.modelId;
                SET refEntities.Item[i].sourceCurrency=refEntitiesInput.sourceCurrency;
                SET refEntities.Item[i].accountingMethod=refEntitiesInput.accountingMethod;
                SET refEntities.Item[i].consolidated=refEntitiesInput.consolidated;
                DECLARE j INTEGER 1;
                SET refEntities.Item[i].addresses TYPE = (JSON.Array);
                CREATE FIELD refEntities.Item.addresses.Item IDENTITY (JSON.Object)Item;
                DECLARE refAddresses REFERENCE TO refEntities.Item[i].addresses;
                FOR refAddressesInput REFERENCE TO refInputRoot.entitities.(JSON.Array)addresses DO
                    SET refAddresses.Item[j].entityId=refAddressesInput.entityId;
                    SET refAddresses.Item[j].addressType=refAddressesInput.addressType;
                    SET refAddresses.Item[j].address1=refAddressesInput.address1;
                    SET refAddresses.Item[j].address2=refAddressesInput.address2;
                    SET refAddresses.Item[j].country=refAddressesInput.country;
                    SET refAddresses.Item[j].state=refAddressesInput.state;
                    SET refAddresses.Item[j].city=refAddressesInput.city;
                    SET refAddresses.Item[j].postCode=refAddressesInput.postCode;
                    SET j=j+1;
                END FOR;
SET i=i+1;
END FOR;

I am getting syntax error on this line of code.

FOR refAddressesInput REFERENCE TO refInputRoot.entitities.(JSON.Array)addresses DO

The desired output is what i have posted in the beginning.

Answer 1

What you can do:

DECLARE i INTEGER 1;
DECLARE B INTEGER 3; -- random number (substitute for your situation)
DECLARE j INTEGER 1;
DECLARE D INTEGER 8; -- since there are 8 fields in addresses

CREATE FIELD OutputRoot.JSON.Data.entities IDENTITY (JSON.Array)entities;

While i <= B DO
    CREATE FIELD OutputRoot.JSON.Data.entities.entitiesObj[i] IDENTITY (JSON.Object)entitiesObj;

    SET OutputRoot.JSON.Data.entities.entitiesObj[i].systemId=refEntitiesInput.systemId;
    SET OutputRoot.JSON.Data.entities.entitiesObj[i].segmentType=refEntitiesInput.segmentType;
    SET OutputRoot.JSON.Data.entities.entitiesObj[i].customerName=refEntitiesInput.customerName;
    SET OutputRoot.JSON.Data.entities.entitiesObj[i].countryCode=refEntitiesInput.countryCode;
    SET OutputRoot.JSON.Data.entities.entitiesObj[i].modelId=refEntitiesInput.modelId;
    SET OutputRoot.JSON.Data.entities.entitiesObj[i].sourceCurrency=refEntitiesInput.sourceCurrency;
    SET OutputRoot.JSON.Data.entities.entitiesObj[i].accountingMethod=refEntitiesInput.accountingMethod;
    SET OutputRoot.JSON.Data.entities.entitiesObj[i].consolidated=refEntitiesInput.consolidated;

    CREATE FIELD OutputRoot.JSON.Data.entities.entitiesObj[i].addresses IDENTITY (JSON.Array)addresses;
    

                
    While j <= D DO
        CREATE FIELD OutputRoot.JSON.Data.entities.entitiesObj[i].addresses.Item[j] IDENTITY (JSON.Object)Item;

        SET OutputRoot.JSON.Data.entities.entitiesObj[i].addresses.Item[j].entityId=refAddressesInput.entityId;
        SET OutputRoot.JSON.Data.entities.entitiesObj[i].addresses.Item[j].addressType=refAddressesInput.addressType;
        SET OutputRoot.JSON.Data.entities.entitiesObj[i].addresses.Item[j].address1=refAddressesInput.address1;
        SET OutputRoot.JSON.Data.entities.entitiesObj[i].addresses.Item[j].address2=refAddressesInput.address2;
        SET OutputRoot.JSON.Data.entities.entitiesObj[i].addresses.Item[j].country=refAddressesInput.country;
        SET OutputRoot.JSON.Data.entities.entitiesObj[i].addresses.Item[j].state=refAddressesInput.state;
        SET OutputRoot.JSON.Data.entities.entitiesObj[i].addresses.Item[j].city=refAddressesInput.city;
        SET OutputRoot.JSON.Data.entities.entitiesObj[i].addresses.Item[j].postCode=refAddressesInput.postCode;
        SET j = j + 1;
    END WHILE;

    SET i = i + 1;
END WHILE;

Answer 2

See ESQL FOR statement

You cannot use the clause 'REFERENCE TO' as part of a FOR statement. That clause is for declaring a reference variable.

The syntax that you need is something like this:

FOR refAddressesInput AS refInputRoot.entitities.(JSON.Array)addresses[] DO