Merging JSON objects - Githubissues

I'm switching from Zapaptos to Kysely right now. One thing I've grown to love from Zapatos is the easy way of doing lateral joins for related tables:

const booksAuthorTags = await db.select('books', db.all, {
  lateral: {
    author: db.selectExactlyOne('authors', { id: db.parent('authorId') }),
    tags: db.select('tags', { bookId: db.parent('id') }),
  }
}).run(pool);

This yields the following Postgres query:

SELECT coalesce(jsonb_agg(result), '[]') AS result
FROM (
  SELECT to_jsonb ("books".*) || jsonb_build_object('author', "lateral_author".result, 'tags', "lateral_tags".result) AS result
  FROM "books"
  LEFT JOIN LATERAL (
    SELECT to_jsonb ("authors".*) AS result
    FROM "authors"
    WHERE ("id" = "books"."authorId")
  LIMIT 1) AS "lateral_author" ON true
  LEFT JOIN LATERAL (
    SELECT coalesce(jsonb_agg(result), '[]') AS result
    FROM (
      SELECT to_jsonb ("tags".*) AS result
      FROM "tags"
      WHERE ("bookId" = "books"."id")) AS "sq_tags") AS "lateral_tags" ON true) AS "sq_books"

And magically returns a list of books along with their tags and author merged into the JSON result all of it performant and optimisable by the query planner:

JSON result (collapsed for readability)

```json [ { "id": 1000, "tags": [ { "tag": "His Dark Materials", "bookId": 1000 }, { "tag": "1/3", "bookId": 1000 } ], "title": "Northern Lights", "author": { "id": 1000, "name": "Philip Pullman", "isLiving": true }, "authorId": 1000, "createdAt": "2024-01-08T11:50:21.508324+00:00" }, { "id": 1001, "tags": [ { "tag": "His Dark Materials", "bookId": 1001 }, { "tag": "2/3", "bookId": 1001 } ], "title": "The Subtle Knife", "author": { "id": 1000, "name": "Philip Pullman", "isLiving": true }, "authorId": 1000, "createdAt": "2024-01-08T11:50:21.50977+00:00" }, { "id": 1002, "tags": [ { "tag": "His Dark Materials", "bookId": 1002 }, { "tag": "3/3", "bookId": 1002 } ], "title": "The Amber Spyglass", "author": { "id": 1000, "name": "Philip Pullman", "isLiving": true }, "authorId": 1000, "createdAt": "2024-01-08T11:50:21.510292+00:00" }, { "id": 1003, "tags": [ { "tag": "mystery", "bookId": 1003 } ], "title": "The Curious Incident of the Dog in the Night-Time", "author": { "id": 1001, "name": "Mark Haddon", "isLiving": true }, "authorId": 1001, "createdAt": "2024-01-08T11:50:21.513693+00:00" }, { "id": 1, "tags": [ { "tag": "physics", "bookId": 1 } ], "title": "A Brief History of Time", "author": { "id": 5, "name": "Stephen Hawking", "isLiving": false }, "authorId": 5, "createdAt": "2024-01-08T11:51:15.452677+00:00" }, { "id": 2, "tags": [ { "tag": "physicist", "bookId": 2 }, { "tag": "autobiography", "bookId": 2 } ], "title": "My Brief History", "author": { "id": 5, "name": "Stephen Hawking", "isLiving": false }, "authorId": 5, "createdAt": "2024-01-08T11:51:15.452677+00:00" }, { "id": 3, "tags": [], "title": "The Universe in a Nutshell", "author": { "id": 5, "name": "Stephen Hawking", "isLiving": false }, "authorId": 5, "createdAt": "2024-01-08T11:51:15.457539+00:00" } ] ```

It's really a marvelous feat of engineering. So, I was looking into the best way to reproduce this using Kysely, and found the relations recipe, which achieves pretty much the same result, albeit with parallel subqueries. I haven't done any benchmarks, but I'm reasonably sure this isn't as ideal as a lateral join. Additionally, the latter yields a combined JSON object.

So I set out to create my own helper functions which replicate the Zapatos query; I'm stumbling over the jsonb merging using the concat operator (||); in Postgres, you can do

select json_build_object('foo', 1)::jsonb || json_build_object('bar', 2)::jsonb

to get a merged object from both:

{"foo": 1, "bar": 2}

As far as I can see, this isn't supported by Kysely right now; I'd have to resort to raw SQL, which would break result types. Therefore, it'd be cool if the ExpressionBuilder had a way to use the concat operator, or even better, merge JSON; this could be chainable on toJson, for example:

.select(({ fn }) => fn.toJson('table').merge(eb => /* must return JSON */).as('alias'))

kysely-org / kysely

Merging JSON objects #968