How to query from a table with a list of dictionaries, only for certain keys (BigQuery) SQL

user_8299140 0
Viewed 1112

I have a table on Google BigQuery where each row has a field called "customized_field" that is a dictionary with a list of 25 dictionaries (each with just 1 key/value pair). Each row has a the same dictionary (with the same "id" names), just different quantities in "value". The dictionary looks like this:

[
  {
    "customized_field": [
      {
        "data": {
          "id": "Bob",
          "value": 3
        }
      },
      {
        "data": {
          "id": "Jim",
          "value": 4
        }
      },
      {
        "data": {
          "id": "Mary",
          "value": 2
        }
      },
      etc etc... (22 more)
   }
]

I want to create a table that creates two columns (specifically for "Bob" and "Mary") where the value of each of the 2 columns is the "value" in the dictionary. So the table would look like this:

Bob Mary
3 2
4 (say this is the value in the next row) 5 (say this is the value in the next row)

My SQL script looks like this:

SELECT
CASE when h.data.id = "Bob" then h.value.value end Bob,
CASE when h.data.id = "Mary" then h.value.value end Mary
FROM `my_database`, UNNEST(`my_database `. customized_field) AS h

However, this gives me a table that looks how I want, but it creates 25 times the number of rows I need (I believe it duplicates because of the 25 key value pairs, and when I do "unnest" it basically creates a ton more rows). How do I get it so that it doesn't do this duplicating?

My query gives a table like this:

Bob Mary
3 2
4 5
3 2
4 5
3 2
4 5

etc etc..

unnest google-bigquery sql
1 Answers

Consider below approach

select * except(key) from (
  select h.data.id, h.data.value, to_json_string(t) key
  from `my_database` t, unnest(t.customized_field) AS h
)
pivot (max(value) for id in ('Bob', 'Mary'))

output will look like below

enter image description here

user_52219440
Related