数据库管理-第184期 23ai:干掉MongoDB的不一定是另一个JSON数据库（20240507）

数据库管理184期 2024-05-07

数据库管理-第184期 23ai:干掉MongoDB的不一定是另一个JSON数据库（20240507）
- 1 JSON需求
- 2 关系型表设计
- 3 JSON关系型二元性视图
- 3 查询视图
- 总结

数据库管理-第184期 23ai:干掉MongoDB的不一定是另一个JSON数据库（20240507）

作者：胖头鱼的鱼缸（尹海文）
Oracle ACE Associate: Database（Oracle与MySQL）
PostgreSQL ACE Partner
10年数据库行业经验，现主要从事数据库服务工作
拥有OCM 11g/12c/19c、MySQL 8.0 OCP、Exadata、CDP等认证
墨天轮MVP、认证技术专家、年度墨力之星，ITPUB认证专家、专家百人团成员，OCM讲师，PolarDB开源社区技术顾问，OceanBase观察团成员
圈内拥有“总监”、“保安”、“国产数据库最大敌人”等称号，非著名社恐（社交恐怖分子）
公众号：胖头鱼的鱼缸；CSDN：胖头鱼的鱼缸（尹海文）；墨天轮：胖头鱼的鱼缸；ITPUB：yhw1809。
除授权转载并标明出处外，均为“非法”抄袭

最早接触JSON数据库还是2015年我在尚观的时候，也算是刚刚入行的时候，那时候了解到了MongoDB，当时对比一般的关系型数据库，对其副本分片架构很感兴趣。后面还是在一些项目上，如用于近期数据快速分析、智能客服、用户信息等场景。
在最新版本Oracle Database还叫23c的时候，也就是第130期的时候，我根据官方PPT加上自己理解内容介绍了JSON关系型二元性视图，Oracle JSON Relational Duality Views，这个功能算是颠覆了关系型表和JSON在数据库层面的使用方式，如需查看相关详细介绍请在CSDN、墨天轮、公众号、ITPUB查看相关内容。那时候并没有做什么实验，今天尝试自己整一个简单场景来使用JSON关系型二元性视图。
为了合理的进行实验，我还是安装了23ai Free版本：

1 JSON需求

这里模拟一个订单系统输出：

{
 "_id"         : "12345678",
 "ordertime"   : "2024-05-07 09:42:21",
 "customer"    : "Cary",
 "details"     : 
   [  {
        "pn"      : "98765",
        "pname"   : "Laptop mode A",
        "price"   : "2000",
        "type"    : "computer"
      },
      {
        "pn"      : "87654",
        "pname"   : "CD A",
        "price"   : "40",
        "type"    : "music"
      },
   ]
}

其中：
order: 订单编号
customer: 订单用户
order_time: 订单时间
order_detail: 订单详情

pn: 商品编号
pname: 商品名称
price: 商品价格
type: 商品分类

2 关系型表设计

分别设计用户表customers、然后是订单表orders、订单详情表order_details、商品表products、商品分类表product_type：

建表并插入一些数据：

--drop tables if exist
drop table order_details purge;
drop table products purge;
drop table product_type purge;
drop table orders purge;
drop table customers purge;

--customers
create table customers
 (customer_id number,
  customer_name varchar2(20),
  CONSTRAINT customers_pk PRIMARY KEY(customer_id));
insert into customers values(123456,'Cary');
insert into customers values(234567,'Calvin');
insert into customers values(345678,'Haiwen');
insert into customers values(456789,'Xiaogang');

--product_type
create table product_type
 (type_id number,
  type_name varchar2(20),
  CONSTRAINT type_pk PRIMARY KEY(type_id));
insert into product_type values(1234,'computer');
insert into product_type values(2345,'music');
insert into product_type values(3456,'food');
insert into product_type values(4567,'book');

--products
create table products
 (product_id number,
  product_name varchar2(20),
  price_number number,
  type_id number,
  CONSTRAINT products_pk PRIMARY KEY(product_id),
  CONSTRAINT products_fk FOREIGN KEY(type_id) REFERENCES product_type(type_id));
insert into products values(98765,'Laptop mode A',2000,1234);
insert into products values(87654,'CD A',40,2345);
insert into products values(76543,'Pork',15,3456);
insert into products values(65432,'Oracle 23ai Document',100,4567);

--orders
create table orders
 (order_id number,
  order_time timestamp,
  customer_id number,
  CONSTRAINT orders_pk PRIMARY KEY(order_id),
  CONSTRAINT orders_fk FOREIGN KEY(customer_id) REFERENCES customers(customer_id));
insert into orders values(12345678,to_timestamp('2024-05-07 09:42:21','yyyy-mm-dd hh24:mi:ss'),123456);
insert into orders values(12345679,to_timestamp('2024-05-07 09:45:25','yyyy-mm-dd hh24:mi:ss'),234567);
insert into orders values(12345680,to_timestamp('2024-05-07 09:48:01','yyyy-mm-dd hh24:mi:ss'),456789);
insert into orders values(12345681,to_timestamp('2024-05-07 09:51:44','yyyy-mm-dd hh24:mi:ss'),345678);

--order_details
create table order_details
 (sub_id number,
  order_id number,
  product_id number,
  CONSTRAINT od_pk PRIMARY KEY(sub_id),
  CONSTRAINT od_fk1 FOREIGN KEY(order_id) REFERENCES orders(order_id),
  CONSTRAINT od_fk2 FOREIGN KEY(product_id) REFERENCES products(product_id));
insert into order_details values(1,12345678,98765);
insert into order_details values(2,12345678,87654);
insert into order_details values(3,12345679,87654);
insert into order_details values(4,12345679,65432);
insert into order_details values(5,12345679,76543);
insert into order_details values(6,12345680,98765);
insert into order_details values(7,12345681,98765);
insert into order_details values(8,12345681,87654);
insert into order_details values(9,12345681,76543);
insert into order_details values(10,12345681,65432);

commit;

检查数据：

select o.order_id orderid,c.customer_name customer,o.order_time ordertime,p.product_name pn,p.product_name pname,p.price_number price,pt.type_name type from orders o,customers c,order_details od,products p,product_type pt where o.customer_id=c.customer_id and o.order_id=od.order_id and od.product_id=p.product_id and p.type_id=pt.type_id order by o.order_id;

通过SQL查询传统关系型表的订单信息会有一些问题，会有重复的上层数据，在本案例中就是orderid、customer、ordertime相关信息。（原谅我用了个比较low的方式写SQL，我写SQL的能力确实一般般）

3 JSON关系型二元性视图

CREATE JSON DUALITY VIEW orders_jdv AS
orders @insert @update @delete
{
   _id          : order_id,
   ordertime    : order_time,
   customers @unnest
   {
     cid        : customer_id,
     customer   : customer_name
   },
   details      : order_details
   [ {
    subid       : sub_id
    products
      {
         pn        : product_id,
         pname     : product_name,
         price     : price_number,       
         product_type @unnest
         {
           typeid  : type_id,
           type    : type_name
         }
       }
     } ]
};