BigQuery JSON Array extraction

Question

I have this JSON

    "type": "list",
    "data": [
        {
            "id": "5bc7a3396fbc71aaa1f744e3",
            "type": "company",
            "url": "/companies/5bc7a3396fbc71aaa1f744e3"
        },
        {
            "id": "5b0aa0ac6e378450e980f89a",
            "type": "company",
            "url": "/companies/5b0aa0ac6e378450e980f89a"
        }
    ],
    "url": "/contacts/5802b14755309dc4d75d184d/companies",
    "total_count": 2,
    "has_more": false
}

I want to dynamically create columns as the number of the companies with their Ids, for example:

company_0	comapny_1
5bc7a3396fbc71aaa1f744e3	5b0aa0ac6e378450e980f89a

Tried to use BigQuery's JSON functions but I didn't get along with it.

Thank you.

did you have chance to search within SO? there are number of posts similar to this one that are answered already! — Mikhail Berlyant
– Mikhail Berlyant, Commented Aug 23, 2022 at 23:04
searched a lot but didn't find the same case as I have, objects inside an array with the same keys. can you please point me to a possible solution? — Grinchush
– Grinchush, Commented Aug 23, 2022 at 23:07

Mikhail Berlyant · Accepted Answer · 2022-08-23 23:30:05Z

2

Consider below approach

select * except(json) from (
  select json, json_extract_scalar(line, '$.id') company, offset
  from your_table t, unnest(json_extract_array(json, '$.data')) line with offset
  where json_extract_scalar(line, '$.type') = 'company'
)
pivot (any_value(company) company for offset in (0, 1))

if applied to sample data in your question - output is

answered Aug 23, 2022 at 23:30

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

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

Collectives™ on Stack Overflow

BigQuery JSON Array extraction

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