0

I'm writing a API for an Q&A section of a project for school. With my current schema, I have 3 tables. One for the questions, one for the answers, and one to store any image URLs for the answers.

You can take a look at the schema design here:

enter image description here

. You can ignore the products table as I've since revised the overall structure and am relying on the product_id to come through as a url query when hitting the API endpoint instead.

Basically, what I'm trying to accomplish is I want to query all 3 tables to get results that match down the chain of 1-to-Many for each table.

So what I want the data to ultimately look like is:

results: [
           { 
              <data from question table>
              answers: [ 
                         {
                           <data from answer table>
                            photos: [
                                      <array of urls from photos table>
                                    ]
                          }, 
                          ...etc
                        ]
            },
            ...etc
         ]

The logical flow would basically be as follows:

->Get all questions associated with the given product_id

->For each question, get all answers associated with it's question_id

->For each answer, get all URLs associated with the answer_id

From what I've seen in the docs for MySQL it looks like I can create JSON objects using JSON_OBJECT() but I'm not sure how to basically create a temporary "column" on the tables to get my data to look how I want it.

I'm sorry if I didn't explain too well, I'm very new to MySQL. Please leave a comment if any clarification is needed.

TL;DR: How can I structure my MySQL query to make the data that's returned become a json object on a new column that doesn't exist in the next table up the chain? Is it feasible to do that in one query? Am I better off querying each table individually and merging the results somehow?

Barmar
  • 741,623
  • 53
  • 500
  • 612
Nate Schroeder
  • 21
  • 6
  • 1
    You can use `JSON_ARRAYAGG()` to create a JSON array from all the related rows. – Barmar Nov 19 '21 at 22:08
  • Generally, you deal with JSON at an API or program level, not database level. What language are you doing the query from? I'm assuming you are intending to send that JSON on to a client side? – computercarguy Nov 19 '21 at 22:15
  • @Barmar could you link me an example snippit? The docs aren't very clear for that function. – Nate Schroeder Nov 19 '21 at 22:43
  • @computercarguy I'm doing an express server to communicate with the DB, and yeah I'm looking to pass the data pretty much directly from the DB to the client – Nate Schroeder Nov 19 '21 at 22:43

0 Answers0