对标mongodb存储类JSON数据文档统计分析详解

[{
  "dev_ip": "123.46.5.111",
  "location": [39.916527, 116.397128],
  "meminfo": {
    "total": 64,
    "used": 23.2
  }
},
{
  "dev_ip": "123.46.5.112",
  "location": [39.916144, 116.392582],
  "meminfo": {
    "total": 64,
    "used": 27.8
  }
}]

CREATE TABLE dev_meminfo (
  dev_ip String,  
  location Array(Float64),
  meminfo Map(String, Float32)
)
ENGINE = MergeTree()
ORDER BY dev_ip;

cat dev_meminfo.json |jq -c .[] | clickhouse-client --database acaidb -m -u acai --passWord '<你的密码>'  \
--query="INSERT INTO dev_meminfo FORMAT JSONEachRow"

INSERT  INTO dev_meminfo FORMAT JSONEachRow {"dev_ip": "123.46.5.112", "location": [39.916144, 116.392582],"meminfo": {"total": 64,"used": 27.8}};

#  clickhouse-client --database acaidb -m -u acai --password '4rfv$RFV'  --query="SELECT * FROM dev_meminfo"
123.46.5.111    [39.916527,116.397128]  {'total':64,'used':23.2}
123.46.5.112    [39.916144,116.392582]  {'total':64,'used':27.8}

# clickhouse-client --database acaidb -m -u acai --password '4rfv$RFV'  --query="SELECT dev_ip,location[1],location[2],meminfo['total'],meminfo['used'] FROM dev_meminfo"
123.46.5.111    39.916527       116.397128      64      23.2
123.46.5.112    39.916144       116.392582      64      27.8