0

I have json string like this:

jstring='[{"userQuery":"select name from abc;","user":"abc"},{"userQuery":"select name from xyz;","user":"xyz"},{"userQuery":"select name from ppp;","user":"ppp"}]'

I wrote a simple for loop using jq to extract values but not getting the desired result.

for i in `echo $jstring | jq '.[] | [.user, .userQuery]'`; do echo ${i}; done

With the help of this line : echo $jstring | jq '.[] | [.user, .userQuery]'. I am able to extract below info: 

[ "abc", "select name from abc;"][ "xyz", "select name from xyz;"][ "ppp", "select name from ppp;"]

Now, I want two variable "user" & "query" for each array and store that info.
Eg: For [ "abc", "select name from abc;"] -- user: abc, query: "select name from abc" and store them.

I am not sure how to iterate over json using jq and get individual values and store them.

PanwarS87
  • 319
  • 5
  • 14
  • do you want to accumulate respective values in an array, like: `user["abc" "xyz" "ppp"]` ? – RomanPerekhrest Sep 16 '17 at 20:18
  • @RomanPerekhrest: No, I want to build a message and log it or email it. Eg: User: ${user} is running : ${query}. – PanwarS87 Sep 16 '17 at 20:34
  • Closely related: [Converting a JSON array to a bash array](https://stackoverflow.com/questions/26717277/converting-a-json-array-to-a-bash-array). – Charles Duffy Sep 16 '17 at 20:50
  • The underlying question of getting results from jq to bash is commonly asked. See also these recent ones: [Convert a nested JSON of objects into array into a bash array using jq](https://stackoverflow.com/questions/46229097/convert-a-nested-json-of-objects-into-array-into-a-bash-array-using-jq) and [Is there a way to read all key-value pairs in the JSON file and then initialize all variables accordingly in shell?](https://stackoverflow.com/questions/46187807/is-there-a-way-to-read-all-key-value-pairs-in-the-json-file-and-then-initialize) – jq170727 Sep 16 '17 at 21:06

2 Answers2

3

I assume you mean you want to store them as shell variables. With bash you can write:

while read -r user query; do 
    echo "user->$user"
    echo "query->$query"
done < <( jq -r '.[] | "\(.user) \(.userQuery)"' <<< "$jstring" )

user->abc
query->select name from abc;
user->xyz
query->select name from xyz;
user->ppp
query->select name from ppp;

The secret sauce is in the formulation of the string inside jq that refers to the object properties with \(.user)

I'm assuming that the user name does not contain spaces. Otherwise we have to use a different separator in that string, and use IFS with the shell read command.

glenn jackman
  • 238,783
  • 38
  • 220
  • 352
  • @gelnnjackman Thank you for the quick response and answer. This is perfect! I was looking at this as well: https://stackoverflow.com/questions/46187807/is-there-a-way-to-read-all-key-value-pairs-in-the-json-file-and-then-initialize but couldn't make it work. Thanks again! – PanwarS87 Sep 18 '17 at 00:26
2

jq + bash solution:

#!/bin/bash
jstring='[{"userQuery":"select name from abc;","user":"abc"},{"userQuery":"select name from xyz;","user":"xyz"},{"userQuery":"select name from ppp;","user":"ppp"}]'

while IFS=$'\t' read -r user query; do
    msg="User: ${user} is running : ${query}"
    mail -s "User query" "youremail@gmail.com" <<< "$msg"
done < <(jq '.[] | [.user,.userQuery] | @tsv' <<< "$jstring")
RomanPerekhrest
  • 88,541
  • 4
  • 65
  • 105
  • The above code did not work as expected, the code which Glenn has posted is working as expected. Please check the output, it is not working as expected. – PanwarS87 Sep 18 '17 at 14:00
  • @PanwarS87, at first, it works fine in terms of parsing and processing. Secondly, your expected output(as you said) was not clear. It was *I want two variable "user" & "query" for each array and store that info* - which does not look/sound clear. So I've done the man and crucial work. So, your claim sounds as preconceived – RomanPerekhrest Sep 18 '17 at 14:10
  • Sorry, I might sound unclear but when I ran the above code I am getting the below output. User: "abc\tselect name from abc;" is running : User: "xyz\tselect name from xyz;" is running : User: "ppp\tselect name from ppp;" is running : I may be missing something here or may be IFS is not working as expected. – PanwarS87 Sep 18 '17 at 14:58