Query to a simple strings array

Question

I have a simple query like this:

SELECT container.category FROM `mybucket` as location
UNNEST location.partsContainers container
WHERE container.category IS NOT null

it gives me json:

[{
    "category": "0028H3:WV CUTTING EDGE AXIAL REAM TRAY"
},
{
    "category": "AVENTURA OASYS 1-2"
}, ... etc.

but what I need it a flat array of strings:

["0028H3:WV CUTTING EDGE AXIAL REAM TRAY",
 "AVENTURA OASYS 1-2",
   ... etc
]

how can I achieve that?

geraldss · Accepted Answer · 2015-10-17 01:42:03Z

3

you can use ARRAY_AGG:

SELECT RAW ARRAY_AGG(container.category)
FROM `mybucket` as location
UNNEST location.partsContainers container
WHERE container.category IS NOT null

edited Oct 17, 2015 at 1:42

answered Oct 17, 2015 at 0:25

geraldss

2,4451 gold badge14 silver badges13 bronze badges

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

5 Comments

iLemming Over a year ago

this is close to what I want. It creates an array with an object, with a unnamed key in it, which contains the array I need. How can I make the array to be at the top level? right now it's: [ { "$1": [ "CAT1","CAT2", "etc."

iLemming Over a year ago

the reason I need it to be completely flat is to be able to merge it (UNION ALL) with another query, and need a flat structure in the end that would cope well with paging (OFFSET and LIMIT)

user1697575 Over a year ago

Have you tried to specify not to return keys in the result, just values?

iLemming Over a year ago

@user1697575 I'm not sure exactly what you mean

geraldss Over a year ago

@Agzam, I added the 'RAW' to address your first comment.

Collectives™ on Stack Overflow

Query to a simple strings array

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related