Mysql - How to alias a whole table data in a left join

Question

Suppose I have two tables,
users(id, name, gender)
posts(id, user_id(foreign_key from users), body, date)

I need to query a post and result should be like below

[
  "id": 1,
  "body": "Some post body",
  "date": "24-10-2015"
  "user": [
   "id": 23,
   "name": "FirstName LastName",
   "gender": "male"
  ]
 ]

I tried this, its giving all attributes from both the tables. But I need attributes from users table as an array. How can I do that?

SELECT * 
  FROM  `posts` 
  LEFT 
  JOIN  `users` 
  ON  `posts`.`user_id` =  `users`.`id` 

Answer 1

Tried this. http://sqlfiddle.com/#!9/a907b/21

You can format further.

SELECT CONCAT('[\n "id": ',POSTS.ID,', \n "body": "'
  ,BODY,'",\n  "date": "' ,DATE,'"\n  "user": [\n   "id": '
  ,USERS.ID,',\n   "name": "', NAME,'",\n   "gender": "'
  ,gender,'"\n  ]\n ]')
FROM USERS
LEFT JOIN POSTS ON users.id=posts.user_id

To view in sqlfiddle, use the dropdown on Run SQL button and choose plaintext output