Skip to content

Instantly share code, notes, and snippets.

@ak--47
Created November 14, 2024 14:48
Show Gist options
  • Save ak--47/0ded82191c82faa89408cd747993c6d2 to your computer and use it in GitHub Desktop.
Save ak--47/0ded82191c82faa89408cd747993c6d2 to your computer and use it in GitHub Desktop.
weird duckDb unnesting thing
{"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"}]}
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;
@ak--47
Copy link
Author

ak--47 commented Nov 14, 2024

👋 i am trying to understand why using UNNEST() to explod loaded JSON data does not support aliasing with AS (like you might expect) ... so i can't UNNEST(table.column) AS foo and then foo.bar ... i have to unnest.bar ?

check out the schema of this view:
weirdSchema

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment