json - How do I parse JSON key pair values in BigQuery?

Question

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 ,

标签： jsongoogle-bigquery

Answer 1

使用以下方法

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    

如果应用于您问题中的样本数据 - 输出是