CODEWITHSUNDEEP
sqlarraysjsonselectclickhouse
Andrey Dovgalyuk
Andrey Dovgalyuk

Reputation: 49

SQL request to Clickhouse: select if value exist in JSON array

I've got a following table:

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"}]

How can I do 'select Status where Name XXX represented at least in one Array[i].Name'?

Upvotes: 0

Views: 1832

Answers (1)

Denny Crane
Denny Crane

Reputation: 13360

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 │
└──────────────────────────────────────────────────────────────────────────────────────────────────────┘

Upvotes: 1

Related Questions