JSON and Virtual Columns in SQLite

Re:JSON and Virtual Columns in SQLite by 41b696ef1113 on Monday May 16, 2022 @12:36PM (#31399918)

One idea I have been considering - use SQLite as my JSON parser in Lua. Lua has solid SQLite bindings, but there are several JSON libraries, with varying performance characteristics. If I create a :memory: database, I could shunt JSON in/out of SQLite and use their performant implementations to extract fields from APIs.

Admittedly, not a standard use case, but something I intend to try in the near future.

[ Parent | on HN ]

Re:JSON and Virtual Columns in SQLite by banana_giraffe on Monday May 16, 2022 @06:47PM (#31404004)

Be careful with that. Since you'll end up coercing JSON types into SQLite types doing this, you might run into some odd edge cases. One big one is around big numbers:

    sqlite> select json_extract('[12345678901234567899]','$[0]') % 10;
    7.0

It may never be a problem for you, but it's been a pain point for me dealing with different JSON libraries in the past when people stuff giant numbers into something because their end has no problem with it.

[ Parent | on HN ]

Re:JSON and Virtual Columns in SQLite by remram on Monday May 16, 2022 @01:20PM (#31400546)

It seems more like a hack than a good idea. If you have to run with only the Lua standard library, and that's the only (performant) JSON parser in the library, do what you have to do; but otherwise it seems like a bad idea.

[ Parent | on HN ]