Create an Array of values taken conditionally from values of an Object

SQL++
1

I have type= user documents in this form:

{
type: ‘user’,
name: …,
companyName: ‘acmeWidgets’,
email: …,
primaryPhone: ‘888-888-888’,
userDefinedProperties: [
{
type: ‘phoneNumber’,
value: ‘777-777-777’
},
{
type: ‘favoriteColor’,
value: ‘blue’
},
{
type: ‘phoneNumber’,
value: ‘222-222-2222’
}
]
}

I want to query a collection of these objects projecting the company, and array of phone numbers, like so:

{
companyName: ‘acmeWidgets’,
phonenumbers: [‘888-888-888’, ‘777-777-777’, ‘222-222-222’]
}

The ‘primaryPhone’ field on the object may or may not be defined. And one or more or none of the userDefinedProperties will have the target type=‘phoneNumber’

So I have to do

Select
companyName,
??? as phonenumbers
From default
Where type = ‘user’

Please help.

2 
SELECT
     d.companyName,
     ARRAY_CONCAT(comp_phones, udp_phones) AS phonenumbers
FROM default AS d
LEFT UNNEST d.userDefinedProperties AS udp
WHERE d.type = "user" AND  udp.type = "phoneNumber"
GROUP BY d.companyName
LETTING udp_phones = ARRAY_AGG(DISTINCT udp.`value`),
        comp_phones = ARRAY_AGG(DISTINCT d.primaryPhone);
1 Like