nest the top resource - Add a way for * to be remapped in select

[ceymard]

/my_table?select=$:* is an error. I can rename a field with select. Since in postgres it is possible to select a whole row, it would be interesting to be able to rename it as well.

GET /my_table
[{"a": 1, "b": 2}, ...]

GET /my_table?$:*
[{"$": {"a": 1, "b", 2}}, {"$": { ... } } ...]

While on its own it may not look much, when traversing relationships, it gets really interesting - especially on the frontend side for deserialization.

[steve-chavez]

Since in postgres it is possible to select a whole row, it would be interesting to be able to rename it as well.

Can you show the SQL query you do for this? Is it something like:

select (id,name) as "$" from test.projects;
        $         
------------------
 (1,"Windows 7")
 (2,"Windows 10")
 (3,IOS)
 (4,OSX)
 (5,Orphan)
(5 rows)

I'm not seeing why would this be useful.

[ceymard]

It's rather

select tbl as "$" from test.projects tbl

When outputing JSON, since this is a row type, then the result is a json object. The beauty of it is that you actually don't even have to list all the fields !

It's useful when calling subrelations so as to not mix fields and objects.

GET /my_table?select=$:*,some_relation(*)
[{"$": {"id": 1, "name": "hello"}, "some_relation": [ ... ] }]

Here, it allows me to use frameworks on the client side that know how to deserialize into custom instances directly "$" into, say, the "principal" object and "some_relation" into other instances.

This is actually a real requirement : it has been a pain point for me to have to deal with the fields of my table being mingled with fields that regard a distant relation. My code actually has to split the result from postgrest.

I think it has a place ; it's kind of the reverse of ... which wants to merge fields, in here on the contrary I want to make sure everything is separate.

[steve-chavez]

I think it has a place ; it's kind of the reverse of ... which wants to merge fields, in here on the contrary I want to make sure everything is separate.

Hm, so instead of "unnesting" you want to "nest" the resulting JSON arbitraritly.
The $ made things confusing for me, but essentially you want:

GET /my_table?select=my_custom_nest:*,some_relation(*)

Q: Is this only relevant for the root resource?

Because you can already rename nested/embedded resources /my_table?select=*,rename:some_relation(*)

[ceymard]

A: Yes it is for the reason you mentioned.

And yes, this is exactly what I wish to do.

[steve-chavez]

GET /my_table?select=$:,some_relation()
[{"$": {"id": 1, "name": "hello"}, "some_relation": [ ... ] }]

Doing the above seems possible on PostgreSQL side, but I don't think that's the right URL syntax.

[steve-chavez]

One idea could be to have a special placeholder that refers to the root resource, say ^:

GET /my_table?select=renamed:^(col1,col2)

[ceymard]

That would do the trick nicely.

[wolfgangwalther]

If we were doing something like this, I think the "alias the star" approach would be really nice and clean. I'd not want to introduce even more new syntax.

[steve-chavez]

With "alias the star" you couldn't control the specified columns. Additionally, the ^ would allow avoiding specifying the same name of the table if that's what you want:

GET /my_table?select=^(col1,col2)
[{"my_table": {"id": 1, "name": "hello"}}]

This is also not a priority for me and not sure if this is the right syntax.

I guess the feature makes sense to allow the complete shaping of the resulting JSON though.

[ceymard]

Actually, better proposition (imo) :

GET /my_table?select=wrapper:(col1,col2),everything:(*),nested:(more_nest:(*))
[{
  "wrapper": {"col1": 1, "col2": 2}, 
  "everything": {"col1": 1, "col2": 2, "some_other_col": 3}, 
  "nested": {
    "more_nest": {"col1": 1, "col2": 2, "some_other_col": 3}
  }
}]

I added indentation for readability. I am thus proposing that instead of aliasing *, opening a ( after : triggers the creation of an object. This could also work with relations that could then be included right there.

What do you think ?

[steve-chavez]

GET /my_table?select=wrapper:(col1,col2),everything:(),nested:(more_nest:())

It does look better in terms of flexibilty but it's strange to have an "empty" resource alias:<empty>(cols), not sure if this would be hard on the parsers. If the resource is "empty" (actually not another resource), maybe we could use something like:

GET /my_table?select=wrapper:_(col1,col2),everything:_(*),nested:_(more_nest:_(*))

_ meaning "not a resource", so PostgREST won't search for a related resource through FKs and it will only nest the json.

The above syntax makes it a bit clearer that we are manipulating the resulting JSON too, whereas with the :( syntax that behavior is unclear.

[steve-chavez]

Still it would be much better we had an explicit operation to do the above instead of a special placeholder. Something like the spread ... operator.

[wolfgangwalther]

It does look better in terms of flexibilty but it's strange to have an "empty" resource alias:<empty>(cols), not sure if this would be hard on the parsers.

I haven't thought about the parsers, but "empty" in this place would really be a natural extension of our existing syntax, so +1 for that.

[steve-chavez]

I haven't thought about the parsers, but "empty" in this place would really be a natural extension of our existing syntax,

I guess it should be fine, since we already support empty embeds. I can't think of a better syntax.