JSON conversion options

[thombashi]

Currently, sqlitebiter may not be able to convert JSON in their original format.
Related issues: #58, #84, #88

[Jacob-Riddle]

I think there is a more fundamental issue here. And that is the unqualified advertisement of JSON support. When I read that sqlitebiter supports JSON, my assumption is that I can throw any valid JSON at it and it will give me something usable. At the moment sqlitebiter does not support arbitrarily complex JSON. Even if you add the ability to flatten JSON, the JSON still cannot be arbitrarily complex. This is do to the fact that every unique path has to be its own column. Thus it does not take much complexity to exceed the allowed number of columns in a table.

The way I see it there are two options:

• Clearly state that sqlitebiter does not support nested JSON, and that if the user's object cannot be flattened easily the user will have to break it up.
• Add support for breaking up nested JSON into a series of tables.

[thombashi]

The document stated the limitation of JSON support and supported JSON schemas.

• https://sqlitebiter.readthedocs.io/en/latest/pages/usage/file/index.html?highlight=json#create-a-sqlite-database-from-csv-excel-json-sqlite-etc-files
• https://pytablereader.readthedocs.io/en/latest/pages/reference/format_specific_loader.html#json-loader-classes

These descriptions are existing since 2017. However, many people create issues or email me about JSON conversion.
It might need to improve descriptions of README/docs/error-messages.

EDIT: I updated README and docs

Add support for breaking up nested JSON into a series of tables.

Certain nested JSON is already supported, and this is also described in docs.

[Vuizur]

Another option that probably would work great for JSON lines is creating a column for each JSON key on the first level. For the second level, strings can be converted to TEXT, numbers to numbers, and arrays/dicts also to TEXT. Due to Sqlite's good JSON capabilities this representation will still be really useful in many cases.

[Vuizur]

I mean something like this which I wrote for another project right now (be aware that it is not very safe due to sql injectable strings):
(Ignore the wiktionary, it should work for each jsonl - unless it crashes due to a (pretty likely) bug.)

def load_dump_in_sqlite(dump_file: str, sqlite_file: str):
    """Loads the dump into a sqlite database."""
    # We have a json lines file, so we can read it line by line
    with open(dump_file, "r", encoding="utf-8") as f:
        already_added_keys: set[str] = set()
        with sqlite3.connect(sqlite_file) as conn:
            # We create the table
            conn.execute("CREATE TABLE wiktionary (id INTEGER PRIMARY KEY);")
            # we parse the json
            for line in f:
                obj = json.loads(line)

                # Now we look at the obj. If it has keys that are not in the
                # already_added_keys set, we add them to the table
                for key in obj.keys():
                    if key not in already_added_keys:
                        # Now we detect the type of the value
                        value = obj[key]
                        if isinstance(value, str):
                            conn.execute(
                                f"ALTER TABLE wiktionary ADD COLUMN {key} TEXT;"
                            )
                        elif isinstance(value, int):
                            conn.execute(
                                f"ALTER TABLE wiktionary ADD COLUMN {key} INTEGER;"
                            )
                        elif isinstance(value, list):
                            conn.execute(
                                f"ALTER TABLE wiktionary ADD COLUMN {key} TEXT;"
                            )
                        elif isinstance(value, dict):
                            conn.execute(
                                f"ALTER TABLE wiktionary ADD COLUMN {key} TEXT;"
                            )
                        elif isinstance(value, bool):
                            conn.execute(
                                f"ALTER TABLE wiktionary ADD COLUMN {key} INTEGER;"
                            )
                        elif isinstance(value, float):
                            conn.execute(
                                f"ALTER TABLE wiktionary ADD COLUMN {key} REAL;"
                            )
                        else:
                            # We have an unknown type, so we add a text column
                            print(f"Unknown type for key {key}: {type(value)}")
                            conn.execute(
                                f"ALTER TABLE wiktionary ADD COLUMN {key} TEXT;"
                            )
                        already_added_keys.add(key)
                
                # Now we have to convert the values to the correct type
                for key in obj.keys():
                    value = obj[key]
                    if isinstance(value, list) or isinstance(value, dict):
                        # We have a list or a dict, so we convert it to a string
                        obj[key] = json.dumps(value, ensure_ascii=False)

                # Now we insert the obj in the table
                conn.execute(
                    "INSERT INTO wiktionary ("
                    + ", ".join(obj.keys())
                    + ") VALUES ("
                    + ", ".join(["?"] * len(obj.keys()))
                    + ");",
                    tuple(obj.values()),
                )

[Jacob-Riddle]

My particular JSON blob represents a tree. Each node in the tree can be a scalar value, a dictionary, or an array. Each of which can contain a scalar value, a dictionary, or an array. Would your idea work for a tree like object?

[Vuizur]

Hmm, if it's only a JSON dictionary (and not a JSON lines or something like a JSON array) with one or two top-level keys then you would get a table with one row and one or two columns. There it probably wouldn't make sense because the JSON capabilities of SQLite don't buy you much there. It always depends on the use case.
But when I hear tree then I also think of self-referential tables, it probably would be pretty difficult to get a solution that can automatically generate something useful there.