Table records to convert into JSON file in unix with jq

Question

I have table on a Linux machine which has a number of records; I am running a query to get:

select * from TABNAME_XYZ


CID CN    XY     NAT   UIC    DATE        Region
12  2123  120.9  29.0  100.0  2018-06-08  JAIPUR
13  0987  78.9   100.3 28.8   2020-12-09  DELHI

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

{"CID":"12","CN":"2123","DATA":{"XY":120.9,"NAT":29.0,"UIC":100.0,"Date":"2018-06-08","REGION":"JAIPUR"}},
{"CID":"13","CN":"0987","DATA":{"XY":78.9,"NAT":100.3,"UIC":28.8,"Date":"2020-12-09","REGION":"DELHI"}}

I already have jq on my system.

Answer 1

Assuming that the fields are always in the given order, that there is a header line on the first line of input, and that multiple space characters delimit fields, you may compress the runs of consecutive spaces with tr and parse the data with jq.

database-client-command |
tr -s ' ' |
jq -c -Rn '
        input  | split(" ") as $head |
        inputs | split(" ") |
                to_entries |
                        map(.key = $head[.key]) |
                        [ .[:2][], { key: "DATA", value: (.[2:] | from_entries) } ] |
                from_entries'

The jq expression reads the raw data from tr as separate lines.

The first line is split into the headers and stored into an array in $head.

We split the remaining lines into arrays as we did with the header. The to_entries filter converts each array into "entry form" (collections of objects with key and value keys), and the map() replaces the numerical array indexes with the headers from $head as keys.

After the map(), the filter rearranges the array the third element on are moved down into a separate DATA sub-object and converted back from "entry form".

When done with the keys and data rearrangement, the from_entries filter returns the array from the "entry form".

The output of the script would be a set of JSON objects, and given the data in your question, these would look as follows.

{"CID":"12","CN":"2123","DATA":{"XY":"120.9","NAT":"29.0","UIC":"100.0","DATE":"2018-06-08","Region":"JAIPUR"}}
{"CID":"13","CN":"0987","DATA":{"XY":"78.9","NAT":"100.3","UIC":"28.8","DATE":"2020-12-09","Region":"DELHI"}}

If you want to change Region into REGION and DATE into Date, then consider doing this as you query the database or as a post-processing step.

Note that your expected result is not valid JSON due to the trailing comma on the first line.

Answer 2

Assuming you don't REALLY want to convert DATE to Date and Region to REGION (if you do it's an easy tweak once you explain the logic for selecting which tags to change) and that you really do want a , at the end of every output line except the last (again, an easy tweak if you don't), then using any awk in any shell on every Unix box:

$ cat tst.awk
NR==1 {
    split($0,tags)
    next
}
{
    printf "%s{%s,%s,\"DATA\":{", sep, fmt(1), fmt(2)
    for (i=3; i<=NF; i++) {
        printf "%s%s", fmt(i), (i<NF ? "," : "}}")
    }
    sep = ",\n"
}
END {
    print ""
}
function fmt(fldNr,     tag, val) {
    tag = tags[fldNr]
    val = $fldNr
    gsub(/"/,"\\\"",val)
    return sprintf("\"%s\":\"%s\"", tag, val)
}

$ awk -f tst.awk file
{"CID":"12","CN":"2123","DATA":{"XY":"120.9","NAT":"29.0","UIC":"100.0","DATE":"2018-06-08","Region":"JAIPUR"}},
{"CID":"13","CN":"0987","DATA":{"XY":"78.9","NAT":"100.3","UIC":"28.8","DATE":"2020-12-09","Region":"DELHI"}}

Answer 3

Here's one way. Save the following as foo.awk:

{
  if(NR==1){
    for(i=1;i<=NF;i++){
      head[i]=$i
    }
  }
  else{
    printf "{\"%s\":\"%s\",\"%s\":\"%s\",\"DATA\":{\"%s\":%s,\"%s\":%s,\"%s\":%s,\"%s\":\"%s\",\"%s\":\"%s\"}}\n", head[1],$1,head[2],$2,head[3],$3,head[4],$4,head[5],$5,head[6],$6,head[7],$7;

  }
}

And then:

$ cat file |  awk -f foo.awk
{"CID":"12","CN":"2123","DATA":{"XY":120.9,"NAT":29.0,"UIC":100.0,"DATE":"2018-06-08","Region":"JAIPUR"}}
{"CID":"13","CN":"0987","DATA":{"XY":78.9,"NAT":100.3,"UIC":28.8,"DATE":"2020-12-09","Region":"DELHI"}}

Here, I have saved the output of your select command in the file file. In your case, you would use whatever command line client your database has. for instance, with mySQL, you would:

mysql -e 'select * from TABNAME_XYZ' | awk -f foo.awk