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";

asked 28 Oct, 10:08

mfkpie8's gravatar image

mfkpie8
240606671
accept rate: 13%

wikified 03 Nov, 02:47


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;
permanent link

answered 28 Oct, 10:30

mfkpie8's gravatar image

mfkpie8
240606671
accept rate: 13%

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, 02:11) Volker Barth

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

(31 Oct, 20:29) mfkpie8
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×436
×23

question asked: 28 Oct, 10:08

question was seen: 123 times

last updated: 03 Nov, 04:51