maria-developers team mailing list archive

[Alexander Barkov <bar@xxxxxxxxxxx>]

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".