Bash sqlite3 -line | How to convert to JSON format

Question

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
  }
}

Answer 1

If your sqlite3 is compiled with the json1 extension (or if you can obtain a version of sqlite3 with the json1 extension), then you can use it to generate JSON objects (one JSON object per row). For example:

select json_object('id', id, 'fname', fname, 'gname', gname, 'genderid', genderid) ...

You can then use a tool such as jq to convert the stream of objects into an array of objects, e.g. pipe the output of the sqlite3 to jq -s ..

(A less tiresome alternative might be to use the sqlite3 function json_array(), which produces an array, which you can reassemble into an object using jq.)

If the json1 extension is unavailable, then you could use the following as a starting point:

awk 'BEGIN { print "["; } 
 function out() {if (n++) {print ","}; if (line) {print "{" line "}"}; line="";}
 function trim(x) { sub(/^ */, "", x); sub(/ *$/, "", x); return x; }
 NF==0 { out(); next}; 
 {if (line) {line = line ", " }
  i=index($0,"="); 
  line = line "\"" trim(substr($0,1,i-1)) ": \"" substr($0, i+2) "\""}
 END {out(); print "]"} '

Alternatively, you could use the following jq script, which converts numeric strings that occur on the RHS of "=" to numbers:

  def trim: sub("^ *"; "") | sub(" *$"; "");
  def keyvalue: index("=") as $i 
    | {(.[0:$i] | trim): (.[$i+2:] | (tonumber? // .))};
  [foreach (inputs, "") as $line ({object: false, seed: {} };
     if ($line|trim) == "" then { object: .seed, seed : {} }
     else {object: false, 
           seed: (.seed + ($line | keyvalue)) }
     end;
     .object | if . and (. != {}) then . else empty end ) ]

Answer 2

Just type -json argument with SQLite 3.33.0 or higher and get json output:

$ sqlite3 -json database.db "select * from TABLE_NAME"

from SQLite Release 3.33.0 note:

...

6. CLI enhancements:

• Added four new output modes: "box", "json", "markdown", and "table".
• The "column" output mode automatically expands columns to contain the longest output row and automatically turns ".header" on if it has not been previously set.
• The "quote" output mode honors ".separator"
• The decimal extension and the ieee754 extension are built-in to the CLI

...

Answer 3

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 '|' '"'