In our product in recent changes, oracle tables are added with clob column having key value pairs in xml/json format with new columns.
example of employee:(Please ignore usage of parenthesis)
100,Adam,{{"key": "dept", "value": "Marketing"},{"key:region","value":"Americas"}}
intent of clob column was to have two more columns: dept, region columns.
Big problems with this: 1. cant write regular sql queries 2.need to build xml data to load data. 3. cant index fields in clob. 4. Any of typical db queries need xml/json parsing
Put it in nutshell, this is case of design done to achieve unlimited columns without having to alter ddl, without regard to basic database design.
Appreciate your comments.
I'm not sure what your question is?
Anyway, in answer to your points:
1. Depends on what you mean by "regular". Provided you're storing JSON and have an is JSON constraint, you can use simple dot-notation access:
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. I'm not following you on this one.
3. Not true, you can create Oracle Text indexes on clobs:
create table t (
c1 clob
);
create index i on t ( c1 )
indextype is ctxsys.context;
If you are storing JSON, from 12.2 you can create JSON search indexes. These allow ad-hoc JSON queries to use the index:
create table t (
c1 clob
check ( c1 is json )
);
create search index i on t ( c1 )
for json;Read more about this at:
https://blogs.oracle.com/sql/how-to-store-query-and-create-json-documents-in-oracle-database#index-json 4. Indeed.
If you're asking
should you store data like this then, as I said in the blog post linked above, my opinion is:
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.As you hint at, this is harder to work with. Your queries are likely to be less efficient. It's harder to ensure data quality. And you place the burden of understanding your schema on the data consumers.
Wanting more than 1,000 columns in one table is a fishy requirement. So hoping for "unlimited" columns is... highly suspicious.
Adding a column is an online, non-blocking operation. And has been so for in Oracle Database as long as I've used it (since 8i).
Plus, you still need to think about the structure of your JSON. Or you're going to end up in a big mess one day...