How to query all entries with a value in a nested bigquery table

1
votes

I generated a BigQuery table using an existing BigTable table, and the result is a multi-nested dataset that I'm struggling to query from. Here's the format of an entry from that BigQuery table just doing a simple select * from my_table limit 1:

[
  {
    "rowkey": "XA_1234_0",
    "info": {
      "column": [],
      "somename": {
        "cell": [
          {
            "timestamp": "1514357827.321",
            "value": "1234"
          }
        ]
      },
      ...
    }
  },
  ...
]

What I need is to be able to get all entries from my_table where the value of somename is X, for instance. There will be multiple rowkeys where the value of somename will be X and I need all the data from each of those rowkey entries.

OR

If I could have a query where rowkey contains X, so to get "XA_1234_0", "XA_1234_1"... The "XA" and the "0" can change but the middle numbers to be the same. I've tried doing a where rowkey like "$_1234_$" but the query goes on for over a minute and is way too long for some reason.

I am using standard SQL.

EDIT: Here's an example of a query I tried that didn't work (with error: Cannot access field value on a value with type ARRAY<STRUCT<timestamp TIMESTAMP, value STRING>>), but best describes what I'm trying to achieve:

SELECT * FROM `my_dataset.mytable` where info.field_name.cell.value=12345

I want to get all records whose value in field_name equals some value.

2
sqlgoogle-bigquery

2 Answers

3
votes

From the sample Firebase Analytics dataset:

#standardSQL
SELECT *
FROM `firebase-analytics-sample-data.android_dataset.app_events_20160607`
WHERE EXISTS(
  SELECT * FROM UNNEST(user_dim.user_properties)
  WHERE key='powers' AND value.value.string_value='20'
)
LIMIT 1000

enter image description here

1
votes

Below is for BigQuery Standard SQL 

#standardSQL
SELECT t.*
FROM `my_dataset.mytable` t, 
  UNNEST(info.somename.cell) c
WHERE c.value = '1234'

above is assuming specific value can appear in each record just once - hope this is a true for you case

If this is not a case - below should make it 

#standardSQL
SELECT *
FROM `yourproject.yourdadtaset.yourtable`
WHERE EXISTS(
  SELECT * 
  FROM UNNEST(info.somename.cell)
  WHERE value = '1234'
)

which I just realised pretty much same as Felipe's version - but just using your table / schema