How to use 'raw' and array function in the same query?

SQL++
1

Using this query:

select raw array_distinct(doc.beers[*].name) from `bucket` doc
where doc.type = 'brewer'

I can get the result:

[
  [
    "IPA",
    "Pale Ale"
  ],
  [
    "Lager",
    "Pale Ale",
    "Stout",
    "American Ale"
  ]
]

I’d like to put this result through array_flatten and array_distinct so that the final result is

[
  [
    "IPA",
    "Lager",
    "Pale Ale",
    "Stout",
    "American Ale"
  ]
]

The issue is I can’t figure out how to use ‘raw’ and the array functions in the same query. What’s the correct way?

2 
SELECT   RAW ARRAY_DISTINCT(ARRAY_FLATTEN(ARRAY_AGG(doc.beers[*].name),2))
FROM bucket doc
WHERE doc.type = "brewer";

OR

SELECT RAW ARRAY_DISTINCT(ARRAY_FLATTEN( (SELECT   RAW doc.beers[*].name
                                          FROM bucket doc
                                          WHERE doc.type = "brewer"),2));

OR

SELECT  DISTINCT RAW b.name 
FROM bucket doc
UNNEST doc.beers AS b
WHERE  doc.type = "brewer";
2 Likes