Skip to content

Instantly share code, notes, and snippets.

@proudlygeek

proudlygeek/nested.txt

Forked from alexey-milovidov/nested.txt
Created January 19, 2022 09:30
Download ZIP

Revisions

  1. @alexey-milovidov alexey-milovidov created this gist Jun 17, 2016.
    58 changes: 58 additions & 0 deletions nested.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,58 @@
    :) 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-01 │ 123 │ ['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-01 │ 123 │ price │ high │
    │ 2016-01-01 │ 123 │ 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-01 │ 123 │ ['price','color'] │ ['high','red'] │
    └────────────┴────────┴───────────────────┴────────────────┘

    1 rows in set. Elapsed: 0.008 sec.