Combine as array of STRUCTs some columns to existing array

Question

I have this schema

I want to get one single ARRAY of STRUCTS that merges (feature,numerical_value) with already array of structs of categorical_value. Be aware that category can be empty string as well, that we want to skip.

I managed to do it this way, but I am looking for an alternative shorter way:

select centroid_id,array_agg(struct(name,value) order by centroid_id) as cluster from (
select centroid_id,concat(feature,'_',category) as name,value 
    FROM
    ML.CENTROIDS(MODEL `modelv1`), unnest(categorical_value)
    where length(category)>0
union all
select centroid_id,feature as name,numerical_value as value
FROM
    ML.CENTROIDS(MODEL `modelv1`)
    where numerical_value is not null
) 
group by centroid_id
order by centroid_id

Mikhail Berlyant · Accepted Answer · 2019-09-19 13:43:34Z

1

#standardSQL
SELECT centroid_id, 
  (
    SELECT ARRAY_AGG(STRUCT(name,value)) FROM (
      SELECT CONCAT(feature,'_',category) AS name,value FROM UNNEST(categorical_value)
      WHERE LENGTH(category)>0
      UNION ALL
      SELECT feature, numerical_value
    ) 
    WHERE value IS NOT NULL
  ) AS cluster
FROM
    ML.CENTROIDS(MODEL `modelv1`)

OR

#standardSQL
SELECT centroid_id, 
  ARRAY(
    SELECT AS STRUCT name,value FROM (
      SELECT CONCAT(feature,'_',category) AS name,value FROM UNNEST(categorical_value)
      WHERE LENGTH(category)>0
      UNION ALL
      SELECT feature, numerical_value
    ) 
    WHERE value IS NOT NULL
  ) AS cluster
FROM
    ML.CENTROIDS(MODEL `modelv1`)

answered Sep 19, 2019 at 13:43

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

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

Collectives™ on Stack Overflow

Combine as array of STRUCTs some columns to existing array

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