Select unnest(<array column>)?

,

Having issues with the ‘unnest’ function on an ARRAY column type. Scenario:

With a table like something like:

CREATE TABLE products (
    id UUID NOT NULL DEFAULT uuid_v4()::UUID,
    tags STRING[] NULL DEFAULT NULL
)

And assuming the table is populated with some not-empty tag columns, I’m trying to do a query like so:

SELECT unnest(tags) FROM products WHERE id = 'whatever';

However, the response is always an error: pq: column name “tags” not found. The only way I’ve been able to use the unnest function is when passing in an array manually created in the query (as in, not pulling any data from the database itself):

SELECT unnest(ARRAY['alpha', 'bravo']);

So my question is: is the unnest function intended to work as I’m trying to do? Or am I missing something here. In the docs, it says input: anyelement[] is the accepted argument.

The ultimate goal is to be able to use this in a subquery with an IN clause, like so:

SELECT * FROM thingsA WHERE things.id IN (
    SELECT unnest(otherThings.col) FROM otherThings WHERE otherThings.id = '...'
);

Hi n00b,

It looks like you’ve found a bug in our implementation of unnest() (and other similar functions. I’ve filed an issue to track it.

In the meantime, as long as the inner subquery returns only one row, I think you can work around it with a second subquery like this:

SELECT * FROM thingsA WHERE things.id IN (
    SELECT unnest((SELECT otherThings.col FROM otherThings WHERE otherThings.id = '...'))
);

@bdarnell thanks for the prompt response. I’ll follow the github issue going forward. The nested subquery doesn’t work in this case as it’ll return more than one row, so no go on that for now.

For posterity / other people who might consider this strategy in the future - if this were working at the moment, would this be a poor design decision performance wise? Or would the recommendation be to prefer normalized tables and just go with JOINs?

It’s hard to say at this point. SQL purists would recommend normalized tables and JOINs, but I think there’s a good chance that the array version would be faster (as long as you don’t have too many elements in the array). It’s going to depend on how efficient the unnest subquery implementation turns out to be.

I think that using the “contains” operator @> is likely to be faster than either the JOIN or unnest() solutions (we don’t support this yet either for arrays, but we’ll support it for JSON in 2.0, and it shouldn’t be hard to add for arrays. https://github.com/cockroachdb/cockroach/issues/18442)

Right on. When the changes land down the road I’ll run some benchmarks and post back here, if it’ll help others. Stoked you all are opting for JSON support for 2.0; thats huge (and sooner than expected!). I’ve been participating in the related discussions on github issues, so… been following that one closely.

CRDB has been a complete treat to work with so far, keep up the good work.

Just ran into this today and it’s a blocker if we can’t unnest array columns. Looks like this is on track for v2.1 so far…

This has been addressed https://github.com/cockroachdb/cockroach/pull/26503 and the fix will be available in CockroachDB 2.1.