[Help-bash] Loop on mysql output with bash

[Boubaker Idir]

*Hello everybody,*




I have a database with the name "lora" with a table wifi, the table wifi
has those elements: id, packetid, bssid,rssi. multiple id have the same
packetid, and different bssid and rssi.
I want to do a select from my database and to put it in a json file like
this:

{"wlan": [{"mac": "78:54:2E:EC:75:96", "powrx": -52},{"mac":
"16:2D:27:96:08:36", "powrx": -56},{"mac": "F8:D1:11:4C:28:EC", "powrx":
-73},{"mac": "00:14:6C:41:37:8E", "powrx": -76},{"mac":
"00:23:04:5C:73:9A", "powrx": -77},{"mac": "00:23:04:5C:73:90", "powrx":
-79},{"mac": "00:23:04:5C:60:40", "powrx": -81}]}

I want this output in a json file for each packetid, the same result each
time to send it via curl to an API, so I must have a loop for each packetid
and each time the results goes to the json file. It worked for one packetid
but not for all of them

#!/bin/bash
#declare array=$(mysql -u root -pPassword -s -N #<<QUERY_INPUT
#use lora;
#*SELECT DISTINCT packetid FROM wifi;*
#QUERY_INPUT
#);

#array=( $( for i in $k ; do echo $i ; done ) )

#echo "${array[@]}";

#for i in "${array[@]}" do

variable=$(mysql -u -u root -pPassword -s -N <<QUERY_INPUT
use lora;

SELECT CONCAT("{""wlan"": [",
GROUP_CONCAT(JSON_OBJECT("mac",bssid,"powrx",rssi)), "]}") FROM wifi GROUP
BY packetid;
QUERY_INPUT
); #*WHERE packetid = $i*

echo $variable>text2.txt; *#change its content everytime for each packetid*

*Thank you for your help*

[Greg Wooledge]

Post by Boubaker Idir
I want to do a select from my database and to put it in a json file

I strongly recommend Anything But Bash.

You want a language that can connect to whatever database this is...

Post by Boubaker Idir
#!/bin/bash
#declare array=$(mysql -u root -pPassword -s -N #<<QUERY_INPUT

... to mysql, and retrieve results into some kind of data structure
(dictionary, list, hash table, whatever). And you also want a language
that can render that data structure (dictionary, etc.) into JSON format.

Bash sucks at both of those. For the JSON piece, there's jq and other
external utilities that you can install, but there is NOTHING you can
do to retrieve SQL results and safely separate them into bash variables
or associative array elements.

https://mywiki.wooledge.org/BashWeaknesses