对 Clickhouse 的 SQL 请求:选择 JSON 数组中是否存在值

Question

我有一张表：

Status;Params
1;[{"Name": "Bob", "Age": 14, "Friend" : "Chuck"},{"Name" : "Alice", "Age" : 16, "Friend" : "John"}]
0;[{"Name": "Chuck", "Age": 14, "Friend" : "Bob"},{"Name" : "John", "Age" : 16, "Friend" : "Alice"}]

如何选择名称 XXX 至少在一个数组中表示的状态[i]。名字'？

Answer 1

select Status
from
where arrayExists(x-> JSONExtractString(x,'Name') = 'XXX', JSONExtractArrayRaw(Params))


SELECT arrayExists(x -> (JSONExtractString(x, 'Name') = 'Alice'), JSONExtractArrayRaw(x))
FROM
(
SELECT '[{"Name": "Bob", "Age": 14, "Friend" : "Chuck"},{"Name" : "Alice", "Age" : 16, "Friend" : "John"}]' AS x
)
┌─arrayExists(lambda(tuple(x), equals(JSONExtractString(x, 'Name'), 'Alice')), JSONExtractArrayRaw(x))─┐
│                                                                                                    1 │
└──────────────────────────────────────────────────────────────────────────────────────────────────────┘