CODEWITHSUNDEEP
mysqljson
Nancy Moore
Nancy Moore

Reputation: 2470

How to create json data using Mysql Database CONCAT AND Group Concat Function

Am Trying to create a json array by leveraging info three database tables.

After leveraging info and solution found here source I came to realize how application performance can be improved using just one queries via Mysql Concat and Group_Concat Method.

The three tables is created as follows each with their reference keys

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));

Here is my expected Json Format...

[
{"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"}]
}]}
]

My success and issues so Far.

I was able to get json records for the first and second table(Category and Product table respectively).

When I tried to get that of third table(Sales) with GROUP_CONCAT(CONCAT()) method it displays error Invalid use of group function

Here is the implementation so far.

 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

Upvotes: 1

Views: 141

Answers (1)

Nick
Nick

Reputation: 147146

You can't nest grouping functions like GROUP_CONCAT. Instead, you will need to create the product and sales JSON inside a subquery and then include that into your top-level JSON. Without sample data it's hard to be certain, but something like this should work:

 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

Upvotes: 1

Related Questions