CODEWITHSUNDEEP
jsongoogle-bigquery
Erechtheus
Erechtheus

Reputation: 765

How do I parse JSON key pair values in BigQuery?

I have a JSON that looks like this

 "promo_codes": {
        "AKNC": "",
        "AZQE": "",
        "BBVY": "xbKn2vA8Z2XoDSxawDIzFrzqSEf1",
        "BJCR": "ou9Y3zXzGXU5loe2CjMGEDwCUln2"
    }

In Bigquery, I would like to store these in a table with type REPEATED where you would have for instance promo_codes.key and promo_codes.value fields.

The resulting table should be one single row containing the list of codes (this JSON is for a single row, there are many others which need to be parsed with different codes).

How do I acomplish this?

Below is almost what I want, but it doesn't pair the correct user to the code.

 ARRAY(
    SELECT AS STRUCT 
    REGEXP_EXTRACT_ALL(JSON_EXTRACT(C.data, '$.promo_codes'), r'"([a-zA-Z0-9\.]+)":') as code,
    REGEXP_EXTRACT_ALL(JSON_EXTRACT(C.data, '$.promo_codes'), r':"([a-zA-Z0-9\.]+)"') as user_id

) as promo_codes ,

Upvotes: 0

Views: 132

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173171

Use below approach

select 
  array(
    select as struct 
      split(kv, ':')[offset(0)] as code, 
      split(kv, ':')[offset(1)] as user_id 
    from unnest(split(translate(json_extract(c.data, '$.promo_codes'), '{}"', ''))) kv
  ) as promo_codes
from `project.dataset.table` c

if applied to sample data in your question - output is

enter image description here

Upvotes: 1

Related Questions