Select relational data into JSON with SingleStore: to_json() and json_agg()

sql-to-json.sql

-- Create database
create database if not exists acme;
use acme;

-- Create table
create rowstore table `record` (
  `name` varchar(100) DEFAULT NULL,
  `type` varchar(100) DEFAULT NULL,
  `amount` int(11) DEFAULT NULL,
  SHARD KEY () 
);

-- Thanks https://randomuser.me/
insert into record (`name`, `type`, `amount`)
values ('Adelia Leuschke','gmail.com',2221.56),
('Cielo Zemlak','gmail.com',6484.61),
('Evelyn Larkin','demarco.com',1331.49),
('Maida McGlynn','gmail.com',4720.33),
('Rodrick Ward','brown.org',2501.06),
('Maxie McCullough','gmail.com',7867.45),
('Fidel Emmerich','gmail.com',3686.67),
('Twila Flatley','demarco.com',7433.51),
('Jaquan Rohan','demarco.com',910.50),
('Randal Purdy','gmail.com',9436.18);

-- Look at the data
select * from record;

-- Turn it into json, 1 object per row
select to_json(t.*) from (
  select name, amount, type from record
) t;

-- Group by
select type, count(*) as count
from record
group by type;

-- Get all names for type
select type, json_agg(name) as records
from record
group by type;

-- nested json_agg()
select json_agg(t.*) records from (
  select type, json_agg(t.*) records
  from record t
  group by type
) t;

-- Group by type, json object per matching row
select type, json_agg(
  row(name, amount):>record(name varchar(10), amount int(11))
) as records
from record
group by type;

-- One big object grouped by type
with r1 as (
  select type, json_agg(
    row(name, amount):>record(name varchar(10), amount int(11))
  ) as records
  from record
  group by type
) select json_agg(r1.*)
from r1;