returning json data row from json query

john.tiger wrote

select * from users where jsondata->>'username' = 'jdoe' works but
returns field names, etc besides the data row. how can we get json
data back ?

select row_to_json from users where jsondata->>'username'='jdoe' didn't
work.

any ideas ? thks.

Using what client? There is no way, using SQL, to get a non-table output;
but your client might give you ways to ignore or suppress the column name.

And row_to_json is a function, you have to pass argument(s) to it to use it.
There is documentation explaining how to do so and what the end result will
look like.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/returning-json-data-row-from-json-query-tp5785293p5785303.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sat, Jan 4, 2014 at 1:44 AM, David Johnston <polobo@yahoo.com> wrote:

john.tiger wrote

select * from users where jsondata->>'username' = 'jdoe' works but
returns field names, etc besides the data row. how can we get json
data back ?

select row_to_json from users where jsondata->>'username'='jdoe' didn't
work.

any ideas ? thks.

Using what client? There is no way, using SQL, to get a non-table output;
but your client might give you ways to ignore or suppress the column name.

I'm guessing OP is asking about psql? Typically when getting 'just
the data', I'll do something like:

psql -XtAqc"select ..."

-X disables .psqlrc (so no \timing) and tAq basically turn off all
output except for the data itself and the field delimiter (if any).

And row_to_json is a function, you have to pass argument(s) to it to use it.
There is documentation explaining how to do so and what the end result will
look like.

Yeah -- for posterity,

select row_to_json(u) from users u where jsondata->>'username'='jdoe';

table-aliases-as-arguments isn't a common practice outside of postgresql...

merlin

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general