# Query in BigQuery

This guide explains how to query data in BigQuery for typical
Manufacturing Data Engine (MDE) use-cases.

## Records join with cloud metadata

If cloud metadata materialization is disabled, you can access cloud metadata
instances by joining relevant record table with the `metadata-store` on the
metadata `instance_id` with the following SQL query:

```sql
SELECT
  dnr.*,
  ms.instance
FROM
  mde_data.`RECORD_TABLE_NAME` AS dnr
LEFT JOIN
  mde_dimension.`metadata-store` AS ms
ON
  ms.instance_id = JSON_VALUE(cloud_metadata_ref, "$.BUCKET_NAME.instance_id")
WHERE
  DATE(event_timestamp) = 'EVENT_TIMESTAMP'
LIMIT 100
```

Replace the following:

- `RECORD_TABLE_NAME`: Name of the record table.
- `BUCKET_NAME`: Name of the cloud metadata bucket.
- `EVENT_TIMESTAMP`: Timestamp of the event.

To improve query performance and since the `metadata-store` is partitioned on
bucket number, you can optionally specify the bucket number in the `ON` clause,
as the following SQL query:

```sql
SELECT
  dnr.*,
  ms.instance
FROM
  mde_data.`<RECORD_TABLE_NAME>` AS dnr
LEFT JOIN
  mde_dimension.`metadata-store` AS ms
ON
  ms.instance_id = JSON_VALUE(cloud_metadata_ref, "$.BUCKET_NAME.instance_id")
  AND ms.bucket_number = <BUCKET_NUMBER>
WHERE
  DATE(event_timestamp) = 'EVENT_TIMESTAMP'
LIMIT 100
```

Replace the following:

- `BUCKET_NAME`: Name of the cloud metadata bucket.
- `EVENT_TIMESTAMP`: Timestamp of the event.

### Cloud metadata instance attributes access

You can access metadata instance attributes using the JSON dot notation which
always returns a JSON object, or using one of the
[BigQuery JSON functions](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/json_functions.md.txt),
such as `JSON_VALUE` to extract strings or other data types. See the following
example:

```sql
SELECT
  dnr.*,
  ms.instance.deviceName                    -- this returns a double quoted JSON string
  JSON_VALUE(ms.instance, '$.deviceName')   -- this returns a string
FROM
  mde_data.`example-record-tbl` AS dnr
LEFT JOIN
  mde_dimension.`metadata-store` AS ms
ON
  ms.instance_id = JSON_VALUE(cloud_metadata_ref, "$.bucket.instance_id")
WHERE
  DATE(event_timestamp) = '2023-01-01'
LIMIT 100
```

Similarly, if cloud metadata materialization is enabled, you can access metadata
instance attributes directly from the record. See the following example:

```sql
SELECT
  *   (EXCEPT materialized_cloud_metadata),
  materialized_cloud_metadata.device.deviceName                     -- this returns a double quoted JSON string
  JSON_VALUE(materialized_cloud_metadata., '$.device.deviceName')   -- this returns a string
FROM
  mde_data.`example-record-tbl`
WHERE
  DATE(event_timestamp) = '2023-01-01'
LIMIT 100
```

## Obtaining a list of all instance IDs contained in cloud_metadata_ref

To obtain an array of all metadata instance IDs contained in
the `cloud_metadata_ref` field of a record follow these steps:

1. Create the user defined function (UDF) with the following SQL query:

   ```sql
   CREATE OR REPLACE FUNCTION `mde_data.get_instance_ids`(input JSON) RETURNS ARRAY<STRING> LANGUAGE js AS R"""
   return input ? Object.keys(input).map(bucketName => input[bucketName].instance_id).filter(instance_id => instance_id != null) : [];
   """;
   ```
2. Execute the function in a query:

   ```sql
   SELECT
    mde_data.get_instance_ids(cloud_metadata_ref) as metadata_instance_ids,
    *,
   FROM
    mde_data.`RECORD_TABLE_NAME`
   WHERE
   DATE(event_timestamp) = 'EVENT_TIMESTAMP'
   LIMIT 100
   ```

   Replace the following:

- `RECORD_TABLE_NAME`: Name of the record table.
- `EVENT_TIMESTAMP`: Timestamp of the event.