12 okt 2015, om 20:22, R.Smith:
>
> On 2015-10-12 07:40 PM, Richard Hipp wrote:
>> On 10/12/15, R.Smith <rsmith at rsweb.co.za> wrote:
>>> More explicitly - would these be valid queries:
>>>
>>> SELECT props.*
>>> FROM (
>>> SELECT 1 as id, JSON_OBJECT('i',5,'n','James') AS obj UNION
>>> ALL
>>> SELECT 2, JSON_OBJECT('i',6,'n','Jill') UNION ALL
>>> SELECT 3, JSON_OBJECT('i',7,'n','John')
>>> ) AS T1, JSON_EACH(T1.obj) AS props
>>> WHERE 1
>>>
>> Yes.
>>
>>> and in future (without JSON):
>>>
>>> SELECT B.* FROM
>>> (SELECT (C1 || C2 || C3) AS CX FROM T) AS A,
>>> (SELECT CX FROM A) AS B
>>> WHERE 1
>>>
>> This is not valid, and has never been valid. I'm sorry, but I don't
>> understand the connection to the previous query. They are completely
>> different, as far as I can see.
>
> Yes, I guess what I am after is quantifying the difference. In The
> first query, an alias to a completely made-up table is passed to a
> table-valued function (the T1 alias) and it knows to reference/read
> from that made up table designated as T1.
>
> In the second query, a completely made up table A is used in a
> second table definition (but this time not a table-valued function -
> which is the only real difference) to produce B.
>
> Of course they are not the same, and the first works but the second
> doesn't - so is it ONLY because the first is a Table-valued function
> (and thus will this be common workings for Table-valued functions
> henceforth), or perhaps it has to be an eponymous virtual table, or
> is it specifically because it's a JSON function which allows this
> uniquely?
>
> I think/hope this will be common workings for all table-valued
> functions - this is essentially what I'm asking.
>
> (The other question about whether this would work with normal table
> functions is already answered and understood to be: Never).
>
> Thank you kindly,
> Ryan
Hello, I still wish to add something and would like to hear if I'm
wrong.
The question if table aliasses are visible within json functions
becomes easier when using an alternative notation. It looks as if
table-like functions do have "column-like" parameters that can be
passed in regular WHERE clauses.
The common notation:
json_each(t1.obj) as props
is then:
json_each as props where props.json=t1.obj
Note that I'm uninvolved and only found this while experimenting with
SQLite 3.9.0.
Ryan Smith's example query:
SELECT t1.*, props.*
FROM (
SELECT 1 as id, JSON_OBJECT('i',5,'n','James') AS obj UNION ALL
SELECT 2, JSON_OBJECT('i',6,'n','Jill') UNION ALL
SELECT 3, JSON_OBJECT('i',7,'n','John')
) AS t1
INNER JOIN JSON_EACH(t1.obj) AS props
can be written as
SELECT t1.id, props.*
FROM (
SELECT 1 as id, JSON_OBJECT('i',5,'n','James') AS json UNION ALL
SELECT 2, JSON_OBJECT('i',6,'n','Jill') UNION ALL
SELECT 3, JSON_OBJECT('i',7,'n','John')
) AS t1
JOIN (SELECT '$.n' AS root) t2
JOIN JSON_EACH AS props
WHERE props.json=t1.json
and that should clear the initial question marks.
I assume it remains so in future versions but actually don't care.
Regards, Edzard.
