CODEWITHSUNDEEP
postgresql
Caroline Kwerts
Caroline Kwerts

Reputation: 225

How can you do projection with array_agg(order by)?

I have a table with three columns: id, name and position. I want to create a JSON array as following:

[
    {"id": 443, "name": "first"},
    {"id": 645, "name": "second"}
]

This should be listed by the position column.

I started with the following query:

with output as
(
    select id, name
    from   the_table
)
select array_to_json(array_agg(output))
from   output

This works, great. Now I want to add the ordering. I started with this:

with output as
(
    select id, name, position
    from   the_table
)
select array_to_json(array_agg(output order by output.position))
from   output

Now the output is as following:

[
    {"id": 443, "name": "first", "position": 1},
    {"id": 645, "name": "second", "position": 2}
]

But I don't want the position field in the output.

I am facing a chicken-egg problem: I need the position column to be able to order on it, but I also don't want the position column, as I don't want it in the result output.

How can I fix this?

I don't think the following query is correct, as table ordering is (theoretically) not preserved between queries:

with output as
(
    select   id, name
    from     the_table
    order by position
)
select array_to_json(array_agg(output))
from   output

Upvotes: 2

Views: 804

Answers (1)

Abelisto
Abelisto

Reputation: 15624

There are two ways (at least):

Build JSON object:

with t(x,y) as (values(1,1),(2,2))
select json_agg(json_build_object('x',t.x) order by t.y) from t;

Or delete unnecessary key:

with t(x,y) as (values(1,1),(2,2))
select json_agg((to_jsonb(t)-'y')::json order by t.y) from t;

Note that in the second case you need some type casts because - operator defined only for JSONB type.

Also note that I used direct JSON aggregation json_agg() instead of pair array_to_json(array_agg())

Upvotes: 1

Related Questions