Schema from Mongolabs Example

nested-query.sql

select
  blog_post.id,
  author.name,
  author.email,
  blog_post.date,
  blog_post.rating,
  (select
     blog_comments.email as "user",
     blog_comments.upvotes as "upVotes",
     blog_comments.downvotes as "downVotes",
     blog_comments.text as text
   from
     blog_comments
   where
     blog_comments.blog_post_id = blog_post.id) as comments,
  (select
     blog_tags.tag
   from 
     blog_tags
   where
     blog_tags.blog_post_id = blog_post.id) as tags
from 
  author,
  blog_post
where
  author.id = blog_post.author_id and
  blog_post.id = 1234

nested2.sql

select ** from author where id = 1

results1.json

{
    "id": 1234,
    "name": "Bob Davis",
    "email": "bob@bob.com",
    "date": "2012-08-07 23:11:38",
    "rating": 2.2,
    "comments": [
        {
            "user": "jgs32@hotmail.com",
            "upVotes": 22,
            "downVotes": 14,
            "text": "Great point! I agree"
        },
        {
            "user": "holly.davidson@gmail.com",
            "upVotes": 421,
            "downVotes": 22,
            "text": "You are a moron"
        }
    ],
    "tags": [
        {
            "tag": "Politics"
        },
        {
            "tag": "Virginia"
        }
    ]
}

results2.json

{
    "id": 1,
    "name": "Bob Davis",
    "email": "bob@bob.com",
    "blog.blog_post": [
        {
            "id": 1234,
            "author_id": 1,
            "date": "2012-08-07 23:11:38",
            "rating": 2.2,
            "blog.blog_comments": [
                {
                    "id": 101,
                    "email": "jgs32@hotmail.com",
                    "upvotes": 22,
                    "downvotes": 14,
                    "text": "Great point! I agree",
                    "blog_post_id": 1234
                },
                {
                    "id": 102,
                    "email": "holly.davidson@gmail.com",
                    "upvotes": 421,
                    "downvotes": 22,
                    "text": "You are a moron",
                    "blog_post_id": 1234
                }
            ],
            "blog.blog_tags": [
                {
                    "blog_post_id": 1234,
                    "tag": "Politics"
                },
                {
                    "blog_post_id": 1234,
                    "tag": "Virginia"
                }
            ]
        },
        {
            "id": 4321,
            "author_id": 1,
            "date": "2012-08-07 23:18:31",
            "rating": 3.3,
            "blog.blog_comments": [],
            "blog.blog_tags": []
        }
    ]
}

schema.sql

create table author
(
  id bigint not null primary key,
  name varchar(128) not null,
  email varchar(128) not null
);

create table blog_post
(
  id bigint not null primary key,
  author_id bigint not null,
  date datetime not null,
  rating float not null,
  grouping foreign key(author_id) references author
);

create table blog_comments
(
  id bigint not null primary key,
  email varchar(128) not null,
  upvotes int not null default 0,
  downvotes int not null default 0,
  text blob not null,
  blog_post_id bigint not null,
  grouping foreign key(blog_post_id) references blog_post
);

create table blog_tags
(
  blog_post_id bigint not null,
  tag varchar(128) not null,
  grouping foreign key(blog_post_id) references blog_post
);