I think most people will find that confusing (since they'll then have to quote a dotted identifier). For example you might write a query:
select t.`a.b`.c.d. While people are free to add quoted periods in their identifiers, I don't think we should in the system. If someone wants fine-grained renaming control, I think they should use our existing capability around selecting individual values. The conflicts thing is problem, but I don't see it as any different than: select t1.*, t2.* from t1,t2 Since SQL doesn't let you do table sub-aliases in a * case, I think we should introducing it. On Mon, Oct 5, 2015 at 2:43 PM, Jinfeng Ni <[email protected]> wrote: > This proposal makes sense to me. It's quite consistent to the current > behavior when dealing with the top level * column. Also, I like the > idea that we start with a restricted case. > > For v1 syntax: > > select t.id, t.b.* from t; > > Given the input > { > id: 123, > b: { > x:10, > y:20 > } > } > > I'm wondering if we should get > > { > id: 123, > b.x: 10, > b.y:20 > } > > In stead of > { > id: 123, > x: 10, > y:20 > } > > For t.* as the top level * case, t is either a table name or table > alias, and in the output field, we do not keep 't'. However, in the > case of t.b.*, I feel 'b' is different from 't', since 'b' is field > name in the record; it would be better to keep 'b' in the output field > after expanding t.b.*. > > This is also useful to resolve naming conflicts, if we have > > select t.b.*, t.c.* from t; > > where b and c both have "x" and "y". > > > > On Sun, Oct 4, 2015 at 3:07 PM, Jacques Nadeau <[email protected]> wrote: > > I agree with Julian about use cases. > > > > As I see it, we have one pressing issue: right now there is no way to > move > > a map upward without have to manually select all the fields. A large > number > > of users I've spoken to have found this frustrating and limiting (and not > > possible depending on the dynamic nature of their data). I think we > should > > solve this now. In general, this thread all seem to agree that the > 'select > > t.b.* from t' makes sense for that use case. We can limit this behavior > as > > follows: the selection can include only one star and it must be the > > termination of selection string. That means the following are allowed: > > > > select * from t (already supported) > > select t.* from t (already supported) > > select t.a.* from t > > select t.a.* as x from t (the x would simply be ignored) > > select t.a.b.c.d.e.* from t > > > > however, the following would not be supported: > > select t.*.a > > select t.b.*.* > > select t.b.*.x.* > > > > Complicated pattern matching is something that hasn't been an issue for > > most customers I've spoken to and should be shelved until we have a solid > > set of requirements. In the meantime, custom UDFs can solve this for > > customers. Once we have a bunch of UDFs floating around for these and > their > > supporting use cases, we can come up with a more consolidated approach. > > > > As far as the JSON_TABLE stuff is concerned, I'm not inclined to adopt > any > > of it since it seems far too complex for most users. (It seems like it is > > trying to fit json into a sql paradigm rather than shape sql to support > the > > flexibility and expressive of json.) > > > > How does that sound as a first step? > > > > > > > > On Sun, Oct 4, 2015 at 5:47 PM, Julian Hyde <[email protected]> wrote: > > > >> For expanding one level, I like the first syntax, "select t.id, t.b.* > >> from t;". > >> > >> Why? It is compatible with SQL (in particular how SQL assigns default > >> aliases to expressions), and is consistent how shell globbing works. > >> The effect is as if the user wrote 'select t.id, t.b.x, t.b.y from t'. > >> > >> For multiple levels, I think it becomes a different problem. Remember > >> the analogy with filename globbing, and in particular, how you > >> selectively delete files in a directory tree. Windows has a recursive > >> delete command that does something akin to globbing inside the command > >> (Windows shell does not do globbing). Unix globbing doesn't work > >> recursively, so you use the 'find' command, which let's you control > >> your search based on depth and file attributes as well as file name. > >> > >> If you are expanding deep json documents, I think you need the same > >> amount of control. The documents might contain multiple fields of the > >> same name, especially if the document has repeating elements, and > >> sometimes you might want to return objects and arrays from deep in the > >> tree, not just leaves. > >> > >> So, for recursive expansion, I think we would need a UDF. It's worth > >> looking at JSON_TABLE[1] in Oracle and the SQL standard. > >> > >> For two-level expansion, people might be happy to write > >> > >> select t.id, t.*, t.*.* from t > >> > >> or even, as Ted suggests, > >> > >> select t.id, t.*, t.(x,y).* from t > >> > >> (note the similarity with unix file globbing syntax {x,y}). > >> > >> Let's find a couple of motivating use cases for this before we commit > >> to a syntax. I bet they will pose issues that do not occur in the toy > >> examples. > >> > >> Julian > >> > >> [1] > >> > https://docs.oracle.com/cloud/latest/db121/SQLRF/functions091.htm#SQLRF56973 > >> > >> On Sun, Oct 4, 2015 at 12:26 PM, Ted Dunning <[email protected]> > >> wrote: > >> > > >> > I would expect full recursion. Anything short of that seems highly > >> inconsistent. > >> > > >> > Sent from my iPhone > >> > > >> >> On Oct 4, 2015, at 14:27, Aman Sinha <[email protected]> wrote: > >> >> > >> >> The v1 syntax 'select t.id, t.b.* from t;' seems reasonable to me > for > >> the > >> >> first level. The alias can be ignored for the star case. This is > what > >> we > >> >> do (and consistent with Postgres behavior) for queries such as > 'select > >> r.* > >> >> as x from region r;'. > >> >> > >> >> For multiple levels, would there be a requirement to expand > recursively > >> to > >> >> more levels within b ? or is that not a valid requirement ? For > example: > >> >> b: { > >> >> c: { x:10, > >> >> y:20 > >> >> }, > >> >> d: { p: 5, > >> >> q: 15 > >> >> } > >> >> } > >> >> > >> >> Is b.* expected to expand only to c and d levels or further ? > >> >
