How to save and return JavaScript Object with Subarray in Normalized SQL

Question

I am using postgres-node, but I think this is a problem for anyone with javascript objects that have subarrays they want to save in SQL. I have a javascript objects with a varying amount (any-length) array of features:

{ 
  name: "Ted",
  features: ['Red Hair', 'Blue Eyes']
}

so when I have several of them, the javascript formats it like:

[
  { 
    name: "Ted",
    features: ['Red Hair', 'Blue Eyes']
  },
  { 
    name: "Ann",
    features: ['Brown Hair', 'Blue Eyes', 'Big Smile']
  }
]

Which is great! But how do I get this back from the database after normalizing? I have normalized this in my database like this:

people Table

+---+------------+
|id | Name       |
+---+------------+
| 1 | Ted        |
| 2 | Ann        |
+---+------------+

features table

+---+--------------+
|id | feature_name |
+---+--------------+
| 1 | Red Hair     |
| 2 | Blue Eyes    |
| 3 | Brown Hair   |
| 4 | Big Smile    |
+---+--------------+

and people_features junction table

+---+-----------+-------------+
|id | person_id | feature_id  |
+---+-----------+-------------+
| 1 | 1         | 1           |
| 2 | 1         | 2           |
| 3 | 2         | 2           |
| 4 | 2         | 3           |
| 5 | 2         | 4           |
+---+-----------+-------------+

If I do a join like this:

SELECT name, feature_name
FROM people
JOIN people_features ON people_features.person_id=people.id
JOIN features ON people_features.feature_id=features.id;

I get one row for every single person. Which isn't what I want.

What I get:

[
  { 
    name: "Ted",
    feature_name: 'Red Hair'
  },
  { 
    name: "Ted",
    feature_name: 'Blue Eyes'
  },
  { 
    name: "Ann",
    feature_name: 'Blue Eyes'
  },
  { 
    name: "Ann",
    feature_name: 'Brown Hair'
  },
  { 
    name: "Ann",
    feature_name: 'Big Smile'
  }
]

What I want:

[
  { 
    name: "Ted",
    features: ['Red Hair', 'Blue Eyes']
  },
  { 
    name: "Ann",
    features: ['Brown Hair', 'Blue Eyes', 'Big Smile']
  }
]

This seems awful! Now I need to loop through these and combine the identical people into a single person object. My other option seems to be making a request for the people

SELECT id, name
FROM people;

Which would return:

[
  { 
    id: 1
    name: "Ted"
  },
  { 
    id: 2
    name: "Ann"
  }
]

And then I need to loop through and make a separate SQL query for every single person?

For each person:

SELECT feature_name
FROM features
JOIN people_features ON features.id=people_features.feature_id
WHERE people_features.person_id = $1

($1 is the person's id that I am looping through)

And then I would get back (for Ted):

[
  { feature_name: 'Red Hair' },
  { feature_name: 'Blue Eyes' }
]

Then I need to remove these from their objects (to just get the string) and then add them to the object.

Is one of these the best way to do it? I feel like they are both really inefficient.

Answer 1

This should do it:

SELECT name, array_agg(feature_name)
FROM people
JOIN people_features ON people_features.person_id=people.id
JOIN features ON people_features.feature_id=features.id
GROUP BY people.id;