Introducing Nested Segments

Published by cce on 2012-03-23

HTSQL now supports nested queries! Generation of multi-layered output is now trivial.

Overview

Often you want to organize summary and detailed data in a single hierarchical structure. In HTSQL, the segment (/) operator embeds the result of a correlated query as a nested list. For instance, a list of school records could include associated department records:

◥
/school{*, /department}

code	name	campus	department
			code	name	school_code
art	School of Art & Design	old	stdart	Studio Art	art
bus	School of Business	south	acc	Accounting	bus
			econ	Economics	bus
			mm	Management & Marketing	bus
edu	College of Education	old	edpol	Educational Policy	edu
			tched	Teacher Education	edu

Navigation

To understand this better, let’s review how navigation works. Suppose we have two related tables, school and department such that each department may be associated with a particular school. Conversely, each school is correlated with a list of its associated departments.

The following query first navigates to school, and then for each school, navigates to the associated departments. These correlated department records are returned.

◥
/school.department

code	name	school_code
stdart	Studio Art	art
acc	Accounting	bus
econ	Economics	bus
mm	Management & Marketing	bus
edpol	Educational Policy	edu
tched	Teacher Education	edu

Note this query is not the same as /department, even though both return records from the department table. The /school.department query returns only departments associated with some school, and orders them by school.

The following example also navigates from school to department. However, this linking happens in the context of the count() aggregate. Hence, we get schools, and for each school, the count of associated departments.

◥
/school{code, count(department)}

code	count(department)
art	1
bus	3
edu	2

The segment operator is also an aggregate — instead of returning a scalar, it returns a nested list.

◥
/school{code, count(department), /department}

code	count(department)	department
		code	name	school_code
art	1	stdart	Studio Art	art
bus	3	acc	Accounting	bus
		econ	Economics	bus
		mm	Management & Marketing	bus
edu	2	edpol	Educational Policy	edu
		tched	Teacher Education	edu

More Examples

This mechanism is not a special case, it is an integral part of our query language. For example, it works with projections.

◥
/school^campus{campus, /school{name}}

campus	school
	name
north	School of Engineering
old	School of Art & Design
	College of Education
	School of Arts and Humanities
	School of Natural Sciences
south	School of Business
	School of Music & Dance

Nested segments play well with aggregates and variables.

◥
/department{name+,
            $avg_credits := avg(course.credits),
            /course{credits, title}?credits>$avg_credits }

name	$avg_credits	course
		credits	title
Accounting	3.5	5	Financial Accounting
		5	Audit
		6	Accounting Internship
Art History	3.5	4	History of Art Criticism I
		6	Antique Art: Greece and Rome
		4	Antique Art: The Middle East
		5	Islamic Art
		5	Art of Photography
		4	Museum and Gallery Management

Nesting can be arbitrarily deep.

◥
/school{name+,
        /department{name,
                    /course{title}}}

name	department
	name	course
		title
College of Education	Educational Policy	Introduction to Education
		Contemporary Society
		Sociology of Childhood
		Technology in the Classroom
		Technology, Society and Schools
		Economics and Education Policy
		Politics and Education Policy
		Education Policy Analysis
		Children’s Literature
		Education Policy and Practice
		Social Analysis of Education Policy
		Classroom Visit
		Organizational Analysis of Education Policy
		Seminar in Education Policy I
		Seminar in Education Policy II
		Qualitative Research in Education Policy
	Teacher Education	Teaching Methodology
		Theory and Practice of Early Childhood Education
		Methods of Early Science Education
		Play as Education Method
		Developmental Psychology
		Selection of Learning Resources
		Teacher Identity
		Problems in Education Management
		Challenges of Teaching the Gifted and Talented
		Techniques of Mathematics Teaching
		Techniques of Science Teaching
		Techniques of Language Teaching
		Problems in Education
		Public School Internship
		Preschool Internship
		Special Topics in Teacher Education
		Practice of Mathematics Teaching
		Practice of Science Teaching
		Practice of Language Teaching

A query may have adjacent nested segments.

◥
/school{name, /department{name}, /program{title}}

name	department	program
	name	title
School of Art & Design	Studio Art	Post Baccalaureate in Art History
		Bachelor of Arts in Art History
		Bachelor of Arts in Studio Art
School of Business	Accounting	Master of Arts in Economics
	Economics	Graduate Certificate in Accounting
	Management & Marketing	Certificate in Business Administration
		B.S. in Accounting
		Bachelor of Business Administration
		Bachelor of Arts in Economics

For tabular formats, we show parallel tables adjacent to each other. In the query above, programs and departments are shown side-by-side even though they are not correlated with each other.

Presentation

Nested segments are supported by all HTSQL output formats. The hierarchical structure of the output is very suitable for XML and JSON formats.

◥
/school{*, /department} /:xml

<?xml version="1.0" encoding="UTF-8" ?>
<htsql:result xmlns:htsql="http://htsql.org/2010/xml">
  <school>
    <code>art</code>
    <name>School of Art &amp; Design</name>
    <campus>old</campus>
    <department>
      <code>stdart</code>
      <name>Studio Art</name>
      <school_code>art</school_code>
    </department>
  </school>
  <school>
    <code>bus</code>
    <name>School of Business</name>
    <campus>south</campus>
    <department>
      <code>acc</code>
      <name>Accounting</name>
      <school_code>bus</school_code>
    </department>
    <department>
      <code>econ</code>
      <name>Economics</name>
      <school_code>bus</school_code>
    </department>
    <department>
      <code>mm</code>
      <name>Management &amp; Marketing</name>
      <school_code>bus</school_code>
    </department>
  </school>
…

◥
/school{*, /department} /:json

{
  "school": [
    {
      "code": "art",
      "name": "School of Art & Design",
      "campus": "old",
      "department": [
        {
          "code": "stdart",
          "name": "Studio Art",
          "school_code": "art"
        }
      ]
    },
    {
      "code": "bus",
      "name": "School of Business",
      "campus": "south",
      "department": [
        {
          "code": "acc",
          "name": "Accounting",
          "school_code": "bus"
        },
        {
          "code": "econ",
          "name": "Economics",
          "school_code": "bus"
        },
        {
          "code": "mm",
          "name": "Management & Marketing",
          "school_code": "bus"
        }
      ]
    },
…

blog comments powered by Disqus