Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is it possible to rename default f1, f2, f3... names when using row_to_json function for only some columns?

I can do

row_to_json(customers)

returning

{"id_customer":2,"first_name":"bla","last_name":"second_bla"}

But if I want only names without id_customer, I have to use

row_to_json(row(first_name, last_name))

and then I get

{"f1":"bla","f2":"second_bla"}

And I would like to get this result with either default column names or my own. I know I can create my own composite type and use

row_to_json(row(first_name, last_name))::my_custom_type

but isn't it possible to do it right in the query without creating that type?

share|improve this question
1  
Also, see: reference 1 and reference 2 for similar –  M.Mugge May 27 at 21:57

3 Answers 3

up vote 6 down vote accepted

A common table expression allows you to specify aliases explicitly, not only for the CTE but for its columns.

WITH data(col1,col2,cola,colb) AS (
  VALUES (1,2,'fred','bob')
)
SELECT row_to_json(data) FROM data;

This is different to @dezso's example in that it doesn't use col AS alias for each col in a SELECT list; it aliases the column names in the CTE table alias.

I've used a VALUES expression as a subquery but you can use a SELECT whatever you like; the point is that whatever column-aliases are provided or assumed in the subquery can be overridden in the CTE definition by specifying a column-name-list.

You can do the same thing in a subquery, again instead of using AS alias:

SELECT row_to_json(data) 
FROM (VALUES (1,2,'fred','bob')) data(col1,col2,cola,colb);

This doesn't work with a ROW expression directly; you can only cast a ROW to a concrete type, you cannot alias it.

regress=> SELECT ROW(1,2,'fred','bob') AS x(a,b,c,d);
ERROR:  syntax error at or near "("
LINE 1: SELECT ROW(1,2,'fred','bob') AS x(a,b,c,d);
share|improve this answer
    
Is there any difference (apart from style and/or readability) between our solutions (usage, performance, etc.)? –  dezso Oct 27 '12 at 17:43
    
@dezso No, and I probably should've just posted a comment. Sorry. –  Craig Ringer Oct 28 '12 at 0:28
    
I think this is OK. I even upvoted your answer b/c it contains useful information which mine does not. –  dezso Oct 28 '12 at 8:08

You can do something like this:

WITH r AS (
  SELECT 'bla' AS name1, 'otherbla' AS name2
)
SELECT row_to_json(r.*)
FROM r
;

(Of course, the same can be achieved with

SELECT row_to_json(r.*)
FROM (SELECT 'bla' AS name1, 'otherbla' AS name2) r
;

but I found the former more readable.)

In the WITH part you can construct rows of any structure on the fly.

share|improve this answer
select 
   c.id,
   (select row_to_json(_) from (select c.first_name, c.last_name) as _) as first_last,
   c.age
from
   customers as c

will do what you want without any performance impact (and is not too verbose):

  id  |   first_last                                |   age
------+---------------------------------------------+---------
  1   | {"fisrt_name": "John", "last_name": "Smit"} |   34
share|improve this answer
    
This is very impressive. Allows you to get the exact JSON you want as well as the key to join with. Thanks. –  toxaq Oct 18 at 11:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.