原作者：戴秋龙

• 解析json格式数据
• 问题概述
• 方案1通过jsonb解析json数据
• 方案2通过jq插件解析json数据

解析json格式数据

接到数据是用json 格式数据存储，存储格式如下：

具体数据格式：

解释  { "containerID": "10000000-10949514",
  "containerName": "XXXX线",
  "containerType": "200000",
  "data": {
    "t_ast_ts_pole": [  
      {
        "maint_group_name": "XXXX",
        "obj_id": "f4db51ed-5XXXXXa7360a",
        "psr_type": 201002,
        "psr_id": "f4db51edCCCe981a7360a",
        "psr_state": "02",
        "city": "1096FA0035D17535E05379FB32000001",
        "line": "10000000-10949514",
        "maint_org": "1096FA0035D17535E05379FB50152125",
        "voltage_level": "0136",
        "start_pole": "10200000-19185423",
        "maint_group": "1096FAXXXX50166089",
        "province_name": "XXX",
        "superior_line": "10110000-1002014762",
        "start_time": 827942400000,
        "last_update_time": 1629723262000,
        "city_name": " XXXXXXXX公司",
        "stop_pole": "10200000-19183500",
        "wire": "10110000-1002014762",
        "province": "10262690",
        "maint_org_name": "XXXX公司本部",
        "name": "XXXXX"
      },
      {
        "maint_group_name": "XXXX",
        "obj_id": "XXX6-f5e296b7c97b",
        "psr_type": 201002,
        "psr_id": "aa210XXXXXXb",
        "psr_state": "02",
        "city": "1096FA0035D17535E05379FB32000001",
        "line": "10000000-10949514",
        "maint_org": "1096FA0035D17535E05379FB50152125",
        "voltage_level": "0136",
        "start_pole": "10200000-19174680",
        "maint_group": "1096FA0035D17535E05379FB50166089",
        "province_name": "国XXXXX司",
        "superior_line": "10110000-1002014762",
        "start_time": 827942400000,
        "last_update_time": 1629723262000,
        "city_name": "XXXXX公司",
        "stop_pole": "10200000-19185423",
        "wire": "10110000-1002014762",
        "province": "10262690",
        "maint_org_name": "XXXX本部",
        "name": "#028XXXX杆"
      }
     .....

问题概述

需要通过给定的格式解析成结构化数据。

方案1通过jsonb解析json数据

解释   with taba as (  
select   json 格式数据  ::jsonb->'data'->'t_ast_ts_pole'    a
）select  jsonb_array_elements(a::jsonb) -> 'obj_id' obj_id , 
        jsonb_array_elements(a::jsonb) -> 'city'  city 
	from taba 
        obj_id                           city 
"f4db51ed-5cdc-4fc7-a507-95e981a7360a"	"1096FA0035D17535E05379FB32000001"
"aa210d28-9d36-4467-abc6-f5e296b7c97b"	"1096FA0035D17535E05379FB32000001"
"9c370ad2-2f15-44d3-91a9-a6b317a9048a"	"1096FA0035D17535E05379FB32000001"
"10100000-2008407858"	"1096FA0035D17535E05379FB32000001"
"10100000-2008414351"	"1096FA0035D17535E05379FB32000001"
"10100000-2008417875"	"1096FA0035D17535E05379FB32000001"
"10100000-2010118356"	"1096FA0035D17535E05379FB32000001"
"10100000-2008411631"	"1096FA0035D17535E05379FB32000001"
"10100000-2008417215"	"1096FA0035D17535E05379FB32000001"
"10100000-2008409471"	"1096FA0035D17535E05379FB32000001"
"10100000-2010118376"	"1096FA0035D17535E05379FB32000001"

带引号的数据另做处理把， 毕竟数据已经入库。后续处理简单的多

方案2通过jq插件解析json数据

下载：https://stedolan.github.io/jq/download/
文本下载1.5版本：
安装：
tar -zvxf jq-1.5.tar.gz
cd jq-1.5/
./configure
make && make install
验证：
代码：

解释  cat data.json | jq '.data' | jq '.t_ast_ts_pole'| jq -r '.[ ]|"\(.equip_code),\(.province),\(.ast_type),\(.rated_voltage_level),\(.model)"'

11M00001614513837,10262690,103000,null,SZT2(4)
11M00001622842273,10262690,103000,null,SZC4（6）
11M00001622842481,10262690,103000,null,ZMCK-54
11M00001800026860,10262690,103000,null,ZM1
11M00001800026863,10262690,103000,null,ZM3
11M00001800027298,10262690,103000,null,ZM1
......

说明：

解释cat data.json | jq '.data'  : 解析根目录中的data标签
cat data.json | jq '.data' | jq '.t_ast_ts_pole' : 解析根目录中的data. t_ast_ts_pole'标签
jq -r '.[ ]|"\(.equip_code),\(.province),\(.ast_type),\(.model) "'
jq -r:循环取数据，后面指定各类标签,即可取出对应数据