1

I have to extract data from a slightly mis-formatted JSON string, hence I first pass it through sed & awk. What I have is a command like:

`sed 's/},/},\n/g' test.json |awk '/"characater"/ { gsub("\"characater\"", "\"char" ++n "\"", $0) } 1'| jq -r '.frames.frame.lps.lp|.characters[]|[.code_ascii,.confidence]|@tsv'` 

to extract data from a JSON string that can be seen here:

{"response":{"container":{"id":"41d6efcb-24d6-490d-8880-762255519b5f","timestamp":"2018-Jul-11 19:51:06.461665"},"id":"00000002-0000-0000-0000-000000000015"},"frames":{"frame":{"id":"5583","timestamp":"2016-Nov-30 13:05:27","lps":{"lp":{"licenseplate":"15451BBL","text":"15451BBL","wtext":"15451BBL","confidence":"20","bkcolor":"16777215","color":"16777215","type":"0","ntip":"11","cct_country_short":"","cct_state_short":"","tips":{"tip":{"poly":{"p":{"x":"1094","y":"643"},"p":{"x":"1099","y":"643"},"p":{"x":"1099","y":"667"},"p":{"x":"1094","y":"667"}},"bkcolor":"16777215","color":"0","code":"49","code_ascii":"1","confidence":"97"},"tip":{"poly":{"p":{"x":"1103","y":"642"},"p":{"x":"1113","y":"642"},"p":{"x":"1112","y":"667"},"p":{"x":"1102","y":"667"}},"bkcolor":"16777215","color":"0","code":"53","code_ascii":"5","confidence":"89"},"tip":{"poly":{"p":{"x":"1112","y":"640"},"p":{"x":"1122","y":"640"},"p":{"x":"1122","y":"666"},"p":{"x":"1112","y":"666"}},"bkcolor":"16777215","color":"0","code":"52","code_ascii":"4","confidence":"97"},"tip":{"poly":{"p":{"x":"1123","y":"640"},"p":{"x":"1132","y":"640"},"p":{"x":"1131","y":"665"},"p":{"x":"1123","y":"665"}},"bkcolor":"16777215","color":"0","code":"53","code_ascii":"5","confidence":"97"},"tip":{"poly":{"p":{"x":"1134","y":"640"},"p":{"x":"1139","y":"640"},"p":{"x":"1139","y":"664"},"p":{"x":"1133","y":"664"}},"bkcolor":"16777215","color":"0","code":"49","code_ascii":"1","confidence":"77"},"tip":{"poly":{"p":{"x":"1154","y":"639"},"p":{"x":"1163","y":"639"},"p":{"x":"1163","y":"663"},"p":{"x":"1153","y":"663"}},"bkcolor":"16777215","color":"0","code":"66","code_ascii":"B","confidence":"97"},"tip":{"poly":{"p":{"x":"1164","y":"638"},"p":{"x":"1173","y":"638"},"p":{"x":"1173","y":"663"},"p":{"x":"1163","y":"663"}},"bkcolor":"16777215","color":"0","code":"66","code_ascii":"B","confidence":"94"},"tip":{"poly":{"p":{"x":"1191","y":"637"},"p":{"x":"1206","y":"636"},"p":{"x":"1205","y":"660"},"p":{"x":"1190","y":"661"}},"bkcolor":"16777215","color":"0","code":"76","code_ascii":"L","confidence":"34"},"tip":{"poly":{"p":{"x":"1103","y":"655"},"p":{"x":"1111","y":"655"},"p":{"x":"1111","y":"667"},"p":{"x":"1103","y":"667"}},"bkcolor":"16777215","color":"0","code":"74","code_ascii":"J","confidence":"57"},"tip":{"poly":{"p":{"x":"1103","y":"655"},"p":{"x":"1111","y":"655"},"p":{"x":"1111","y":"667"},"p":{"x":"1103","y":"667"}},"bkcolor":"16777215","color":"0","code":"74","code_ascii":"J","confidence":"57"},"tip":{"poly":{"p":{"x":"1176","y":"638"},"p":{"x":"1185","y":"637"},"p":{"x":"1184","y":"661"},"p":{"x":"1175","y":"662"}},"bkcolor":"16777215","color":"0","code":"52","code_ascii":"4","confidence":"7"}},"ncharacter":"8","characters":{"characater":{"poly":{"p":{"x":"1094","y":"643"},"p":{"x":"1099","y":"643"},"p":{"x":"1099","y":"667"},"p":{"x":"1094","y":"667"}},"bkcolor":"16777215","color":"0","code":"49","code_ascii":"1","confidence":"97"},"characater":{"poly":{"p":{"x":"1103","y":"642"},"p":{"x":"1113","y":"642"},"p":{"x":"1112","y":"667"},"p":{"x":"1102","y":"667"}},"bkcolor":"16777215","color":"0","code":"53","code_ascii":"5","confidence":"89"},"characater":{"poly":{"p":{"x":"1112","y":"640"},"p":{"x":"1122","y":"640"},"p":{"x":"1122","y":"666"},"p":{"x":"1112","y":"666"}},"bkcolor":"16777215","color":"0","code":"52","code_ascii":"4","confidence":"97"},"characater":{"poly":{"p":{"x":"1123","y":"640"},"p":{"x":"1132","y":"640"},"p":{"x":"1131","y":"665"},"p":{"x":"1123","y":"665"}},"bkcolor":"16777215","color":"0","code":"53","code_ascii":"5","confidence":"97"},"characater":{"poly":{"p":{"x":"1134","y":"640"},"p":{"x":"1139","y":"640"},"p":{"x":"1139","y":"664"},"p":{"x":"1133","y":"664"}},"bkcolor":"16777215","color":"0","code":"49","code_ascii":"1","confidence":"77"},"characater":{"poly":{"p":{"x":"1154","y":"639"},"p":{"x":"1163","y":"639"},"p":{"x":"1163","y":"663"},"p":{"x":"1153","y":"663"}},"bkcolor":"16777215","color":"0","code":"66","code_ascii":"B","confidence":"97"},"characater":{"poly":{"p":{"x":"1164","y":"638"},"p":{"x":"1173","y":"638"},"p":{"x":"1173","y":"663"},"p":{"x":"1163","y":"663"}},"bkcolor":"16777215","color":"0","code":"66","code_ascii":"B","confidence":"94"},"characater":{"poly":{"p":{"x":"1191","y":"637"},"p":{"x":"1206","y":"636"},"p":{"x":"1205","y":"660"},"p":{"x":"1190","y":"661"}},"bkcolor":"16777215","color":"0","code":"76","code_ascii":"L","confidence":"34"}},"det_time_us":"1072592","poly":{"p":{"x":"1088","y":"642"},"p":{"x":"1210","y":"634"},"p":{"x":"1210","y":"661"},"p":{"x":"1087","y":"669"}}}},"det_time_us":"1720812"}}}

or on this link: https://drive.google.com/file/d/18wCzjMBpw7SIeVFByAGPQiqCBjg_0te3/view?usp=sharing
Now, that works fine but what I need is, to extract the .frames.frame.lps.lp.ncharacter from the JSON, too. I know I could simply do something like cat test.json | jq -r '.frames.frame.lps.lp.ncharacter'; in front of the above but that won't work as I need these commands to parse a huge file of JSON strings that are formatted as seen on the link and I need the .ncharacter parameter to show up in line with the extracted chars what means I would like to have an output like:

...
X       99
Y       99 previous data formatted in the same way
8
1       97
5       89
4       97
5       97
1       77
B       97
B       94
L       34
6          following data formatted in the same way
Z       99
...

Where the 8 on top is the .ncharacter parameter. I have tried:

sed 's/},/},\n/g' test.json |awk '/"characater"/ { gsub("\"characater\"", "\"char" ++n "\"", $0) } 1'| jq -r '[.frames.frame.lps.lp.ncharacter],.frames.frame.lps.lp|.characters[]|[.code_ascii,.confidence]|@tsv'

but that gives me jq: error (at <stdin>:102): Cannot index array with string "characters" and I'm not sure why that is...

2

Check this:

First variant

perl -pe 's/"characater"/"\"char" . (++$n) . "\""/ge' input.json |
jq -r '.frames.frame.lps.lp|.ncharacter,(.characters[]|[.code_ascii,.confidence]|@tsv)'

Explanation

  1. perl -pe 's/"characater"/"\"char" . (++$n) . "\""/ge' input.json

    • -p - loop through each line and print, like sed.
    • -e - May be used to enter one line of program. If -e is given, Perl will not look for a filename in the argument list.
    • s///ge - g: global substitution, e: evaluate the right part of the substitution command as an expression.
    • "\"char" . (++$n) . "\"" - dots are used for concatenation.
  2. jq -r '.frames.frame.lps.lp|.ncharacter,(.characters[]|[.code_ascii,.confidence]|@tsv)'

    • .frames.frame.lps.lp| - it can be written as the .frames | .frame | .lps | .lp |, so it works this way: take input, pick all frames fields and pipe them to the another filter - .frame, then take all frame fields and pipe them to the next filter - .lps, so on. See jq Manual, the Pipe section.
    • |.ncharacter,(.characters[]|...)' - jq Manual, the Comma section: "If two filters are separated by a comma, then the same input will be fed into both and the two filters’ output value streams will be concatenated in order: first, all of the outputs produced by the left expression, and then all of the outputs produced by the right. For instance, filter .foo, .bar, produces both the “foo” fields and “bar” fields as separate outputs."
    • (.characters[]|[.code_ascii,.confidence]|@tsv) - parenthesis are used for processing .characters[]'s output separately from the .ncharacter filter output.

Second variant - using gawk instead perl for json file fixing, the jq part the same as in the first variant:

gawk '{ORS= (RT) ? "\"char" NR "\"" : ""; print}' RS='"characater"' input.json

Note - the perl and gawk commands don't reset the char block's counter every frame. That is, it starts from char1 in the beginning and incrementing it to the end.

Input - your sample repeated by three times.

Output

8
1   97
5   89
4   97
5   97
1   77
B   97
B   94
L   34
8
1   97
5   89
4   97
5   97
1   77
B   97
B   94
L   34
8
1   97
5   89
4   97
5   97
1   77
B   97
B   94
L   34

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.