Extract key value pair data from avro file into a rows and column form using unix command/shell script

Question

I have a file with following dataset.

{"table":{"string":"ABC"} "TYPE":{"string":"U"} "TS":{"string":"2019-08-19 12:45:12.006362"} "C_TS":{"string":"2019-08-19 07:45:13.631000"} "dpt":{"string":"12345"} "ID":{"string":"123456789"} "FUC":{"string":"ABC"} "QDSA":{"string":"0"} "SVCD":{"string":"S"} }

Expecting the output in below format. output expected :

table|type|TS|C_TS|dpt|ID|FUC|QDSA|SVCD

ABC|U|2019-08-19 12:45:12.006362|2019-08-19 07:45:13.631000|12345|123456789|ABC|0|S|

Answer 1

Tried with below command and it worked fine

command

awk -F ":" '{print $3,$NF}' p.txt| sed 's/"\}*//g'| sed "s/,NAME/|/g"| sed -r "s/\s+//g"| sed '1i table|NAME'

output

table|NAME
ABC|A

ABC|A

ABC|B

ABC|A

ABC|B

Answer 2

$ { echo 'table|NAME'; jq -r '"\(.table.string)|\(.NAME.string)"' file; }
table|NAME
ABC|A
ABC|A
ABC|B
ABC|A
ABC|B

This uses jq to parse out the data from each JSON object, creating a string for each output line that contains the table name and NAME string found in the object.

The header is created using a simple echo.

Answer 3

Another jq answer:

{ echo 'table|NAME'; <file jq -r 'map(.string)|join("|")'; }

which takes advantage of map function for combining both table and NAME object, and join to concatenate both object string string.

---

Based on the last OP's question update, you would need this command (requires jq version >=1.5):

<file jq -r 'keys_unsorted,map(.string)|join("|")'