如何通过shell语句解析某一列的json并输出里面字段

Question

怎么编写shell语句，将数据中的某一列json格式的数据提取解析出来，json里面数据的顺序不固定，需要按照第一条那样的顺序输出，并追加在对应的那条数据后面。尽量用shell语言实现，如果有其他简便方法也可以

原始数据内容如下：
335970 | 115 | {"traceid":"0421000841sgm6p","pixeldata":-13075235,"pixelcoordinate":"703,160","pixelabnormaldata":0,"collectedpixeldata":"703,160"}

335971 | 116 | {"pixeldata":"-12325235","traceid":0421000666sgm6p,"pixelcoordinate":"733,144","pixelabnormaldata":1,"collectedpixeldata":"333,132"}

335972 | 117 | {"pixelabnormaldata":"0","traceid":0231000666sgm6p,"pixelcoordinate":"723,124","pixeldata":-12325235,"collectedpixeldata":"333,132"}

结果数据：
335970 | 115 | 0421000841sgm6p | -13075235 | 703,160 | 0 | 703,160

335971 | 116 | 0421000666sgm6p | -12325235 | 733,144 | 1 | 333,132

335972 | 117 | 0231000666sgm6p | -12325235 | 723,124 | 0 | 333,132

Answer 1

使用命令：

awk -F "|"  -vOFS="|"  '{l=split($3,arr,",\"");$3="";traceid="";pixeldata=0;pixelcoordinate="";pixelabnormaldata="";collectedpixeldata="";for(i=1;i<=l;i++){gsub(/ /,"",arr[i]);gsub(/\"/,"",arr[i]);gsub(/\}/,"",arr[i]);gsub(/\{/,"",arr[i]);ll=split(arr[i],arr2,":"); if(arr2[1]=="traceid") traceid=arr2[2];if(arr2[1]=="pixeldata") pixeldata=arr2[2];if(arr2[1]=="pixelcoordinate") pixelcoordinate=arr2[2];if(arr2[1]=="pixelabnormaldata") pixelabnormaldata=arr2[2];if(arr2[1]=="collectedpixeldata") collectedpixeldata=arr2[2];} ;$3=traceid"|"pixeldata"|"pixelcoordinate"|"pixelabnormaldata"|"collectedpixeldata;print}' ./data.log

执行结果为：

过程分解：主要就是在循环之前，对字符串替换掉无用字符；然后对二次拆分的 key 比对，得到各个 key 的值，最后按顺序拼接起来就可以了。

awk -F "|"  -vOFS="|"  '{l=split($3,arr,",\"");
$3="";
traceid="";
pixeldata=0;
pixelcoordinate="";
pixelabnormaldata="";
collectedpixeldata="";
for(i=1;i<=l;i++){
gsub(/ /,"",arr[i]);gsub(/\"/,"",arr[i]);gsub(/\}/,"",arr[i]);gsub(/\{/,"",arr[i]);
ll=split(arr[i],arr2,":"); 
if(arr2[1]=="traceid") traceid=arr2[2];
if(arr2[1]=="pixeldata") pixeldata=arr2[2];
if(arr2[1]=="pixelcoordinate") pixelcoordinate=arr2[2];
if(arr2[1]=="pixelabnormaldata") pixelabnormaldata=arr2[2];
if(arr2[1]=="collectedpixeldata") collectedpixeldata=arr2[2];
} ;$3=traceid"|"pixeldata"|"pixelcoordinate"|"pixelabnormaldata"|"collectedpixeldata;print}' ./data.log