MySQL How to join 2 tables and get a combined response

Question

I have 2 tables projects and images. My goal is to get all images of a project and get this kind of response.

{
  "data": {
    "id": "this is project id",
    "title": "project title",
    ...
    "images": [
      {
         "image": "images/image-name.jpg",
      },
      {
         "image": "images/image-name.jpg",
      }
    ]
  }
}

But what I get is this

{
  "data": [
     {
       "image": "image-path.jpg"
     },
     {
       "image": "image-path.jpg"
     }
   ]
}

My table structure look like this

projects

id	title	description	collaboration	role	case_study	slug	direction
uuid	text	text	text	text	text	text	enum(ltr, rtl)

images

id	images	project_id
uuid	image_path	uuid

My current query looks like this

`SELECT images.image FROM images JOIN projects ON images.project_id = ${id}`

Answer 1

You can try this to combine your queries from two tables to one json:

SELECT json_object(
    'id',p.id,
    'title',p.title,
    'images',( select json_arrayagg(json_object('image',i.`image`)) from images i where i.project_id = 1 )
) as json
FROM projects p WHERE p.id = 1;

You will get this:

{
"id": 1,
"title": "gx",
"images": [
    {
        "image": "foo"
    },
    {
        "image": "bar"
    }
  ]
}

But finally i have to say that combine your query result in your code is the better way.