5 ms·

A silly, genuine question: when I'm writing a NodeJS app, and I need to store a JSON in a DB that looks like this: { "a" : "b", "c" : { "ca":"cb", "cd"

A silly, genuine question: when I'm writing a NodeJS app, and I need to store a JSON in a DB that looks like this: { "a" : "b", "c" : { "ca":"cb", "cd":"ce" }, "d": { ... } } How do I store it? To me, NoSQL seemed like the choice in the past. This is not difficult in relational DBs, but it requires rather long SQL commands, and SQL table design. It seems to me that "MongoDB.insert(obj)" seems like the way to go, as it is much simpler. How do experienced Node devs solve this issue? Is it simply relational all the way? Is it Postgress's JSON field..?

sandGorgon 8y ago
postgres has a jsonb datatype that works pretty much like mongodb. https://blog.codeship.com/unleash-the-power-of-storing-json-in-postgres/ https://blog.codeship.com/unleash-the-power-of-storing-json-...
- willvarfar 8y ago
  And other regular RDBMS like MariaDB and MySQL have much the same functionality. I store JSON in RDBMS all the time.
  - sandGorgon 8y ago
    interesting - i never knew that. I use postgresql, so dont have first hand knowledge, but i saw this https://tableplus.io/blog/2018/09/mariadb-vs-postgresql-a-quick-comparison.html https://tableplus.io/blog/2018/09/mariadb-vs-postgresql-a-qu... > JSON support: PostgreSQL supports JSON and JSONB while MariaDB doesn’t. It supports an alias for JSON instead, which is a LONGTEXT column. Even the proposed json column, is not indexed - postgresql jsonb is indexed and queries fast.
    - willvarfar 8y ago
      Yeah MariaDB got 'dynamic columns' first, which iirc supported key values but not arrays, and then MySQL added a more complete 'json' data type, so the exact functionality differs by flavour and version. The postgres support is simply nicer to use and more powerful, e.g. the indexing. But if your RDBMS is any fairly-recent version of MariaDB or MySQL, you can use JSON too. Of the top of my head, the thing I find most griping about the MySQL syntax is you have to do thing->>"$.key" instead of postgres-style thing->>'key' Whereas what I really want to be able to do is thing.key But most of the consumers of these modern 'dynamic columns' are apps and then it all really matters less if you don't spend all your days at the sql prompt writing stuff by hand.
      - philliphaydon 8y ago
        Didn't PostgreSQL get hstore before MariaDB got dynamic columns?
        willvarfar 8y ago
        Yeah I seem what I wrote could be ambiguous, but in that first para I was meaning to compare MariaDB and MySQL, not claim that MariaDB beat PostgreSQL.
    - johannes1234321 8y ago
      Ntoe that MySQL's `JSON` type is using an efficient binary storage scheme which allows fast lookups etc. https://dev.mysql.com/doc/refman/8.0/en/json.html https://dev.mysql.com/doc/refman/8.0/en/json.html My favorite MySQL JSON feature probably is JSON_TABLE which converts a JSON document on the fly into a table which can hen be processed using SQL features. https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html https://dev.mysql.com/doc/refman/8.0/en/json-table-functions... (Disclaimer: I work for Oracle's MySQL Engineering team)
- PurpleRamen 8y ago
  But will it perform as good as a dedicated nosql-db? Will the abilitys match? The querys? There usually is a tradeoff when something is forced into something alien. Is this the case here too?
  - amarraja 8y ago
    > There usually is a tradeoff when something is forced into something alien. Exactly! My thoughts whenever I see people using nosql databases.
  - raphaelj 8y ago
    IIRC, Postgres use a key-value store internally, so it does not really matters if you store records (i.e. tables) or objects (i.e. JSON documents). There was an article on HN a couple of months ago that was comparing Mongo and PgSQL performance, and PgSQL ended up being as fast if not faster for most use cases.
    - anarazel 8y ago
      I don't see how you'd describe postgres validly as a key-value store at the storage level / internally. It's a pretty traditional row-store (without support for index oriented tables), i.e. all columns of a table are stored together. At the storage level there's no designation of what the key of a row is. Of course you can have primary keys, but that's a higher layer concern.
      - twic 8y ago
        Aren't tables a bit like key-value stores where the key is an object ID and the value is the column values? My understanding is very vague, but i think that came out of PostgreSQL's heritage as an "object-relational" database, even if it's vestigial today.
        taffer 8y ago
        Since 8.1 PostgreSQL does not work this way anymore. The oid type is currently implemented as an unsigned four-byte integer. Therefore, it is not large enough to provide database-wide uniqueness in large databases, or even in large individual tables. So, using a user-created table's OID column as a primary key is discouraged. OIDs are best used only for references to system tables.[1] Besides, "object-relational" only means that it supports custom data types and inheritance, and at least custom data types are still a core feature of PostgreSQL. [1] https://www.postgresql.org/docs/8.1/datatype-oid.html https://www.postgresql.org/docs/8.1/datatype-oid.html
pilif 8y ago
> How do I store it? To me, NoSQL seemed like the choice in the past. If you're using postgres, you could use a column of type jsonb. Postgres comes with many operators and functions (https://www.postgresql.org/docs/11/functions-json.html https://www.postgresql.org/docs/11/functions-json.html) to query into jsonb typed columns and many of them even allow index usage for very quick access. However, the other option would be to use proper SQL tables and normalization to store your data. Then you can query very easily for your data and by making use of a real typed schema, you get free validation of your input and protection against corrupted data later down the line. And aggregating your information to produce reports over various documents becomes very easy too. Here's above schema in SQL DDL, Postgres dialect. I'm not sure about nullability and types of your values, but by making use of database types and constraints, you can describe the shape of your data much better which further helps you preventing invalid data from being stored due to bugs create table things ( id bigserial primary key, a text not null ); create table c_things ( thing_id bigint not null primary key references things (id) on delete cascade, ca text not null, cd text not null ); create table d_things( thing_id bigint not null primary key references things (id) on delete cascade -- ... )
- philliphaydon 8y ago
  In .NET I use Marten to use PostgreSQL as a document database, gives you a typed schema with JSONB storage.
- ako 8y ago
  Or you can store the jsonb in Postgres and use views to also present it in a normalized way. See example here: https://www.endpoint.com/blog/2016/02/29/converting-json-to-postgresql-values https://www.endpoint.com/blog/2016/02/29/converting-json-to-...
patwolf 8y ago
You could do it in a JSON field, and I've personally done that but mostly out of laziness. I think in the end it's better to actually come up with a normalized schema. I typically reserve JSON fields for storing data whose schema I don't control. For example, when Stripe gives back a giant chunk of JSON after a transaction, I use a JSON field.
eftpotrm 8y ago
Commenting from a SQL Server perspective as it's what I know best - there's a native JSON data type, so you're absolutely able to store JSON in columns and interact with it as such. If you don't mind getting a bit more sophisticated..... Certainly with XML (the JSON tools weren't as mature as the XML ones, though they're hopefully improving) it isn't tricky to sling a document at the database and have it create a whole tree of very standard RDBMS-style table records. Nor is it that tricky to write code which, when given the ID of a record, returned it and its children as a document tree. Whether it's the right approach for you is another matter, but it's very doable.
janee 8y ago
I suppose the answer, like all db answers are..it depends :) My take on it is if the structure of "c" isn't likely to vary wildly over different entries and you have use cases that need queries like: get rows where c.ca == x and/or c.<some other field> == x ...and either the frequency or table size makes it unfeasible to scan through all entries, I'd opt for using a relational storage solution and chuck "c" into its own table with a foreign key entry in the original data structure. Otherwise a non-relational structure could suite it better, in which case you can still use Postgres or SQLServer or any other relational DBMS that supports json. I'd make the deciding factor in that case more about what do I forsee myself using for other use cases, e.g. if I'm already using a RDBMS for other models in the same app then just keep using it, but if not and all my data are arbitrary blobs of JSON then use any ol document store ¯\_(ツ)_/¯¯

sandGorgon 8y ago

postgres has a jsonb datatype that works pretty much like mongodb. https://blog.codeship.com/unleash-the-power-of-storing-json-in-postgres/ https://blog.codeship.com/unleash-the-power-of-storing-json-...

willvarfar 8y ago
And other regular RDBMS like MariaDB and MySQL have much the same functionality. I store JSON in RDBMS all the time.
- sandGorgon 8y ago
  interesting - i never knew that. I use postgresql, so dont have first hand knowledge, but i saw this https://tableplus.io/blog/2018/09/mariadb-vs-postgresql-a-quick-comparison.html https://tableplus.io/blog/2018/09/mariadb-vs-postgresql-a-qu... > JSON support: PostgreSQL supports JSON and JSONB while MariaDB doesn’t. It supports an alias for JSON instead, which is a LONGTEXT column. Even the proposed json column, is not indexed - postgresql jsonb is indexed and queries fast.
  - willvarfar 8y ago
    Yeah MariaDB got 'dynamic columns' first, which iirc supported key values but not arrays, and then MySQL added a more complete 'json' data type, so the exact functionality differs by flavour and version. The postgres support is simply nicer to use and more powerful, e.g. the indexing. But if your RDBMS is any fairly-recent version of MariaDB or MySQL, you can use JSON too. Of the top of my head, the thing I find most griping about the MySQL syntax is you have to do thing->>"$.key" instead of postgres-style thing->>'key' Whereas what I really want to be able to do is thing.key But most of the consumers of these modern 'dynamic columns' are apps and then it all really matters less if you don't spend all your days at the sql prompt writing stuff by hand.
    - philliphaydon 8y ago
      Didn't PostgreSQL get hstore before MariaDB got dynamic columns?
      - willvarfar 8y ago
        Yeah I seem what I wrote could be ambiguous, but in that first para I was meaning to compare MariaDB and MySQL, not claim that MariaDB beat PostgreSQL.
  - johannes1234321 8y ago
    Ntoe that MySQL's `JSON` type is using an efficient binary storage scheme which allows fast lookups etc. https://dev.mysql.com/doc/refman/8.0/en/json.html https://dev.mysql.com/doc/refman/8.0/en/json.html My favorite MySQL JSON feature probably is JSON_TABLE which converts a JSON document on the fly into a table which can hen be processed using SQL features. https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html https://dev.mysql.com/doc/refman/8.0/en/json-table-functions... (Disclaimer: I work for Oracle's MySQL Engineering team)
PurpleRamen 8y ago
But will it perform as good as a dedicated nosql-db? Will the abilitys match? The querys? There usually is a tradeoff when something is forced into something alien. Is this the case here too?
- amarraja 8y ago
  > There usually is a tradeoff when something is forced into something alien. Exactly! My thoughts whenever I see people using nosql databases.
- raphaelj 8y ago
  IIRC, Postgres use a key-value store internally, so it does not really matters if you store records (i.e. tables) or objects (i.e. JSON documents). There was an article on HN a couple of months ago that was comparing Mongo and PgSQL performance, and PgSQL ended up being as fast if not faster for most use cases.
  - anarazel 8y ago
    I don't see how you'd describe postgres validly as a key-value store at the storage level / internally. It's a pretty traditional row-store (without support for index oriented tables), i.e. all columns of a table are stored together. At the storage level there's no designation of what the key of a row is. Of course you can have primary keys, but that's a higher layer concern.
    - twic 8y ago
      Aren't tables a bit like key-value stores where the key is an object ID and the value is the column values? My understanding is very vague, but i think that came out of PostgreSQL's heritage as an "object-relational" database, even if it's vestigial today.
      - taffer 8y ago
        Since 8.1 PostgreSQL does not work this way anymore. The oid type is currently implemented as an unsigned four-byte integer. Therefore, it is not large enough to provide database-wide uniqueness in large databases, or even in large individual tables. So, using a user-created table's OID column as a primary key is discouraged. OIDs are best used only for references to system tables.[1] Besides, "object-relational" only means that it supports custom data types and inheritance, and at least custom data types are still a core feature of PostgreSQL. [1] https://www.postgresql.org/docs/8.1/datatype-oid.html https://www.postgresql.org/docs/8.1/datatype-oid.html

pilif 8y ago

> How do I store it? To me, NoSQL seemed like the choice in the past. If you're using postgres, you could use a column of type jsonb. Postgres comes with many operators and functions (https://www.postgresql.org/docs/11/functions-json.html https://www.postgresql.org/docs/11/functions-json.html) to query into jsonb typed columns and many of them even allow index usage for very quick access. However, the other option would be to use proper SQL tables and normalization to store your data. Then you can query very easily for your data and by making use of a real typed schema, you get free validation of your input and protection against corrupted data later down the line. And aggregating your information to produce reports over various documents becomes very easy too. Here's above schema in SQL DDL, Postgres dialect. I'm not sure about nullability and types of your values, but by making use of database types and constraints, you can describe the shape of your data much better which further helps you preventing invalid data from being stored due to bugs create table things ( id bigserial primary key, a text not null ); create table c_things ( thing_id bigint not null primary key references things (id) on delete cascade, ca text not null, cd text not null ); create table d_things( thing_id bigint not null primary key references things (id) on delete cascade -- ... )

philliphaydon 8y ago
In .NET I use Marten to use PostgreSQL as a document database, gives you a typed schema with JSONB storage.
ako 8y ago
Or you can store the jsonb in Postgres and use views to also present it in a normalized way. See example here: https://www.endpoint.com/blog/2016/02/29/converting-json-to-postgresql-values https://www.endpoint.com/blog/2016/02/29/converting-json-to-...

patwolf 8y ago

You could do it in a JSON field, and I've personally done that but mostly out of laziness. I think in the end it's better to actually come up with a normalized schema. I typically reserve JSON fields for storing data whose schema I don't control. For example, when Stripe gives back a giant chunk of JSON after a transaction, I use a JSON field.

eftpotrm 8y ago

Commenting from a SQL Server perspective as it's what I know best - there's a native JSON data type, so you're absolutely able to store JSON in columns and interact with it as such. If you don't mind getting a bit more sophisticated..... Certainly with XML (the JSON tools weren't as mature as the XML ones, though they're hopefully improving) it isn't tricky to sling a document at the database and have it create a whole tree of very standard RDBMS-style table records. Nor is it that tricky to write code which, when given the ID of a record, returned it and its children as a document tree. Whether it's the right approach for you is another matter, but it's very doable.

janee 8y ago

I suppose the answer, like all db answers are..it depends :) My take on it is if the structure of "c" isn't likely to vary wildly over different entries and you have use cases that need queries like: get rows where c.ca == x and/or c.<some other field> == x ...and either the frequency or table size makes it unfeasible to scan through all entries, I'd opt for using a relational storage solution and chuck "c" into its own table with a foreign key entry in the original data structure. Otherwise a non-relational structure could suite it better, in which case you can still use Postgres or SQLServer or any other relational DBMS that supports json. I'd make the deciding factor in that case more about what do I forsee myself using for other use cases, e.g. if I'm already using a RDBMS for other models in the same app then just keep using it, but if not and all my data are arbitrary blobs of JSON then use any ol document store ¯\_(ツ)_/¯¯