Today (Saturday) We will make some minor tuning adjustments to MySQL.

You may experience 2 up to 10 seconds "glitch time" when we restart MySQL. We expect to make these adjustments around 1AM Eastern Daylight Saving Time (EDT) US.


JSON structure to table form in awk, bash


Login or Register to Reply

 
Thread Tools Search this Thread
# 1  
JSON structure to table form in awk, bash

Hello guys,

I want to parse a JSON file in order to get the data in a table form.

My JSON file is like this:
Code:
{
   "document":{
      "page":[
         {
            "@index":"0",
            "image":{
               "@data":"ABC",
               "@format":"png",
               "@height":"620.00",
               "@type":"base64encoded",
               "@width":"450.00",
               "@x":"85.00",
               "@y":"85.00"
            }
         },
         {
            "@index":"1",
            "row":[
               {
                  "column":[
                     {
                        "text":""
                     },
                     {
                        "text":{
                           "#text":"Text1",
                           "@fontName":"Arial",
                           "@fontSize":"12.0",
                           "@height":"12.00",
                           "@width":"71.04",
                           "@x":"121.10",
                           "@y":"83.42"
                        }
                     }
                  ]
               },
               {
                  "column":[
                     {
                        "text":""
                     },
                     {
                        "text":{
                           "#text":"Text2",
                           "@fontName":"Arial",
                           "@fontSize":"12.0",
                           "@height":"12.00",
                           "@width":"101.07",
                           "@x":"121.10",
                           "@y":"124.82"
                        }
                     }
                  ]
               }
            ]
         },
         {
            "@index":"2",
            "row":[
               {
                  "column":{
                     "text":{
                        "#text":"Text3",
                        "@fontName":"Arial",
                        "@fontSize":"12.0",
                        "@height":"12.00",
                        "@width":"363.44",
                        "@x":"85.10",
                        "@y":"69.62"
                     }
                  }
               },
               {
                  "column":{
                     "text":{
                        "#text":"Text4",
                        "@fontName":"Arial",
                        "@fontSize":"12.0",
                        "@height":"12.00",
                        "@width":"382.36",
                        "@x":"85.10",
                        "@y":"83.42"
                     }
                  }
               },
               {
                  "column":{
                     "text":{
                        "#text":"Text5",
                        "@fontName":"Arial",
                        "@fontSize":"12.0",
                        "@height":"12.00",
                        "@width":"435.05",
                        "@x":"85.10",
                        "@y":"97.22"
                     }
                  }
               }
            ]
         },
         {
            "@index":"3"
         }
      ]
   }
}

I've been trying with awk doing like below, but I get an output far from what I´d like:
Code:
awk -F: '
        /"#text"/      {a01=$2}
        /"@data"/      {a02=$2}
        /"@fontName"/  {a03=$2}
        /"@fontSize"/  {a04=$2}
        /"@fontStyle"/ {a05=$2}
        /"@format"/    {a06=$2}
        /"@height"/    {a07=$2}
        /"@type"/      {a08=$2}
        /"@width"/     {a09=$2}
        /"@x"/         {a10=$2}
        /"@y"/         {z=a01" "a01" "a02" "a03" "a04" "a05" "a06" "a07" "a08" "a09" "a10" "$2; print z}' input.json

|"85.00",,coded",
|"83.42"",coded",
|"124.82",coded",
|"69.62",,coded",
|"83.42",,coded",
|"97.22",,coded",

I'd like to get an output like this(where NaN is to know that for that parameter there is no value):
Code:
   #text @data @fontName @fontSize @format @height          @type  @width      @x      @y
0    NaN   ABC       NaN       NaN     png  620.00  base64encoded  450.00   85.00   85.00
1  Text1   NaN     Arial      12.0     NaN   12.00            NaN   71.04  121.10   83.42
2  Text2   NaN     Arial      12.0     NaN   12.00            NaN  101.07  121.10  124.82
3  Text3   NaN     Arial      12.0     NaN   12.00            NaN  363.44   85.10   69.62
4  Text4   NaN     Arial      12.0     NaN   12.00            NaN  382.36   85.10   83.42
5  Text5   NaN     Arial      12.0     NaN   12.00            NaN  435.05   85.10   97.22

May someone help me out with this problem. Thanks
# 2  
Please search the forums for json shell utilities.

This same question has been asked and answered a few times already.

Thanks!
# 3  
Thanks for your answer. Maybe you or someone other expert could show me an example that applies to my case with awk or another specific tool.

Thanks in advance
# 4  
Maybe you can do some of your own reading and study:

Code:
https://stedolan.github.io/jq/

jq is a lightweight and flexible command-line JSON processor.

Seems plenty of examples on the jq site.
# 6  
Small modifications to your awk approach get you pretty close to what you want:

Code:
awk -F: '
BEGIN           {a01 = a02 = a03 = a04 = a05 = a06 = a07 = a08 = a09 = a10 = "NaN"
                 print "#text @data @fontName @fontSize @format @height          @type  @width      @x    @y"
                }
                {gsub (/[",]/,_)
                }
/#text/         {a01=$2}
/@data/         {a02=$2}
/@fontName/     {a03=$2}
/@fontSize/     {a04=$2}
/@fontStyle/    {a05=$2}
/@format/       {a06=$2}
/@height/       {a07=$2}
/@type/         {a08=$2}
/@width/        {a09=$2}
/@x/            {a10=$2}
/@y/            {print CNT++ " " a01 " " a02 " " a03 " " a04 " " a05 " " a06 " " a07 " " a08 " " a09 " " a10 " " $2
                 a01 = a02 = a03 = a04 = a05 = a06 = a07 = a08 = a09 = a10 = "NaN"
                }
'  file
#text @data @fontName @fontSize @format @height          @type  @width      @x    @y
0 NaN ABC NaN NaN NaN png 620.00 base64encoded 450.00 85.00 85.00
1 Text1 NaN Arial 12.0 NaN NaN 12.00 NaN 71.04 121.10 83.42
2 Text2 NaN Arial 12.0 NaN NaN 12.00 NaN 101.07 121.10 124.82
3 Text3 NaN Arial 12.0 NaN NaN 12.00 NaN 363.44 85.10 69.62
4 Text4 NaN Arial 12.0 NaN NaN 12.00 NaN 382.36 85.10 83.42
5 Text5 NaN Arial 12.0 NaN NaN 12.00 NaN 435.05 85.10 97.22

These 2 Users Gave Thanks to RudiC For This Post:
# 7  
Quote:
Originally Posted by RudiC
Small modifications to your awk approach get you pretty close to what you want:

Code:
awk -F: '
BEGIN           {a01 = a02 = a03 = a04 = a05 = a06 = a07 = a08 = a09 = a10 = "NaN"
                 print "#text @data @fontName @fontSize @format @height          @type  @width      @x    @y"
                }
                {gsub (/[",]/,_)
                }
/#text/         {a01=$2}
/@data/         {a02=$2}
/@fontName/     {a03=$2}
/@fontSize/     {a04=$2}
/@fontStyle/    {a05=$2}
/@format/       {a06=$2}
/@height/       {a07=$2}
/@type/         {a08=$2}
/@width/        {a09=$2}
/@x/            {a10=$2}
/@y/            {print CNT++ " " a01 " " a02 " " a03 " " a04 " " a05 " " a06 " " a07 " " a08 " " a09 " " a10 " " $2
                 a01 = a02 = a03 = a04 = a05 = a06 = a07 = a08 = a09 = a10 = "NaN"
                }
'  file
#text @data @fontName @fontSize @format @height          @type  @width      @x    @y
0 NaN ABC NaN NaN NaN png 620.00 base64encoded 450.00 85.00 85.00
1 Text1 NaN Arial 12.0 NaN NaN 12.00 NaN 71.04 121.10 83.42
2 Text2 NaN Arial 12.0 NaN NaN 12.00 NaN 101.07 121.10 124.82
3 Text3 NaN Arial 12.0 NaN NaN 12.00 NaN 363.44 85.10 69.62
4 Text4 NaN Arial 12.0 NaN NaN 12.00 NaN 382.36 85.10 83.42
5 Text5 NaN Arial 12.0 NaN NaN 12.00 NaN 435.05 85.10 97.22

Thanks so much RudyC. It works pretty nice.
Login or Register to Reply

|
Thread Tools Search this Thread
Search this Thread:
Advanced Search

More UNIX and Linux Forum Topics You Might Find Helpful
Extract hive table structure
rohit_shinez
Hi, I need to extract only the create table structure with columns alone. for eg hive_table show create table hive_table: create table hive_table(id number,age number) OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat' LOCATION 'hdfs:/path/' I need only below ...... Shell Programming and Scripting
5
Shell Programming and Scripting
Getting data in table form
Vivekit82
Hi, I have a csv file from which i am fetching few columns as below: IFILE=/home/home1/Report1.csv OFILE=/home/home1/`date +"%m%d%y%H%M%S"`.dat if #Checks if file exists and readable then awk -F "," '(NR>4) {print $1,$6,$2,$3,$4,$5,$6}' ${IFILE} >> ${OFILE} fi cat $OFILE | mail...... Shell Programming and Scripting
7
Shell Programming and Scripting
Help in copying table structure to another table with constraints in Oracle
senkerth
hi, i need to copy one table with data into another table, right now am using create table table1 as select * from table2 i want the constraints of table1 to be copied to table2 also , can anyone give me some solution to copy the constraints also, now am using oracle 10.2.0.3.0...... UNIX and Linux Applications
1
UNIX and Linux Applications
How to create a C structure using table description.
ehari
There is table 'DEPT' in the database with the following desciption: Name Null? Type ------- -------- ------------------------ DEPTNO NOT NULL NUMBER(2) DNAME NULL VARCHAR2(14) LOC NULL VARCHAR2(13) Using shell script, I need to create a structure for the...... Shell Programming and Scripting
2
Shell Programming and Scripting
help on formatting output (Table Form)
blurboy
Data in File ABC:DEFGHI:123 ABCZYE:DEFI:123 ABCFGD:DEF:123 ABCEERRRRR:DEFGHI:123 Expected Format 1 ABC DEFGHIFE 123 2 ABCZYE DEFI 123 3 ABCFGD DEF 123 4 ABCEERRRRR DEFGHI 123 However when i enter the following...... Shell Programming and Scripting
2
Shell Programming and Scripting

Featured Tech Videos