[Maria-developers] FOR JSON
Hello Monty, We discussed extracting data in JSON format today, to make it possible to return data as a single JSON value (as a result set consisting of a single row and a single column): SELECT id,name,address FROM users FOR JSON; Note, in some cases the result of a such query can obviously be very huge. So it would be nice to be able to use "FOR JSON" in subselects, like this: SELECT id, (SELECT name,address FROM users AS b WHERE a.id= b.id FOR JSON) AS RowJSON FROM users AS a; This query will return a result set with multiple rows (one row per one record) and with two columns: - the ID, as a regular number - the JSON value with "name" and "address" embedded into it. Or, another example, if one does not need "id" as a separate column: SELECT (SELECT id,name,address FROM users AS b WHERE a.id= b.id FOR JSON) AS RowJSON FROM users AS a; This query will return a result set with multiple rows (one row per one record) with a single JSON value embedding all three columns: "id", "name" and "address".
Hi, Alexander! On May 17, Alexander Barkov wrote:
Hello Monty,
We discussed extracting data in JSON format today, to make it possible to return data as a single JSON value (as a result set consisting of a single row and a single column):
SELECT id,name,address FROM users FOR JSON;
This can be built on the existing functionality. "FOR JSON" is a result filter, something that gets the result of a query and transforms it into something different, different set of columns even. Natirally, in MySQL language this should've been SELECT id,name,address FROM users PROCEDURE JSON; And tada - it took only 15 years for a second useful procedure to appear :) Although, seriously, I don't expect PROCEDURE JSON to work out of the box - procedure api is way too old and hardly ever used. I woldn't be surprised if it's broken in many subtle ways. Regards, Sergei
Hello Sergei, On 05/25/2014 08:10 PM, Sergei Golubchik wrote:
Hi, Alexander!
On May 17, Alexander Barkov wrote:
Hello Monty,
We discussed extracting data in JSON format today, to make it possible to return data as a single JSON value (as a result set consisting of a single row and a single column):
SELECT id,name,address FROM users FOR JSON;
This can be built on the existing functionality.
"FOR JSON" is a result filter, something that gets the result of a query and transforms it into something different, different set of columns even.
Natirally, in MySQL language this should've been
SELECT id,name,address FROM users PROCEDURE JSON;
We can make these two as synonyms: SELECT id,name,address FROM users PROCEDURE JSON; and SELECT id,name,address FROM users FOR JSON; so internally they map into the same thing.
And tada - it took only 15 years for a second useful procedure to appear :)
:)
Although, seriously, I don't expect PROCEDURE JSON to work out of the box - procedure api is way too old and hardly ever used. I woldn't be surprised if it's broken in many subtle ways.
At least this does not work: select * from (select user from mysql.user procedure analyse())\G ERROR 1221 (HY000): Incorrect usage of PROCEDURE and subquery So it does need some hacking. I think having JSON in subselects is important, to be able to get one JSON value per record when needed (instead of a single huge JSON value with one row and one column bundling all records).
Regards, Sergei
Hi, Alexander! On May 26, Alexander Barkov wrote:
SELECT id,name,address FROM users FOR JSON;
This can be built on the existing functionality.
"FOR JSON" is a result filter, something that gets the result of a query and transforms it into something different, different set of columns even. Naturally, in MySQL language this should've been
SELECT id,name,address FROM users PROCEDURE JSON;
We can make these two as synonyms:
SELECT id,name,address FROM users PROCEDURE JSON;
and
SELECT id,name,address FROM users FOR JSON;
so internally they map into the same thing.
Yes, it's a detail. My point was that we shouldn't have two implementations for the same feature (arbitrary transformation of the query result). So either we reuse the code for procedures, or we do a new implementation and move PROCEDURE ANALYSE to use it.
Although, seriously, I don't expect PROCEDURE JSON to work out of the box - procedure api is way too old and hardly ever used. I woldn't be surprised if it's broken in many subtle ways.
At least this does not work:
select * from (select user from mysql.user procedure analyse())\G ERROR 1221 (HY000): Incorrect usage of PROCEDURE and subquery So it does need some hacking.
No wonder - procedures were added many years before subqueries :)
I think having JSON in subselects is important, to be able to get one JSON value per record when needed (instead of a single huge JSON value with one row and one column bundling all records).
Yes, sure. I totally agree with that. Regards, Sergei
Hi Sergei, On 05/26/2014 04:48 PM, Sergei Golubchik wrote: <skip>
Yes, it's a detail. My point was that we shouldn't have two implementations for the same feature (arbitrary transformation of the query result). So either we reuse the code for procedures, or we do a new implementation and move PROCEDURE ANALYSE to use it.
By the way, JSON is not really an arbitrary transformation. Well, it is a transformation, but not very arbitrary :) Another way to implement this would be to have a GROUP_CONCAT-alike aggregate function, so SELECT a,b FROM t1 FOR JSON; is automatically mapped into: SELECT JSON_CONCAT(a,b) AS JSON FROM t1; A function like this would give even more flexibility, e.g. having two or more JSON-formatted columns in the same result set: SELECT group_ID, JSON_CONCAT(a,b), JSON_CONCAT(c,d) FROM t1 GROUP BY group_ID; So we have these ideas: 1. A session variable. 2. SELECT ... FROM t1 FOR JSON 3. SELECT JSON_CONCAT() FROM t1 The last one seems to be the most flexible. And the fist one is the least flexible. <skip>
participants (2)
-
Alexander Barkov -
Sergei Golubchik