JSONB support

[ngortheone]

sqlite has recently landed JSONB support.
https://sqlite.org/forum/forumpost/fa6f64e3dc1a5d97

Is this feature available in the go implementation?

[zombiezen]

Yup! As of v1.1.2, we pull in modernc.org/sqlite 1.29.0, which is equivalent to upstream SQLite 3.45.1.

[ngortheone]

Is there an example of go API how to work with jsonb?

[zombiezen]

There's not a Go (or C)-specific API for JSONB because AFAIK everything is done through SQL functions: https://sqlite.org/json1.html

[ngortheone]

Yeah, I am being stupid. For others like me what I was looking for is this:

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  data BLOB
);

INSERT INTO users (id, data) VALUES (1, jsonb('{
  "name": "Alice",
  "age": 25,
  "email": "[email protected]"
}'));

and then

select id, data -> 'age' from users;

or

select id, json(data) from users;

[ngortheone]

can be tried here: https://sqliteonline.com/