How do I get a count of the number of objects in a json array that lives in a BigQuery table?

0
votes

I am querying a table in BigQuery that has columns Id (integer) and user_json (string). user_json is a json array structured like so: { "user_id": "123456", "is_deleted": false, "address_book": { "uaddr_Qi6vCZ3a": { "city": "Nowhere", "state": "CA", "last_name": "Smith", "first_name": "John", "postal_code": "99999", "iso2_country": "US", "phone_number": "1234567809", "address_line_1": "123 Spring Drive", "address_line_2": "Apt 101", "address_book_item_id": "uaddr_Qi6vCZ3a" }, "uaddr_hZxdzoOE": { "city": "Somewhere", "state": "NV", "last_name": "Smith", "first_name": "John", "postal_code": "88888", "iso2_country": "US", "phone_number": "9087654321", "address_line_1": "321 Fall Blvd", "address_book_item_id": "uaddr_hZxdzoOE" } } }

I need to find the number of address for each Id. The "uaddr_xxxx " is unique for each "address_book" entry. I would like be able to count how many "uaddr_xxxx " are in user_json for each unique Id.

1
jsoncountgoogle-bigquery

1 Answers

0
votes

Consider below approach

select id,
  array_length(regexp_extract_all(user_json, r'"(uaddr_[^"]+)":')) uaddr_count,
  regexp_extract_all(user_json, r'"(uaddr_[^"]+)":') uaddr_items
from `project.dataset.table`

if applied to sample data in your question - output is

enter image description here