4 ms·

> If you're just using SQLite to write and read full JSON blobs, the JSON datatype will be the best pick. There is no json data type ! If you are just storing

> If you're just using SQLite to write and read full JSON blobs, the JSON datatype will be the best pick. There is no json data type ! If you are just storing json blobs, then the BLOB or TEXT data types will be the best picks.

gwd 3y ago
In context, what is clearly meant is, "If you're just reading and writing full JSON blobs, use `insert ... ($id,$json)`; if you're primarily querying the data, use `insert ... ($id, jsonb($json))`, which will convert the text into JSONB before storing it.
dheera 3y ago
> There is no json data type Why not? I feel like a database should allow for a JSON data type that stores only valid JSON or throws an exception. It would also be nice to be able to access subfields of the JSON. SELECT userid, username, some_json_field["some_subfield"][0] FROM users where ... Not sure where to give feature suggestions so I'm just leaving this here for future devs to find
- sroussey 3y ago
  SQLite has a surprisingly small number of types: null, integer, real, text, and blob. That’s it. https://www.sqlite.org/datatype3.html https://www.sqlite.org/datatype3.html
- mdaniel 3y ago
  The H2 database uses almost that exact syntax: create table foo(my_json json); insert into foo values ('{"a":{"b":{"c":"d"}}}' FORMAT JSON); select (my_json)."a"."b"."c" from foo; -- where the () around the field is mandatory https://h2database.com/html/grammar.html#field_reference https://h2database.com/html/grammar.html#field_reference
- chungy 3y ago
  > It would also be nice to be able to access subfields of the JSON. Not that it's not _useful_ sometimes, but it amuses me that this is a huge violation of 1NF and people are often ok with it. It really depends on whether you're treating the JSON object as an atomic unit on its own, regardless of contents, or using the JSON to store more fine-grained information. I guess the same argument can be made for XML data types, and everyone's OK with it too.
  - marcosdumay 3y ago
    The principles of relational algebra aren't always compatible with real applications. They are successful beyond anything that I can imagine people expecting when creating them. But they are not that complete silver bullet that solves every problem humanity will ever need solved.
  - icedchai 3y ago
    Database design is, unfortunately, a lost art. Up through the early 2010's I remember having design reviews for database schemas, etc. That isn't "agile"... so you just fix it in the next sprint, as you explain to someone what a unique constraint is and why a table is now full of duplicates.
  - yencabulator 3y ago
    1NF is a theory construct that makes no accommodation for real-world performance. I'm shuddering even thinking how many tables and joins I would need to store some of these 3rd party JSON things I need to import & refine.
- 0x457 3y ago
  SQLite has a very few actual data types. There is a function that validates json and you can use it as a check on your `TEXT` or` `BLOB` column. You can access subfields using `json_` and `jsonb_` functions.

gwd 3y ago

In context, what is clearly meant is, "If you're just reading and writing full JSON blobs, use `insert ... ($id,$json)`; if you're primarily querying the data, use `insert ... ($id, jsonb($json))`, which will convert the text into JSONB before storing it.

dheera 3y ago

> There is no json data type Why not? I feel like a database should allow for a JSON data type that stores only valid JSON or throws an exception. It would also be nice to be able to access subfields of the JSON. SELECT userid, username, some_json_field["some_subfield"][0] FROM users where ... Not sure where to give feature suggestions so I'm just leaving this here for future devs to find

sroussey 3y ago
SQLite has a surprisingly small number of types: null, integer, real, text, and blob. That’s it. https://www.sqlite.org/datatype3.html https://www.sqlite.org/datatype3.html
mdaniel 3y ago
The H2 database uses almost that exact syntax: create table foo(my_json json); insert into foo values ('{"a":{"b":{"c":"d"}}}' FORMAT JSON); select (my_json)."a"."b"."c" from foo; -- where the () around the field is mandatory https://h2database.com/html/grammar.html#field_reference https://h2database.com/html/grammar.html#field_reference
chungy 3y ago
> It would also be nice to be able to access subfields of the JSON. Not that it's not _useful_ sometimes, but it amuses me that this is a huge violation of 1NF and people are often ok with it. It really depends on whether you're treating the JSON object as an atomic unit on its own, regardless of contents, or using the JSON to store more fine-grained information. I guess the same argument can be made for XML data types, and everyone's OK with it too.
- marcosdumay 3y ago
  The principles of relational algebra aren't always compatible with real applications. They are successful beyond anything that I can imagine people expecting when creating them. But they are not that complete silver bullet that solves every problem humanity will ever need solved.
- icedchai 3y ago
  Database design is, unfortunately, a lost art. Up through the early 2010's I remember having design reviews for database schemas, etc. That isn't "agile"... so you just fix it in the next sprint, as you explain to someone what a unique constraint is and why a table is now full of duplicates.
- yencabulator 3y ago
  1NF is a theory construct that makes no accommodation for real-world performance. I'm shuddering even thinking how many tables and joins I would need to store some of these 3rd party JSON things I need to import & refine.
0x457 3y ago
SQLite has a very few actual data types. There is a function that validates json and you can use it as a check on your `TEXT` or` `BLOB` column. You can access subfields using `json_` and `jsonb_` functions.