Add carriage return if field does not exist in awk

Question

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'

Answer 1

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.

Answer 2

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.

Answer 3

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.