I'd like to query my database for data, but the problem is that when I do so, I'm unable to retrieve the data in the desired format (Building a API-Endpoint). I'm aiming for a single comprehensive request instead of two separate smaller requests.

This is what I am receiving currently with my requests:

{
    "category_signature": "dajuiqwndiuqwdqw",
    "category_name": "food",
    "category_img": "foodimg"
},
{
    "category_signature": "weoflhjwefoiwjefwe",
    "category_name": "drink",
    "category_img": "drinkimg"
},
//and so on...

and (e. g. for category: "dajuiqwndiuqwdqw"):

{
    "product_signature": "asfwhregw",
    "product_name": "banana",
    "product_description": "looks yellow",
    "product_price": "100$",
    "product_img": "bananaimg"
},
//and so on...

This is what I want to revive

"category_signature": "dajuiqwndiuqwdqw",
"category_name": "food",
"category_img": "foodimg"
{
    "product_signature": "asfwhregw",
    "product_name": "banana",
    "product_description": "looks yellow",
    "product_price": "100$",
    "product_img": "bananaimg"
},
//and so on...

What I tried

I tried using the "JSON_ARRAYAGG"-Method, but I received this error: "Error: SQLITE_ERROR: no such function: JSON_ARRAYAGG". I also tried using the "json_group_array"-Method, and it didn't work neither. The format was still not how I planed it to be.

My current alternative

I tried using this SQL-Request, but the result was badly formatted.

SELECT
  c.category_signature,
  c.category_name,
  c.category_img,
  JSON_ARRAYAGG(
    JSON_OBJECT(
      'product_signature', p.product_signature,
      'product_name', p.product_name,
      'product_description', p.product_description,
      'product_price', p.product_price,
      'product_img', p.product_img
    )
  ) AS products
FROM categories c
JOIN products p ON c.category_signature = p.category_signature
WHERE c.menu_signature = ?
GROUP BY c.category_signature, c.category_name, c.category_img;

Result:

{
    "category_signature": "asdasdasy",
    "category_name": "testa",
    "category_img": "tes2as",
    "products": "[{\"product_signature\":\"asdasdasy\",\"product_name\":\"aaaabdedbbbbb\",\"product_description\":\"aabbb\",\"product_price\":1.1,\"product_img\":\"aaabbbbbb\"}, /* and so on */ }]"
}

Is the only possibility to use another DB -- or can I do it in SQLite? Thank you for your time.

I tried using the "JSON_ARRAYAGG"-Method, but I revived this method: "Error: SQLITE_ERROR: no such function: JSON_ARRAYAGG". I also tried using the "json_group_array"-Method, and it didn't work neither.