jq
type
$(jq '.performer | type' <<<"$schema")
type can be null, boolean, number, string, array or object.
has(key)
https://github.com/shngli/Database-apps/tree/master/SQL
create table College(cName text, state text, enrollment int);
create table Student(sID int, sName text, GPA real, sizeHS int);
create table Apply(sID int, cName text, major text, decision text);
insert into Student values (123, 'Amy', 3.9, 1000);
insert into Student values (234, 'Bob', 3.6, 1500);
insert into Student values (345, 'Craig', 3.5, 500);
insert into Student values (456, 'Doris', 3.9, 1000);
insert into Student values (567, 'Edward', 2.9, 2000);
insert into Student values (678, 'Fay', 3.8, 200);
insert into Student values (789, 'Gary', 3.4, 800);
insert into Student values (987, 'Helen', 3.7, 800);
insert into Student values (876, 'Irene', 3.9, 400);
insert into Student values (765, 'Jay', 2.9, 1500);
insert into Student values (654, 'Amy', 3.9, 1000);
insert into Student values (543, 'Craig', 3.4, 2000);
insert into College values ('Stanford', 'CA', 15000);
insert into College values ('Berkeley', 'CA', 36000);
insert into College values ('MIT', 'MA', 10000);
insert into College values ('Cornell', 'NY', 21000);
insert into Apply values (123, 'Stanford', 'CS', 'Y');
insert into Apply values (123, 'Stanford', 'EE', 'N');
insert into Apply values (123, 'Berkeley', 'CS', 'Y');
insert into Apply values (123, 'Cornell', 'EE', 'Y');
insert into Apply values (234, 'Berkeley', 'biology', 'N');
insert into Apply values (345, 'MIT', 'bioengineering', 'Y');
insert into Apply values (345, 'Cornell', 'bioengineering', 'N');
insert into Apply values (345, 'Cornell', 'CS', 'Y');
insert into Apply values (345, 'Cornell', 'EE', 'N');
insert into Apply values (678, 'Stanford', 'history', 'Y');
insert into Apply values (987, 'Stanford', 'CS', 'Y');
insert into Apply values (987, 'Berkeley', 'CS', 'Y');
insert into Apply values (876, 'Stanford', 'CS', 'N');
insert into Apply values (876, 'MIT', 'biology', 'Y');
insert into Apply values (876, 'MIT', 'marine biology', 'N');
insert into Apply values (765, 'Stanford', 'history', 'Y');
insert into Apply values (765, 'Cornell', 'history', 'N');
insert into Apply values (765, 'Cornell', 'psychology', 'Y');
insert into Apply values (543, 'MIT', 'CS', 'N');
Converted into Json using https://beautifytools.com/sql-to-json-converter.php and stored in students.json
{
"Student": [{
"sID": 123,
"sName": "Amy",
"GPA": 3.9,
"sizeHS": 1000
},
{
"sID": 234,
"sName": "Bob",
"GPA": 3.6,
"sizeHS": 1500
},
{
"sID": 345,
"sName": "Craig",
"GPA": 3.5,
"sizeHS": 500
},
{
"sID": 456,
"sName": "Doris",
"GPA": 3.9,
"sizeHS": 1000
},
{
"sID": 567,
"sName": "Edward",
"GPA": 2.9,
"sizeHS": 2000
},
{
"sID": 678,
"sName": "Fay",
"GPA": 3.8,
"sizeHS": 200
},
{
"sID": 789,
"sName": "Gary",
"GPA": 3.4,
"sizeHS": 800
},
{
"sID": 987,
"sName": "Helen",
"GPA": 3.7,
"sizeHS": 800
},
{
"sID": 876,
"sName": "Irene",
"GPA": 3.9,
"sizeHS": 400
},
{
"sID": 765,
"sName": "Jay",
"GPA": 2.9,
"sizeHS": 1500
},
{
"sID": 654,
"sName": "Amy",
"GPA": 3.9,
"sizeHS": 1000
},
{
"sID": 543,
"sName": "Craig",
"GPA": 3.4,
"sizeHS": 2000
}
],
"College": [{
"cName": "Stanford",
"state": "CA",
"enrollment": 15000
},
{
"cName": "Berkeley",
"state": "CA",
"enrollment": 36000
},
{
"cName": "MIT",
"state": "MA",
"enrollment": 10000
},
{
"cName": "Cornell",
"state": "NY",
"enrollment": 21000
}
],
"Apply": [{
"sID": 123,
"cName": "Stanford",
"major": "CS",
"decision": "Y"
},
{
"sID": 123,
"cName": "Stanford",
"major": "EE",
"decision": "N"
},
{
"sID": 123,
"cName": "Berkeley",
"major": "CS",
"decision": "Y"
},
{
"sID": 123,
"cName": "Cornell",
"major": "EE",
"decision": "Y"
},
{
"sID": 234,
"cName": "Berkeley",
"major": "biology",
"decision": "N"
},
{
"sID": 345,
"cName": "MIT",
"major": "bioengineering",
"decision": "Y"
},
{
"sID": 345,
"cName": "Cornell",
"major": "bioengineering",
"decision": "N"
},
{
"sID": 345,
"cName": "Cornell",
"major": "CS",
"decision": "Y"
},
{
"sID": 345,
"cName": "Cornell",
"major": "EE",
"decision": "N"
},
{
"sID": 678,
"cName": "Stanford",
"major": "history",
"decision": "Y"
},
{
"sID": 987,
"cName": "Stanford",
"major": "CS",
"decision": "Y"
},
{
"sID": 987,
"cName": "Berkeley",
"major": "CS",
"decision": "Y"
},
{
"sID": 876,
"cName": "Stanford",
"major": "CS",
"decision": "N"
},
{
"sID": 876,
"cName": "MIT",
"major": "biology",
"decision": "Y"
},
{
"sID": 876,
"cName": "MIT",
"major": "marine biology",
"decision": "N"
},
{
"sID": 765,
"cName": "Stanford",
"major": "history",
"decision": "Y"
},
{
"sID": 765,
"cName": "Cornell",
"major": "history",
"decision": "N"
},
{
"sID": 765,
"cName": "Cornell",
"major": "psychology",
"decision": "Y"
},
{
"sID": 543,
"cName": "MIT",
"major": "CS",
"decision": "N"
}
]
}
Basic Queries
IDs, names, and GPAs of students with GPA > 3.6
select sID, sName, GPA
from Student
where GPA > 3.6;
Translating to jq involves using select(boolean_expression)
jq '[ .Student[] | select(.GPA > 3.6) | {"sID": .sID, "sName": .sName, "GPA": .GPA} ]' students.json
[
{
"sID": 123,
"sName": "Amy",
"GPA": 3.9
},
{
"sID": 456,
"sName": "Doris",
"GPA": 3.9
},
{
"sID": 678,
"sName": "Fay",
"GPA": 3.8
},
{
"sID": 987,
"sName": "Helen",
"GPA": 3.7
},
{
"sID": 876,
"sName": "Irene",
"GPA": 3.9
},
{
"sID": 654,
"sName": "Amy",
"GPA": 3.9
}
]
Student names and majors for which they’ve applied
select sName, major
from Student, Apply
where Student.sID = Apply.sID;
Here we need jq’s SQL-Style Operators,
JOIN($idx; stream; idx_expr; join_expr) combined with INDEX(stream; index_expression)
jq '[ JOIN(INDEX(.Student[]; .sID); .Apply[]; .sID | tostring; .) |
{ "sName": .[1].sName, "major": .[0].major} ]' students.json
select distinct sName, major
from Student, Apply
where Student.sID = Apply.sID;
[
{
"sName": "Amy",
"major": "CS"
},
{
"sName": "Amy",
"major": "EE"
},
{
"sName": "Amy",
"major": "CS"
},
{
"sName": "Amy",
"major": "EE"
},
{
"sName": "Bob",
"major": "biology"
},
{
"sName": "Craig",
"major": "bioengineering"
},
{
"sName": "Craig",
"major": "bioengineering"
},
{
"sName": "Craig",
"major": "CS"
},
{
"sName": "Craig",
"major": "EE"
},
{
"sName": "Fay",
"major": "history"
},
{
"sName": "Helen",
"major": "CS"
},
{
"sName": "Helen",
"major": "CS"
},
{
"sName": "Irene",
"major": "CS"
},
{
"sName": "Irene",
"major": "biology"
},
{
"sName": "Irene",
"major": "marine biology"
},
{
"sName": "Jay",
"major": "history"
},
{
"sName": "Jay",
"major": "history"
},
{
"sName": "Jay",
"major": "psychology"
},
{
"sName": "Craig",
"major": "CS"
}
]
The number of “rows” (objects in JSON parlance) returned is 19 which we can get by:
jq '[ JOIN(INDEX(.Student[]; .sID); .Apply[]; .sID | tostring; .) |
{ "sName": .[1].sName, "major": .[0].major} ] | length' students.json
Same query with Distinct
select distinct sName, major
from Student, Apply
where Student.sID = Apply.sID;
Here we need jq’s unique
jq '[ JOIN(INDEX(.Student[]; .sID); .Apply[]; .sID | tostring; .) |
{ "sName": .[1].sName, "major": .[0].major} ] | unique' students.json
[
{
"sName": "Amy",
"major": "CS"
},
{
"sName": "Craig",
"major": "CS"
},
{
"sName": "Helen",
"major": "CS"
},
{
"sName": "Irene",
"major": "CS"
},
{
"sName": "Amy",
"major": "EE"
},
{
"sName": "Craig",
"major": "EE"
},
{
"sName": "Craig",
"major": "bioengineering"
},
{
"sName": "Bob",
"major": "biology"
},
{
"sName": "Irene",
"major": "biology"
},
{
"sName": "Fay",
"major": "history"
},
{
"sName": "Jay",
"major": "history"
},
{
"sName": "Irene",
"major": "marine biology"
},
{
"sName": "Jay",
"major": "psychology"
}
]
Stripping out duplicates reduces the number of rows returned from 19 to 13.
jq '[ JOIN(INDEX(.Student[]; .sID); .Apply[]; .sID | tostring; .) |
{ "sName": .[1].sName, "major": .[0].major} ] | unique | length' students.json
Names and GPAs of students with sizeHS < 1000 applying to CS at Stanford, and the application decision
select sname, GPA, decision
from Student, Apply
where Student.sID = Apply.sID
and sizeHS < 1000 and major = 'CS' and cname = 'Stanford';
jq '[ JOIN(INDEX(.Student[]; .sID); .Apply[]; .sID | tostring; .) |
select(.[1].sizeHS < 1000) | select(.[0].major == "CS") | select(.[0].cName == "Stanford") |
{ "sName": .[1].sName, "GPA": .[1].GPA, "decision": .[0].decision} ]' students.json
[
{
"sName": "Helen",
"GPA": 3.7,
"decision": "Y"
},
{
"sName": "Irene",
"GPA": 3.9,
"decision": "N"
}
]
All large campuses with CS applicants
select distinct College.cName
from College, Apply
where College.cName = Apply.cName
and enrollment > 20000 and major = 'CS';
jq '[ JOIN(INDEX(.College[]; .cName); .Apply[]; .cName; .) |
select(.[1].enrollment < 20000) | select(.[0].major == "CS") |
{ "cName": .[0].cName } ] | unique ' students.json
[
{
"cName": "MIT"
},
{
"cName": "Stanford"
}
]
Application information (introduces sorting)
Sort by decreasing GPA
select Student.sID, sName, GPA, Apply.cName, enrollment
from Student, College, Apply
where Apply.sID = Student.sID and Apply.cName = College.cName
order by GPA desc;
Need to figure out how to join three tables in jq.
Then by increasing enrollment
select Student.sID, sName, GPA, Apply.cName, enrollment
from Student, College, Apply
where Apply.sID = Student.sID and Apply.cName = College.cName
order by GPA desc, enrollment;