4 ms·

Yeah, later on in the article it has an "X" for PostgreSQL for "JSON_OBJECTAGG" and "JSON_ARRAYAGG" and I'm 100% sure postgres has the former (I used it like 10

Yeah, later on in the article it has an "X" for PostgreSQL for "JSON_OBJECTAGG" and "JSON_ARRAYAGG" and I'm 100% sure postgres has the former (I used it like 10 minutes ago), and like 99% sure it has the latter.

MarkusWinand 9y ago
https://www.postgresql.org/search/?q=json_objectagg https://www.postgresql.org/search/?q=json_objectagg But there is a patch pending for this. Not sure if this gonna be included in 11.
- Klathmon 9y ago
  It's json_object_agg in PostgreSQL https://www.postgresql.org/docs/current/static/functions-aggregate.html https://www.postgresql.org/docs/current/static/functions-agg...
  - irishsultan 9y ago
    Given the name difference it's possible that the semantics are slightly different.
    - deleted 9y ago
      [deleted]
  - MarkusWinand 9y ago
    Those gory details I've mentioned in the article. This is how standard SQL JSON_OBJECTAGG works: https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016#json_objectagg https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016#js... It can actually do much more: <JSON object aggregate constructor> ::= JSON_OBJECTAGG <left paren> <JSON name and value> [ <JSON constructor null clause> ] [ <JSON key uniqueness constraint> ] [ <JSON output clause> ] <right paren> This free technical report of ISO has some examples: http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip http://standards.iso.org/ittf/PubliclyAvailableStandards/c06...
    - Klathmon 9y ago
      I haven't done any exact test, but it looks identical to how the json_object_agg function in postgres works, but with a different syntax. Is it really that different to not even warrant a note in the table? Clearly you know better than I do, but the big red X surprised me as the fantastic JSON support is one of the reasons why I tend to reach for Postgres so often over other DB engines.
      - MarkusWinand 9y ago
        A big red X doesn't mean the database doesn't have other, similar but proprietary functionality. It just means: it doesn't support this function in a standard confirming way yet. There is nothing against using non-standard functionality in that case. In this particular case. PostgreSQL got JSON support (~2012?) long before it was added to the standard (2016). Obviously, their JSON functions differ from the standard (as they didn't lobby their functions into the standard). However, slowly but surely database will add the standard functionality, which makes life easier. Having that said, the standard JSON_OBJECTAGG is more powerful as it gives you control over null handling (NULL ON NULL -vs- ABSENT ON NULL) and allows checking for duplicate keys ([(WITH|WITHOUT) UNIQUE KEYS]).
        masklinn 9y ago
        Maybe add some little footnotes to the X? It took me a few minutes of thinking "Of course postgres has json object aggregation here it's working perfectly and it's documented" before I realised that sure it existed but it's not the standard version, at all, which is probably why it's marked as a no, because it's not Troel's page and Markus really really cares about the standard.
- deleted 9y ago
  [deleted]
cpburns2009 9y ago
I noticed that too and PostgreSQL does have them. They're just named slightly differently: `json_object_agg()` and `json_agg()`.
masklinn 9y ago
The table is very strict, it requires that the actual standard be supported (possibly incompletely). Postgres has ways to aggregate into a json object or array, but they're non-standard. If you write standard SQL making use of those features, it will not work.

MarkusWinand 9y ago

https://www.postgresql.org/search/?q=json_objectagg https://www.postgresql.org/search/?q=json_objectagg But there is a patch pending for this. Not sure if this gonna be included in 11.

Klathmon 9y ago
It's json_object_agg in PostgreSQL https://www.postgresql.org/docs/current/static/functions-aggregate.html https://www.postgresql.org/docs/current/static/functions-agg...
- irishsultan 9y ago
  Given the name difference it's possible that the semantics are slightly different.
  - deleted 9y ago
    [deleted]
- MarkusWinand 9y ago
  Those gory details I've mentioned in the article. This is how standard SQL JSON_OBJECTAGG works: https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016#json_objectagg https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016#js... It can actually do much more: <JSON object aggregate constructor> ::= JSON_OBJECTAGG <left paren> <JSON name and value> [ <JSON constructor null clause> ] [ <JSON key uniqueness constraint> ] [ <JSON output clause> ] <right paren> This free technical report of ISO has some examples: http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip http://standards.iso.org/ittf/PubliclyAvailableStandards/c06...
  - Klathmon 9y ago
    I haven't done any exact test, but it looks identical to how the json_object_agg function in postgres works, but with a different syntax. Is it really that different to not even warrant a note in the table? Clearly you know better than I do, but the big red X surprised me as the fantastic JSON support is one of the reasons why I tend to reach for Postgres so often over other DB engines.
    - MarkusWinand 9y ago
      A big red X doesn't mean the database doesn't have other, similar but proprietary functionality. It just means: it doesn't support this function in a standard confirming way yet. There is nothing against using non-standard functionality in that case. In this particular case. PostgreSQL got JSON support (~2012?) long before it was added to the standard (2016). Obviously, their JSON functions differ from the standard (as they didn't lobby their functions into the standard). However, slowly but surely database will add the standard functionality, which makes life easier. Having that said, the standard JSON_OBJECTAGG is more powerful as it gives you control over null handling (NULL ON NULL -vs- ABSENT ON NULL) and allows checking for duplicate keys ([(WITH|WITHOUT) UNIQUE KEYS]).
      - masklinn 9y ago
        Maybe add some little footnotes to the X? It took me a few minutes of thinking "Of course postgres has json object aggregation here it's working perfectly and it's documented" before I realised that sure it existed but it's not the standard version, at all, which is probably why it's marked as a no, because it's not Troel's page and Markus really really cares about the standard.
deleted 9y ago
[deleted]

cpburns2009 9y ago

I noticed that too and PostgreSQL does have them. They're just named slightly differently: `json_object_agg()` and `json_agg()`.

masklinn 9y ago

The table is very strict, it requires that the actual standard be supported (possibly incompletely). Postgres has ways to aggregate into a json object or array, but they're non-standard. If you write standard SQL making use of those features, it will not work.