CODEWITHSUNDEEP
google-bigquery
Grinchush
Grinchush

Reputation: 23

BigQuery JSON Array extraction

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.

Upvotes: 2

Views: 261

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

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

enter image description here

Upvotes: 2

Related Questions