4 ms·

if you really need indexes, extract the fields from your json and keep them as additional columns in the table. Key value stores have always had problematic ind

by viggity 11y ago

if you really need indexes, extract the fields from your json and keep them as additional columns in the table. Key value stores have always had problematic indexing on anything that wasn't the key. RDBMS does indexing really well, lean on that side for what it is good for.

rosser 11y ago
Postgres perfectly supports indexing attributes within JSON fields, whether stored as the native JSON type, or using the hstore extension. I just created a couple of indexes on our DML audit logging archive table this afternoon, which stores old and new row state in hstore fields.
est 11y ago
> extract the fields from your json and keep them as additional columns in the table You can do that only for simplest key-value json, not the more complicated ones IRL. E.g. an array ID, book_title, book_intro, book_tags here book_tags is a json array. Now try index that! Mongodb could index it, full text could index it, pg 9.4+ could, but not other RDBMS. Support nested data structure in RDBMS is hard. You have to implement flat/unflat voodoo in a weird & lame, non-SQL DSL
- raphaelj 11y ago
  > here book_tags is a json array. Now try index that! You just need to create a new table which links tags to books. That's how relational databases are normalized ...
  - est 11y ago
    Expanding a billion row table into a hundreds of billions of rows table is not really a good idea MongoDB handled this really well, create two index entries pointing to the same row.
    - blumkvist 11y ago
      w00t? A billion attributes on hundreds of billions of rows? Could you think more of an edge case? You're just coming up with shit so you can show off how l33t you are. If you have that kind of a need, just go for a NoSQL server, nobody is stopping you. This feature is a nicety to have, not a replacement of NoSQL.
- ahachete 11y ago
  You could also do it in pg pre 9.4. Just create functional indexes on the paths you wanted to index. The advantage of 9.4 is special indexes for jsonb, which may index the whole field or just some paths. So even pg 9.2 was better than SQLServer's approach...
mattmanser 11y ago
"if you really need" = do loads of work you can't afford to do "it just works!" = means you will actually use it Your post is one of those that is both technically correct and practically useless.

by viggity 11y ago

rosser 11y ago

Postgres perfectly supports indexing attributes within JSON fields, whether stored as the native JSON type, or using the hstore extension. I just created a couple of indexes on our DML audit logging archive table this afternoon, which stores old and new row state in hstore fields.

est 11y ago

> extract the fields from your json and keep them as additional columns in the table You can do that only for simplest key-value json, not the more complicated ones IRL. E.g. an array ID, book_title, book_intro, book_tags here book_tags is a json array. Now try index that! Mongodb could index it, full text could index it, pg 9.4+ could, but not other RDBMS. Support nested data structure in RDBMS is hard. You have to implement flat/unflat voodoo in a weird & lame, non-SQL DSL

raphaelj 11y ago
> here book_tags is a json array. Now try index that! You just need to create a new table which links tags to books. That's how relational databases are normalized ...
- est 11y ago
  Expanding a billion row table into a hundreds of billions of rows table is not really a good idea MongoDB handled this really well, create two index entries pointing to the same row.
  - blumkvist 11y ago
    w00t? A billion attributes on hundreds of billions of rows? Could you think more of an edge case? You're just coming up with shit so you can show off how l33t you are. If you have that kind of a need, just go for a NoSQL server, nobody is stopping you. This feature is a nicety to have, not a replacement of NoSQL.
ahachete 11y ago
You could also do it in pg pre 9.4. Just create functional indexes on the paths you wanted to index. The advantage of 9.4 is special indexes for jsonb, which may index the whole field or just some paths. So even pg 9.2 was better than SQLServer's approach...

mattmanser 11y ago

"if you really need" = do loads of work you can't afford to do "it just works!" = means you will actually use it Your post is one of those that is both technically correct and practically useless.