Using Big Query, how can I query within a record for multiple objects?

Question

Below is a structure that I am using. It is Google Analytic events coming from firebase.

I need to AND across the params array and return only one of the param values with the key of 'eventData'. While at the same time I need to limit the query by two other objects (having keys values of 'peripheral' and 'eventType') found in the same params array.

I'm using Legacy SQL but willing to work with something else. I looked and played with FLATTEN but that didn't seem to give me what I was looking for in the end. A query example may make more sense.

    SELECT event_dim.params.value.int_value -- this needs to come from the eventData object
    FROM 
   [table]
    where event_dim.name = 'EPILOG_BAT'
    AND event_dim.timestamp_micros > 1529423373498009
    -- AND event_dim.params contains (key='peripheral', string_value = 'CA5D6D4C-9B4C-9BB1-03CF-5A8BCD7651FE')
    -- AND event_dim.params contains (key='eventType', string_value = 'V')
    order by event_dim.timestamp_micros ,
    event_dim.params.key

the row I would expect to get would be 4105

Data:

 "event_dim": [
  {{
    "date": "20180620",
    "name": "EPILOG_BAT",
    "params": [

      {
        "key": "eventData",
        "value": {
          "string_value": null,
          "int_value": "4105",
          "float_value": null,
          "double_value": null
        }
      },
      {
        "key": "peripheral",
        "value": {
          "string_value": "CA5D6D4C-9B4C-9BB1-03CF-5A8BCD7651FE",
          "int_value": null,
          "float_value": null,
          "double_value": null
        }
      },
      {
        "key": "eventTime",
        "value": {
          "string_value": null,
          "int_value": "1529499380000",
          "float_value": null,
          "double_value": null
        }
      },
      {
        "key": "eventType",
        "value": {
          "string_value": "V",
          "int_value": null,
          "float_value": null,
          "double_value": null
        }
      },
      {
        "key": "firmwareVersion",
        "value": {
          "string_value": "0.8.6\u0000\u0000\u0000\u0000\u0000\u0000\u0000",
          "int_value": null,
          "float_value": null,
          "double_value": null
        }
      }
    ],
    "timestamp_micros": "1529478561132285",
    "previous_timestamp_micros": "1529478560856003",
    "value_in_usd": null
  },}

Is your JSON file correct? I have tried to import it, to try to help you, but I can't. Like it is, it's not a valid JSON, and even after trying to 'fix it', it's not recognised by Big Query. — Mangu, Jun 21 '18 at 13:06
@Mangu it's probably not, the record is too big to include. Here is a gist to a record. I copied this as json from the bigquery ui. https://gist.github.com/anwarhamr/c08a66db6e91535376d4598180156d60 — Robel Robel Lingstuyl, Jun 21 '18 at 15:07
Why are you using 2 dashes before 2 AND conditions in your query example? Are you sure you are using Legacy SQL? If you are using Standard SQL the 2 dashes are comments and your are not filtering that. Can you clarify this? — J.L Valtueña, Jun 22 '18 at 10:44
@J.LValtueña I'm clear on commenting out code and yes that is intential for the example; the query fails when included. Yes I'm sure it's Legacy since it works w/ the Legacy checkbox set in the options. If you have a solution it can be in either Legacy or Standard SQL — Robel Robel Lingstuyl, Jun 25 '18 at 15:54

score 0 · Accepted Answer · answered Jun 26 '18 at 12:48

Maybe something like this (excuse my formatting):

#standardSql
SELECT
(SELECT params.value.int_value FROM event.params WHERE params.key = 
'eventData') AS id
FROM `data_source`, 
UNNEST(event_dim) AS event,
UNNEST(event.params) AS params
WHERE event.name = 'EPILOG_BAT'
AND event_dim.timestamp_micros > 1529423373498009
AND (SELECT params.value.string_value FROM event.params WHERE params.key = 
'peripheral') = 'CA5D6D4C-9B4C-9BB1-03CF-5A8BCD7651FE'
AND (SELECT params.value.string_value FROM event.params WHERE params.key = 
'eventType') = 'V'

@Robel Robel Lingstuyl Has this answer worked for you? – Ggrimaldo Jun 28 '18 at 14:45 — Ggrimaldo, Jun 28 '18 at 14:45

Using Big Query, how can I query within a record for multiple objects?

1 Answers1