[odb-users] Store JSON value to DB

[Boris Kolpackov]

Hi,

Anaswara Nair <anaswara.nn at gmail.com> writes:

> Is there any method other than by converting it to wstring to store a JSON
> value as such to SQLite database using ODB?

SQLite just added the json1 loadable extension[1]. While it still stores
the data as TEXT (as discussed in the documentation, due to backwards
compatibility that's the only way), it provides some functions that can
be useful.

If I were to use this extension with ODB I would probably map an
extended database type[2] so that I get automatic validation and
minification on INSERT/UPDATE:

#pragma db map type("JSON_TEXT") as("TEXT") to("json((?))")

#pragma db object
struct object
{
  #pragma db type("JSON_TEXT")
  std::string json;
};

And then use the other functions json1 provides in ODB views if I
wanted to extract only some parts of the JSON document.

[1] https://sqlite.org/json1.html
[2] http://www.codesynthesis.com/~boris/blog/2012/07/18/custom-database-to-cxx-type-mapping-in-odb/

Boris