Extracting value from JSON column in SQLite

[exetico]

Hi,

I've loaded up a SQLite database, where the column named Payload are identified as a field containing JSON:

I understand that Metabase does not support unfolding JSON with SQLite. That I don't really understand, but that's for another day.

I noticed that regexextract are a thing, but sadly, SQLite are not supported there, too.

Going the SQL route

I know it's possible to just use the json_extract function from SQLite, so a query like json_extract(payload, '$.referer') as "referer" can do it for me.

However, that implements some limitations of using Metabase with a cool date selector, and stuff like that:

I know it's possible to use a query like:

[AND DATETIME("activity"."created_ts", 'unixepoch') BETWEEN DATETIME(coalesce(cast({{DateStart}} as date), cast(getdate()-999999 as Date) )) AND DATETIME(coalesce(cast({{DateEnd}} as date), cast(getdate() as Date) ))

But, with that kind of solution, the user needs to select both at "DateStart" and "DateEnd", instead of the awesome "adaptive" date selector which normally are provided in Metabase.

The great "All options" Time-Filter type cannot be used here.

Unable to escape " character

I also noticed that it should be possible to make a custom expression, doing most of the job, however, I', unable to escape the " character.

I tried thinks like this: replace(substring(replace(lower(trim([Payload])), " ", ""), 1, 32), "\"", "")

But also things like """, "" and similar.

Next step

Now I'd like to ask here, to see if some of you have a good suggestion on how to going forward with this.

See the next post, where I'm touching the "Field Filters" option, but are unable to add Metadata (Foreign key), as I cannot use variables in a Model.

[exetico]

While walking through the forum and documentation I noticed this post, where Field Filters was mentioned. I must say, I've never used them before. At least, it's lost in my current solid-state memory allocation ().

And wolla! Now I can use both a Date Filter and my json_extract.

But now I get yet another problem.

Pointing the "shortcutId" to another database column, as a "Foreign Key"

I thought, "Well, maybe I can just save the model, without the filter, and use a Filter in the Dashboard, but no:

So, in sort:

• I cannot use the standard "unfold JSON" option, as it's SQLite
• I cannot use the regexextract, as it's SQLite
• I can use a SQL query with the SQLite json_extract function, and combine that with a "Field Fliter", however, I cannot turn that into a Model, as "_Variables in models aren't supported yet", and therefore, I cannot add Metadata, so I can make a "hot link" between two tables, with a Foreing Key

In other words, I must decide, what to leave on the table, and not implement.

Can that be right? Have I missed something here?

I'd also like to filter on basis of my local custom column "shortcutId", with suggestions from another table, but that's also quite hard, now that I'm unable to make it as a Model.

Let me know what you guys think.

[exetico]

My temporary work-around are to just join the other table, so I have a common key, on basis of my json_extract solution.

With that said, leaving options like "summarize", "group by" and all the other build-in solutions, does hurt a bit. I'm able to do things in SQL too, but I'd be happy to do more in the GUI, whenever possible.

But I'd like to know, if I've missed some awesome functions in Metabase. I've not touched it for more than 1,5 year, so I'm both a bit outdated, and have dropped a bit of knowledge in that period.

With that said, I'm still struggling with getting Metabase to cheat my "date" values as a action date, and not a string:

A pointed out here, by another user: Add Special Type: DateTime String (ISO) · Issue #1576 · metabase/metabase · GitHub

[AndrewMBaines]

Rather than leaving you talking to your self, I'll try and help

Is now the time to switch to a better database? SQLite is great for configuration and sample databases, but you'd find life a lot simpler with Postgres (or even MySQL if you don't like Postgres).

[exetico]

Thanks for joining in. It was a wierd evening yesterday .

Normally I would, for sure. I also considered just setting up a migration-script, to stage the data into another data-source, before importing it in Metabase. But for this cause, it's better to simply hot "read-only" load the resource.

So rather than making the simple "copy data to another DB and add better schema, relation and cast data as types" in Python or similar, I decided to see how long I could come with Metabase, without the transformation.

I've done what I need to do, as this point. But I'm still open for suggestion, regarding the SQLite journey in Metabase. Many resources still uses a SQLite DB, so I think it's relevant for other than myself.