|
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";
|
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;
1
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.
(29 Oct '21, 02:11)
Volker Barth
Thank you Volker Barth,Let me know a new function：CARDINALITY
(31 Oct '21, 20:29)
mfkpie8
|