Big query unnest array with json values

Question

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

Ugo · Accepted Answer · 2022-03-22 08:27:11Z

2

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;

answered Mar 22, 2022 at 8:27

Ugo

1594 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Big query unnest array with json values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related