6 ms·
Great product! Thanks for sharing it! Question: I thought Clickhouse already has native support for flattening JSON [1], although it was released recently (ver
by giovannibonetti 3y ago
Great product! Thanks for sharing it!
Question: I thought Clickhouse already has native support for flattening JSON [1], although it was released recently (version 22.3.1). Did you start working on it [2] before that? Or is it a different take? I'm curious about the pros and cons of each one.
[1] https://clickhouse.com/docs/en/integrations/data-formats/json#semi-structured-approach https://clickhouse.com/docs/en/integrations/data-formats/jso...
[2] https://scratchdb.com/blog/flatten-json/ https://scratchdb.com/blog/flatten-json/
- memset 3y agoThis is a really good question. My snarky answer is "the way we ingest JSON doesn't require 50 pages and n configuration settings to explain." Here's a more factual one: - We don't use tuples for nested JSON. We just use underscores to show parent_child relationships in keys. - We don't use arrays. Instead, we make it easy to split into multiple rows so you can use regular SQL. I haven't directly compared Clickhouse's all of different JSON treatments compared to what I've implemented, but my goal was to build something that you could run and would "just work the way it should."
- antoniojtorres 3y agoClickHouse JSON certainly has some sharp edges because they do a lot of schema inference to transparently column store the component parts, which makes it tricky with anything that could be irregular. Big fan of CH on my end. Will follow your project closely.
- zepolen 3y agoWhat happens with this? { "some_key": "1" "some": { "key": "2" } }
- memset 3y agoProbably an error! Should create a ticket for that. What would we want the behavior to be? (This hasn't come up with existing customers because theoretically it could and we should handle it.)
- namtab00 3y agomaybe double the underscore on conflict? some_key =1 some__key = 2
- memset 3y agoAgree. (What if the original users key has the double underscore?) maybe this can be defined as a param?
- namtab00 3y ago> What if the original users key has the double underscore? Double it again. some_key = 1 some__key = 2 some____key = 3 This is analogous to what C#11 does to allow raw string literal quotes containing those same quotes. https://learn.microsoft.com/en-us/dotnet/csharp/language-reference/builtin-types/reference-types#string-literals https://learn.microsoft.com/en-us/dotnet/csharp/language-ref...
- dicriseg 3y agoThe convention when flattening is exactly that I thought - an underscore per level of nesting. This is what I would expect to see too.
- gostsamo 3y agoescape the underscore. this is the only sane one, I think.
- mosen 3y agoI tested it with the “explode” flattening mode, and it resulted in { “some_key”: “2” }
- memset 3y agoFeel free to create a gh issue - really interested in what the right solution would be here!
- datatrashfire 3y agoas a huge clickhouse fan, i have to agree json is simply not a great experience even with all the changes in the last year
- lionkor 3y agomaybe use the json flattening RFC instead of inventing your own?
- zX41ZdbW 3y agoSchema inference for nested JSON objects is a feature of ClickHouse 23.9. I've made a video about it: https://www.youtube.com/watch?v=yS8YU-rBpMM&t=1846s https://www.youtube.com/watch?v=yS8YU-rBpMM&t=1846s