How to use procedures to decompose JSON data？

[ximen]

Deserialize JSON into objects The following is a JSON string. How to use procedures to decompose JSON data？ And insert the table

 [
    {
        "id": "GR244",
        "description": "APPle 12",
        "sum_2019": 95359.000000,
        "sum_2020": 62531.000000

    },
    {
        "ID": "LX041",
        "description": "APPle 11",
         "sum_2019": 24782.000000,
        "sum_2020": 18805.000000

    }
]

thaks

the table

CREATE TABLE "DBA"."AP" (
    "ID" BIGINT NOT NULL,
    "description" VARCHAR(221) NULL,
    "sum_2019" VARCHAR(221) NULL,
    "sum_2020" VARCHAR(2221) NULL,
    PRIMARY KEY ( "ID" ASC )
) IN "system";

[ximen]

BEGIN
    DECLARE json_data LONG VARCHAR;
    CREATE LOCAL TEMPORARY TABLE test (
        name AS VARCHAR(64),
        age AS INT);

    INSERT INTO test (name, age) VALUES ('Frank',51);
    INSERT INTO test (name, age) VALUES ('Bill',22);
    INSERT INTO test (name, age) VALUES ('Jackie',37);

    SELECT * INTO json_data FROM test FOR JSON RAW;

    CALL sp_parse_json ( 'sql_array', json_data );

    SELECT sql_array [[row_num]] .name AS name, sql_array [[row_num]] .age AS age
        FROM sa_rowgenerator ( 1, 3 );
END;

[VolkerBarth]

I don't know whether your posted answer does answer your question, however, I'd suggest to use the cardinality function to access all array elements, such as...

    ...
    SELECT sql_array [[row_num]] .name AS name, sql_array [[row_num]] .age AS age
        FROM sa_rowgenerator ( 1, CARDINALITY(sql_array) );

Sigh, I just noticed you simply copied a sample from the docs without further comments.

[ximen]

Thank you Volker Barth,Let me know a new function：CARDINALITY