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?

• Michael M: Also, see: reference 1 and reference 2 for similar

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.

• Andrew Scott Evans: To concatenate non-jsonb with jsonb :: SELECT row_to_json(r.*) FROM (SELECT c1,c2 ::jsonb FROM us_ca_monterey_aoc.test) as r

Answer 5

Here is a variant of including field names in the resulting json with a nested json structure

select row_to_json(for2tables_json) from (
 SELECT 
 json_agg(row_to_json(table1_json)) as table1,
 json_agg(row_to_json(table2_json)) as table2
 FROM 
 (select row_to_json(table1_data) as data, row_to_json(table1_info) as info from 
 (SELECT 1 as col1, 
 'x1' as col2, 
 numeric '1.1' as col3) table1_data,
 (SELECT '' as state,'' as guid) as table1_info 
 ) table1_json,
 (select row_to_json(table2_data) as data, row_to_json(table2_info) as info from 
 (SELECT 2 as col1, 
 'x2' as col2, 
 numeric '2.2' as col3) table2_data,
 (SELECT '' as state,'' as guid) as table2_info 
 ) table2_json 
 ) for2tables_json

Answer 6

As simple as below:

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

• Michael Green: How is this different to existing answers? You can edit the answer to explain.
• Rohil Patel: @michael-green In existing answers, they have suggested using CTE (common table expression) whereas I have proposed my way directly to achieve without CTE.
• Michael Green: Well .. yes, you did. Given the question is about constructing JSON of a certain structure I don't think moving a subquery from one construction to another is a substantive change. But fair enough.