Storing JSON Array in Oracle table

Question

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?

You can: Use CLOB column type and have a look at this question then stackoverflow.com/q/2887362/4636715 — vahdet
– vahdet, Commented Feb 12, 2019 at 12:48
How big is you JSON ? If you are not goiing to exceed the max varchar2 size I would recommend you to not use clob. — Alexander Petrov
– Alexander Petrov, Commented Feb 12, 2019 at 12:52
Not sure about JSON size in real time I am going to process. 5700 records with 100 fields as of now. In real time it may go up to 100,000 records. — user3575174
– user3575174, Commented Feb 12, 2019 at 12:56
Why don't you simply normalize your data model and generate the JSON "on-the-fly" if needed? — user330315
– user330315, Commented Feb 12, 2019 at 13:57
What is your preferred solution: to store the data as a JSON document or shred the data into an array of key-pairs? — APC
– APC, Commented Feb 12, 2019 at 16:59

score 1 · Accepted Answer · 2019-02-12 13:08:22Z

1

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

edited Feb 12, 2019 at 13:08

answered Feb 12, 2019 at 12:55

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

user3575174 Over a year ago

Need to store that data in a standard datatype formats so that I can retrieve or update easily. CLOB and BLOB are last options.Looking for some special datatypes like varray etc..I am not sure about 12c features. If something new in 12c please suggest.

user330315 Over a year ago

@user3575174: CLOB is a standard data type. You can "easily" retrieve and update the value in there (as I have shown). There is no need to treat a CLOB differently in your Java code. Do read the manual link from my answer.

Hijesh V L · Accepted Answer · 2019-02-12 13:39:43Z

0

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

answered Feb 12, 2019 at 13:39

Hijesh V L

2792 silver badges11 bronze badges

1 Comment

Lieutenant Dan Over a year ago

This should be a comment not a answer, bro

M.Kara · Accepted Answer · 2025-04-18 07:30:57Z

If your table has fields that will not be queried but need to be displayed in the UI, it makes sense to save it this way instead of keeping it in a child table. You avoid making extra database queries

import com.fasterxml.jackson.databind.ObjectMapper;

import jakarta.persistence.AttributeConverter;
import jakarta.persistence.Converter;
import java.util.List;

@Converter
public class JsonArrayConverter<T> implements AttributeConverter<List<T>, String> {

    private final ObjectMapper objectMapper = new ObjectMapper();

    @Override
    public String convertToDatabaseColumn(List<T> attribute) {
        if (attribute == null) {
            return null;
        }
        try {
            return objectMapper.writeValueAsString(attribute);
        } catch (Exception e) {
            throw new RuntimeException("JSON serialization error", e);
        }
    }

    @Override
    public List<T> convertToEntityAttribute(String dbData) {
        if (dbData == null || dbData.isEmpty()) {
            return null;
        }
        try {
            return objectMapper.readValue(dbData, objectMapper.getTypeFactory().constructCollectionType(List.class, getEntityClass()));
        } catch (Exception e) {
            throw new RuntimeException("JSON deserialization error", e);
        }
    }

    protected Class<T> getEntityClass() {
        throw new UnsupportedOperationException("getEntityClass() must be implemented");
    }
}

This is how I have a generic class.

public record PersonDetail(String name, Integer degree) {
}

Let's assume we have such an example class(PersonDetail).

import jakarta.persistence.Converter;


@Converter
public class PersonDetailConverter extends JsonArrayConverter<PersonDetail> {

    @Override
    protected Class<PersonDetail> getEntityClass() {
        return PersonDetail.class;
    }
}

This is how I have converter classes.

    @Column(name = "Detail", columnDefinition = "CLOB")
    @Convert(converter = PersonDetailConverter.class)
    private List<PersonDetail> personDetailList;

I define it like this in my Entity class.In this way, it saves it in the table as CLOB by converting it to a json array. When it is retrieved from the table, it is kept as a list in memory.

Collectives™ on Stack Overflow

Storing JSON Array in Oracle table

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related