0

I have this query:
select hostname,size from tableinfo
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 convert its output to json but i really dont 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 Jan 19 at 10:43
  • @Kusalananda not with this format. it only produce it in a way that only db can use – BlackCrystal Jan 19 at 11:02
  • The required keys are strange "{#HOSTNAME}" ... {#SIZE}. Any options to have a more readable keys? – RomanPerekhrest Jan 19 at 11:03
  • @BlackCrystal PostgreSQL, for example, is a database engine that can both read and write JSON. – Kusalananda Jan 19 at 11:05
  • 1
    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 Jan 19 at 13:03
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"
    }
  ]
}
  • thanks it will work but i'm not allowed to install extra package on any system. – BlackCrystal Jan 19 at 11:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.