Skip to content

Instantly share code, notes, and snippets.

@VeryFatBoy
Forked from robrich/sql-to-json.sql
Created October 12, 2021 16:30
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save VeryFatBoy/a38cc0efb07fb47dc39bf9304771fdd4 to your computer and use it in GitHub Desktop.
Save VeryFatBoy/a38cc0efb07fb47dc39bf9304771fdd4 to your computer and use it in GitHub Desktop.
Select relational data into JSON with SingleStore: to_json() and json_agg()
-- 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment