I'm trying to create a basic API and I have one table called orders and another called order\_lines which has a row for each product ordered with a field called order\_id that corresponds back to orders. When doing a GET request how can I have the order as a parent and each order\_lines record within it as a child array? I'm not sure if I need to do this in SQL somehow or if I can easily do it in Python. I just need a general idea how to pull and construct this data.

My API GET request looks like this:

```py
@app.route('/api/v1/ordertest', methods=['GET'])
def orders_test2():
    # try:

    if request.method == 'GET':
        query_parameters = request.args

        id = query_parameters.get('id')
        customer_id = query_parameters.get('customer_id')

        query = '''
                    SELECT 
                        o.*,
                        ol.product_id, 
                        p.name, 
                        ol.quantity, 
                        p.price as price, 
                        round((p.price * ol.quantity), 2) as total_price
                    FROM orders o
                    join order_lines ol
                    on o.id = ol.order_id
                    join products p
                    on ol.product_id = p.id
                    WHERE o.
                '''
        to_filter = []

        if id:
            query += 'id=? AND'
            to_filter.append(id)
        if customer_id:
            query += 'customer_id=? AND'
            to_filter.append(customer_id)

        if not (id or customer_id):
            return page_not_found(404)

        query = query[:-4] + ';'

        conn = sqlite3.connect(db)
        conn.row_factory = dict_factory
        cur = conn.cursor()

        results = cur.execute(query, to_filter).fetchall()

        return jsonify(results)

```

Doing `/api/v1/ordertest?id=1` returns all the data I want, but it has the information from the orders table listed in the object for each product.

```json
[
  {
    "created_at": "2020-11-12 00:08:32", 
    "customer_id": 1, 
    "id": 1, 
    "name": "plum", 
    "price": 0.3, 
    "product_id": 4, 
    "quantity": 2, 
    "status": "delivered", 
    "total_price": 0.6, 
    "updated_at": "2021-02-05 18:41:20"
  }, 
  {
    "created_at": "2020-11-12 00:08:32", 
    "customer_id": 1, 
    "id": 1, 
    "name": "salmon", 
    "price": 6.76, 
    "product_id": 20, 
    "quantity": 4, 
    "status": "delivered", 
    "total_price": 27.04, 
    "updated_at": "2021-02-05 18:41:20"
  }, 
  {
    "created_at": "2020-11-12 00:08:32", 
    "customer_id": 1, 
    "id": 1, 
    "name": "mint chocolate bar", 
    "price": 2.01, 
    "product_id": 30, 
    "quantity": 3, 
    "status": "delivered", 
    "total_price": 6.03, 
    "updated_at": "2021-02-05 18:41:20"
  }
]

```

However, I want the data to look something like this where each order\_lines row with the matching order\_id lives inside the main object:

```json
[
    {
      "created_at": "2020-11-12 00:08:32", 
      "customer_id": 1, 
      "id": 1, 
      "status": "delivered", 
      "updated_at": "2021-02-05 18:41:20",
      "products": 
      {
          "name": "plum", 
          "price": 0.3, 
          "product_id": 4, 
          "quantity": 2,
          "total_price": 0.6
          }, 
      {
          "name": "salmon", 
          "price": 6.76, 
          "product_id": 20, 
          "quantity": 4, 
          "total_price": 27.04
      }, 
      {
          "name": "mint chocolate bar", 
          "price": 2.01, 
          "product_id": 30, 
          "quantity": 3, 
          "total_price": 6.03
        }
    }
  ]

```