Created
November 14, 2024 14:48
-
-
Save ak--47/0ded82191c82faa89408cd747993c6d2 to your computer and use it in GitHub Desktop.
weird duckDb unnesting thing
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
{"origRow": 1, "foo":42,"bar":"fortyTwo","myNest":[{"hello":"space","goodbye":"world"},{"hello":"time","goodbye":"gravity"}]} | |
{"origRow": 2, "foo":420,"bar":"fortyTwenty","myNest":[{"hello":"you","goodbye":"me"},{"hello":"we","goodbye":"thee"}]} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE OR REPLACE VIEW temp_view AS | |
WITH MY_FUN_TABLE AS ( | |
SELECT | |
* | |
FROM | |
read_json('/Users/ak/Desktop/example.json', | |
format = 'newline_delimited', | |
auto_detect = TRUE | |
) | |
), | |
MY_WEIRD_SCHEMA AS ( | |
SELECT | |
origRow, | |
foo, | |
bar, | |
something.*, | |
-- ▲ the ONLY way TO access our 'exploded' json | |
-- ▼ is with the 'unnest' prefix? | |
unnest.hello, | |
unnest.goodbye | |
-- something.hello -- fails: Binder Error... but WHYYY???? | |
FROM | |
MY_FUN_TABLE AS T, | |
UNNEST(T.myNest) AS something -- this ends up being named "unnest" despite the alias ... and you can't change it! | |
) | |
SELECT * FROM MY_WEIRD_SCHEMA; | |
DESCRIBE temp_view; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
👋 i am trying to understand why using
UNNEST()
to explod loaded JSON data does not support aliasing withAS
(like you might expect) ... so i can'tUNNEST(table.column) AS foo
and thenfoo.bar
... i have tounnest.bar
?check out the schema of this view: