Big query unnest array with json values-CodePudding

Lets consider the following table on Google BigQuery:

WITH example AS (
    SELECT 1 AS id, ["{\"id\":1, \"name\":\"AAA\"}", "{\"id\":2, \"name\":\"BBB\"}","{\"id\":3, \"name\":\"CCC\"}"] 
    UNION ALL
    SELECT 2 AS id, ["{\"id\":5, \"name\":\"XXX\"}", "{\"id\":6, \"name\":\"ZZZ\"}"] 
)
SELECT * 
FROM example;

I would like to compose a query that will return names with their parent row's id.

like:

I tried using unnest with json functions and I just cant make this right.

Can anyone help me?

Thanks Ido

CodePudding user response：

According to your query, you already have json elements in your array. So with the use of unnest, you can use a json function like json_value to extract the name attribute of your elements.

select 
    id,
    json_value(elt, '$.name')
from example, unnest(r) as elt;