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

Are you using a database engine that is able to produce JSON output? — Kusalananda
– Kusalananda ♦, Commented Jan 19, 2019 at 10:43
@Kusalananda not with this format. it only produce it in a way that only db can use — BlackCrystal
– BlackCrystal, Commented Jan 19, 2019 at 11:02
The required keys are strange "{#HOSTNAME}" ... {#SIZE}. Any options to have a more readable keys? — RomanPerekhrest
– RomanPerekhrest, Commented Jan 19, 2019 at 11:03
@BlackCrystal PostgreSQL, for example, is a database engine that can both read and write JSON. — Kusalananda
– Kusalananda ♦, Commented Jan 19, 2019 at 11:05
You may want to look at getting the output of the query directly as JSON: ibm.com/support/knowledgecenter/en/ssw_ibm_i_73/sqlp/… — Haxiel
– Haxiel, Commented Jan 19, 2019 at 13:03

RomanPerekhrest · Accepted Answer · 2019-01-19 11:32:07Z

4

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

answered Jan 19, 2019 at 11:32

RomanPerekhrest

30.9k5 gold badges47 silver badges68 bronze badges

thanks it will work but i'm not allowed to install extra package on any system.

BlackCrystal
– BlackCrystal

2019-01-19 11:56:47 +00:00
Commented Jan 19, 2019 at 11:56
@BlackCrystal ask your systems administrators to install it for you

Chris Davies
– Chris Davies

2021-04-16 15:54:21 +00:00
Commented Apr 16, 2021 at 15:54
@roaima I am the administrator. we are not allowed to install any packages before a lot of test. once a package was not compatible with another important packages (not the version) and we ran into disaster.

BlackCrystal
– BlackCrystal

2021-04-18 05:54:16 +00:00
Commented Apr 18, 2021 at 5:54

Add a comment |

Kusalananda · Accepted Answer · 2023-11-10 15:28:28Z

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).

Beware jq, mlr (don't know about jtc) expect UTF-8 encoded JSON which sqlite3 doesn't necessarily produce — Stéphane Chazelas
– Stéphane Chazelas, Commented Nov 11, 2023 at 14:07

Stack Exchange Network

Make JSON from SQL query output

2 Answers 2

You must log in to answer this question.

Linked

Hot Network Questions

Make JSON from SQL query output

2 Answers 2

You must log in to answer this question.

Linked

Related

Hot Network Questions