Skip to content

Instantly share code, notes, and snippets.

@akurkin
Forked from alexey-milovidov/nested.txt
Last active Jun 29, 2019
Embed
What would you like to do?
Example of Nested data type in ClickHouse.
:) CREATE TABLE test.nested (EventDate Date, UserID UInt64, Attrs Nested(Key String, Value String)) ENGINE = MergeTree(EventDate, UserID, 8192)
CREATE TABLE test.nested
(
EventDate Date,
UserID UInt64,
Attrs Nested(
Key String,
Value String)
) ENGINE = MergeTree(EventDate, UserID, 8192)
Ok.
0 rows in set. Elapsed: 0.003 sec.
:) INSERT INTO test.nested VALUES ('2016-01-01', 123, ['price', 'color'], ['high', 'red'])
INSERT INTO test.nested VALUES
Ok.
1 rows in set. Elapsed: 0.003 sec.
:) SELECT * FROM test.nested
SELECT *
FROM test.nested
┌──EventDate─┬─UserID─┬─Attrs.Key─────────┬─Attrs.Value────┐
2016-01-01123 │ ['price','color'] │ ['high','red'] │
└────────────┴────────┴───────────────────┴────────────────┘
1 rows in set. Elapsed: 0.004 sec.
:) SELECT * FROM test.nested ARRAY JOIN Attrs
SELECT *
FROM test.nested
ARRAY JOIN Attrs
┌──EventDate─┬─UserID─┬─Attrs.Key─┬─Attrs.Value─┐
2016-01-01123 │ price │ high │
2016-01-01123 │ color │ red │
└────────────┴────────┴───────────┴─────────────┘
2 rows in set. Elapsed: 0.002 sec.
:) SELECT * FROM test.nested WHERE Attrs.Value[indexOf(Attrs.Key, 'color')] = 'red'
SELECT *
FROM test.nested
WHERE Attrs.Value[indexOf(Attrs.Key, 'color')] = 'red'
┌──EventDate─┬─UserID─┬─Attrs.Key─────────┬─Attrs.Value────┐
2016-01-01123 │ ['price','color'] │ ['high','red'] │
└────────────┴────────┴───────────────────┴────────────────┘
1 rows in set. Elapsed: 0.008 sec.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment