Object projection in attribute api

[hongsudt]

Projected result set goals

To make it easier for browser applications to fetch denormalized copies of entities referenced by foreign key, we'd like to be able to do some compact request and receive rows of data like:

[
  { 
    "a": { columns of S:T, ... }, 
    "b": { columns of entity referenced by S:T:b, ... }, 
    "c": { columns of entity referenced by S:T:c, ... }
  },
  ...
]

i.e. each row has a nested object containing column values for the main entity and referenced entities, where the client can define the output names for each object using our normal output renaming syntax in the URI.

API design challenge

This needs to handle the general case for specifying what foreign key to follow for each projected sub-object, which unfortunately means a messy syntax like ( path cols )=( schema cols ) to unambiguously specify a complete foreign key link structure.

This also raises questions as to how general it should be. Does it support both outbound and inbound foreign keys? Multiple hops, i.e. for association tables?

Possible API notations

Let's assume we want to add new projection syntax to the /attribute/ API.

Define object projection operators for one link

This approach tries to simplify notation with the limitation that at most one link can be traversed in the projection:

• obj( link ) produces null or a single object {...}
  • error to use with inbound links? or choose one entity arbitrarily?
• objs( link ) produces an array of zero or more objects [{...}, ...]

Link notations:

example using obj	equivalent link within entity paths
obj()	N/A (resolves to path context)
obj(col1,col2)	... /(col1,col2)/ ...
obj(S:T:col1,col2)	... /(S:T:col1,col2)/ ...
obj(Alias:col1,col2)	... /(Alias:col1,col2)/ ...
obj(Alias:col1,col2=S:T:col1,col2)	... /(Alias:col1,col2)=(S:T:col1,col2)/ ...

The obj operator could also be replaced with objs to get an array instead of single object with no other difference in syntax.

Define subquery operators for one or more links

These all resolve an entity relpath relative to the current path context. This means that they use the notation for entity paths, starting with a path link element which denotes a link relative to the path context, instead of starting with a bare table reference from the schema. They can contain any number of filters and additional link elements after the first link element. An empty entity relpath is allowed to project the path context itself.

• @E( entity relpath ) behaves like the /entity/ API as a subquery rooted at the current path context. (E stands for entity.)
• @A( entity relpath / projection ) behaves like the /attribute/ API as a subquery rooted at the current path context. (A stands for attribute.)
• @G( entity relpath / group keys ; projection ) behaves like the /attributegroup/ API as a subquery rooted at the current path context. (G stands for group.)
• @R( entity relpath / projection ) behaves like the /aggregate/ API as a subquery rooted at the current path context. (R stands for reduce.)

The @(...) or @E(...) projection could express the same things as the previously proposed obj proposal but without the syntactic short-hands that omit parentheses. These parentheses are necessary to disambiguate table references from link endpoints.

example
@E()
@E((col1,col2))
@E((S:T:col1,col2))
@E((Alias:col1,col2))
@E((Alias:col1,col2)=(S:T:col1,col2))
@E(S:T)
@E((col1,col2)/(col3)/(col1))

We could add variants like @E1(...) to request a single object instead of a variable length array?

What we gain is the flexibility to express complex path navigation, do aggregate reductions of related entities, and possibly even use the notation recursively to build more deeply structured object trees as results. (Of course, such generalization of the syntax also requires a lot more development work to add the feature.)

[karlcz]

I revised the issue description again. This needs more thought and design work as it is precariously close to the previously deferred goal of having scalar subqueries in the projection list...

[karlcz]

@hongsudt @RFSH @robes @shinyichen @mikedarcy

This general sub-query feature is blocked on design and invasive code refactoring. In the interim, outer joins in #99 may be sufficient for some simpler use cases where you just want a core entity and several optional extensions that it points at by nullable foreign key.

/attributegroup/A:=T1/B:=left(A:fk1)=(T2:id)/C:=left(A:fk2)=(T3:id)/$A/id;a:=array(A:*),b:=array(B:*),c:=array(C:*)

This will produce an output very similar to that in the issue description, only marred by the redundant row key and the wrapping of each sub-object as an array of length 1:

[
  { 
    "id": X,
    "a": [{ columns of T1, ... }], 
    "b": [{ columns of T2 entity referenced by T1:fk1, ... }], 
    "c": [{ columns of T3 entity referenced by T1:fk2, ... }]
  },
  ...
]

Furthermore, you need to understand your model enough to include the main entity key column (or columns) as the output group key. The use of the left keyword to enable left outer joins means that this will work even if the foreign key values happen to be NULL. With an inner join, entities in T1 with NULL outbound foreign keys would be excluded.

[karlcz]

A few more UX discussions have exposed more feature desires for the generalized sub-query idea. GUI app writers would like more control over the aggregate reduction mechanism than is currently provided by the /attributegroup/ API.

1. Rather than always grouping and aggregating over a general cross-product, sometimes the apps want to combine the /entity/ or /attribute/ API style navigation with aggregation. They would like to expand a set of reachable entities and then reduce that set with aggregate functions. They do not want to reduce the cross-product, distorting the population with multipath duplication.
2. This entity-based aggregation should also be combined with grouping mechanisms to return a sub-population aggregate per parent record. This is often written in idiomatic SQL using correlated sub-queries.
3. Sometimes the apps want to use aggregate functions like array to return sets of values, but they would want to apply a limit and a customized sort order, e.g. to only fetch the first page from a potentially large result. If performing grouped aggregations, they want a limited "page" per grouped result row to efficiently retrieve an initial result set. (Further scrolling through the pages would throw away this outer grouped structure and drill into a single group's result using a different ermrest query form, e.g. as the user clicks a "more..." link.)