My first reaction to this is - "Ugh. Haven't we been down this road with XML?" I don't want the format du jour baked into my datastore. Now, if it had a field that stored some sort of abstract lists-and-hashes structure (sort of redis-in-a-field), that sounds more interesting to me. I realize JSON is isomorphic to just that, but it still seems like flavor of the week.
hstore is all kinds of awesome, but one limitation is that the values are pure text. You can nest an hstore inside another, but only if you're smart enough to know that it SHOULD be treated as an hstore; as far as Postgres is concerned, it's text that happens to contain '=>'.
If JSON support means first-class type support in a nested object, that's a huge leap forward.
Database-generated XML offers an excellent abstraction layer, allowing the business language to vary independently of the data format. (Swapping Java for C would require rewriting the entire XML construction code.) Coupling it to the database makes sense: swap rows for XML content. For example:
SELECT xml_user_roster( 1 );
Given the extensive use of JSON in jQuery (Ajax) web applications, integrating JSON with PostgreSQL is smart. It allows the application code to vary independently of the business code that relies on JSON as a data interchange format.
That said, it would be great if JSON and XML were pluggable modules (similar to PL/R) that could be installed when needed.
Pluggable modules would be fine. But surely rewriting marshaling code is a relatively small part of swapping out your application code... And what if you can't or don't want to work with the particular flavor of XML it generates? JSON is better in that regard, I suppose, since there are fewer options to begin with, but you still have to deal with things like date/time formats.
The XML is generated manually through statements such as:
SELECT
xmlroot(
xmlelement( name root,
xmlelement( name description,
xmlelement( name title, table.title ),
xmlelement( name diet, table.diet ) ), etc.
If the format of the XML was out of the developer's control, XSL is a relatively easy way to convert XML from one format to another. If XSL won't handle it, then any number of ETL tools would be more than sufficient. Unless you mean something else by "flavour of XML"?
To get the same document using a language external to the database requires the following steps:
1. Write the SQL statement (a stored procedure, view, or string).
2. Instantiate an XML document library (e.g., PHP's DOM).
3. Iterate over the result set(s).
4. Build the XML document from the results.
Note that the first step is always required in both situations (whether the query is internal or external to the database).
Steps 2 to 4 effectively echo the first step: they tightly couple the XML format to the expected result set(s) from the database query. There is no abstraction to the application, there is no visible gain.
Also, with XML you can add meta information to the element: <date format="dd-MMM-yyyy">02-FEB-2012</date>. In PostgreSQL, you would use the xmlattribute function.
Comments
My first reaction to this is - "Ugh. Haven't we been down this road with XML?" I don't want the format du jour baked into my datastore. Now, if it had a field that stored some sort of abstract lists-and-hashes structure (sort of redis-in-a-field), that sounds more interesting to me. I realize JSON is isomorphic to just that, but it still seems like flavor of the week.
Look at PostgreSQL's hstore data type - that's pretty much what you're asking for.
hstore is all kinds of awesome, but one limitation is that the values are pure text. You can nest an hstore inside another, but only if you're smart enough to know that it SHOULD be treated as an hstore; as far as Postgres is concerned, it's text that happens to contain '=>'.
If JSON support means first-class type support in a nested object, that's a huge leap forward.
Database-generated XML offers an excellent abstraction layer, allowing the business language to vary independently of the data format. (Swapping Java for C would require rewriting the entire XML construction code.) Coupling it to the database makes sense: swap rows for XML content. For example:
Given the extensive use of JSON in jQuery (Ajax) web applications, integrating JSON with PostgreSQL is smart. It allows the application code to vary independently of the business code that relies on JSON as a data interchange format.That said, it would be great if JSON and XML were pluggable modules (similar to PL/R) that could be installed when needed.
Pluggable modules would be fine. But surely rewriting marshaling code is a relatively small part of swapping out your application code... And what if you can't or don't want to work with the particular flavor of XML it generates? JSON is better in that regard, I suppose, since there are fewer options to begin with, but you still have to deal with things like date/time formats.
The XML is generated manually through statements such as:
If the format of the XML was out of the developer's control, XSL is a relatively easy way to convert XML from one format to another. If XSL won't handle it, then any number of ETL tools would be more than sufficient. Unless you mean something else by "flavour of XML"?To get the same document using a language external to the database requires the following steps:
Note that the first step is always required in both situations (whether the query is internal or external to the database).Steps 2 to 4 effectively echo the first step: they tightly couple the XML format to the expected result set(s) from the database query. There is no abstraction to the application, there is no visible gain.
Also, with XML you can add meta information to the element: <date format="dd-MMM-yyyy">02-FEB-2012</date>. In PostgreSQL, you would use the xmlattribute function.