How to get rows from javascript array response?

Question

I have skills in one table, user_skills in other table and getting skills against id from skills table in for loop.I have stored query results in javascript array. I want a array with objects in it. I'm getting array in array structure. As you can see in image that i am getting multiple arrays in array.It should be objects in single array.

var userExist = await ctx.app.pool.query("SELECT * FROM USER_SKILLS WHERE user_id = $1",
            [`${user_id}`]);

        var strArray = userExist.rows[0].skill_array.split(",");

        var i;
        var skillsArray = [];
        for (i = 0; i < strArray.length; i++) {

          var findSkill = await ctx.app.pool.query("SELECT skill_name FROM ALL_SKILLS WHERE id = $1",
                [`${strArray[i]}`]);

            skillsArray.push(findSkill.rows);

        }
        console.log('skillsArray', skillsArray);

        ctx.body = {
            status: 200,
            error: false,
            message: "Skills found",
            data: skillsArray
        };

This can be easily performed in one query, but you'd need to give more info about the structure: what data type is skill_array? If it's an actual postgres array, will it not include the {} characters at the beginning/end? (Assuming it comes into pg as a string, else why would you be splitting it?) And more generally, you'll have to tell us exactly what you mean by "not working". Error? Not returning the expected results? — 404
– 404, Commented Jan 1, 2020 at 8:55
can you add your table schema here? this would help a lot in fixing your error also as mentioned by @404 this should be in single database query using join. — ROOT
– ROOT, Commented Jan 1, 2020 at 10:24

404 · Accepted Answer · 2020-01-01 13:13:09Z

Assuming skill_array is in fact an array in postgres, you can combine both those queries into one:

const query = "SELECT skill_name
               FROM ALL_SKILLS
               WHERE id = ANY((
                   SELECT skill_array
                   FROM USER_SKILLS
                   WHERE user_id = $1
               ))";
const res = await ctx.app.pool.query(query, [`${user_id}`]);

That will be more performant than doing multiple queries.

Then the reason why you're getting an array with arrays is because skillsArray.push(findSkill.rows) puts the whole rows property, which is an array, into your skillsArray array.

It's not entirely clear to me exactly what is the format of the result you want, but I'll assume it's the actual skill names in an array, something like:

{
    message: "Skills found",
    data: [
        "PHP",
        "Node js"
    ]
}

In which case you could restructure your code to be something like this:

const query = "SELECT STRING_AGG(skill_name, ',') AS skill_names
               FROM ALL_SKILLS
               WHERE id = ANY((
                   SELECT skill_array
                   FROM USER_SKILLS
                   WHERE user_id = $1
               ))";
const res = await ctx.app.pool.query(query, [`${user_id}`]);
const skillNames = res.rows[0].skill_names.split(',');
ctx.body = {
    status: 200,
    error: false,
    message: "Skills found",
    data: skillNames
};

I've added a STRING_AGG because I like to get postgres results in a single row if possible rather than have pg read multiple rows sequentially, I believe it will be faster. I'm not using ARRAY_AGG because I don't know how the pg module treats arrays, whether it converts them to string or a js array. So I return one field with the skills concatenated with a comma, e.g. "PHP,Node js", then just need to split that one field by the comma to get the desired array.

ROOT · Accepted Answer · 2020-01-01 11:57:08Z

I believe this is what you want

var userExist = await ctx.app.pool.query("SELECT * FROM USER_SKILLS WHERE user_id = $1",
    [`${user_id}`]);

var strArray = userExist.rows[0].skill_array.split(",");

var i;
var skillsArray = [];
for (i = 0; i < strArray.length; i++) {
  var findSkill = await ctx.app.pool.query("SELECT skill_name FROM ALL_SKILLS WHERE id = $1",
        [`${strArray[i]}`]);

    skillsArray.push(findSkill.rows[0]);

}
console.log('skillsArray', skillsArray);

ctx.body = {
    status: 200,
    error: false,
    message: "Skills found",
    data: skillsArray
};

But I as i mentioned in my comment, I suggest that you have your database tables/schema in relationships M:M, in your case, where you can just get the data you need in a single query, and it is considered bad practice to do a query in for(...) loop like this. you should use ORM like Sequelize or any other ORM that you prefer and works well with KOA, I hope this help you in achieving this:

Collectives™ on Stack Overflow

How to get rows from javascript array response?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related