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

I can do




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


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?

   (select row_to_json(_) from (select c.first_name, c.last_name) as _) as first_last,
   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
  • 4
    This answer is a gem. – tiffon Mar 22 '16 at 3:34
  • Thanks a lot you saved my afternoon, too bad this is not a cited example in PostgreSQL API. I knew it was possible – jlandercy Apr 28 '16 at 13:31

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);
  • 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
  • Is there a syntax for getting the column aliases dynamically? I am pulling from an EAV (entity attribute value) schema where the desired key names are also selected from the attribute.name column. – Chris Mar 1 '15 at 18:42
  • @Chris You'll need the more sophisticated json functions in 9.4. – Craig Ringer Mar 1 '15 at 21:39

You can use json_build_object.

  json_build_object('id', data.customer_id, 'first_name', data.first_name, 'last_name', data.last_name) as your_json
FROM data;
  • Thanks brother! The best soluction :D – karanalpe Aug 12 '20 at 14:40

You can do something like this:

  SELECT 'bla' AS name1, 'otherbla' AS name2
SELECT row_to_json(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.

  • To concatenate non-jsonb with jsonb :: SELECT row_to_json(r.*) FROM (SELECT c1,c2 ::jsonb FROM us_ca_monterey_aoc.test) as r – Andrew Scott Evans May 25 '16 at 17:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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