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.

Are you looking for something like this? - stackoverflow.com/questions/39805736/… — vitaly-t
– vitaly-t, Commented Apr 18, 2017 at 7:07
The answer from Vao Tsun was exactly what I was looking for. Thank you for sharing that though! — Luke Schlangen
– Luke Schlangen, Commented Apr 18, 2017 at 13:43

Luke Schlangen · Accepted Answer · 2017-04-17 20:23:33Z

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;

Luke Schlangen · Accepted Answer · 2017-04-17 20:40:16Z

Vao Tsun's answer is perfect. For those interested, here is the minimal version of what I ended up using in my node code to get it working with node-postgres:

var pg = require('pg'); var config = { user: process.env.PG_USER || null, //env var: PGUSER password: process.env.DATABASE_SECRET || null, //env var: PGPASSWORD host: process.env.DATABASE_SERVER || 'localhost', // Server hosting the postgres database port: process.env.DATABASE_PORT || 5432, //env var: PGPORT database: process.env.DATABASE_NAME || 'lukeschlangen', //env var: PGDATABASE max: 10, // max number of clients in the pool idleTimeoutMillis: 30000, // how long a client is allowed to remain idle before being closed }; var pool = new pg.Pool(config) pool.connect(function (err, client, done) { if (err) { console.log('There was an error', err); } else { client.query( '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;', function (err, results) { done(); console.log(results.rows); // This was exactly the array I wanted } ); } });

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related