CODEWITHSUNDEEP
google-bigquery
Blazerg
Blazerg

Reputation: 849

keep null records in bigquery when added a new element to a repeated record

I have a field of type repeated record in bigquery that stores this json:

  "users": [{
    "key": "name",
    "value": {
      "string_value": foo,
      "int_value": null,
      "float_value": null,
      "double_value": null
    }
  }, {
    "key": "age",
    "value": {
      "string_value": null,
      "int_value": "477",
      "float_value": null,
      "double_value": null
    }
  }

I query those field using UNNEST, just like this:

  select * from myTable t,
  unnest(users) as name, 
  unnest(users) as age, 

  WHERE name.key = 'name' 
  AND age.key = 'age'

My problem is that I have added a third field

  "users": [{
    "key": "name",
    "value": {
      "string_value": foo,
      "int_value": null,
      "float_value": null,
      "double_value": null
    }
  }, {
    "key": "age",
    "value": {
      "string_value": null,
      "int_value": "477",
      "float_value": null,
      "double_value": null
    }
  },
  {
    "key": "lastName",
    "value": {
      "string_value": Johnson,
      "int_value": null,
      "float_value": null,
      "double_value": null
    }
  }

The last name field didn't exist before so I have "historical" rows that contained only the 2 previous fields that are being excluded from my query now, even using a left join.

  select * from myTable t,
  unnest(users) as name, 
  unnest(users) as age
  left join unnest (users) as lastName 

  WHERE name.key = 'name' 
  AND age.key = 'age'
  AND lastName.key = 'lastName'

this ONLY returns rows that contain the 3 fields (even tho is a left join) and I'm missing all the data from before I added the new element to the repeated field. How can I keep null rows?

To clarify, my query should return something like this (right now I'm not getting the first one):

  John, 12, null
  Juan, 14, Perez

Upvotes: 0

Views: 47

Answers (1)

Sabri Karagönen
Sabri Karagönen

Reputation: 2365

I guess you're querying Firebase data in BQ.

Firstly, you shouldn't add a new join for each field in user struct. Instead, you should search for the key in the array and then get the relevant field in value struct.

  select 
    (select value.string_value from unnest(users) where key = 'name') as user,
    (select value.int_value from unnest(users) where key = 'age') as age,
    (select value.string_value from unnest(users) where key = 'lastName') as lastName,
  from myTable

Above, I used string value for name and lastname, and int value for age fields. Please adjust these for your data.

Upvotes: 1

Related Questions