ESQL IIB 中的 JSON 嵌套数组

JSON Nested Array in ESQL IIB

提问人：Loopinfility 提问时间：3/3/2022 更新时间：7/14/2023 访问量：2220

问：

我是 IIB 的新手，我正在努力从扩展 SQL 中的嵌套数组创建以下 JSON 数据。请推荐我。提前非常感谢。

{
"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"
            }
        ]
}
]

我的 ESQL 代码

            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;

我在这行代码上遇到语法错误。

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

所需的输出是我一开始发布的。

数组 json ibm-integration-bus extended-sql

评论

答：

0赞 kimbert 3/3/2022 #1

请参阅 ESQL FOR 语句

不能将子句“REFERENCE TO”用作 FOR 语句的一部分。该子句用于声明引用变量。

您需要的语法如下所示：

FOR refAddressesInput AS refInputRoot.entitities.(JSON.Array)addresses[] DO
0赞 somto 7/14/2023 #2

您能做什么：

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;

上一个：Velero BackupStorageLocation 访问被拒绝

下一个：ESQL-&gt; 如何使用 SELECT 语句从输入 XMLNSC 中检索值？