mysql - 如何使用Mysql数据库CONCAT AND Group Concat函数创建json数据

Question

我正在尝试利用三个数据库表来创建一个 json 数组。

利用此处找到的信息和解决方案后 source我开始意识到如何通过 Mysql Concat 和 Group_Concat 方法仅使用一次查询即可提高应用程序性能。

这三个表的创建方式如下，每个表都有其引用键

Create table categorys(id int primary key auto_increment, category_name varchar(30), buyer_userid int(30));

Create table product(id int primary key auto_increment, product_name varchar(30), buyer_userid int(30),category_id int,
foreign key (category_id) references categorys(id));


Create table sales(items_id int primary key auto_increment, item_name varchar(30),quantity varchar(30), buyer_userid int(30),
product_id int, foreign key (product_id) references product(id));

这是我期望的 Json 格式...

[
{"id":"1","cat_name":"Provision","catbuy_userid":"100",
"products_info":[{"productid":"2","product_name":"Malt","buyer_userid":"100",
"sales_Info":[{"items_id":"1","item_name":"malt","buyer_userid":"100","quantity":"72"}]
}]},
{"id":"2","cat_name":"Cosmetics","catbuy_userid":"200",
"products_info":[{"productid":"3","product_name":"soapy","buyer_userid":"200",
"sales_Info":[{"items_id":"2","item_name":"cream","buyer_userid":"200","quantity":"83"}]
}]}
]

到目前为止我的成功和问题。

我能够获取第一个和第二个表(分别是“类别”和“产品”表)的 json 记录。

当我尝试使用 GROUP_CONCAT(CONCAT()) 方法获取第三个表(销售)时，它显示错误 Invalid use of group function

这是迄今为止的实现。

 SELECT 
     CONCAT(
       "{"
     ,     '"id"' , ":" , '"' , categorys.id , '"' , ","
     ,     '"name"' , ":" , '"' , categorys.category_name , '"' , ","
     ,     '"productInfo"' , ":" , "["
                              ,GROUP_CONCAT(CONCAT(
"{"
     ,     '"pid"' , ":" , '"' , product.id , '"' , ","
     ,     '"productname"' , ":" , '"' , product.product_name , '"' , ","


     ,     '"SalesInfo"' , ":" , "["
                              , GROUP_CONCAT(CONCAT(
"{"
     ,     '"sales id"' , ":" , '"' , sales.items_id , '"' , ","
     ,     '"item name"' , ":" , '"' , sales.item_name , '"' , ","

  , "}"
))
                          , "]"



  , "}"
))
                          , "]"
     , "}"
     ) AS json 
  FROM 
   categorys 
  INNER JOIN 
    product 
  ON 
    categorys.id = product.category_id
  INNER JOIN
    sales 
  ON
    product.id = sales.product_id
  WHERE 
    categorys.id IN(SELECT id FROM categorys)
  GROUP BY
     categorys.id

Answer 1

您不能嵌套像 GROUP_CONCAT 这样的分组函数。相反，您需要在子查询中创建 product 和 sales JSON，然后将其包含到顶级 JSON 中。没有样本数据很难确定，但类似这样的东西应该可以工作:

 SELECT 
     CONCAT(
       "{"
     ,     '"id"' , ":" , '"' , categorys.id , '"' , ","
     ,     '"name"' , ":" , '"' , categorys.category_name , '"' , ","
     ,     '"productInfo"' , ":" , "["
                              ,GROUP_CONCAT(product_sales)
                          , "]"
     , "}"
     ) AS json 
  FROM 
   categorys 
  INNER JOIN (SELECT CONCAT(
"{"
     ,     '"pid"' , ":" , '"' , product.id , '"' , ","
     ,     '"productname"' , ":" , '"' , product.product_name , '"' , ","


     ,     '"SalesInfo"' , ":" , "["
                              , GROUP_CONCAT(CONCAT(
"{"
     ,     '"sales id"' , ":" , '"' , sales.items_id , '"' , ","
     ,     '"item name"' , ":" , '"' , sales.item_name , '"' , ","

  , "}"
))
                          , "]"



  , "}"
) AS product_sales
             FROM product 
  INNER JOIN
    sales 
  ON
    product.id = sales.product_id) p
  ON 
    categorys.id = p.category_id
  WHERE 
    categorys.id IN(SELECT id FROM categorys)
  GROUP BY
     categorys.id