I'm working on a project that save/retrieve JSON-type string to a database.
Everything works ok, i.e., save and update is safe for types String,Number and Boolean, but for List and Map, I want to see what is the safe way to manipulate List and Map when the data comes back and forth the database especially when items on the list become large, i.e thousands of items, say for list of "friends" and "followers"
I am also concerned with potential data corruption when processing the a JSON List or Map in Java.
What is the safe way to update a List and Map using JSON.Simple library while not loading everything (every item) in memory.
For example I just need to insert one (1) item in a JSON list string that is stored in a database.
JSON isn't suitable for ORM (object relational mapping). This is why NoSQL databases store JSON as document (i.e. the whole thing). Therefore, JSON.Simple has no support for lazy loading part of the JSON structures.
Relational database don't map well to JSON (except for primitives) as you noticed because the natural data structure for List is a 1:N mapping where the list type has an index column (i.e. position of the element in the list) while Map needs an N:M mapping.
So a better solution might be to store the whole JSON string in a CLOB (instead of trying to break it apart) or saving the whole JSON string in a CLOB + extracting a few key fields so you can index the data properly.
But when you work with the JSON you will either have to write your own OR mapper which supports lazy loading of JSON arrays and maps or you will have to read the whole structure into RAM every time.