Oracle 21c对JSON支持功能增强

问题1，Oracle有没有字段可以直接存储JSON串并支持检索的？

问题2，试过几次，用来读取和约束还行，但是要用来存并还需要修改，还是不太容易。联想到DB2之前的XmlQuery语法，门槛太高，不小心就出错

SQL> create table tjson(id number, json_data json);
Table created.

SQL> insert into tjson values(1, '{"name":"Cristiano Ronaldo","goal":30}');
1 row created.

SQL> insert into tjson values(2, json('{"name":"Leo Messi","goal":25}'));
1 row created.

SQL> select * from tjson;
        ID JSON_DATA
---------- -------------------------------------------------------
         1 {"name":"Cristiano Ronaldo","goal":30}
         2 {"name":"Leo Messi","goal":25}

SQL> select j.json_data.name from tjson j;
NAME
--------------------------------------------------------------------------------
"Cristiano Ronaldo"
"Leo Messi"

SQL> select j.id, json_value(j.json_data, '$.name') as name,
  2  json_value(j.json_data, '$.goal' returning number) as goal
  3  from tjson j;
        ID NAME                    GOAL
---------- ---------------------- ----------
         1 Cristiano Ronaldo       30
         2 Leo Messi               25

SQL> select j.id, json_query(j.json_data, '$.name' returning varchar2) as name,
  2  json_query(j.json_data, '$.goal' returning varchar2) as goal
  3  from tjson j;
        ID NAME                        GOAL
---------- -------------------------- ---------------
         1 "Cristiano Ronaldo"         30
         2 "Leo Messi"                 25

SQL> select j.id, jt.name, jt.goal
  2  from tjson j,
  3  json_table(j.json_data, '$' columns(name varchar2(50 char) path '$.name',
  4                                      goal number path '$.goal')) jt;
        ID NAME                                                     GOAL
---------- -------------------------------------------------- ----------
         1 Cristiano Ronaldo                                          30
         2 Leo Messi                                                  25

SQL> update tjson set json_data = json_transform(json_data, set '$.goal' = 35) where id = 1;
1 row updated.

SQL> select j.id, json_value(j.json_data, '$.name') as name,
  2  json_value(j.json_data, '$.goal' returning number) as goal
  3  from tjson j;
        ID NAME                                                     GOAL
---------- -------------------------------------------------- ----------
         1 Cristiano Ronaldo                                          35
         2 Leo Messi                                                  25

SQL> update tjson set json_data = json_transform(json_data, set '$.name' = 'L.Messi', replace '$.goal' = 30 returning json) where id = 2;
1 row updated.

removeOp | insertOp | replaceOp | appendOp | setOp | renameOp | keepOp

SQL> select j.id, json_value(j.json_data, '$.name') as name,
  2  json_value(j.json_data, '$.goal' returning number) as goal
  3  from tjson j;
        ID NAME                        GOAL
---------- -------------------------- ----------
         1 Cristiano Ronaldo           35
         2 L.Messi                     30