Strange ARRAY_AGG behavior on join

SQL++
1

I have the following buckets:

// makes
{ "id": "1", "name": "Chevrolet" }

// models
{ "id": "2", "name": "Blazer" }

// make_models
{ "id": "3", "makeId": "1", "modelId": "2" }

The targeted output I am looking for is

{ "makeId": "1"
, "models": [
    { "id": "2", "name": "Blazer" }
  ]
}

I am using the following query and it works (almost all of the time)

select
  mm.makeId as makeId
  , ARRAY_AGG((
    select
      m.id
      , m.name
    from models m
    use keys mm.modelId
  )) as makes
from
  make_models mm
join
  models mods
on keys
  mm.modelId
where mm.modelId = '< some id >'
group by
  mm.makeId

The only time it gives strange results is when the makeId does not exist. It returns the following:

[{ "models": null }]

Anyone have any advice on this? I would expect no results to be returned here.

Thanks in advance!

2

Hi,

You can use either of the following two approaches, but cannot combine them:

SELECT mm.makeId, ARRAY_AGG( { "id": mo.id, "name": mo.name } ) AS models
FROM make_models mm
JOIN models mo ON KEYS mm.modelId
WHERE ...
GROUP BY mm.makeId;

SELECT mm.makeId, (SELECT id, name FROM	models USE KEYS	mm.modelId) AS models
FROM make_models mm
WHERE ...;
1 Like
3

Hi @geraldss. Thanks for responding.

So using the first approach, I still get the same problem.

Using the second one, I keep getting “Expression must be a group key or aggregate” error. Any thoughts?

4

Can you post your exact queries, one for each approach?

5

First:

select mm.makeId, ARRAY_AGG({ "id": mo.id, "name": mo.name }) as models
from make_models mm
join models mo on keys mm.modelId
where mm.modelId = '<id>'
group by mm.makeId

The second one is now working, so not sure what I was doing wrong on that. Thanks!

6

Ah I see why it wasn’t working. I am trying to only get the makeId at the top level, not at the models level (each model has it’s own id, but it is different). So it seems like the group by is causing the issue. I get an error like “Expression must be group key or aggregate”, but I can’t group by a subquery, can I?

7

Hi @evanlucas,

For me to follow along, you would have to post both queries, and then label each one as “working” or “not working”.

Thanks,
Gerald

8

I ended up just changing my layout some. Thanks for the help though!