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:


I already have jq on my system.

  • What database engine are you using?
    – Kusalananda
    Oct 7, 2021 at 8:16
  • i can query the same table on oracle sql developer but need to generate this query via shell script only;; i can also run sql statement on unix command line but it gives general output without any line..it only contain the blank spaces between the records
    – user439643
    Oct 7, 2021 at 8:32
  • 1
    does this helps stackoverflow.com/q/23560996/4023950? Oct 7, 2021 at 8:53
  • 1
    If you REALLY want to convert DATE to Date and Region to REGION then edit your question to explain the logic around which fields to select to change the case of. If you don't really want that then edit your question to fix your example.
    – Ed Morton
    Oct 7, 2021 at 10:41
  • Are you sure that , should be present at the end of the first row of expected output? That doesn't appear to be valid JSON.
    – Ed Morton
    Oct 7, 2021 at 11:24

3 Answers 3


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

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.


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.


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 {
    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"
    print ""
function fmt(fldNr,     tag, val) {
    tag = tags[fldNr]
    val = $fldNr
    return sprintf("\"%s\":\"%s\"", tag, val)

$ awk -f tst.awk file

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

    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

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
  • but you still need to care about fields containing double quotes to escape them too, simple gsub(/"/,"\\"") might do that. Oct 7, 2021 at 9:23
  • It would be better to let the database output JSON. Many database engines can do this. I have no clue about Oracle.
    – Kusalananda
    Oct 7, 2021 at 9:29
  • @they Oracle can according to the SO link that αғsнιη left in a comment: Return results of a sql query as JSON in oracle 12c. But it looks like something needs to be installed and the OP requested a shell solution.
    – terdon
    Oct 7, 2021 at 9:31

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .