Advanced JSON functionality

It would be great if the JSON functionality could be expanded.
Currently, we have the problem that Db2 doesn't support the 'nested path' JSON expression.

For example,

create table msct.json_test (id integer not null, json_data CLOB(10485760 OCTETS));

ALTER TABLE MSCT.json_test
    ADD CONSTRAINT json_testpk PRIMARY KEY
        (id)
    ENFORCED;

insert into msct.json_test values (1, '{
"authors":
[ {
"first":"Peter","last":"Bird"
}, {
"first":"Paul","last":"Mueller"
} ]
}');

insert into msct.json_test values (2, '[ {
"first":"Peter","last":"Bird"
}, {
"first":"Paul","last":"Mueller"
} ]');

SELECT T.*
from MSCT.JSON_TEST t1,
JSON_TABLE(t1.json_data, 'strict $'
COLUMNS( "authors[0].first" VARCHAR(20),
"authors[0].last" VARCHAR(20))
ERROR ON ERROR) AS T
where id = 1;
-- for ID=1: It is not possible to get all entries. In this case you only get
Peter Bird

SELECT T.*
from MSCT.JSON_TEST t1,
JSON_TABLE(t1.json_data, 'strict $'
COLUMNS( "first" VARCHAR(20),
"last" VARCHAR(20))
ERROR ON ERROR) AS T
where id = 2;
-- for ID=2: The result is null, it would be greate to get the 2 rows
Peter Bird
Paul Mueller

Another idea is a function that converts JSON to XML (and vice versa) so that we can use the XML functions.

Needed By

Quarter

Post comment

By clicking the "Post Comment" or "Submit Idea" button, you are agreeing to the IBM Ideas Portal Terms of Use.
Do not place IBM confidential, company confidential, or personal information into any field.

Please enter your email address

RELATED IDEAS

Advanced JSON functionality