Make JSON from SQL query output

Question

I have this database query:

select hostname,size from tableinfo

The output is like this:

  hostname                   size
------------------------- -----------
  host1                        28
  host2                        13
  host3                        79
  host4                        28
  host5                        17  

Or, I can make it like this:

host1                        28
host2                        13
host3                        79
host4                        28
host5                        17

I want to write a shell script that converts this output to JSON, but I don't really know where to begin or what to do. The JSON must be like this:

{
   "data":[
   {  "{#HOSTNAME}":"host1",  "{#SIZE}":"28"  } ,
   {  "{#HOSTNAME}":"host2",  "{#SIZE}":"13"  } ,
   {  "{#HOSTNAME}":"host3",  "{#SIZE}":"79"  } ,
   {  "{#HOSTNAME}":"host4",  "{#SIZE}":"28"  } ,
   {  "{#HOSTNAME}":"host5",  "{#SIZE}":"17"  }
   ]
}

Answer 1

jq solution:

 <your sql output> | jq -Rs '{"data": [split("\n") | map(select(length > 0))[] 
                             | split(" +";"g") 
                             | {"{#HOSTNAME}": .[0], "{#SIZE}": .[1]}]}'

The output:

{
  "data": [
    {
      "{#HOSTNAME}": "host1",
      "{#SIZE}": "28"
    },
    {
      "{#HOSTNAME}": "host2",
      "{#SIZE}": "13"
    },
    {
      "{#HOSTNAME}": "host3",
      "{#SIZE}": "79"
    },
    {
      "{#HOSTNAME}": "host4",
      "{#SIZE}": "28"
    },
    {
      "{#HOSTNAME}": "host5",
      "{#SIZE}": "17"
    }
  ]
}

Answer 2

Assuming this is an SQLite3 database (other database engines will have their own native ways to produce JSON output):

sqlite3 database.db \
    '.mode json' \
    'SELECT hostname AS `{#HOSTNAME}`, size AS `{#SIZE}` FROM tableinfo' |
jq '{ data: . }'

This gets the data in JSON format from the database as an array, and then uses jq to insert it under a data key in an object. The SELECT query changes the names of the keys used for the hostnames and sizes according to what is required.

This gives you a JSON document that is equivalent to what you ask for:

{
  "data": [
    {
      "{#HOSTNAME}": "host1",
      "{#SIZE}": "28"
    },
    {
      "{#HOSTNAME}": "host2",
      "{#SIZE}": "13"
    },
    {
      "{#HOSTNAME}": "host3",
      "{#SIZE}": "79"
    },
    {
      "{#HOSTNAME}": "host4",
      "{#SIZE}": "28"
    },
    {
      "{#HOSTNAME}": "host5",
      "{#SIZE}": "17"
    }
  ]
}

If you want the exact format of the output that you mention, then replace the jq command in the pipeline above with the following jtc command:

jtc -T '{"data": {{}} }' -tc

This would insert the data read from sqlite3 into a template, as the value of the data key, and the -tc would cause jtc to produce its "half-compact" pretty-printed output format, and it would look like this:

{
   "data": [
      { "{#HOSTNAME}": "host1", "{#SIZE}": "28" },
      { "{#HOSTNAME}": "host2", "{#SIZE}": "13" },
      { "{#HOSTNAME}": "host3", "{#SIZE}": "79" },
      { "{#HOSTNAME}": "host4", "{#SIZE}": "28" },
      { "{#HOSTNAME}": "host5", "{#SIZE}": "17" }
   ]
}

---

From your tabular data (the two simple columns), you can use Miller (mlr) to create the initial array and then the same jq or jtc command to insert it all under the data key:

$ mlr --n2j -S label '{#HOSTNAME},{#SIZE}' file | jtc -T '{ "data": {{}} }' -tc
{
   "data": [
      { "{#HOSTNAME}": "host1", "{#SIZE}": "28" },
      { "{#HOSTNAME}": "host2", "{#SIZE}": "13" },
      { "{#HOSTNAME}": "host3", "{#SIZE}": "79" },
      { "{#HOSTNAME}": "host4", "{#SIZE}": "28" },
      { "{#HOSTNAME}": "host5", "{#SIZE}": "17" }
   ]
}

I use --n2j as a shorthand for --inidx and --ojson ("read toolbox indexed formatted data, write JSON"), and I added -S to stop Miller from inferring that the size data should be integers (and instead tread it as strings).