@svidgen The data in the custom format is relational, with the exception of the JSON, which would need to be in a separate column. 90% of the fields from the custom format can be converted to a relational structure, but the other 10% need to go into JSON or some other standard format, because they are semantic 'tags' that have variable structure. The JSON is the only thing that changes in terms of structure. I added this clarification to my question.

@svidgen And the fact that this one JOSN column has a large number of variations, it doesn't fit into a relational model at all :) if I could make this JSON into a set of relational tables, I would for obvious reasons, but I can't. Our custom text format is some form of 'compression' that works, I guess, but I have to now maintain 4 separate parsers in 3 languages to support it, which is why I'm looking for alternatives.

Then I would have to rely on array indexing hacks to present the data once I've loaded it - and if some items were missing descriptions, for example (which they can), the array indexing would break due to the arrays not being of equal length. This seems like a really hacky way to 'compress' things; no offense, I just don't think this is a good idea.

@JohnR.Strohm I would only need to send this over the wire in order to download the data from our web application where it is managed, to the mobile devices where it is used by clients. I'm only really concerned about the database size increase for the mobile devices, as our database is lowly-managed and updates happen infrequently. Though I agree, if this were a public JSON API being exposed, the bandwidth for API requests would be one of my top concerns.

@JacquesB It's not the rows that have a varying number of fields - the JSON does. The JSON can have a different amount of properties, but every database row in the table will have the same attributes (including the JSON column having a value). That's why I don't want to try to model the JSON as database tables - it's too varying in its structure, and the number of needed tables would grow uncontrollably.

@JimmyJames Indeed, so I'm limited to SQLite, or JSON or binary files. Obviously SQLite would be the best option, since I want to migrate this custom text format to a database for its querying simplicity. I intend to store the JSON I've been discussing in a simple TEXT column, and compress the database is possible. I may instead use the msgpack protocol as suggested by Arne, and store the data in a binary BLOB, and parse it out later. Since I haven't actually tested any of this with real data, I don't know what the 'bloat' will be, but I think my concerns are worth discussing.

Regarging your edit response to my edit: when I say "unstructured", I really mean that it can be represented in a structured format because it has properties and values. However, it is not structured in the sense that there is not any explicit 'rhyme or reason' to what data is in what JSON object. In one database row, we could have 4 fields. In the next database row, there could be 13, with several layers of embedded objects for different JSON keys. The point is, it would be incredibly difficult, and really unhelpful to try to model these differences in actual database tables...

I added clarification to my question. I'm not storing everything as JSON - merely a subset of the converted custom format. This JSON would be stored in one column of the database, and would represent only a small portion of the data as a whole.

@ArneBurmeister Indeed. I added some clarification to the bottom of my question which should clear things up. I'm not going to store everything as JSON, just a small subset of the data from the custom format that is highly variable in its structure. The rest of the custom format will be converted to a normal relational model.

It's a non-critical startup app that runs on smartphones with on average 8 GB of hard drive space. The increase in data storage is negligible for what we could provide our customers if we do the migration. We'll be able to develop better features, faster, which of course they would appreciate. I was curious as to if there was a threshold to "how much increase in footprint is too much" - and your answer identifies some key aspects to examine (customer base, particularly).

@walpen I should be able to compress the SQLite database and unpack it when the app loads; I haven't actually tested that yet, as I just did some rough math using something similar to the example I gave in my question. Given that our competitors have about 10x the data storage requirements as us, I don't think it will be a problem except that users will likely be surprised by the update when we do migrate and the app bloats in size with no real additional functionality. But the whole migration will make dev and maintenance much easier, so I think the tradeoff is worth it in this case.

[a], [b], and [c] are our biggest points right now in this design decision. The custom text format is causing massive headaches in maintaining parsers, complex build processes, a binary (flatfile) database that can't be opened with standard tools, and debugging is extremely cumbersome. With a relational database and some JSON columns, we'll probably increase our storage by 25-75 MB depending on compression. Our competitors are using even more than that. It would just be a large jump for our user base of 400% storage space increase on mobile devices. Cons here are negligible, compared to pros!

@walpen The issue comes from JSON properties being duplicated in database entries, whereas before the property name was embedded in the structure of the custom text format. The format would have to be JSON as opposed to BSON, as they need to be human-readable. Any other text format would cause similar issues with 'property bloat', as I'll call it. The data would be in SQLite, with JSON in a text column. I think that this could be compressed, but I don't know by how much. I guess I should definitely try that.