Thread: JSON output

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?

(googled, found only 'planner' output does this)
Dennis Gearon

Signature Warning
----------------
EARTH has a Right To Life,
  otherwise we all die.

Read 'Hot, Flat, and Crowded'
Laugh at http://www.yert.com/film.php

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/

No, don't need all the set stuff, just the result of either:
  1/ A SELECT * from a view that is a query,
  2/ Or the result of the same query.

Seems like it'd take a lot of horsepower to:
  A/ Do the query
  B/ Convert it to XML
  C/ Convert it to JSON
  D/ Then insert it into ElasticSearch.

I'm either going to use C++/Perl/PHP/JAVA doing a simple query to a result array, then convert it and send it to
ElasticSearch(via a http socket) 

I'll start with PHP.

Probably going to have to use a cursor (one more thing to learn) as eventually, I'll have 'billions and billlions' of
records,Mwah Ha Ha <evil grin accompaniment /> 


Dennis Gearon

Signature Warning
----------------
EARTH has a Right To Life,
  otherwise we all die.

Read 'Hot, Flat, and Crowded'
Laugh at http://www.yert.com/film.php


--- On Sun, 9/12/10, Craig Ringer <craig@postnewspapers.com.au> wrote:

> From: Craig Ringer <craig@postnewspapers.com.au>
> Subject: Re: [GENERAL] JSON output
> To: "Dennis Gearon" <gearond@sbcglobal.net>, "PG-General Mailing List" <pgsql-general@postgresql.org>
> Date: Sunday, September 12, 2010, 8:57 PM
> 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/
>