8 ms·

SQLite 3.45 released with JSONB support

321 points by genericlemon24 3y ago

rmrf100 3y ago
This is great.
deleted 3y ago
[deleted]
jitl 3y ago
From the original forum post [0] announcing this improvement: > But if you modify your application to start storing JSONB instead of text JSON, you might see a 3-times performance improvement, at least for the JSON-intensive operations. JSONB is also slightly smaller than text JSON in most cases (about 5% or 10% smaller) so you might also see a modest reduction in your database size if you use a lot of JSON. I for one am excited about these improvements (specifically the disk use reduction) since we store a lot of JSON here at Notion Labs, and we’re increasing our use of SQLite. [0]: https://sqlite.org/forum/forumpost/fa6f64e3dc1a5d97 https://sqlite.org/forum/forumpost/fa6f64e3dc1a5d97
- emptysea 3y ago
  Curious how you're using SQLite at Notion, do you have anything public?
  - jbverschoor 3y ago
    Probably one per workspace/tenant ?
    - safetytrick 3y ago
      Who is doing this and where can I read more? What are the tradeoffs? I imagine that you get a a dataset that is significantly smaller but it is much trickier to keep a dataset in memory the way you could with MySQL. It's like having a free implicit index on the customer (because you had to lookup the sqlite db file before you could start querying). I spend a lot of time thinking about tenancy and how to handle it. Tenancy is such a common problem. Performance is the number one reason tickets are hard to estimate. The second in my experience is security. Time and tenancy are the number one opportunities for SQL to just be better (I always need tenancy and my Order By or at least one constraint can typically be satisfied with time).
      - aomix 3y ago
        I wonder if Turso https://turso.tech/ https://turso.tech/ supports that use case. They support 10k databases in the step above free pricing tier.
      - yawaramin 3y ago
        dhh is doing it: https://world.hey.com/dhh/multi-tenancy-is-what-s-hard-about-scaling-web-services-dd1e0e81 https://world.hey.com/dhh/multi-tenancy-is-what-s-hard-about...
      - abhibeckert 3y ago
        I'm doing it, though I haven't written anything up. Happy to share my opinion though, with a bit more experience than you have. The databases I'm working with are pretty small - ballpark 4MB of data per "tenant". So, I guess, a single large database sever with half a terabyte of RAM could keep well over a hundred thousand tenants in memory at the same time (I don't have anywhere near that many tenants, so I haven't tested that... and honestly if I did have that many I'd probably split them up between different servers). Without getting stuck into the into too much detail - "tenant" isn't really a good fit for how we split them up. Our business is largely based on events that happen at a specific date, with maybe a few months of activity before that date. We have an sqlite database for each event (so ~4MB per event). Once the event passes, it's essentially archived and will almost never be accessed. But it won't actually never be accessed so we can't delete it. I haven't run into any performance issues so far, just with regular sqlite databases on the filesystem. I expect the kernel is doing it's thing and making sure "hot" databases are RAM as with any other frequently accessed file on the disk. My understanding (it's a theoretical problem I haven't actually encountered...) is SQLite only really struggles when you have a bunch of simultaneous writes. Our business model doesn't have that. The most actively written table is the one where we record credit card payments... and unfortunately we don't make tens of thousands of sales per second. If we did have that "problem" I'm sure we could allocate some of our billions of dollars per day in profits to finding a way to make it work... my gut instinct would be to continue to use SQLite with some kind of cache in front of it. All writes would go to something faster than SQLite, then be copied to SQLite later. Reads would check the write cache first, and SQLite if the cache misses. My experience working with a single large database is you end up with a lot of stale data that you is almost never needed. When a table has a hundred million rows, with indexes on multiple columns, even the simplest operating like adding a new row can get slow. My approach with SQLite eliminates that - I'll often have just hundreds of rows in a table and access is blazingly fast. When I need to access another database that hasn't been touched in a long time (years possibly), having to wait, what, an entire millisecond, for the SSD to load that database off the filesystem into memory isn't a big deal. No user is going to notice or complain. Obviously that's more challenging with some data sets and if you're constantly accessing old data, those milliseconds will add up to significant iowait and things will fall over. I definitely don't use SQLite for all of my databases... but in general if you're doing enough writes for SQLite's simultaneous write performance issue to be a problem... then chances are your data set is going to get very large, very quickly, and you're going to have performance headaches no matter what database you're using. Finding some way to divide your database is an obvious performance win... and SQLite makes that really easy.
  - jitl 3y ago
    Nothing public. We’ve used SQLite in our native apps (including desktop) for years, like you’d expect. We’re considering how we could use it in the browser in a few ways now that OPFS and the ecosystem there are stabilizing. We’re also looking at some use cases server side, but not one-db-per-tenant. I don’t think SQLite’s single-writer model would mesh well with Notion’s collaborative features. I’m actually very curious if the one-db-per-tenant concept turns out to be a good idea or a fad. To me it seems like a small app can very happily fit all their users on a single Postgres instance with much less orchestration effort, and a large app demanding of its database would hit the single-write lock thing. If you want to know more, think about joining?? :) https://notion.so/careers https://notion.so/careers or @jitl on Twitter
elpocko 3y ago
> The internal JSONB format is also uses slightly less disk space then text JSON.
nalgeon 3y ago
If you find the official release notes a bit dry, I've made an interactive version: https://antonz.org/sqlite-3-45 https://antonz.org/sqlite-3-45
stabbles 3y ago
> Fix a couple of harmless compiler warnings that appeared in debug builds with GCC 16. Some projects use -Werror, only ever test with older GCC, and builds fail with anything recent. SQLite on the other hand anticipates the new compiler warnings of GCC 3 major versions in the future, that's impressive!
- mgaunard 3y ago
  GCC 16!? Are they from the future?
  - stefanos82 3y ago
    I'm sure they mistyped '6' over '3' from numpad; it can happen to any of us.
    - mdaniel 3y ago
      While I find your explanation plausible, who has the muscle memory to press "g", "c", "c", spacebar, lift hand to numpad, "1", "6(no 3 sire!)", hand back to home row?
      - mrcarruthers 3y ago
        It's faster for me than looking down to figure out where the numbers are on the top row
        abhibeckert 3y ago
        If you used the top row more often, you wouldn't need to look down...
      - jwiz 3y ago
        To be fair, you can be typing "gcc" with the left hand, while the right hand moves to the numpad. Efficiency.
      - 3y ago
ado__dev 3y ago
Very welcome improvement. I overlooked SQLite for far too long relegating it to just a "toy database, not meant for real world apps". Boy was I wrong.
rthkljlkrj 3y ago
[dead]
simonw 3y ago
If anyone wants to try this out on macOS here's the fastest way I've found to try a new SQLite version there: https://til.simonwillison.net/sqlite/sqlite-version-macos-python https://til.simonwillison.net/sqlite/sqlite-version-macos-py... Short version: cd /tmp wget 'https://www.sqlite.org/2024/sqlite-amalgamation-3450000.zip' unzip sqlite-amalgamation-3450000.zip cd sqlite-amalgamation-3450000 gcc -dynamiclib sqlite3.c -o libsqlite3.0.dylib -lm -lpthread DYLD_LIBRARY_PATH=$PWD python3 -c "import sqlite3; print(sqlite3.sqlite_version)" That prints "3.45.0" for me. If you have https://datasette.io/ https://datasette.io/ installed you can then get a web UI for trying it out by running: DYLD_LIBRARY_PATH=$PWD datasette
- csdvrx 3y ago
  and the easiest way for those who can wait for the next update will be to get the binaries from https://cosmo.zip/pub/cosmos/bin/datasette https://cosmo.zip/pub/cosmos/bin/datasette and https://cosmo.zip/pub/cosmos/bin/sqlite3 https://cosmo.zip/pub/cosmos/bin/sqlite3
- sgbeal 3y ago
  > If anyone wants to try this out on macOS here's the fastest way I've found to try a new SQLite version ... https://sqlite.org/fiddle https://sqlite.org/fiddle is always updated as part of the release process and is updated periodically between releases.
- mmebane 3y ago
  FWIW, this works for me with Python 3.12 from Homebrew, but not Python 3.12 from python.org. _sqlite3.cpython-312-darwin.so in Homebrew's Python appears to dynamically link /opt/homebrew/opt/sqlite/lib/libsqlite3.0.dylib, but the version in python.org's Python statically links the sqlite3 library. EDIT: Python 3.9.6 from Xcode doesn't work either. It has _sqlite3.cpython-39-darwin.so which dynamically links /usr/lib/libsqlite3.dylib, but that dylib doesn't exist on my system, and I don't know enough about macOS internals to tell where it's coming from. The _sqlite3 so doesn't seem big enough to have it statically linked. EDIT2: Xcode's Python works when launching via the real path instead of using the /usr/bin/python3 alias, I assume because /usr/bin is SIP-protected or something.
jkljsfdasdf 3y ago
Embarrasing question tbh but with all the cloud-native sqlite stuff like cloudflare d1 and fly LiteFS I'm seriously thinking of switching from postgres to sqlite. Does anyone have a compare/contrast sort of thing between the two?
- mixmastamyk 3y ago
  It sounds great until you need a centralized billing database and then you might want to just stick with postgres rather than run two kinds of database. Does anyone have ideas on how to solve that? Not to mention complicating migrations. Unfortunately sqlite-based product docs seem to end right before getting to the hard stuff. Or perhaps I missed them.
  - robertlagrant 3y ago
    We use Alembic migrations with SQLite. No complaints.
    - graemep 3y ago
      Does Alembic provide a simple solution to the limitations of SQLite's alter table?
      - robertlagrant 3y ago
        You're right. Sorry. I shouldn't post when I'm tired. This is listed as a limitation in Alembic, from memory. Edit: I looked[0]. While it is a SQLite limitation, Alembic does seem to have a way to work around it. [0] https://alembic.sqlalchemy.org/en/latest/batch.html#working-in-offline-mode https://alembic.sqlalchemy.org/en/latest/batch.html#working-...
        graemep 3y ago
        I was asking, not correcting you! Django ORM also uses the same copy and move method but it warns you against doing it on production databases: "same copy and move method but it warns you against doing it on production databases". That makes me reluctant to use SQLite for a use case it is otherwise well suited to: multi-tenant applications. Alembic does not have such dire warnings, but still looks problematic with regard to constraints?
Retr0id 3y ago
Trying to store JSON-like data in a way that's both compact and fast to operate on directly is a challenge. IIUC this is is something SQLite has wanted to introduce for a while, but it took them some time to find a viable approach.
mdaniel 3y ago
I thought I recognized this but its submission URL was goofy; previously discussed: JSONB has landed - https://news.ycombinator.com/item?id=38540421 https://news.ycombinator.com/item?id=38540421 - Dec 2023 (205 comments)
radarsat1 3y ago
I find it really odd that the decision was made to store ints and floats as text in JSONB. It seems to defeat a lot of use cases for it as far as I can tell. There are few solutions for storing and retrieving/querying unstructured numerical data.
- malkia 3y ago
  I don't know the rationale, but I would assume exact preservation of data maybe the case. Does json (yaml/others) have well defined handling of ints/floats? Especially ints with more than 52bits set?
  - nycdotnet 3y ago
    This sounds right. This JSONB seems to be oriented not around semantic parsing, just structural parsing. https://sqlite.org/draft/jsonb.html https://sqlite.org/draft/jsonb.html Note too that JSON doesn’t really have ints or floats - the number type in JSON doesn’t specify a max size. Most implementations of course do rely on the native number types of their platform, so this implementation choice for SQLite allows them to keep a simpler implementation that sidesteps a lot of complexity that would come from deeper parsing. See the number section on json.org https://www.json.org/json-en.html https://www.json.org/json-en.html
  - ncruces 3y ago
    The rational was to design a format that can serve as a (flattened) parse tree for the JSON. JSON handling functions in SQLite took in textual JSON and mostly spat out JSON text. So their structure was: (1) parse JSON, (2) massage in memory representation, (3) serialize JSON. If you can come up with a format that can serve as the in memory representation, and persist that to disk, your functions can skip (1) and (3), and focus on (2). Still, many times you'll need JSON text at the boundary, so making (1) and (3) fast are needed to. Parsing and formatting numbers can also be an unnecessary expense, if you do that more often than you actually need it.
- nick_ 3y ago
  IIRC Sqlite stores all data types as text. It seems like a very weird decision to me.
ckok 3y ago
The only thing missing with Json support now seems to be something like a gin index on jsonb fields to make querying efficiently on any member of the field itself.
yencabulator 3y ago
It's still so weird to me that SQLite returns it's internal format to callers. create table foo (bar); insert into foo (bar) values ('{"answers": [42]}'); select jsonb_extract(bar, '$.answers') from foo; ;#42

rmrf100 3y ago

This is great.

deleted 3y ago

[deleted]

jitl 3y ago

From the original forum post [0] announcing this improvement: > But if you modify your application to start storing JSONB instead of text JSON, you might see a 3-times performance improvement, at least for the JSON-intensive operations. JSONB is also slightly smaller than text JSON in most cases (about 5% or 10% smaller) so you might also see a modest reduction in your database size if you use a lot of JSON. I for one am excited about these improvements (specifically the disk use reduction) since we store a lot of JSON here at Notion Labs, and we’re increasing our use of SQLite. [0]: https://sqlite.org/forum/forumpost/fa6f64e3dc1a5d97 https://sqlite.org/forum/forumpost/fa6f64e3dc1a5d97

emptysea 3y ago
Curious how you're using SQLite at Notion, do you have anything public?
- jbverschoor 3y ago
  Probably one per workspace/tenant ?
  - safetytrick 3y ago
    Who is doing this and where can I read more? What are the tradeoffs? I imagine that you get a a dataset that is significantly smaller but it is much trickier to keep a dataset in memory the way you could with MySQL. It's like having a free implicit index on the customer (because you had to lookup the sqlite db file before you could start querying). I spend a lot of time thinking about tenancy and how to handle it. Tenancy is such a common problem. Performance is the number one reason tickets are hard to estimate. The second in my experience is security. Time and tenancy are the number one opportunities for SQL to just be better (I always need tenancy and my Order By or at least one constraint can typically be satisfied with time).
    - aomix 3y ago
      I wonder if Turso https://turso.tech/ https://turso.tech/ supports that use case. They support 10k databases in the step above free pricing tier.
    - yawaramin 3y ago
      dhh is doing it: https://world.hey.com/dhh/multi-tenancy-is-what-s-hard-about-scaling-web-services-dd1e0e81 https://world.hey.com/dhh/multi-tenancy-is-what-s-hard-about...
    - abhibeckert 3y ago
      I'm doing it, though I haven't written anything up. Happy to share my opinion though, with a bit more experience than you have. The databases I'm working with are pretty small - ballpark 4MB of data per "tenant". So, I guess, a single large database sever with half a terabyte of RAM could keep well over a hundred thousand tenants in memory at the same time (I don't have anywhere near that many tenants, so I haven't tested that... and honestly if I did have that many I'd probably split them up between different servers). Without getting stuck into the into too much detail - "tenant" isn't really a good fit for how we split them up. Our business is largely based on events that happen at a specific date, with maybe a few months of activity before that date. We have an sqlite database for each event (so ~4MB per event). Once the event passes, it's essentially archived and will almost never be accessed. But it won't actually never be accessed so we can't delete it. I haven't run into any performance issues so far, just with regular sqlite databases on the filesystem. I expect the kernel is doing it's thing and making sure "hot" databases are RAM as with any other frequently accessed file on the disk. My understanding (it's a theoretical problem I haven't actually encountered...) is SQLite only really struggles when you have a bunch of simultaneous writes. Our business model doesn't have that. The most actively written table is the one where we record credit card payments... and unfortunately we don't make tens of thousands of sales per second. If we did have that "problem" I'm sure we could allocate some of our billions of dollars per day in profits to finding a way to make it work... my gut instinct would be to continue to use SQLite with some kind of cache in front of it. All writes would go to something faster than SQLite, then be copied to SQLite later. Reads would check the write cache first, and SQLite if the cache misses. My experience working with a single large database is you end up with a lot of stale data that you is almost never needed. When a table has a hundred million rows, with indexes on multiple columns, even the simplest operating like adding a new row can get slow. My approach with SQLite eliminates that - I'll often have just hundreds of rows in a table and access is blazingly fast. When I need to access another database that hasn't been touched in a long time (years possibly), having to wait, what, an entire millisecond, for the SSD to load that database off the filesystem into memory isn't a big deal. No user is going to notice or complain. Obviously that's more challenging with some data sets and if you're constantly accessing old data, those milliseconds will add up to significant iowait and things will fall over. I definitely don't use SQLite for all of my databases... but in general if you're doing enough writes for SQLite's simultaneous write performance issue to be a problem... then chances are your data set is going to get very large, very quickly, and you're going to have performance headaches no matter what database you're using. Finding some way to divide your database is an obvious performance win... and SQLite makes that really easy.
- jitl 3y ago
  Nothing public. We’ve used SQLite in our native apps (including desktop) for years, like you’d expect. We’re considering how we could use it in the browser in a few ways now that OPFS and the ecosystem there are stabilizing. We’re also looking at some use cases server side, but not one-db-per-tenant. I don’t think SQLite’s single-writer model would mesh well with Notion’s collaborative features. I’m actually very curious if the one-db-per-tenant concept turns out to be a good idea or a fad. To me it seems like a small app can very happily fit all their users on a single Postgres instance with much less orchestration effort, and a large app demanding of its database would hit the single-write lock thing. If you want to know more, think about joining?? :) https://notion.so/careers https://notion.so/careers or @jitl on Twitter

elpocko 3y ago

> The internal JSONB format is also uses slightly less disk space then text JSON.

nalgeon 3y ago

If you find the official release notes a bit dry, I've made an interactive version: https://antonz.org/sqlite-3-45 https://antonz.org/sqlite-3-45

stabbles 3y ago

> Fix a couple of harmless compiler warnings that appeared in debug builds with GCC 16. Some projects use -Werror, only ever test with older GCC, and builds fail with anything recent. SQLite on the other hand anticipates the new compiler warnings of GCC 3 major versions in the future, that's impressive!

mgaunard 3y ago
GCC 16!? Are they from the future?
- stefanos82 3y ago
  I'm sure they mistyped '6' over '3' from numpad; it can happen to any of us.
  - mdaniel 3y ago
    While I find your explanation plausible, who has the muscle memory to press "g", "c", "c", spacebar, lift hand to numpad, "1", "6(no 3 sire!)", hand back to home row?
    - mrcarruthers 3y ago
      It's faster for me than looking down to figure out where the numbers are on the top row
      - abhibeckert 3y ago
        If you used the top row more often, you wouldn't need to look down...
    - jwiz 3y ago
      To be fair, you can be typing "gcc" with the left hand, while the right hand moves to the numpad. Efficiency.
    - 3y ago

ado__dev 3y ago

Very welcome improvement. I overlooked SQLite for far too long relegating it to just a "toy database, not meant for real world apps". Boy was I wrong.

rthkljlkrj 3y ago

[dead]

simonw 3y ago

If anyone wants to try this out on macOS here's the fastest way I've found to try a new SQLite version there: https://til.simonwillison.net/sqlite/sqlite-version-macos-python https://til.simonwillison.net/sqlite/sqlite-version-macos-py... Short version: cd /tmp wget 'https://www.sqlite.org/2024/sqlite-amalgamation-3450000.zip' unzip sqlite-amalgamation-3450000.zip cd sqlite-amalgamation-3450000 gcc -dynamiclib sqlite3.c -o libsqlite3.0.dylib -lm -lpthread DYLD_LIBRARY_PATH=$PWD python3 -c "import sqlite3; print(sqlite3.sqlite_version)" That prints "3.45.0" for me. If you have https://datasette.io/ https://datasette.io/ installed you can then get a web UI for trying it out by running: DYLD_LIBRARY_PATH=$PWD datasette

csdvrx 3y ago
and the easiest way for those who can wait for the next update will be to get the binaries from https://cosmo.zip/pub/cosmos/bin/datasette https://cosmo.zip/pub/cosmos/bin/datasette and https://cosmo.zip/pub/cosmos/bin/sqlite3 https://cosmo.zip/pub/cosmos/bin/sqlite3
sgbeal 3y ago
> If anyone wants to try this out on macOS here's the fastest way I've found to try a new SQLite version ... https://sqlite.org/fiddle https://sqlite.org/fiddle is always updated as part of the release process and is updated periodically between releases.
mmebane 3y ago
FWIW, this works for me with Python 3.12 from Homebrew, but not Python 3.12 from python.org. _sqlite3.cpython-312-darwin.so in Homebrew's Python appears to dynamically link /opt/homebrew/opt/sqlite/lib/libsqlite3.0.dylib, but the version in python.org's Python statically links the sqlite3 library. EDIT: Python 3.9.6 from Xcode doesn't work either. It has _sqlite3.cpython-39-darwin.so which dynamically links /usr/lib/libsqlite3.dylib, but that dylib doesn't exist on my system, and I don't know enough about macOS internals to tell where it's coming from. The _sqlite3 so doesn't seem big enough to have it statically linked. EDIT2: Xcode's Python works when launching via the real path instead of using the /usr/bin/python3 alias, I assume because /usr/bin is SIP-protected or something.

jkljsfdasdf 3y ago

Embarrasing question tbh but with all the cloud-native sqlite stuff like cloudflare d1 and fly LiteFS I'm seriously thinking of switching from postgres to sqlite. Does anyone have a compare/contrast sort of thing between the two?

mixmastamyk 3y ago
It sounds great until you need a centralized billing database and then you might want to just stick with postgres rather than run two kinds of database. Does anyone have ideas on how to solve that? Not to mention complicating migrations. Unfortunately sqlite-based product docs seem to end right before getting to the hard stuff. Or perhaps I missed them.
- robertlagrant 3y ago
  We use Alembic migrations with SQLite. No complaints.
  - graemep 3y ago
    Does Alembic provide a simple solution to the limitations of SQLite's alter table?
    - robertlagrant 3y ago
      You're right. Sorry. I shouldn't post when I'm tired. This is listed as a limitation in Alembic, from memory. Edit: I looked[0]. While it is a SQLite limitation, Alembic does seem to have a way to work around it. [0] https://alembic.sqlalchemy.org/en/latest/batch.html#working-in-offline-mode https://alembic.sqlalchemy.org/en/latest/batch.html#working-...
      - graemep 3y ago
        I was asking, not correcting you! Django ORM also uses the same copy and move method but it warns you against doing it on production databases: "same copy and move method but it warns you against doing it on production databases". That makes me reluctant to use SQLite for a use case it is otherwise well suited to: multi-tenant applications. Alembic does not have such dire warnings, but still looks problematic with regard to constraints?

Retr0id 3y ago

Trying to store JSON-like data in a way that's both compact and fast to operate on directly is a challenge. IIUC this is is something SQLite has wanted to introduce for a while, but it took them some time to find a viable approach.

mdaniel 3y ago

I thought I recognized this but its submission URL was goofy; previously discussed: JSONB has landed - https://news.ycombinator.com/item?id=38540421 https://news.ycombinator.com/item?id=38540421 - Dec 2023 (205 comments)

radarsat1 3y ago

I find it really odd that the decision was made to store ints and floats as text in JSONB. It seems to defeat a lot of use cases for it as far as I can tell. There are few solutions for storing and retrieving/querying unstructured numerical data.

malkia 3y ago
I don't know the rationale, but I would assume exact preservation of data maybe the case. Does json (yaml/others) have well defined handling of ints/floats? Especially ints with more than 52bits set?
- nycdotnet 3y ago
  This sounds right. This JSONB seems to be oriented not around semantic parsing, just structural parsing. https://sqlite.org/draft/jsonb.html https://sqlite.org/draft/jsonb.html Note too that JSON doesn’t really have ints or floats - the number type in JSON doesn’t specify a max size. Most implementations of course do rely on the native number types of their platform, so this implementation choice for SQLite allows them to keep a simpler implementation that sidesteps a lot of complexity that would come from deeper parsing. See the number section on json.org https://www.json.org/json-en.html https://www.json.org/json-en.html
- ncruces 3y ago
  The rational was to design a format that can serve as a (flattened) parse tree for the JSON. JSON handling functions in SQLite took in textual JSON and mostly spat out JSON text. So their structure was: (1) parse JSON, (2) massage in memory representation, (3) serialize JSON. If you can come up with a format that can serve as the in memory representation, and persist that to disk, your functions can skip (1) and (3), and focus on (2). Still, many times you'll need JSON text at the boundary, so making (1) and (3) fast are needed to. Parsing and formatting numbers can also be an unnecessary expense, if you do that more often than you actually need it.
nick_ 3y ago
IIRC Sqlite stores all data types as text. It seems like a very weird decision to me.

ckok 3y ago

The only thing missing with Json support now seems to be something like a gin index on jsonb fields to make querying efficiently on any member of the field itself.

yencabulator 3y ago

It's still so weird to me that SQLite returns it's internal format to callers. create table foo (bar); insert into foo (bar) values ('{"answers": [42]}'); select jsonb_extract(bar, '$.answers') from foo; ;#42