Resolving array relations from nested queries breaks

[teaguesterling]

Running a query such as:

{
   Album {
      Tracks {
         Name 
      }
   }
}

When Album and Track are in different databases results in a SQL exceptions due to the JSON_EACH function not existing in DuckDB.

[teaguesterling]

The JSON_EACH function takes a json array and returns a row with a lot of columns for every element in the array. Only the value column is needed for this particular functionality, not the indexes or type annotations.

[teaguesterling]

We can reproduce the JSON_EACH behavior with the following table expression:

UNNEST(                  -- Convert every element of the list[json] values into a table with a single json column
   json_extract(         -- Use JSON extract to convert a String to JSON and retrieve the relevant values
     '[{"columnA":"A1","columnB":"B1"},{"columnA":"A2","columnB":"B2"}]',  -- some JSON string rooted on a array
    '$[*]'                      -- In our case, we want every element of the array extracted into a list
   )
) _json_each(value) -- Alias the expression to make sure the JSON column is named value for compatibility 

or more concisely: UNNEST(json_extract($SOME_STRING_WITH_A_JSON_ARRAY, '$[*]')) _json_each(value)

[teaguesterling]

Fixed in https://github.com/teaguesterling/duckdb-dataconnector-agent/commit/e46c635c8ed67c7439e4b1521944c19566e0b5bc.