Bash sqlite3 -line | How to convert to JSON format

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

...

Sqlite-utils does exactly what you're looking for. By default, the output will be JSON.

Better late than never to plug jo.

Save sqlite3 to a text file.

Get jo (jo's also available in distro repos)

and use this bash script.

while read line
do 
    id=`echo $line | cut -d"|" -f1`
    fname=`echo $line | cut -d"|" -f2`
    gname=`echo $line | cut -d"|" -f3`
    genderid=`echo $line | cut -d"|" -f4`
    jsonline=`jo id="$id" fname="$fname" gname="$gname" genderid="$genderid"`
    json="$json $jsonline"
done < "$1"

jo -a $json

Please don't create (or parse) json with awk. There are dedicated tools for this. Tools like xidel.
While first and foremost a html, xml and json parser, xidel can also parse plain text.

I'd like to offer a very elegant solution using this tool (with much less code than jq).
I'll assume your 'members.txt'.

First to create a sequence of each json object to-be:

xidel -s members.txt --xquery 'tokenize($raw,"\n\n")'

Or...

xidel -s members.txt --xquery 'tokenize($raw,"\n\n") ! (position(),.)'
1
      id = 1
   fname = Leif
   gname = Håkansson
genderid = 1
2
      id = 2
   fname = Yvonne
   gname = Bergman
genderid = 2
3
      id = 3
   fname = Roger
   gname = Sjöberg
genderid = 1

...to better show you the individual items in the sequence.

Now you have 3 multi-line strings. To turn each item/string into another sequence where each item is a new line:

xidel -s members.txt --xquery 'tokenize($raw,"\n\n") ! x:lines(.)'

(x:lines(.) is a shorthand for tokenize(.,'\r\n?|\n'))

Now for each line tokenize on the " = " (which creates yet another sequence) and save it to a variable. For the first line for example this sequence is ("id","1"), for the second line ("fname","Leif"), etc.:

xidel -s members.txt --xquery 'tokenize($raw,"\n\n") ! (for $x in x:lines(.) let $a:=tokenize($x," = ") return ($a[1],$a[2]))'

Finally remove leading whitespace (normalize-space()), create a json object ({| {key-value-pair} |}) and put all json objects in an array ([ ... ]):

xidel -s members.txt --xquery '[tokenize($raw,"\n\n") ! {|for $x in x:lines(.) let $a:=tokenize($x," = ") return {normalize-space($a[1]):$a[2]}|}]'

Prettified + output:

xidel -s members.txt --xquery '
  [
    tokenize($raw,"\n\n") ! {|
      for $x in x:lines(.)
      let $a:=tokenize($x," = ")
      return {
        normalize-space($a[1]):$a[2]
      }
    |}
  ]
'
[
  {
    "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"
  }
]

Note: For xidel-0.9.9.7173 and newer --json-mode=deprecated is needed to create a json array with [ ]. The new (XQuery 3.1) way to create a json array is to use array{ }.