PostgreSQL 11: coalesce() on row_to_json() still returns null

Question

In PostgreSQL 11, I’d like to return a row in JSON format. But unfortunately, COALESCE(row_to_json(t), '{}'::json) still returns null:

CREATE OR REPLACE FUNCTION myschema.get_log(log_id int) RETURNS text AS $$
DECLARE
    result text;
BEGIN
    SELECT COALESCE(row_to_json(t), '{}'::json)
    INTO result
    FROM (SELECT id, foo, bar FROM myschema.mytable WHERE id = log_id) t;
    RETURN result;
END;
$$ LANGUAGE plpgsql;

How can I return an empty JSON object if there is no row?

For the result variable to be empty, the inner query you are running likely returned no results. You cannot COALESCE across zero rows. What you can probably do, however, is change your return statement to RETURN COALESCE(result, '{}'::json). — Chris Hep
– Chris Hep, Commented Mar 23, 2020 at 16:45

Gordon Linoff · Accepted Answer · 2020-03-23 16:48:27Z

2

The problem is that the query returns no results, not that the result is NULL. One method is to aggregate the results -- because you can aggregate json -- and take the first value:

SELECT COALESCE( (array_agg(row_to_json(t)))[1], '{}'::json)
INTO result
FROM (SELECT id, foo, bar FROM myschema.mytable WHERE id = log_id) t;

RETURN result;

Alternatively, you could you conditional logic:

if not exists () then use '{}'::json
else use your query

answered Mar 23, 2020 at 16:48

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL 11: coalesce() on row_to_json() still returns null

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related