具有键值对的RDBMS(oracle) 表中的Clob列

在我们的产品在最近的变化中，oracle表添加了clob列，具有xml/json格式的键值对和新列。

example of employee:(Please ignore usage of parenthesis)


100，Adam ，{{ "key": "部门"，"value": "Marketing" },{ "key:region"，"value": "Americas"}}

clob列的意图是增加两个列: 部门、区域列。


这样做的大问题: 1.无法编写常规sql查询2.需要构建xml数据以加载数据。3.clob中的cant索引字段4.任何典型的db查询都需要xml/json解析

简而言之，这是为了实现无限列而完成的设计，而无需更改ddl，而无需考虑基本的数据库设计。

感谢您的评论。

我不确定你的问题是什么？

无论如何，回答你的观点:

1.取决于您所说的 “常规” 是什么意思。如果您存储JSON并具有is JSON约束，则可以使用简单的点符号访问:

create table t (
  c1 varchar2(100)
    check ( c1 is json ) 
);

insert into t values (
 '{"vals": [
   {"key":"dept","value": "Marketing"},
   {"key":"region","value":"Americas"}
  ]
}'
);

select t.c1.vals[0]
from   t t
where  t.c1.vals[0].key = 'dept';

VALS                                 
{"key":"dept","value":"Marketing"} 

2.这次我没有关注你。

3.不正确，您可以在clobs上创建Oracle文本索引:

create table t (
  c1 clob
);

create index i on t ( c1 )
  indextype is ctxsys.context;

如果要存储JSON，则可以从12.2创建JSON搜索索引。这些允许ad-hoc JSON查询使用索引:

create table t (
  c1 clob
    check ( c1 is json ) 
);

create search index i on t ( c1 )
  for json;

在以下位置阅读有关此的更多信息:

https://blogs.oracle.com/sql/how-to-store-query-and-create-json-documents-in-oracle-database#index-json

4.确实。

如果你问should你这样存储数据然后，正如我在上面链接的博客文章中所说的，我的观点是:

Personally I think traditional relational tables should be the default choice for data storage. Taking JSON input and it storing as-is in your database should be the last resort.

正如你所暗示的，这很难处理。您的查询可能效率较低。很难确保数据质量。你把理解你的模式的负担放在数据消费者身上。

在一个表中想要超过1,000列是一个可疑的要求。所以希望 “无限” 栏目是...高度可疑。

添加列是一种在线的，非阻塞的操作。并且在Oracle数据库中一直如此，只要我使用它 (自8i以来)。

另外，您仍然需要考虑JSON的结构。否则总有一天你会陷入一片混乱...