penkala_embedded_to_empty_relation.sql

WITH "cte-133417" AS MATERIALIZED (
  SELECT
    "film"."film_id" AS "film-id",
    "film"."title" AS "title"
  FROM
    "film" AS "film"
  ORDER BY
    "film"."title" FETCH NEXT 5 ROWS ONLY
)
SELECT
  (
    SELECT
      json_build_object (
        'heading',
        array_to_json("data-and-types-embedded-134147"."heading"),
        'body',
        array_to_json("data-and-types-embedded-134147"."body")
      )
    FROM
      (
        SELECT
          array [ array [ 'film-actor__film-id',
          pg_typeof("data-embedded-134147"."film-actor__film-id") :: text ],
          array [ 'first-name',
          pg_typeof("data-embedded-134147"."first-name") :: text ],
          array [ 'last-name',
          pg_typeof("data-embedded-134147"."last-name") :: text ] ] as heading,
          array_agg(
            array [ to_json("data-embedded-134147"."film-actor__film-id"),
            to_json("data-embedded-134147"."first-name"),
            to_json("data-embedded-134147"."last-name") ]
          ) AS body
        FROM
(
            SELECT
              "actor"."first_name" AS "first-name",
              "actor"."last_name" AS "last-name",
              "film-actor"."film-id" AS "film-actor__film-id"
            FROM
              "actor" AS "actor"
              INNER JOIN (
                SELECT
                  "film_actor"."actor_id" AS "actor-id",
                  "film_actor"."film_id" AS "film-id",
                  "film_actor"."last_update" AS "last-update"
                FROM
                  "film_actor" AS "film_actor"
              ) "film-actor" ON (
                "film-actor"."film-id" IN (
                  SELECT
                    "cte-133417"."film-id" AS "film-id"
                  FROM
                    "cte-133417"
                )
                AND "actor"."actor_id" = "film-actor"."actor-id"
              )
          ) "data-embedded-134147"
        GROUP BY
          heading
      ) "data-and-types-embedded-134147"
  ) AS "actors",
  (
    SELECT
      json_build_object (
        'heading',
        array_to_json("data-and-types-embedded-134149"."heading"),
        'body',
        array_to_json("data-and-types-embedded-134149"."body")
      )
    FROM
      (
        SELECT
          array [ array [ 'film-category__film-id',
          pg_typeof("data-embedded-134149"."film-category__film-id") :: text ],
          array [ 'name',
          pg_typeof("data-embedded-134149"."name") :: text ] ] as heading,
          array_agg(
            array [ to_json("data-embedded-134149"."film-category__film-id"),
            to_json("data-embedded-134149"."name") ]
          ) AS body
        FROM
(
            SELECT
              "category"."name" AS "name",
              "film-category"."film-id" AS "film-category__film-id"
            FROM
              "category" AS "category"
              INNER JOIN (
                SELECT
                  "film_category"."category_id" AS "category-id",
                  "film_category"."film_id" AS "film-id",
                  "film_category"."last_update" AS "last-update"
                FROM
                  "film_category" AS "film_category"
              ) "film-category" ON (
                "film-category"."film-id" IN (
                  SELECT
                    "cte-133417"."film-id" AS "film-id"
                  FROM
                    "cte-133417"
                )
                AND "category"."category_id" = "film-category"."category-id"
              )
          ) "data-embedded-134149"
        GROUP BY
          heading
      ) "data-and-types-embedded-134149"
  ) AS "categories",
  (
    SELECT
      json_build_object (
        'heading',
        array_to_json("data-and-types-embedded-134151"."heading"),
        'body',
        array_to_json("data-and-types-embedded-134151"."body")
      )
    FROM
      (
        SELECT
          array [ array [ 'film-id',
          pg_typeof("data-embedded-134151"."film-id") :: text ],
          array [ 'title',
          pg_typeof("data-embedded-134151"."title") :: text ] ] as heading,
          array_agg(
            array [ to_json("data-embedded-134151"."film-id"),
            to_json("data-embedded-134151"."title") ]
          ) AS body
        FROM
(
            SELECT
              "cte-133417"."film-id" AS "film-id",
              "cte-133417"."title" AS "title"
            FROM
              "cte-133417"
          ) "data-embedded-134151"
        GROUP BY
          heading
      ) "data-and-types-embedded-134151"
  ) AS "films"