patternsqlMajor
Set names to attributes when creating JSON with row_to_json
Viewed 0 times
creatingwithnamesattributesrow_to_jsonwhenjsonset
Problem
Is it possible to rename default
I can do
returning
But if I want only names without
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
but isn't it possible to do it right in the query without creating that type?
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 userow_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_typebut isn't it possible to do it right in the query without creating that type?
Solution
The query
will do what you want without any performance impact (and is not too verbose):
select
c.id,
(select row_to_json(_) from (select c.first_name, c.last_name) as _) as first_last,
c.age
from
customers as cwill do what you want without any performance impact (and is not too verbose):
id | first_last | age
------+---------------------------------------------+---------
1 | {"first_name": "John", "last_name": "Smit"} | 34Code 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 cid | first_last | age
------+---------------------------------------------+---------
1 | {"first_name": "John", "last_name": "Smit"} | 34Context
StackExchange Database Administrators Q#27732, answer score: 47
Revisions (0)
No revisions yet.