I'm using bigQuery i have a table a with column details which is an array of data i want to select this array and every element in this array get an object name from another table: I have use this query
select a.*,(SELECT a.details,n.name FROM a.details d LEFT JOIN names n ON d.id = n.id ) AS name_details FROM
table1 AS a
LEFT JOIN (SELECT * FROM table2) as b
ON a.id = b.data_id
But i got this error :
Correlated subqueries that reference other tables are not supported unless they can be de-correlated, such as by transforming them into an efficient JOIN.
Example
Table1 (
Id int
Details Array(id int)
)
Names(
Id int
Name string
)
table2(
data_id int
)
I want to get
Table(
Id int (from table1)
Details Array (id ,name (from names table))
)
Any helps please