Skip to Main Content
  • Questions
  • Creating partition on json column in Oracle Database

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question.

Asked: January 07, 2019 - 12:05 pm UTC

Last updated: January 07, 2019 - 4:20 pm UTC

Version: 12.2

Viewed 1000+ times

You Asked

Hi,
Is there a way to create partition on oracle table based on values or keys from JSON column?

and Chris said...

You can use JSON_value to expose an attribute's value as a virtual column. Then partition on that:

create table t (
  c1 varchar2(1000) 
    check ( c1 is json ),
  vc int generated always as ( 
    json_value ( c1, '$.attr' ) 
  )
) partition by list ( vc ) (
  partition p1 values ( 1 ),
  partition p2 values ( 2 )
);

insert into t ( c1 ) values ( '{ "attr": 1 }' );
insert into t ( c1 ) values ( '{ "attr": 2 }' );
commit;

select t.*, uo.subobject_name
from   t, user_objects uo
where  dbms_rowid.rowid_object ( t.rowid ) = uo.data_object_id
order  by 1;

C1              VC   SUBOBJECT_NAME   
{ "attr": 1 }      1 P1               
{ "attr": 2 }      2 P2   


Now, whether you should do this is debatable. As the docs say:

You can partition a table using a JSON virtual column, but it is generally preferable to use a non-JSON column. A partitioning key specifies which partition a new row is inserted into. A partitioning key defined as a JSON virtual column uses SQL/JSON function json_value, and the partition-defining json_value expression is executed each time a row is inserted. This can be costly, especially for insertion of large JSON documents.

https://docs.oracle.com/en/database/oracle/oracle-database/18/adjsn/partitioning-json-data.html#GUID-E1AA281B-483C-48EC-8E5C-5B460EB8EA57

So test carefully before forging ahead with this.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.