Revisions to Table records to convert into JSON file in unix with jq

Rollback to Revision 1

Source Link

edited Oct 7, 2021 at 9:25

41.9k
17
75
118

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

{
  if(NR==1){
    for(i=1;i<=NF;i++){
      head[q i q]=q $i qhead[i]=$i
    }
  }
  else{
    printf "{%s\"%s\":%s\"%s\",%s\"%s\":%s\"%s\",\"DATA\":{%s\"%s\":%s,%s\"%s\":%s,%s\"%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 -v q='"' -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 -v q='"' -f foo.awk

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

{
  if(NR==1){
    for(i=1;i<=NF;i++){
      head[q i q]=q $i q
    }
  }
  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 -v q='"' -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 -v q='"' -f foo.awk

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

deleted 14 characters in body

Source Link

edited Oct 7, 2021 at 9:19

αғsнιη

41.9k
17
75
118

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

{
  if(NR==1){
    for(i=1;i<=NF;i++){
      head[i]=$ihead[q i q]=q $i q
    }
  }
  else{
    printf "{\"%s\"%s:\"%s\"%s,\"%s\"%s:\"%s\"%s,\"DATA\":{\"%s\"%s:%s,\"%s\"%s:%s,\"%s\"%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 -v q='"' -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 -v q='"' -f foo.awk

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

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

{
  if(NR==1){
    for(i=1;i<=NF;i++){
      head[q i q]=q $i q
    }
  }
  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 -v q='"' -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 -v q='"' -f foo.awk

Source Link

answered Oct 7, 2021 at 9:12

terdon ♦

252.7k
69
481
719

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

Stack Exchange Network

Return to Answer