Nested Knex query

js-query.js

knex
  .select([
    'users.*',
    knex.raw('json_agg("posts") as posts')
  ])
  .from('users')
  .leftJoin(function () {
    this.select(['posts.*', knex.raw('json_agg("comments") as comments')])
      .from('posts')
      .leftJoin('comments', { 'posts.id': 'comments.post' })
      .groupBy('posts.id')
      .as('posts')
  }, { 'users.id': 'posts.user' })
  .groupBy('users.id');

// DB Schema built from:

[
  {
    name: 'users',
    columns: {
      id: 'id_uuid',
      name: 'string',
      login: 'string',
      password: 'string',
    },
  },
  {
    name: 'posts',
    columns: {
      id: 'id_uuid',
      user: ['foreign', 'users'],
      title: 'string',
      text: 'string',
    },
  },
  {
    name: 'comments',
    columns: {
      id: 'id_uuid',
      post: ['foreign', 'posts'],
      user: ['foreign', 'users'],
      text: 'string',
    },
  },
];

query-sql.sql

/* Knex query sql output */
select 
  "users".*, 
  json_agg("posts") as posts 
from 
  "users" 
  left join (
    select 
      "posts".*, 
      json_agg("comments") as comments 
    from 
      "posts" 
      left join "comments" on "posts"."id" = "comments"."post" 
    group by 
      "posts"."id"
  ) as "posts" on "users"."id" = "posts"."user" 
group by 
  "users"."id";

result.json

[
  {
    "id": "5d85a42f-fea2-45f7-987d-83290e0bb6ce",
    "name": "James Dean",
    "login": "jdean",
    "password": "",
    "posts": [
      {
        "id": "4282206a-8949-4656-bb2a-e9fe9240a74b",
        "user": "5d85a42f-fea2-45f7-987d-83290e0bb6ce",
        "title": "James Dean's confusing life",
        "text": "Like, this would actually be long tho.",
        "comments": [
          {
            "id": "062ef3ca-fa45-449e-aa0f-6565d984c355",
            "post": "4282206a-8949-4656-bb2a-e9fe9240a74b",
            "user": "a07c08bf-da49-4718-8eaf-491adf058b5f",
            "text": "Comment by Jenna on James Deans confusing Life!"
          }
        ]
      }
    ]
  },
  {
    "id": "a07c08bf-da49-4718-8eaf-491adf058b5f",
    "name": "Jenna Marbles",
    "login": "jmarb",
    "password": "",
    "posts": [
      {
        "id": "4f041d1d-a233-40d1-b0d6-a634d032e9d8",
        "user": "a07c08bf-da49-4718-8eaf-491adf058b5f",
        "title": "Losing yourself to sand",
        "text": "Like, this would actually be long tho.",
        "comments": [
          null
        ]
      },
      {
        "id": "ba277a3f-48ad-4f6a-9f07-aebd4a6ebcc2",
        "user": "a07c08bf-da49-4718-8eaf-491adf058b5f",
        "title": "I'm not a starfish man",
        "text": "Like, this would actually be long tho.",
        "comments": [
          null
        ]
      },
      {
        "id": "dbe2141e-d8e6-494b-ab8c-bb4696578268",
        "user": "a07c08bf-da49-4718-8eaf-491adf058b5f",
        "title": "Smelling farts for fun and profit!",
        "text": "Like, this would actually be long tho.",
        "comments": [
          null
        ]
      },
      {
        "id": "fb919cfc-e9b0-4770-9455-ce5871728d0a",
        "user": "a07c08bf-da49-4718-8eaf-491adf058b5f",
        "title": "Transparency is cool",
        "text": "Like, this would actually be long tho.",
        "comments": [
          {
            "id": "30e52161-736c-4cbc-a525-9ea9c0e93af8",
            "post": "fb919cfc-e9b0-4770-9455-ce5871728d0a",
            "user": "a07c08bf-da49-4718-8eaf-491adf058b5f",
            "text": "Comment by Jenna on Transparency is cool!"
          },
          {
            "id": "8f93f707-78ff-4f66-ac76-207e601f5f71",
            "post": "fb919cfc-e9b0-4770-9455-ce5871728d0a",
            "user": "5d85a42f-fea2-45f7-987d-83290e0bb6ce",
            "text": "Comment by James on Transparency is cool!"
          }
        ]
      }
    ]
  }
]