3 ms·

> 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 als

by 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.

by dheera 3y ago

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.