Re: JSON output

On 13/09/10 10:36, Dennis Gearon wrote:
> I'm trying to import from a postgres database (which will work in parallel) to a ElasticSearch databse (JSON input).
>
> Is there anyway to get JSON output from postgres?

Not out of the box.

The closest you'll get, AFAIK, is XML output from the SQLXML functions.

http://www.postgresql.org/docs/current/static/datatype-xml.html
http://www.postgresql.org/docs/current/static/functions-xml.html

In particular, table_to_xml, query_to_xml or cursor_to_xml will probably
be helpful. Once you have the XML, you can use any existing tool for an
XML-to-JSON transform, possibly in a plpython/plperl stored procedure
within PostgreSQL its self.



classads=> select * FROM table_to_xml( 'ad_status'::regclass, true,
false, '');

                      table_to_xml
-------------------------------------------------------------------
 <ad_status xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

 <row>
   <id>0</id>
   <name>OK</name>
 </row>

 <row>
   <id>1</id>
   <name>HELD</name>
 </row>

 <row>
   <id>2</id>
   <name>CANCELLED</name>
 </row>

 </ad_status>






If you need something more complex than table contents or the end result
of a query, you'll have to roll your own. There isn't anything to do
ORM-like extraction of join results into nested sets if you need that.

--
Craig Ringer

Tech-related writing: http://soapyfrogs.blogspot.com/