4 ms·

Lots of nice stuff in here. SELECT t.j.foo.bar[2], ... FROM tbl t ... I'm going to miss having this for years, probably.

by mastax 4y ago

paulddraper 4y ago
Or.....you could just use PostgreSQL 14+ and have it today :)
- deleted 4y ago
  [deleted]
- masklinn 4y ago
  I don’t think postgres has that tho? It has support for SQL2016’s jsonpath, but those are string queries, not sql-level traversal.
  - k_bx 4y ago
    Not sure if Postgres has it, but last time I was using TimescaleDB I've written my own functions in Rust and it wasn't hard at all. I've heard it became even easier recently with PL/Rust https://news.ycombinator.com/item?id=35501065 https://news.ycombinator.com/item?id=35501065
    - oblio 4y ago
      "Not sure if Postures has it, but for a totally unrelated database I wrote my own implementation in a famously finicky, but powerful language."
      - masklinn 4y ago
        Made worse by “it” specifically not being a function, that’s the entire point. It’s already available behind a function and a stringly-typed path.
  - pdw 4y ago
    It has it, but the syntax is different: SELECT t->'j'->'foo'->'bar'->2, ... FROM tbl t ... I think SQLite now also supports this syntax.
    - masklinn 4y ago
      I would not call “has similar features with a completely incompatible syntax” “having it”. After all if that’s your benchmark every database with a TEXT type already has T081.
  - paulddraper 4y ago
    Sort of. The syntax is different, but close. SQL 2023: SELECT a.key PostgreSQL 9.3: SELECT a->'key' PostgreSQL 14: SELECT a['key']
    - Shorn 4y ago
      I've always disliked the postgres JSON syntax. Don't get me wrong - I love the feature and use it a lot, but I find the syntax awkward and honestly just... strange (though I've not researched it and have no idea of the reasoning behind it). Will postgres adopt the SQL standard for JSON "simplified accessor" syntax? Has there been any statements or communications from the development team?
      - masklinn 4y ago
        > Don't get me wrong - I love the feature and use it a lot, but I find the syntax awkward and honestly just... strange (though I've not researched it and have no idea of the reasoning behind it). I'm pretty sure the syntax was just reused from hstore (https://www.postgresql.org/docs/current/hstore.html https://www.postgresql.org/docs/current/hstore.html), for familiarity and simplicity. hstore predates json by 4-5 years. It might also have made expressions easier to parse, as it doesn't conflict with normal identifier access.
superdupershant 4y ago
Spark has something similar but you need to use a colon to as the first member. So something like: t.j:foo.bar Hopefully all the different dialects can adopt this syntax. https://docs.databricks.com/optimizations/semi-structured.html https://docs.databricks.com/optimizations/semi-structured.ht...

Lots of nice stuff in here. SELECT t.j.foo.bar[2], ... FROM tbl t ... I'm going to miss having this for years, probably.

by mastax 4y ago

Lots of nice stuff in here. SELECT t.j.foo.bar[2], ... FROM tbl t ... I'm going to miss having this for years, probably.

paulddraper 4y ago

Or.....you could just use PostgreSQL 14+ and have it today :)

deleted 4y ago
[deleted]
masklinn 4y ago
I don’t think postgres has that tho? It has support for SQL2016’s jsonpath, but those are string queries, not sql-level traversal.
- k_bx 4y ago
  Not sure if Postgres has it, but last time I was using TimescaleDB I've written my own functions in Rust and it wasn't hard at all. I've heard it became even easier recently with PL/Rust https://news.ycombinator.com/item?id=35501065 https://news.ycombinator.com/item?id=35501065
  - oblio 4y ago
    "Not sure if Postures has it, but for a totally unrelated database I wrote my own implementation in a famously finicky, but powerful language."
    - masklinn 4y ago
      Made worse by “it” specifically not being a function, that’s the entire point. It’s already available behind a function and a stringly-typed path.
- pdw 4y ago
  It has it, but the syntax is different: SELECT t->'j'->'foo'->'bar'->2, ... FROM tbl t ... I think SQLite now also supports this syntax.
  - masklinn 4y ago
    I would not call “has similar features with a completely incompatible syntax” “having it”. After all if that’s your benchmark every database with a TEXT type already has T081.
- paulddraper 4y ago
  Sort of. The syntax is different, but close. SQL 2023: SELECT a.key PostgreSQL 9.3: SELECT a->'key' PostgreSQL 14: SELECT a['key']
  - Shorn 4y ago
    I've always disliked the postgres JSON syntax. Don't get me wrong - I love the feature and use it a lot, but I find the syntax awkward and honestly just... strange (though I've not researched it and have no idea of the reasoning behind it). Will postgres adopt the SQL standard for JSON "simplified accessor" syntax? Has there been any statements or communications from the development team?
    - masklinn 4y ago
      > Don't get me wrong - I love the feature and use it a lot, but I find the syntax awkward and honestly just... strange (though I've not researched it and have no idea of the reasoning behind it). I'm pretty sure the syntax was just reused from hstore (https://www.postgresql.org/docs/current/hstore.html https://www.postgresql.org/docs/current/hstore.html), for familiarity and simplicity. hstore predates json by 4-5 years. It might also have made expressions easier to parse, as it doesn't conflict with normal identifier access.

superdupershant 4y ago

Spark has something similar but you need to use a colon to as the first member. So something like: t.j:foo.bar Hopefully all the different dialects can adopt this syntax. https://docs.databricks.com/optimizations/semi-structured.html https://docs.databricks.com/optimizations/semi-structured.ht...