How to turn repeating elements in MYSQL to array?

Question

Currently I've got the following query:

SELECT c.Permlink
     , c.ParentID
     , c.AuthorID
     , u.Username AS Author
     , c.Title
     , c.Body
     , c.Metadata
     , c.PostTime
     , v.VoterID
     , v.VoteValue
     , voterID.Username AS Voter
  FROM comments c
  LEFT 
  JOIN deleted_comments d
    ON c.Permlink = d.Permlink
  LEFT 
 JOIN votes v
    ON c.Permlink = v.Permlink 
 JOIN users u
    ON u.id = c.AuthorID 
  LEFT 
  JOIN users As voterID 
    ON voterID.ID = v.VoterID
 WHERE c.AuthorID = 88077 
  AND c.ParentID IS NULL
  AND d.Permlink IS NULL

And I get the following result:

[
    {
        "Permlink" : 3,
        "ParentID" : null,
        "AuthorID" : 88077,
        "Author" : "rishi556",
        "Title" : "",
        "Body" : "foxon",
        "Metadata" : "{}",
        "PostTime" : 1589475759,
        "VoterID" : 88077,
        "VoteValue" : 1,
        "Voter" : "rishi556"
    },
    {
        "Permlink" : 5,
        "ParentID" : null,
        "AuthorID" : 88077,
        "Author" : "rishi556",
        "Title" : "",
        "Body" : "foxonier?",
        "Metadata" : "{}",
        "PostTime" : 1589475774,
        "VoterID" : 1278255,
        "VoteValue" : 1,
        "Voter" : "gamer556"
    },
    {
        "Permlink" : 5,
        "ParentID" : null,
        "AuthorID" : 88077,
        "Author" : "rishi556",
        "Title" : "",
        "Body" : "foxonier?",
        "Metadata" : "{}",
        "PostTime" : 1589475774,
        "VoterID" : 88077,
        "VoteValue" : 1,
        "Voter" : "rishi556"
    },
    {
        "Permlink" : 9,
        "ParentID" : null,
        "AuthorID" : 88077,
        "Author" : "rishi556",
        "Title" : "",
        "Body" : "does this work?",
        "Metadata" : "{}",
        "PostTime" : 1589475858,
        "VoterID" : null,
        "VoteValue" : null,
        "Voter" : null
    }
]

To reduce repetition, I would like the data in VoteID, VoteValue, Voter to be put into an array, so something like this, using the Permlink : 5 as the example:

[
    {
        "Permlink" : 5,
        "ParentID" : null,
        "AuthorID" : 88077,
        "Author" : "rishi556",
        "Title" : "",
        "Body" : "foxonier?",
        "Metadata" : "{}",
        "PostTime" : 1589475774,
        "Votes" : [{"VoterID" : 1278255,"VoteValue" : 1,"Voter" : "gamer556"},{"VoterID" : 88077, "VoteValue" : 1,"Voter" : "rishi556"} ]

    }
]

And for those with null in their votes(meaning that it hasn't received votes yet), it should just be an empty array.

What would I need to add to the query to do that? Or would it not be possible with a change to the query and I'd need to manipulate in my code?

MySQL doesn't know what an array is, and it doesn't care, it can only speak in terms of rows and columns. Do you mean in an application or scripting layer? — tadman, May 14 '20 at 18:43
mysql does not support "array" datatypes. if we were returning JSON (MySQL 5.7+) we could make use of the JSON_ARRAYAGG function https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_json-arrayagg, but the resultset posted in the question doesn't look to me like JSON and it doesnt resemble a normal resultset returned by SQL, looks more like some kind of representation of a data structure dumped from some programming language, not SQL) MySQL has a GROUP_CONCAT function, returns a VARCHAR not an array, but that would return a column in a SQL resultset, not the result shown — spencer7593, May 14 '20 at 18:53
You can create the grouped array in a programming language like PHP or Python. See https://stackoverflow.com/questions/35473174/creating-one-array-from-another-array-in-php for how to do it in PHP. — Barmar, May 14 '20 at 18:59

Snaddyvitch Dispenser · Accepted Answer · 2020-05-14T20:59:49.397

Ok, so I see the "Array Table" is a separate table. This means that you have 2 choices in order to solve this problem.

1. In Your Code (Untested!!!!)

Ideally, you can just separate it into 2 queries (one which gets the comment, and one which gets the votes cast on it)

SELECT c.Permlink
     , c.ParentID
     , c.AuthorID
     , u.Username AS Author
     , c.Title
     , c.Body
     , c.Metadata
     , c.PostTime
  FROM comments c
  LEFT 
  JOIN deleted_comments d
    ON c.Permlink = d.Permlink
 JOIN users u
    ON u.id = c.AuthorID 
  AND c.ParentID IS NULL
  AND d.Permlink IS NULL

And then within that query, loop through the votes table in order to get the votes

SELECT votes.VoterID, votes.VoteValue,(SELECT Users.username FROM users WHERE users.ID = votes.VoterID) FROM votes WHERE comments.Permlink = votes.Permlink

2. Purely in MySQL

If you have to do it in MySQL or have good reason to do so, this is a query which gives you exactly what you want (A JSON string of all the Votes)

SELECT
  comments.Permlink,
  comments.ParentID,
  comments.AuthorID,
  users.Username AS Author,
  comments.Title,
  comments.Body,
  comments.Metadata,
  comments.PostTime,
  CONCAT("[", 
    (SELECT GROUP_CONCAT(CONCAT('{"voter_id":',votes.VoterID,', "voteValue": ',votes.VoteValue,',"voter_name":"',(SELECT Users.username FROM users WHERE users.ID = votes.VoterID), '"}'))
  FROM votes WHERE
    comments.Permlink = votes.Permlink),"]") as vote_data
FROM
  comments
LEFT JOIN
  deleted_comments
ON
  comments.Permlink = deleted_comments.Permlink
JOIN
  users
ON
  users.id = comments.AuthorID
WHERE
  comments.AuthorID = 0 AND comments.ParentID IS NULL AND deleted_comments.Permlink IS NULL

All good, except field name is incorrect(using pure MYSQL approach). — Rishi556, May 14 '20 at 20:58

How to turn repeating elements in MYSQL to array?

1 Answers1