Nested selection into complex columns

[solidsnack]

It's not possible to search more than one level deep in complex columns (json, jsonb) at present.

[solidsnack]

Let note be a JSON column, selected into as follows:

note {
  tags,
  author {
    first,
    last {
      prefix,
      suffix
    }
  }
}

Here is a first pass at generated code:

SELECT to_json("sub/2") AS note
  FROM (VALUES (note->'tags', note->'author'))
            AS _(tags, author),
       LATERAL (
         SELECT to_json("sub/2") AS author
           FROM (VALUES (author->'first', author->'last'))
                     AS _(first, last),
                LATERAL (
                  SELECT to_json(_) AS last
                    FROM (VALUES (last->'prefix', last->'suffix'))
                              AS _(prefix, suffix)
                ) AS "sub/1",
                LATERAL (
                  SELECT first, "sub/1".last
                ) AS "sub/2"
       ) AS "sub/1",
       LATERAL (
         SELECT tags, "sub/1".author
       ) AS "sub/2"

First we SELECT out all the fields at top-level, and for every nested selection we generate a subquery. At the bottom, we select the columns that make up the result. This always has the form:

SELECT to_json("sub/final") AS result
  FROM (VALUES (result->'first_field', result->'another', ...))
            AS _(first_field, another, ...),
       LATERAL (
         SELECT to_json("sub/final") AS ...
         --- The structure is repeated here, for every field that has a
         --- nested selection
       ) AS "sub/1",
       ...                                         -- Potentially many of these
       LATERAL (
         SELECT first_field, another, ...
       ) AS "sub/final"

[emrul]

This looks very cool! We've done something similar using plv8 functions to parse the graphql query into a SQL statement.

Things work well but nested JSON is what we're looking at next. Postgres has to load the entirety of any JSON column to service a query on it so we're thinking it may be quicker (and cleaner) to filter JSON column values using a Plv8 function.

Anyway, thanks for creating this project!