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 ?
