Create an empty array in an SQL query using PostgreSQL instead of an array with NULL inside

Question

I am using the following schema:

CREATE TABLE person (
  person_name VARCHAR PRIMARY KEY
);

CREATE TABLE pet (
  animal_name VARCHAR,
  person_name VARCHAR REFERENCES person(person_name),
  PRIMARY KEY (animal_name, person_name)
);

I wish to create a table where, for each person_name, I get an array with the pets of that person. I am using PostgreSQL 9.3.4.

I have the following values in each table:

Person

PERSON_NAME
-----------
Alice
Bob

Pet

ANIMAL_NAME | PERSON_NAME
-------------------------
Woof        | Alice
Meow        | Alice

I wish to create the following table:

PERSON_NAME | PETS
--------------------------
Alice       | {Woof, Meow}
Bob         | {}

I cannot, however, create the empty array. What I get is the following:

PERSON_NAME | PETS
--------------------------
Alice       | {Woof, Meow}
Bob         | {NULL}

This is the query I am using:

SELECT
  person.person_name,
  array_agg(pet.animal_name) AS pets
FROM
  person LEFT JOIN pet ON person.person_name = pet.person_name
GROUP BY
  person.person_name;

I understand why I am getting the array with the NULL value inside, I want to know how to get an empty array instead.

Here is a fiddle with the code needed to create the schema, insert the values and with the query I am using. The result shown in the website doesn't show the NULL value, although it is there.

EDIT

The result will be parsed to JSON, that is why {NULL} is not an acceptable result, as it will be parsed to [null], which is different from the [] I require. For the same reason, something like {""} is not an acceptable result either.

Answer 1

The most simple way of doing this, is to use the ARRAY constructor's sub-query variant:

SELECT
  person.person_name,
  ARRAY(SELECT animal_name FROM pet WHERE person.person_name = pet.person_name) AS pets
FROM
  person;

SQLFiddle