I have a database that has 2 columns. One is labeled 'job_id' and are a unique identifier that is a STRING data type. The second column is labeled 'personnel_data' and is a STRUCT data type. Inside 'personnel_data' is a STRUCT[] named 'person'. Inside of that are 2 data types: 'personnel_guid' and 'person_id', which are a STRING and INT32 data type respectively.
Looking through the data, for each 'job_id', it could have 0 or even up to 100 'personnel_guid' numbers.
I'm trying to create a query that groups the 'job_id' by 'personnel_guid' count. Less than 10 people 10-25 people 25-75 people 75+ people
I am aware that with how the 2nd column is set up, I would need the UNNEST() for that. However, I'm stuck on what the syntax would be for the query. My best guess is that a CASE statement would be also be a part of the query but I'm not 100% sure. Any tips would be appreciated. Thanks.
I looked at articles written by SQL enthusiasts and I'm still stuck.