CODEWITHSUNDEEP
javajsonoracle-database
user3575174
user3575174

Reputation: 17

Storing JSON Array in Oracle table

I am processing huge JSON data from a file and each JSON object has an array like

field28436": [{
            "id": "C28679",
            "value": "Dutch"
        }, {
            "id": "C28963",
            "value": "English"
        }, {
            "id": "C28966",
            "value": "French"
        }, {
            "id": "C28968",
            "value": "German"
        }]

I need to store it into oracle database in a single column. Please suggest a datatype or way to store it in a single column. I am using JAVA to parse the JSON. For Example if I parse this value as a key, value pair using hashmap how can I store it in a single column? Is it possible?

Upvotes: 0

Views: 3670

Answers (2)

Hijesh V L
Hijesh V L

Reputation: 269

i had this situation where i had to store the json message in a column in a table. we used CLOB Datatype and it worked well

Upvotes: 0

user330315
user330315

Reputation:

Use a varchar2 column if you are 100% certain the length will never exceed 4000 bytes. Otherwise use a CLOB or BLOB column.

In any case, you should "enhance" that column with a CHECK constraint that validates that the value is a valid JSON, e.g.: 

create table my_table
(
   id integer primary key, 
   data clob, 
   constraint validate_json CHECK (data IS JSON)
);

Oracle recommends to use a BLOB column instead to avoid the overhead of a multi-byte character set used by CLOB. However that makes handling the JSON a bit more complicated from within Java (or any SQL client).

To store such a value use a PreparedStatement and use the setString() method. Current Oracle drivers don't need setClob() for long strings any more - at least not for INSERT or UPDATE statements. 

String jsonData = "field28436": [....]";
PreparedStatement pstmt = connection.prepareStatement(
      "insert into my_table (id, data) values (?, ?)");
pstmt.setInt(1, 42);
pstmt.setString(2, jsonData);
pstmt.executeUpdate();

To read the data would use something similar: 

PreparedStatement pstmt = connection.prepareStatement("select data from my_table where id = ?");
pstmt.setInt(1, 42);
ResultSet rs = psmt.executeQuery();
if (rs.next()) {
  String jsonData = rs.getString(1);
}

For more information I recommend to read the JSON Developer's Guide

Upvotes: 1

Related Questions