Question 20850 in Mysql Question posted in
The official MySQL documentation can be found here.

How to intersect mysql lines for a json file in nodejs

I would like to ask you for advice as I don’t know which method to use:
For example with this query in Mysql i have this table:

user_Id name age photo
1 Zack 23 Blob[2.1]
1 Zack 23 Blob[3.3]
1 Zack 23 Blob[1.9]
3 Amy 19 Blob[2.9]
3 Amy 19 Blob[3.3]

In node js when i transform it in a Json file i have 5 rows, but i want 2 rows with the grouped photos. Example:

{
  "0": {
    "user_id": 1,
    "name": "Zack",
    "age": 23,
    "photo": [
      "Blob[2.1]",
      "Blob[3.3]",
      "Blob[1.9]"
    ]
  },
  "1": {
    "user_id": 2,
    "name": "Amy",
    "age": 19,
    "photo": [
      "Blob[2.9]",
      "Blob[3.3]"
    ]
  }
}

So, do I have to use some function in nodejs or do I have to add something in mysql query?

Tags:

2

Answers


  1. 0

    You need to aggregate then photo values

    Query #1

    SELECT
`user_Id`, `name`, `age`, JSON_ARRAYAGG(`photo`) as photo
FROM Table1
GROUP BY `user_Id`, `name`, `age`;
    user_Id name age photo
    1 Zack 23 ["Blob[2.1]", "Blob[3.3]", "Blob[1.9]"]
    3 Amy 19 ["Blob[2.9]", "Blob[3.3]"]

    View on DB Fiddle

    Login or Signup to reply.
  2. 0

    You can generate row indexes as aggregating photo reference values, and then aggregate the resultant individual objects with keys derived from those indexes such as

    SELECT JSON_PRETTY( JSON_OBJECTAGG(rn,js) ) AS Result
  FROM ( 
         SELECT @i := @i+1 AS rn,
                JSON_OBJECT(
                            'user_id',
                             user_id,
                            'name',
                             name,
                            'age',
                             age,
                            'photo',
                             JSON_ARRAYAGG(photo)
                            ) AS js
          FROM t
          JOIN (SELECT @i := -1) AS q_iter         
         GROUP BY user_id, name, age
         ORDER BY user_id 
       ) j;

    Demo

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search