Call to_json on multiple columns using Postgres

Question

Say I have the following table schema in Postgres:

CREATE TABLE users (id text, email text, phone_number text);

And I, for whatever reason, want to select the email and the phone number as JSON:

SELECT to_json(users.email, users.phone_number) AS user FROM users WHERE id=usr_123;

I get an error that looks like this:

function to_json(text, text) does not exist 

No function matches the given name and argument types. You might need to add explicit type casts.

But this works just fine:

SELECT to_json(users.*) AS user FROM users WHERE id=usr_123;

How can I select just a few columns (not all of them) using a to_json call in Postgres?

8 years later, but the accepted answer over here actually worked, and seems to have been 1 year earlier than the question ;) dba.stackexchange.com/questions/27732/… — RoundSparrow hilltx
– RoundSparrow hilltx, Commented Aug 31, 2023 at 21:10

Community · Accepted Answer · 2017-05-23 11:59:17Z

The fine manual says that to_json's signature is:

to_json(anyelement)

so you're supposed say to_json(one_single_value). When you say:

to_json(users.email, users.phone_number)

you're trying to call to_json with two values and there is no such to_json function. When you say:

to_json(user.*)

you're actually calling to_json with a single ROW argument so it works just fine.

You can use a derived table or CTE as klin suggests or you can build the ROWs by hand:

select to_json(row(users.email, users.phone_number)) ...

The problem with this is that that ROW won't have any column names so your JSON will use useless keys like "f1" and "f2". To get around that you need to cast the ROW to something that does have names. One way to get some names is to create a custom type:

create type email_and_phone_number as (
    email text,
    phone_number text
)

and then cast your ROW to that type:

select to_json(row(users.email, users.phone_number)::email_and_phone_number) ...

You could also use a temporary table instead of a custom type:

create temporary table email_and_phone_number (
    email text,
    phone_number text
)

and then use the same cast as with a custom type.

If you're building this specific JSON format a lot then a custom type would make sense. If this is a one-off then a temporary table would make sense, the temporary table will automatically disappear when the session ends so there's nothing to clean up. Of course, a derived table or CTE might also make sense depending on the query and what tools you're using to interface with your database.

klin · Accepted Answer · 2015-09-20 00:03:37Z

4

Use a subquery, e.g.:

select to_json(sub)
from (
    select email, phone_number
    from users
    where id = 'usr_123'
    ) sub;

or with query:

with cte as (
    select email, phone_number
    from users
    where id = 'usr_123')
select to_json(cte)
from cte;

edited Sep 20, 2015 at 0:03

answered Sep 19, 2015 at 23:32

klin

123k15 gold badges240 silver badges262 bronze badges

Comments

ivan-capk-me · Accepted Answer · 2023-02-01 18:43:06Z

0

If you just want to remove one specific field from the result there is an elegant solution:

SELECT to_json(users)::jsonb - 'id' AS user FROM users WHERE id=usr_123;

answered Feb 1, 2023 at 18:43

ivan-capk-me

3113 silver badges10 bronze badges

Collectives™ on Stack Overflow

Call to_json on multiple columns using Postgres

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related