0

I have a JSON document column in Oracle DB table. The JSON column structure is as follows:

{
  "C1":1.1,
  "C2":1.2,
  "C3":1.3,
  "C4":1.4,
  "C5":1.5,
  "C6":1.6
}

Keys are some variables and value is amount which can be very big. Now, since the document is growing and we can have around 1000 key-value there is possibility of document exceeding 32767 bytes. Is there a way to split this document after say 60 key-value. So, first 60 key-value in one doc, next 60 in another doc and so on..

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Bhawana Solanki
  • 37
  • 5
  • I don't know if this can be done in Oracle itself (REGEXP has similar limitations), have a look at https://stackoverflow.com/questions/49808581/using-jq-how-can-i-split-a-very-large-json-file-into-multiple-files-each-a-spec – Wernfried Domscheit Mar 15 '23 at 08:11
  • 2
    And why 32k is a problem? use clob for the json column instead of varchar2 – gsalem Mar 15 '23 at 08:52
  • I have used CLOB datatype only, we can extend the document size also but in case of huge data it would be better to split the documents – Bhawana Solanki Mar 15 '23 at 09:10
  • 1
    Depending on your use case, you may be better off storing the data in relational format, as it is really a very simple json. – gsalem Mar 15 '23 at 09:35
  • No I can not have it in relational format because these key-value are dynamic..they could be in any number 1,2,100,1000 as many. – Bhawana Solanki Mar 15 '23 at 09:55
  • 2
    If you're already using a CLOB then why is the varchar2 size limit an issue at all; and *why* would it be better to split it up? You'd need to add additional data to identify related sub-documents and manipulate those as one combined document, with no obvious benefit from what you've shared so far. – Alex Poole Mar 15 '23 at 11:25
  • The issue of size limitation may be solved by `clob` as a storage as mentioned above (while in relational DBMS it would be better to use relational structures), but you still will have a limitation on the number of JSON keys (that is either 32k or 65k) – astentx Mar 15 '23 at 11:36
  • So, you mean to say CLOB will not have any size limitations? even if number of key-value increases. – Bhawana Solanki Mar 16 '23 at 05:27

0 Answers0