ClickHouse arrayMap的json解析

1.arraymap的基本操作

 SELECT arrayMap(x -> (x + 2), [1, 2, 3]) as res;
┌─res─────┐
│ [3,4,5] │
└─────────┘

•  arrayMap的第一个参数 是lambda匿名函数，即从第二个数组里取值，通过lambda函数使用数据计算 x表示数组中每一个元素，->后面的表达式表示对数组中每一个数据的操作

JSONExtractArrayRaw：

SELECT JSONExtractArrayRaw('{"numbers": [1, 2, 3, 4, 5]}', 'numbers')
┌─test──────────────────┐
│ ['1','2','3','4','5'] │
└───────────────────────┘

 JSONExtractString:

SELECT JSONExtractString('{"name": "John", "age": 30, "city": "New York"}', 'name')
┌─test─┐
│ John │
└──────┘

---

2.json解析

•  RESTFUL接口数据获取
• 使用自定义函数wsdJavaPost获取post过来的数据,先对json进行解析再array join

select 
y.1 as _id,
y.2 as creator,
y.3 as updater,
y.4 as deleter ,
addHours(toDateTime64(replaceAll(y.5, 'Z', ''), 3), 8)  _create_time ,
addHours(toDateTime64(replaceAll(y.6, 'Z', ''), 3), 8)  _update_time,
toDateTime64(replaceAll(y.7, 'Z', ''), 3) _delete_time,
y.8 city_name,
y.9 city_code,
addHours(toDateTime64(replaceAll(y.10, 'Z', ''), 3), 8) supply_price_month,
toDecimal64(y.12,2) supply_price,
y.11 order_no,
now() as odg_time
from ( 
  select JSONExtractArrayRaw(x,'data')as t from(select wsdJavaPost('https://******/a/b/data',  concat('{"data_id": "","limit": 10000,"fields": [],"filter": {"rel": "and","cond": [{ "field": "updateTime","type": "datetime", "method": "range", "value": ["',toString(addHours(now(),-32)),'Z"]}]}}'), '{"Authorization":"Bearer SkAS7l7hh2kpjgBt2vEJ42HCuhhR22zt9Yx","Content-Type": "application/json"}')as x))
ARRAY JOIN arrayMap(x -> (
JSONExtractString(x, '_id'),
JSONExtractString(x, 'creator'), 
JSONExtractString(x, 'updater'),
JSONExtractString(x, 'deleter'),
JSONExtractString(x, '_create_time'),
JSONExtractString(x, 'updateTime'),
JSONExtractString(x, 'deleteTime'),
JSONExtractString(x, 'city_name'),
JSONExtractString(x, 'city_code'),
JSONExtractString(x, 'supply_price_month'),
JSONExtractString(x, 'order_no'),
JSONExtractString(x, 'supply_price')),
t) AS y 

---

 3.arraymap配合argmax使用，我想取最新时间的name

• argmax：在ClickHouse中，argmax是一个聚合函数，用于找到一个分组中具有最大值的行的索引。

WITH JSONExtractArrayRaw('[{"name":"jack","height":100,"sex":"man","odg_dt":20230405},      {"name":"tom","height":90,"sex":"woman","odg_dt":20230406}]') AS person
SELECT
    argMax(arr1.1, arr1.2) AS name,
    max(arr1.2) AS odg_dt
FROM
(
    SELECT arrayJoin(arrayMap(x -> (JSONExtractString(x, 'name'), JSONExtractString(x, 'odg_dt')), person)) AS arr1
)

┌─name─┬─odg_dt───┐
│ tom  │ 20230406 │
└──────┴──────────┘