sqlite-utils extract could handle nested objects

[simonw]

Imagine a table (imported from a nested JSON file) where one of the columns contains values that look like this:

{"email": "anonymous@noreply.airtable.com", "id": "usrROSHARE0000000", "name": "Anonymous"}

The sqlite-utils extract command already uses single text values in a column to populate a new table. It would not be much of a stretch for it to be able to use JSON instead, including specifying which of those values should be used as the primary key in the new table.

[simonw]

The Python .extract() method currently starts like this:

def extract(self, columns, table=None, fk_column=None, rename=None):
        rename = rename or {}
        if isinstance(columns, str):
            columns = [columns]
        if not set(columns).issubset(self.columns_dict.keys()):
            raise InvalidColumns(
                "Invalid columns {} for table with columns {}".format(
                    columns, list(self.columns_dict.keys())
                )
            )
        ...

Note that it takes a list of columns (and treats a string as a single item list). That's because it can be called with a list of columns and it will use them to populate another table of unique tuples of those column values.

So a new mechanism that can instead read JSON values from a single column needs to be compatible with that existing design.

[simonw]

Likewise the sqlite-utils extract command takes one or more columns:

Usage: sqlite-utils extract [OPTIONS] PATH TABLE COLUMNS...

  Extract one or more columns into a separate table

Options:
  --table TEXT             Name of the other table to extract columns to
  --fk-column TEXT         Name of the foreign key column to add to the table
  --rename <TEXT TEXT>...  Rename this column in extracted table

[simonw]

For the Python version I'd like to be able to provide a transformation callback function - which can be json.loads but could also be anything else which accepts the value of the current column and returns a Python dictionary of columns and their values to use in the new table.

[simonw]

It would be OK if the CLI version only allows you to specify a single column if you are using the --json option.

[simonw]

Maybe the Python version takes an optional dictionary mapping column names to transformation functions? It could then merge all of those results together - and maybe throw an error if the same key is produced by more than one column.

    db["Reports"].extract(["Reported by"], transform={"Reported by": json.loads})

Or it could have an option for different strategies if keys collide: first wins, last wins, throw exception, add a prefix to the new column name. That feels a bit too complex for an edge-case though.

[simonw]

I'm going to go with last-wins - so if multiple transform functions return the same key the last one will over-write the others.

[simonw]

Problem with calling this argument transform= is that the term "transform" already means something else in this library.

I could use convert= instead.

... but that doesn't instantly make me think of turning a value into multiple columns.

How about expand=? I've not used that term anywhere yet.

db["Reports"].extract(["Reported by"], expand={"Reported by": json.loads})

I think that works. You're expanding a single value into several columns of information.

[simonw]

Here's the current implementation of .extract():

sqlite-utils/sqlite_utils/db.py

Lines 1049 to 1074 in 806c210

	# Populate the lookup table with all of the extracted unique values
	lookup_columns_definition = {
	(rename.get(col) or col): typ
	for col, typ in self.columns_dict.items()
	if col in columns
	}
	if lookup_table.exists():
	if not set(lookup_columns_definition.items()).issubset(
	lookup_table.columns_dict.items()
	):
	raise InvalidColumns(
	"Lookup table {} already exists but does not have columns {}".format(
	table, lookup_columns_definition
	)
	)
	else:
	lookup_table.create(
	{
	**{
	"id": int,
	},
	**lookup_columns_definition,
	},
	pk="id",
	)
	lookup_columns = [(rename.get(col) or col) for col in columns]

Tricky detail here: I create the lookup table first, based on the types of the columns that are being extracted.

I need to do this because extraction currently uses unique tuples of values, so the table has to be created in advance.

But if I'm using these new expand functions to figure out what's going to be extracted, I don't know the names of the columns and their types in advance. I'm only going to find those out during the transformation.

This may turn out to be incompatible with how .extract() works at the moment. I may need a new method, .extract_expand() perhaps? It could be simpler - work only against a single column for example.

I can still use the existing sqlite-utils extract CLI command though, with a --json flag and a rule that you can't run it against multiple columns.

[simonw]

WIP in a pull request.