BigQuery - Add elements from array into array of structs

Question

I have a struct which looks like this:

{"event": {
    "timestamp": [        
        "2019-01-13 17:21:08.570140 UTC",
        "2019-01-14 14:10:55.475515 UTC",
        "2019-01-09 14:02:51.848917 UTC"
    ],
    "properties": [
        {"device_model": "iPhone", "country": "United Kingdom"},
        {"device_model": "Android", "country": "United States"},
        {"device_model": "iPhone", "country": "Sweden"}
    ]
}

I would like to achieve this: so that each timestamp enters corresponding struct.

{"event": [
        {"timestamp": "2019-01-13 17:21:08.570140 UTC","device_model": 
         "iPhone", "country": "United Kingdom"},
        {"timestamp": "2019-01-14 14:10:55.475515 UTC", "device_model": 
         "Android", "country": "United States"},
        {"timestamp": "2019-01-09 14:02:51.848917 UTC", "device_model": 
         "iPhone", "country": "Sweden"}
    ]
}

I created the current structure from a query like this:

WITH
  events AS (
  SELECT
    "customer_1" AS customer_id,
    "timestamp_1" AS timestamp,
    STRUCT("iphone" AS device_model,
      "uk" AS country ) AS properties
  UNION ALL
  SELECT
    "customer_2" AS customer_id,
    "timestamp_2" AS timestamp,
    STRUCT("android" AS device_model,
      "us" AS country) AS properties
  UNION ALL
  SELECT
    "customer_2" AS customer_id,
    "timestamp_3" AS timestamp,
    STRUCT("iphone" AS device_model,
      "sweden" AS country) AS properties )
SELECT
  customer_id,
  STRUCT(ARRAY_AGG(timestamp) AS timestamp,
    ARRAY_AGG(properties) AS properties) AS event
FROM
  events
GROUP BY
  customer_id

How can I modify the query to achieve the desired structure?

--- Edit

I could do it this way, but this requires knowing the schema of properties at the time when I am generating query - which is possible, but not very pretty. Is there a simpler way?

WITH
  events AS (
  SELECT
    "customer_1" AS customer_id,
    "timestamp_1" AS timestamp,
    STRUCT("iphone" AS device_model,
      "uk" AS country ) AS properties
  UNION ALL
  SELECT
    "customer_2" AS customer_id,
    "timestamp_2" AS timestamp,
    STRUCT("android" AS device_model,
      "us" AS country) AS properties
  UNION ALL
  SELECT
    "customer_2" AS customer_id,
    "timestamp_3" AS timestamp,
    STRUCT("iphone" AS device_model,
      "sweden" AS country) AS properties )
SELECT
  customer_id,
  ARRAY_AGG(properties) AS event
FROM (
  SELECT
    customer_id,
    struct(timestamp as timestamp, 
           properties.device_model as device_model, 
           properties.country as country) as properties
  FROM
    events)
GROUP BY
  customer_id

Pentium10 · Accepted Answer · 2019-02-27 13:48:30Z

2

You could do something like this leveraging SELECT AS STRUCT and using properties as a selector.

SELECT
  customer_id,
  ARRAY_AGG(properties) AS prop
FROM (
  SELECT
    customer_id,
    (
    SELECT
      AS STRUCT timestamp,
      properties.*) AS properties
  FROM
    events e )
GROUP BY
  1

this returns:

[
  {
    "customer_id": "customer_1",
    "prop": [
      {
        "timestamp": "timestamp_1",
        "device_model": "iphone",
        "country": "uk"
      }
    ]
  },
  {
    "customer_id": "customer_2",
    "prop": [
      {
        "timestamp": "timestamp_2",
        "device_model": "android",
        "country": "us"
      },
      {
        "timestamp": "timestamp_3",
        "device_model": "iphone",
        "country": "sweden"
      }
    ]
  }
]

You could further write the piece like:

SELECT AS STRUCT e.* except(customer_id)

edited Feb 27, 2019 at 13:48

answered Feb 27, 2019 at 13:24

Pentium10

209k124 gold badges435 silver badges513 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

BigQuery - Add elements from array into array of structs

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related