Convert query result into array in shell script

Question

I am trying very simple thing using shell script on BASH shell. I am trying to get keyids of all Users into an array in a shell script. I will later loop them for further processing.

I wrote very simple code. But I am stuck while converting a space separated QUERY_RESULTS string into an array.

I tried various options e.g. set -A , read -a, array=( $ QUERY_RESULTS } ) etc. Nothing is working for me. Every-thing I try, gives me some error.

For e.g.

set -A gives me illegal option -A.
read -a also gives me illegal option -a.
array=( $ QUERY_RESULTS } ); gives me ( unexpected.

Below is my code. Please help me here.

#IFS="";
echo "Get list of Active Users from Table ";
QUERY_RESULTS=$( mysql -u username password -e "SELECT keyid from users where isActive = 1" );
echo $QUERY_RESULTS;
#eval `read -a arr <<$QUERY_RESULTS`;
#array=( $ QUERY_RESULTS } );
echo $array;

That seems unlikely because bash's read does support -a. — melpomene
– melpomene, Commented Jan 28, 2013 at 16:21
Your question gave me hints, but ultimately I need more. If you want to capture the result of a MySql query into an array, you can do this: stackoverflow.com/a/57061108/470749 — Ryan
– Ryan, Commented Jul 16, 2019 at 15:51

William · Accepted Answer · 2013-01-28 17:13:42Z

2

Did you try something like this:

read -r -a array <<< $QUERY_RESULTS

Note the "<<<" instead of "<<"

If the contents of QUERY_RESULTS consist of multiple lines you may want to do it this way:

mapfile -t array <<< $QUERY_RESULTS

edited Jan 28, 2013 at 17:13

answered Jan 28, 2013 at 17:06

William

4,9652 gold badges17 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Learn More · Accepted Answer · 2013-01-29 14:33:52Z

1

I could achieve my target using following code. However still I am having problem in converting QUERY RESULT to string.

echo "Get list of Active Users from Table ";
QUERY_RESULTS=$( mysql -u username password -e "SELECT keyid from users where isActive = 1" );

for i in `echo $QUERY_RESULTS | tr ' ' '\n' |sed  '1d' | tr '\n' ' '`
do
sh doAction.sh $i;
done

answered Jan 29, 2013 at 14:33

Learn More

1,5615 gold badges29 silver badges52 bronze badges

Comments

zhihong · Accepted Answer · 2016-06-27 11:27:42Z

1

In bash, you can get the array and string as below:

query="SELECT keyid from users where isActive = 1;" 
QUERY_RESULTS=($(mysql -u username password -e "$query"))

or

QUERY_RESULTS=(`mysql -u username password -e "$query"`)
QUERY_RESULTS_STR=$(printf "%s," "${QUERY_RESULTS[@]}")

Pls do notice that you need the "()" out of the mysql cmd, which make the result a bash array that can be used in a loop. And "%s,", the "," is the delimiter to separate the elements when change to str, you can change to other delimiter if needed.

answered Jun 27, 2016 at 11:27

zhihong

1,9662 gold badges25 silver badges35 bronze badges

2 Comments

Ryan Over a year ago

Cool, I used your hint here: stackoverflow.com/a/57061108/470749

zhihong Over a year ago

@Ryan, great to know this still works and helped you!

Collectives™ on Stack Overflow

Convert query result into array in shell script

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related