Mysql 5.7 group concat - 1 lakh json rows - throws an error

Databases
1

Hi ,

I have a table called goods:
Table structure as follows:
Goods

ID int,
goods JSON

Sample data are :
id goods

1 {“item1”:“500”,“item2”:“600”,“item3”:“500”,“item4”:“200”,“item5”:“900”,“item6”:“500”,“item7”:“500”}
2 {“item1”:“700”,“item2”:“600”,“item3”:“500”,“item4”:“500”,“item5”:“500”,“item6”:“300”,“item7”:“500”}
3 {“item1”:“500”,“item2”:“800”,“item3”:“500”,“item4”:“400”,“item5”:“500”,“item6”:“500”,“item7”:“100”}
.
.
.
Upto 1 Lakh rows.

Now I want to make this one lakh rows into one row.

I have used group concat, but the data is truncating even I increased to maximum length

Any other options are there ?

Thanks in Advance.

2

What is a lakh row?

3

re-think your approach

GROUP_CONCAT is obviously not going to be your answer

4
1 Like
5

When you find yourself doing sequential numbering of columns it is a big fat red flag that your DB design is wrong. You need to fix the DB before you do anything. Learn “Database Normalization”.

6

good point, but OP’s table has only two columns – id and goods

7

It appears OP is storing numerous pieces of separate data in the same record which is a pretty bad idea. Still, there is a DB design problem.

8

I agree that the schema is wonky. The “goods” field is JSON which might be better when the field value is going to be worked with as JSON. But any advantage it offers in that respect comes at the cost of making it more difficult for the database to work with the field.

I would be tempted to redesign the database, or if unable to do that, have the server-side code do the work instead of the database.

Anyway, MySQL does have JSON functions. Have you tried SELECT JSON_MERGE_PRESERVE(goods)
https://dev.mysql.com/doc/refman/5.7/en/json-modification-functions.html#function_json-merge-preserve

9

Hi Mittineague

I have tried all json functions to group all rows into one row. But its not worked.

10

Hi benanamen ,

I am storing huge data set into one column which is named as " Goods". But my table contains only 2 columns. Id,goods respectively. I think DB design is not a problem.

Bcoz , My all data is accommodated into one table. But hereafter I want to group all rows into one row.
Then I want to store the output data into new field that is into only one cell.

Any other options ?

11

Yes Exactly bcoz group concat having a limited scope.

Any other options ?

12

re-think your design

1 Like
13

ok sure. Thank u.

14

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.