Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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 '21, 10:08

mfkpie8's gravatar image

mfkpie8
273667175
accept rate: 12%

wikified 03 Nov '21, 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 '21, 10:30

mfkpie8's gravatar image

mfkpie8
273667175
accept rate: 12%

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
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:

×438
×30

question asked: 28 Oct '21, 10:08

question was seen: 856 times

last updated: 03 Nov '21, 04:51