0

I am using

awk -F'[":]' '$2=="id"{printf("pri,%s,",$5)}$2=="name"{printf("%s,",$5)}$2=="objectId"{printf$4}$2=="polledName"{print$5}' | sed -e 's/, /,/g'

Which turns this

  }, {
    "id" : "1",
    "name" : "host1",
    "objectId" : 0001,
    "polledName" : "192.168.1.1"
  }, {
    "id" : "2",
    "name" : "host2",
    "objectId" : 0002,
    "polledName" : "192.168.1.2"
  }, {
    "id" : "3",
    "name" : "host3",
    "objectId" : 0003,
  }, {
    "id" : "4",
    "name" : "host4",
    "objectId" : 0004,
    "polledName" : "192.168.1.3"
  }, {

Into this

pri,1,host1,0001,192.168.1.1
pri,2,host2,0002,192.168.1.2
pri,3,host3,0003,pri,4,host4,0004,192.168.1.3

Any idea how this could be amended so that when the entry for polledName does not exist, it goes to the next line rather than wrapping to the current line, i.e. if $5 returns nothing then add a newline instead.


Here's the above code with the awk script pretty-printed by gawk -o- so it's legible:

awk -F'[":]' '
    $2 == "id" {
            printf "pri,%s,", $5
    }
    
    $2 == "name" {
            printf "%s,", $5
    }
        
    $2 == "objectId" {
            printf $4
    }
    
    $2 == "polledName" {
            print $5
    }
' | sed -e 's/, /,/g'
5
  • 2
    "carriage return" is not the line delimiter on Unix. It's "newline" aka "linefeed" instead. May 4 at 11:03
  • 2
    Would it be possible for you to post a more complete JSON document? It would be trivial to do this using any JSON parser.
    – Kusalananda
    May 4 at 11:15
  • 3
    Your file is json, so why not just use jq? or miller
    – cas
    May 4 at 11:25
  • Unfortunately our servers are pretty locked down so restricted to tools supplied out of the box
    – Cisco_Dave
    May 4 at 12:39
  • 2
    Apply to your Change Board for the appropriate tools to be installed. No-one should be using a hammer to tighten a screw. (Or a screwdriver to hammer a nail.)
    – roaima
    May 4 at 22:10

3 Answers 3

2

For anyone that does have access to the proper tools for working with JSON on the command line, this is how you may extract the fields into a quoted CSV data set using jq:

$ jq -r '.[] | [ "pri", .id, .name, .objectId, .polledName ] | @csv' file
"pri","1","host1",1,"192.168.1.1"
"pri","2","host2",2,"192.168.1.2"
"pri","3","host3",3,
"pri","4","host4",4,"192.168.1.3"

This assumes that the data displayed in the question is part of a top-level array and that it is correctly formatted (the 3rd element in the question contains an invalid trailing comma):

[
    {"id":"1","name":"host1","objectId":1,"polledName":"192.168.1.1"},
    {"id":"2","name":"host2","objectId":2,"polledName":"192.168.1.2"},
    {"id":"3","name":"host3","objectId":3},
    {"id":"4","name":"host4","objectId":4,"polledName":"192.168.1.3"}
]

If you want a quoted empty string in place of nothing for the missing .polledName value, change .polledName in the jq expression to .polledName // "". This will use the empty string rather than a null value if the key is not available (or if its value is null).

Change @csv to the @tsv output operator to get tab-delimited values.

The benefit of using a JSON-aware tool for doing this is that you'll get decoded strings in the output rather than JSON-encoded data. Also, embedded quotes etc., are handled correctly automatically, and it does not matter if the JSON input is on a single line or formatted in some other special way.

0

if you realy must use awk, have a variable set/unset for polledName

 awk -F'[":]' '$2 == "id" {if(lf) print "" ; printf("pri,%s,",$5); lf=1;}
               $2 == "name" {printf("%s,",$5)}
               $2 == "objectId" {gsub(" ","",$4); printf "%s", $4}
               $2 == "polledName" {print $5; lf=0; }
               END {if(lf) print "" ;}' 

this is basically your code a bit expanded, what I add :

  • line with "id" if(lf) print "" ; print a new line, if lf not zero ; lf=1 : set lf
  • optional line with "polledName" : lf=0; clear lf when line is encountered.
  • as per comment using print "" (note that print without argument print current/last line)
  • added gsub(" ","",$4); to strip space from $4 (space are kept as separator is non space)

Beware that awk is a poor solution to parse json (or xml) file.

You relay on program producing this json file, field order may change, especially if you are in a closed server or appliance.

5
  • Unfortunately get the error 'ran out for this one' $ cat test.txt | awk -F'[":]' '$2 == "id" {if(lf) print "" ; printf("pri,%s,",$5); lf=1;} > $2 == "name" {printf("%s,",$5)} > $2 == "objectId" {printf "%s" $4} > $2 == "polledName" {print $5; lf=0; } > END {if(lf) print "" ;}' pri,1,host1,awk: cmd. line:3: (FILENAME=- FNR=4) fatal: not enough arguments to satisfy format string `%s 0001,' ^ ran out for this one
    – Cisco_Dave
    May 4 at 14:09
  • 1
    Change printf "%s" $4 to printf "%s", $4
    – Ed Morton
    May 4 at 14:23
  • Yes that works now Ed, thanks! Just an extra space to remove but I'll figure it out. awk -F'[":]' '$2=="id" {if(lf) print "" ; printf("pri,%s,",$5);lf=1;}$2=="name"{printf("%s,",$5)}$2=="objectId"{printf"%s",$4}$2=="polledName" {print $5; lf=0; } END {if(lf) print "" ;}' test.txt pri,1,host1, 0001,192.168.1.1 pri,2,host2, 0002,192.168.1.2 pri,3,host3, 0003, pri,4,host4, 0004,192.168.1.3
    – Cisco_Dave
    May 4 at 14:34
  • added gsub(" ","",$4); to strip space from $4 (space are kept as separator is non space)
    – Archemar
    May 4 at 14:40
  • Cool, thanks for the answer Archemar
    – Cisco_Dave
    May 4 at 14:41
0

Using any awk even if the order of lines might vary and/or different or additional lines may be missing:

$ awk -F'(^ *")|("?,?$)|(" : "?)' -v OFS=',' '
    /}, {/ {
        if ( NR>1 ) {
            print "pri", f["id"], f["name"], f["objectId"], f["polledName"]
        }
        delete f
        next
    }
    { f[$2] = $3 }
' file
pri,1,host1,0001,192.168.1.1
pri,2,host2,0002,192.168.1.2
pri,3,host3,0003,
pri,4,host4,0004,192.168.1.3

The above works by creating an array f[] that maps each tag (e.g. objectId) to the associated value (e.g. 002) on the same line every time a tag : value line is read and then printing the contents of that array at the end of every block, i.e. when }, { is seen.

Creating an array of tags-values like this is a far better general approach to this type of problem than printing values as you go as it's agnostic to the order of the tags in the input, you can decide some different order for the output, and you can use the values stored in the array to test conditions and take actions in the current printing block, e.g.

if ( f["objectId"] > 27 ) print "The objectId is too big for name", f["name"]

if ( !("polledName" in f) ) print "polledName missing for ID", f["id"]

if ( f["objectId"] ~ /7/) && (f["id"] !~ /7/) ) {
    printf "objectId %s vs id %s mismatch\n", f["objectId"], f["id"]
}

or whatever real [combinations of] conditions you might be interested in.

Alternatively, using GNU awk for multi-char RS if the order of lines in each block is guaranteed to be consistent across all blocks as shown in your example:

$ awk -v RS='}, {\n' -F'("?,?\n)|(" : "?)' -v OFS=',' '
    NR>1 { print "pri", $2, $4, $6, $8 }
' file
pri,1,host1,0001,192.168.1.1
pri,2,host2,0002,192.168.1.2
pri,3,host3,0003,
pri,4,host4,0004,192.168.1.3

Regarding printf $4 etc. in your code - never do printf input_data as it'll fail if/when the input data contains printf formatting characters, always do printf "%s", input_data instead, e.g. printf "%s", $4.

Also, you never need sed when you're using awk so if you find yourself doing so you have the wrong approach.

4
  • Think this relies on only those fields being present, so doesn't work unfortunately
    – Cisco_Dave
    May 4 at 14:09
  • I assume you didn't try it. Please do rather than assuming it won't work as I don't see any reason why it wouldn't work no matter which fields are/aren't present.
    – Ed Morton
    May 4 at 14:22
  • Did try it on the full JSON Ed but it returned back other fields that i considered erroneous for testing
    – Cisco_Dave
    May 4 at 14:24
  • Are you referring to the first any-awk script in my answer or the second gawk-only script? It's impossible for that first script to fail as you say it does no matter which desired fields are missing or undesired fields are present as long as your input is formatted as you show since print "pri", f["id"], f["name"], f["objectId"], f["polledName"] is hard-coded to ONLY print the desired fields (or null strings if absent) but obviously if your real input has fields you don't want printed then you should include such cases in the sample input/output you provided for us to test with.
    – Ed Morton
    May 4 at 14:31

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.