[SOLVED] Bash sqlite3 -line | How to convert to JSON format

Issue

I want to convert my sqlite data from my database to JSON format.

I would like to use this syntax:

sqlite3 -line members.db "SELECT * FROM members LIMIT 3" > members.txt

OUTPUT:

      id = 1
   fname = Leif
   gname = Håkansson
genderid = 1

      id = 2
   fname = Yvonne
   gname = Bergman
genderid = 2

      id = 3
   fname = Roger
   gname = Sjöberg
genderid = 1

How to do this with nice and structur code in a for loop? (Only in Bash)

I have tried some awk and grep but not with a great succes yet.

Would be nice with some tips.

I want a result similar to this:

[
  {
    "id":1,
    "fname":"Leif",
    "gname":"Hakansson",
    "genderid":1
  },
  {
    "id":2,
    "fname":"Yvonne",
    "gname":"Bergman",
    "genderid":2
  },
  {
    "id":3,
    "fname":"Roger",
    "gname":"Sjberg",
    "genderid":1
  }
}

Solution

I think I would prefer to parse sqlite output with a single line per record rather than the very wordy output format you suggested with sqlite3 -line. So, I would go with this:

sqlite3 members.db "SELECT * FROM members LIMIT 3"

which gives me this to parse:

1|Leif|Hakansson|1
2|Yvonne|Bergman|2
3|Roger|Sjoberg|1

I can now parse that with awk if I set the input separator to | with

awk -F '|'

and pick up the 4 fields on each line with the following and save them in an array like this:

{ id[++i]=$1; fname[i]=$2; gname[i]=$3; genderid[i]=$4 }

Then all I need to do is print the output format you need at the end. However, you have double quotes in your output and they are a pain to quote in awk, so I temporarily use another pipe symbol (|) as a double quote and then, at the very end, I get tr to replace all the pipe symbols with double quotes - just to make the code easier on the eye. So the total solution looks like this:

sqlite3 members.db "SELECT * FROM members LIMIT 3" | awk -F'|' '
   # sqlite output line - pick up fields and store in arrays
   { id[++i]=$1; fname[i]=$2; gname[i]=$3; genderid[i]=$4 }

   END {
      printf "[\n";
      for(j=1;j<=i;j++){
         printf "  {\n"
         printf "    |id|:%d,\n",id[j]
         printf "    |fname|:|%s|,\n",fname[j]
         printf "    |gname|:|%s|,\n",gname[j]
         printf "    |genderid|:%d\n",genderid[j]
         closing="  },\n"
         if(j==i){closing="  }\n"}
         printf closing;
      }
      printf "]\n";
   }' | tr '|' '"'

Answered By - Mark Setchell

Answer Checked By - Timothy Miller (WPSolving Admin)