When i use `json_arrayagg` & `json_object` with inner join in MySQL, Why are the results repeated?

Question

resumes table

CREATE TABLE if not exists resumes (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        title VARCHAR(100) NOT NULL,
        content VARCHAR(255),
        user_id BIGINT UNSIGNED NOT NULL,

        PRIMARY KEY (id),
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
      );

educations, careers tables

CREATE TABLE if not exists educations (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        type VARCHAR(20),
        school_name VARCHAR(100),
        resume_id BIGINT UNSIGNED NOT NULL,

        PRIMARY KEY (id),
        FOREIGN KEY (resume_id) REFERENCES resumes(id) ON DELETE CASCADE
      );

      CREATE TABLE if not exists careers (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        company VARCHAR(100),
        department VARCHAR(100),
        resume_id BIGINT UNSIGNED NOT NULL,

        PRIMARY KEY (id),
        FOREIGN KEY (resume_id) REFERENCES resumes(id) ON DELETE CASCADE
      );

and insert datas

INSERT INTO resumes (title, content) VALUES ("this_is_title", "this_is_content"); // id 1

INSERT INTO educations (type, school_name, resume_id) VALUES ("High_School", "This_is_school_name", 1),("College", "This_is_college_name", 1);

INSERT INTO careers (company, department, resume_id) VALUES ("company_1", "software", 1);

and now I want to get educations and careers rows with resume_id of 1. Like this

{
 educations: [
  {"type": "highschool", "school_name": "This_is_school_name"}, 
  {"type": "college", "school_name": "This_is_college_name"}
  ],
 careers: [
 {"company": "company_1", "department": "software"},
 ]
}

so i used this query

SELECT 
    json_arrayagg(json_object('type', E.type, 'school_name', E.school_name)) AS educations,
    json_arrayagg(json_object('company', C.company, 'department', C.department)) AS careers
FROM resumes R
inner join educations E
    on E.resume_id = R.id
inner join careers C
    on C.resume_id = R.id
where R.id = 1;

but result is

{
 educations: [
  {"type": "highschool", "school_name": "This_is_school_name"}, 
  {"type": "college", "school_name": "This_is_college_name"}
  ],
 careers: [
 {"company": "company_1", "department": "software"},
 {"company": "company_1", "department": "software"}
 ]
}

why does careers repeat? help!

edit I followed this link How to return distinct values in a JSON_ARRAYAGG

but, results are the same as before.

SELECT
    json_arrayagg(json_object('company', C.company, 'department', C.department)) AS careers,
    json_arrayagg(json_object('type', E.type, 'school_name', E.school_name)) AS educations
FROM (select distinct * from test0129.resumes) R
inner join test0129.careers C
    on C.resume_id = R.id
inner join test0129.educations E
    on E.resume_id = R.id
where R.id = 1 ;

Didn't you ask this same question earlier? I linked it to a duplicate that shows how to use DISTINCT in JSON_ARRAYAGG(). — Barmar
– Barmar, Commented Feb 11, 2022 at 2:23
Don't create a new question. Edit the original question with what you tried, and ask for it to be reopened. — Barmar
– Barmar, Commented Feb 11, 2022 at 2:27
And if you're going to post a new question, you should at least show your attempt to use the recommended solution. — Barmar
– Barmar, Commented Feb 11, 2022 at 2:27
I'm sorry. because I haven't adapted to the stack overflow yet. — DoHan_Kim
– DoHan_Kim, Commented Feb 11, 2022 at 2:29

Barmar · Accepted Answer · 2022-02-11 02:43:32Z

2

You have to do one of the aggregations in a subquery, otherwise you get a cross product of the careers and educations tables.

SELECT
    json_arrayagg(json_object('company', C.company, 'department', C.department)) AS careers,
    e.educations
FROM resumes AS R
JOIN careers AS C ON C.resume_id = R.id
JOIN (
    SELECT resume_id, json_arrayagg(json_object('type', E.type, 'school_name', E.school_name)) AS educations
    FROM educations AS E
    GROUP BY resume_id
) AS e ON e.resume_id = R.id
WHERE r.id = 1

DEMO

answered Feb 11, 2022 at 2:43

Barmar

789k57 gold badges554 silver badges669 bronze badges

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

Collectives™ on Stack Overflow

When i use `json_arrayagg` & `json_object` with inner join in MySQL, Why are the results repeated?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related