CODEWITHSUNDEEP
mysqljsonbash
kovir
kovir

Reputation: 11

bash - extract data from mysql table (GROUP BY)- how to process

I have mySQL table:

+----+---------------------+-------+
| id | timestamp           | value |
+----+---------------------+-------+
|  1 | 2016-03-29 18:53:28 |     1 |
|  2 | 2016-03-29 20:26:06 |     1 |
|  3 | 2016-03-29 20:26:22 |     1 |
+----+---------------------+-------+
3 rows in set (0.00 sec)

It is a table to hold water consumption data (each 1 in value is a 1 liter of water).

I wrote a bash script to extract data - sum of litres of water by months.

watersum=`echo " SELECT MONTHNAME(timestamp), SUM(value) FROM woda GROUP BY YEAR(timestamp), MONTH(timestamp);" | mysql  -s -u$SQUSER -p$SQPASS -h$SQHOST $SQLDB` 

echo $watersum

gives me: 

March 693 April 9768 May 11277 June 11987 July 10047 August 8570

I would like to save this data in json file. How do convert the string in $watersum to a json string?

Upvotes: 1

Views: 896

Answers (1)

sjsam
sjsam

Reputation: 21965

Make watersum an array

watersum=( $(echo " SELECT MONTHNAME(timestamp), SUM(value) FROM woda GROUP BY YEAR(timestamp), MONTH(timestamp);" | mysql  -s -u$SQUSER -p$SQPASS -h$SQHOST $SQLDB) )

echo "{" && for((i=0;i<"${#watersum[@]}";i+=2))
do  
echo -n "\"${watersum[$i]}\":\"${watersum[((i+1))]}\""; 
(( (i+2) == "${#watersum[@]}" )) || echo ","
done && echo;echo "}"

Output

{
"March":"693",
"April":"9768",
"May":"11277",
"June":"11987",
"July":"10047",
"August":"8570"
}

Upvotes: 1

Related Questions