HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMajor

Set names to attributes when creating JSON with row_to_json

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
creatingwithnamesattributesrow_to_jsonwhenjsonset

Problem

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?

Solution

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

Code Snippets

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
id  |   first_last                                |   age
------+---------------------------------------------+---------
  1   | {"first_name": "John", "last_name": "Smit"} |   34

Context

StackExchange Database Administrators Q#27732, answer score: 47

Revisions (0)

No revisions yet.