Set names to attributes when creating JSON with row_to_json

Question

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?

Answer 1

The query

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   | {"first_name": "John", "last_name": "Smit"} |   34

Answer 2

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);

Answer 3

You can use json_build_object.

SELECT 
  json_build_object('id', data.customer_id, 'first_name', data.first_name, 'last_name', data.last_name) as your_json
FROM data;

Answer 4

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.

Answer 5

As simple as below:

(select row_to_json(row) from (select 'rkp' as "xyz","Title","Name","Code","Description" from Table) row )