Frontier Software

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;