Symfony Profiler

# product-listing-route::loading::aggregation::price
SELECT
  MIN(
    IFNULL(
      COALESCE(
        (
          ROUND(
            (
              ROUND(
                CAST(
                  (
                    JSON_UNQUOTE(
                      JSON_EXTRACT(
                        `product`.`cheapest_price_accessor`,
                        "$.rule4a9217f95c68413eaa21544ec53e5482.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross"
                      )
                    ) * 1
                  ) as DECIMAL(30, 20)
                ),
                2
              )
            ) * 100,
            0
          ) / 100
        ),
        (
          ROUND(
            (
              ROUND(
                CAST(
                  (
                    JSON_UNQUOTE(
                      JSON_EXTRACT(
                        `product`.`cheapest_price_accessor`,
                        "$.rule943f79f0e9da46258b8491e5bab9dab7.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross"
                      )
                    ) * 1
                  ) as DECIMAL(30, 20)
                ),
                2
              )
            ) * 100,
            0
          ) / 100
        ),
        (
          ROUND(
            (
              ROUND(
                CAST(
                  (
                    JSON_UNQUOTE(
                      JSON_EXTRACT(
                        `product`.`cheapest_price_accessor`,
                        "$.ruleafaf48c4c3c54ef1acf5664ebb345d05.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross"
                      )
                    ) * 1
                  ) as DECIMAL(30, 20)
                ),
                2
              )
            ) * 100,
            0
          ) / 100
        ),
        (
          ROUND(
            (
              ROUND(
                CAST(
                  (
                    JSON_UNQUOTE(
                      JSON_EXTRACT(
                        `product`.`cheapest_price_accessor`,
                        "$.rule4712b0abde13450789672dacb7864a9c.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross"
                      )
                    ) * 1
                  ) as DECIMAL(30, 20)
                ),
                2
              )
            ) * 100,
            0
          ) / 100
        ),
        (
          ROUND(
            (
              ROUND(
                CAST(
                  (
                    JSON_UNQUOTE(
                      JSON_EXTRACT(
                        `product`.`cheapest_price_accessor`,
                        "$.rule6b8db4b5d42d4e8195c64073a3e6aa9c.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross"
                      )
                    ) * 1
                  ) as DECIMAL(30, 20)
                ),
                2
              )
            ) * 100,
            0
          ) / 100
        ),
        (
          ROUND(
            (
              ROUND(
                CAST(
                  (
                    JSON_UNQUOTE(
                      JSON_EXTRACT(
                        `product`.`cheapest_price_accessor`,
                        "$.rule95c3d16c891f4ed68d06352d893b006d.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross"
                      )
                    ) * 1
                  ) as DECIMAL(30, 20)
                ),
                2
              )
            ) * 100,
            0
          ) / 100
        ),
        (
          ROUND(
            (
              ROUND(
                CAST(
                  (
                    JSON_UNQUOTE(
                      JSON_EXTRACT(
                        `product`.`cheapest_price_accessor`,
                        "$.rulea1f3643482be416dbd70add87d07cc44.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross"
                      )
                    ) * 1
                  ) as DECIMAL(30, 20)
                ),
                2
              )
            ) * 100,
            0
          ) / 100
        ),
        (
          ROUND(
            (
              ROUND(
                CAST(
                  (
                    JSON_UNQUOTE(
                      JSON_EXTRACT(
                        `product`.`cheapest_price_accessor`,
                        "$.ruleab415488c86f4494803369c9e6e76aa1.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross"
                      )
                    ) * 1
                  ) as DECIMAL(30, 20)
                ),
                2
              )
            ) * 100,
            0
          ) / 100
        ),
        (
          ROUND(
            (
              ROUND(
                CAST(
                  (
                    JSON_UNQUOTE(
                      JSON_EXTRACT(
                        `product`.`cheapest_price_accessor`,
                        "$.rulec1145f73d5c2450e97aca5fa3d5f08aa.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross"
                      )
                    ) * 1
                  ) as DECIMAL(30, 20)
                ),
                2
              )
            ) * 100,
            0
          ) / 100
        ),
        (
          ROUND(
            (
              ROUND(
                CAST(
                  (
                    JSON_UNQUOTE(
                      JSON_EXTRACT(
                        `product`.`cheapest_price_accessor`,
                        "$.rulef01a3ca4ea264cf78d1a52246aabfac5.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross"
                      )
                    ) * 1
                  ) as DECIMAL(30, 20)
                ),
                2
              )
            ) * 100,
            0
          ) / 100
        ),
        (
          ROUND(
            (
              ROUND(
                CAST(
                  (
                    JSON_UNQUOTE(
                      JSON_EXTRACT(
                        `product`.`cheapest_price_accessor`,
                        "$.ruledefault.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross"
                      )
                    ) * 1
                  ) as DECIMAL(30, 20)
                ),
                2
              )
            ) * 100,
            0
          ) / 100
        )
      ),
      COALESCE(
        (
          ROUND(
            (
              ROUND(
                CAST(
                  (
                    JSON_UNQUOTE(
                      JSON_EXTRACT(
                        `product.parent`.`cheapest_price_accessor`,
                        "$.rule4a9217f95c68413eaa21544ec53e5482.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross"
                      )
                    ) * 1
                  ) as DECIMAL(30, 20)
                ),
                2
              )
            ) * 100,
            0
          ) / 100
        ),
        (
          ROUND(
            (
              ROUND(
                CAST(
                  (
                    JSON_UNQUOTE(
                      JSON_EXTRACT(
                        `product.parent`.`cheapest_price_accessor`,
                        "$.rule943f79f0e9da46258b8491e5bab9dab7.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross"
                      )
                    ) * 1
                  ) as DECIMAL(30, 20)
                ),
                2
              )
            ) * 100,
            0
          ) / 100
        ),
        (
          ROUND(
            (
              ROUND(
                CAST(
                  (
                    JSON_UNQUOTE(
                      JSON_EXTRACT(
                        `product.parent`.`cheapest_price_accessor`,
                        "$.ruleafaf48c4c3c54ef1acf5664ebb345d05.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross"
                      )
                    ) * 1
                  ) as DECIMAL(30, 20)
                ),
                2
              )
            ) * 100,
            0
          ) / 100
        ),
        (
          ROUND(
            (
              ROUND(
                CAST(
                  (
                    JSON_UNQUOTE(
                      JSON_EXTRACT(
                        `product.parent`.`cheapest_price_accessor`,
                        "$.rule4712b0abde13450789672dacb7864a9c.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross"
                      )
                    ) * 1
                  ) as DECIMAL(30, 20)
                ),
                2
              )
            ) * 100,
            0
          ) / 100
        ),
        (
          ROUND(
            (
              ROUND(
                CAST(
                  (
                    JSON_UNQUOTE(
                      JSON_EXTRACT(
                        `product.parent`.`cheapest_price_accessor`,
                        "$.rule6b8db4b5d42d4e8195c64073a3e6aa9c.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross"
                      )
                    ) * 1
                  ) as DECIMAL(30, 20)
                ),
                2
              )
            ) * 100,
            0
          ) / 100
        ),
        (
          ROUND(
            (
              ROUND(
                CAST(
                  (
                    JSON_UNQUOTE(
                      JSON_EXTRACT(
                        `product.parent`.`cheapest_price_accessor`,
                        "$.rule95c3d16c891f4ed68d06352d893b006d.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross"
                      )
                    ) * 1
                  ) as DECIMAL(30, 20)
                ),
                2
              )
            ) * 100,
            0
          ) / 100
        ),
        (
          ROUND(
            (
              ROUND(
                CAST(
                  (
                    JSON_UNQUOTE(
                      JSON_EXTRACT(
                        `product.parent`.`cheapest_price_accessor`,
                        "$.rulea1f3643482be416dbd70add87d07cc44.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross"
                      )
                    ) * 1
                  ) as DECIMAL(30, 20)
                ),
                2
              )
            ) * 100,
            0
          ) / 100
        ),
        (
          ROUND(
            (
              ROUND(
                CAST(
                  (
                    JSON_UNQUOTE(
                      JSON_EXTRACT(
                        `product.parent`.`cheapest_price_accessor`,
                        "$.ruleab415488c86f4494803369c9e6e76aa1.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross"
                      )
                    ) * 1
                  ) as DECIMAL(30, 20)
                ),
                2
              )
            ) * 100,
            0
          ) / 100
        ),
        (
          ROUND(
            (
              ROUND(
                CAST(
                  (
                    JSON_UNQUOTE(
                      JSON_EXTRACT(
                        `product.parent`.`cheapest_price_accessor`,
                        "$.rulec1145f73d5c2450e97aca5fa3d5f08aa.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross"
                      )
                    ) * 1
                  ) as DECIMAL(30, 20)
                ),
                2
              )
            ) * 100,
            0
          ) / 100
        ),
        (
          ROUND(
            (
              ROUND(
                CAST(
                  (
                    JSON_UNQUOTE(
                      JSON_EXTRACT(
                        `product.parent`.`cheapest_price_accessor`,
                        "$.rulef01a3ca4ea264cf78d1a52246aabfac5.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross"
                      )
                    ) * 1
                  ) as DECIMAL(30, 20)
                ),
                2
              )
            ) * 100,
            0
          ) / 100
        ),
        (
          ROUND(
            (
              ROUND(
                CAST(
                  (
                    JSON_UNQUOTE(
                      JSON_EXTRACT(
                        `product.parent`.`cheapest_price_accessor`,
                        "$.ruledefault.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross"
                      )
                    ) * 1
                  ) as DECIMAL(30, 20)
                ),
                2
              )
            ) * 100,
            0
          ) / 100
        )
      )
    )
  ) as `price.min`,
  MAX(
    IFNULL(
      COALESCE(
        (
          ROUND(
            (
              ROUND(
                CAST(
                  (
                    JSON_UNQUOTE(
                      JSON_EXTRACT(
                        `product`.`cheapest_price_accessor`,
                        "$.rule4a9217f95c68413eaa21544ec53e5482.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross"
                      )
                    ) * 1
                  ) as DECIMAL(30, 20)
                ),
                2
              )
            ) * 100,
            0
          ) / 100
        ),
        (
          ROUND(
            (
              ROUND(
                CAST(
                  (
                    JSON_UNQUOTE(
                      JSON_EXTRACT(
                        `product`.`cheapest_price_accessor`,
                        "$.rule943f79f0e9da46258b8491e5bab9dab7.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross"
                      )
                    ) * 1
                  ) as DECIMAL(30, 20)
                ),
                2
              )
            ) * 100,
            0
          ) / 100
        ),
        (
          ROUND(
            (
              ROUND(
                CAST(
                  (
                    JSON_UNQUOTE(
                      JSON_EXTRACT(
                        `product`.`cheapest_price_accessor`,
                        "$.ruleafaf48c4c3c54ef1acf5664ebb345d05.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross"
                      )
                    ) * 1
                  ) as DECIMAL(30, 20)
                ),
                2
              )
            ) * 100,
            0
          ) / 100
        ),
        (
          ROUND(
            (
              ROUND(
                CAST(
                  (
                    JSON_UNQUOTE(
                      JSON_EXTRACT(
                        `product`.`cheapest_price_accessor`,
                        "$.rule4712b0abde13450789672dacb7864a9c.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross"
                      )
                    ) * 1
                  ) as DECIMAL(30, 20)
                ),
                2
              )
            ) * 100,
            0
          ) / 100
        ),
        (
          ROUND(
            (
              ROUND(
                CAST(
                  (
                    JSON_UNQUOTE(
                      JSON_EXTRACT(
                        `product`.`cheapest_price_accessor`,
                        "$.rule6b8db4b5d42d4e8195c64073a3e6aa9c.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross"
                      )
                    ) * 1
                  ) as DECIMAL(30, 20)
                ),
                2
              )
            ) * 100,
            0
          ) / 100
        ),
        (
          ROUND(
            (
              ROUND(
                CAST(
                  (
                    JSON_UNQUOTE(
                      JSON_EXTRACT(
                        `product`.`cheapest_price_accessor`,
                        "$.rule95c3d16c891f4ed68d06352d893b006d.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross"
                      )
                    ) * 1
                  ) as DECIMAL(30, 20)
                ),
                2
              )
            ) * 100,
            0
          ) / 100
        ),
        (
          ROUND(
            (
              ROUND(
                CAST(
                  (
                    JSON_UNQUOTE(
                      JSON_EXTRACT(
                        `product`.`cheapest_price_accessor`,
                        "$.rulea1f3643482be416dbd70add87d07cc44.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross"
                      )
                    ) * 1
                  ) as DECIMAL(30, 20)
                ),
                2
              )
            ) * 100,
            0
          ) / 100
        ),
        (
          ROUND(
            (
              ROUND(
                CAST(
                  (
                    JSON_UNQUOTE(
                      JSON_EXTRACT(
                        `product`.`cheapest_price_accessor`,
                        "$.ruleab415488c86f4494803369c9e6e76aa1.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross"
                      )
                    ) * 1
                  ) as DECIMAL(30, 20)
                ),
                2
              )
            ) * 100,
            0
          ) / 100
        ),
        (
          ROUND(
            (
              ROUND(
                CAST(
                  (
                    JSON_UNQUOTE(
                      JSON_EXTRACT(
                        `product`.`cheapest_price_accessor`,
                        "$.rulec1145f73d5c2450e97aca5fa3d5f08aa.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross"
                      )
                    ) * 1
                  ) as DECIMAL(30, 20)
                ),
                2
              )
            ) * 100,
            0
          ) / 100
        ),
        (
          ROUND(
            (
              ROUND(
                CAST(
                  (
                    JSON_UNQUOTE(
                      JSON_EXTRACT(
                        `product`.`cheapest_price_accessor`,
                        "$.rulef01a3ca4ea264cf78d1a52246aabfac5.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross"
                      )
                    ) * 1
                  ) as DECIMAL(30, 20)
                ),
                2
              )
            ) * 100,
            0
          ) / 100
        ),
        (
          ROUND(
            (
              ROUND(
                CAST(
                  (
                    JSON_UNQUOTE(
                      JSON_EXTRACT(
                        `product`.`cheapest_price_accessor`,
                        "$.ruledefault.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross"
                      )
                    ) * 1
                  ) as DECIMAL(30, 20)
                ),
                2
              )
            ) * 100,
            0
          ) / 100
        )
      ),
      COALESCE(
        (
          ROUND(
            (
              ROUND(
                CAST(
                  (
                    JSON_UNQUOTE(
                      JSON_EXTRACT(
                        `product.parent`.`cheapest_price_accessor`,
                        "$.rule4a9217f95c68413eaa21544ec53e5482.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross"
                      )
                    ) * 1
                  ) as DECIMAL(30, 20)
                ),
                2
              )
            ) * 100,
            0
          ) / 100
        ),
        (
          ROUND(
            (
              ROUND(
                CAST(
                  (
                    JSON_UNQUOTE(
                      JSON_EXTRACT(
                        `product.parent`.`cheapest_price_accessor`,
                        "$.rule943f79f0e9da46258b8491e5bab9dab7.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross"
                      )
                    ) * 1
                  ) as DECIMAL(30, 20)
                ),
                2
              )
            ) * 100,
            0
          ) / 100
        ),
        (
          ROUND(
            (
              ROUND(
                CAST(
                  (
                    JSON_UNQUOTE(
                      JSON_EXTRACT(
                        `product.parent`.`cheapest_price_accessor`,
                        "$.ruleafaf48c4c3c54ef1acf5664ebb345d05.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross"
                      )
                    ) * 1
                  ) as DECIMAL(30, 20)
                ),
                2
              )
            ) * 100,
            0
          ) / 100
        ),
        (
          ROUND(
            (
              ROUND(
                CAST(
                  (
                    JSON_UNQUOTE(
                      JSON_EXTRACT(
                        `product.parent`.`cheapest_price_accessor`,
                        "$.rule4712b0abde13450789672dacb7864a9c.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross"
                      )
                    ) * 1
                  ) as DECIMAL(30, 20)
                ),
                2
              )
            ) * 100,
            0
          ) / 100
        ),
        (
          ROUND(
            (
              ROUND(
                CAST(
                  (
                    JSON_UNQUOTE(
                      JSON_EXTRACT(
                        `product.parent`.`cheapest_price_accessor`,
                        "$.rule6b8db4b5d42d4e8195c64073a3e6aa9c.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross"
                      )
                    ) * 1
                  ) as DECIMAL(30, 20)
                ),
                2
              )
            ) * 100,
            0
          ) / 100
        ),
        (
          ROUND(
            (
              ROUND(
                CAST(
                  (
                    JSON_UNQUOTE(
                      JSON_EXTRACT(
                        `product.parent`.`cheapest_price_accessor`,
                        "$.rule95c3d16c891f4ed68d06352d893b006d.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross"
                      )
                    ) * 1
                  ) as DECIMAL(30, 20)
                ),
                2
              )
            ) * 100,
            0
          ) / 100
        ),
        (
          ROUND(
            (
              ROUND(
                CAST(
                  (
                    JSON_UNQUOTE(
                      JSON_EXTRACT(
                        `product.parent`.`cheapest_price_accessor`,
                        "$.rulea1f3643482be416dbd70add87d07cc44.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross"
                      )
                    ) * 1
                  ) as DECIMAL(30, 20)
                ),
                2
              )
            ) * 100,
            0
          ) / 100
        ),
        (
          ROUND(
            (
              ROUND(
                CAST(
                  (
                    JSON_UNQUOTE(
                      JSON_EXTRACT(
                        `product.parent`.`cheapest_price_accessor`,
                        "$.ruleab415488c86f4494803369c9e6e76aa1.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross"
                      )
                    ) * 1
                  ) as DECIMAL(30, 20)
                ),
                2
              )
            ) * 100,
            0
          ) / 100
        ),
        (
          ROUND(
            (
              ROUND(
                CAST(
                  (
                    JSON_UNQUOTE(
                      JSON_EXTRACT(
                        `product.parent`.`cheapest_price_accessor`,
                        "$.rulec1145f73d5c2450e97aca5fa3d5f08aa.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross"
                      )
                    ) * 1
                  ) as DECIMAL(30, 20)
                ),
                2
              )
            ) * 100,
            0
          ) / 100
        ),
        (
          ROUND(
            (
              ROUND(
                CAST(
                  (
                    JSON_UNQUOTE(
                      JSON_EXTRACT(
                        `product.parent`.`cheapest_price_accessor`,
                        "$.rulef01a3ca4ea264cf78d1a52246aabfac5.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross"
                      )
                    ) * 1
                  ) as DECIMAL(30, 20)
                ),
                2
              )
            ) * 100,
            0
          ) / 100
        ),
        (
          ROUND(
            (
              ROUND(
                CAST(
                  (
                    JSON_UNQUOTE(
                      JSON_EXTRACT(
                        `product.parent`.`cheapest_price_accessor`,
                        "$.ruledefault.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross"
                      )
                    ) * 1
                  ) as DECIMAL(30, 20)
                ),
                2
              )
            ) * 100,
            0
          ) / 100
        )
      )
    )
  ) as `price.max`
FROM
  `product`
  LEFT JOIN `product` `product.parent` ON `product`.`parent_id` = `product.parent`.`id`
  AND `product`.`parent_version_id` = `product.parent`.`version_id`
  LEFT JOIN `product_visibility` `product.visibilities` ON `product`.`visibilities` = `product.visibilities`.`product_id`
  AND `product`.version_id = `product.visibilities`.product_version_id
  LEFT JOIN `product_category_tree` `product.categoriesRo.mapping` ON `product`.`id` = `product.categoriesRo.mapping`.`product_id`
  AND `product`.`version_id` = `product.categoriesRo.mapping`.`product_version_id`
  LEFT JOIN (
    SELECT
      `product.translation`.`product_id`,
      `product.translation`.`product_version_id`,
      `product.translation`.`custom_fields` as `product.translation.customFields`
    FROM
      `product_translation` `product.translation`
    WHERE
      `product.translation`.`language_id` = ?
  ) `product.product_translation` ON `product.product_translation`.`product_id` = `product`.`id`
  AND `product.product_translation`.`product_version_id` = `product`.`version_id`
  LEFT JOIN (
    SELECT
      `product.parent.translation`.`product_id`,
      `product.parent.translation`.`product_version_id`,
      `product.parent.translation`.`custom_fields` as `product.parent.translation.customFields`
    FROM
      `product_translation` `product.parent.translation`
    WHERE
      `product.parent.translation`.`language_id` = ?
  ) `product.parent.product_translation` ON `product.parent.product_translation`.`product_id` = `product.parent`.`id`
  AND `product.parent.product_translation`.`product_version_id` = `product.parent`.`version_id`
  LEFT JOIN `category` `product.categoriesRo` ON `product.categoriesRo.mapping`.`category_id` = `product.categoriesRo`.`id`
  AND `product.categoriesRo`.`version_id` = `product.categoriesRo.mapping`.`category_version_id`
WHERE
  (`product`.`version_id` = ?)
  AND (
    (
      (
        COALESCE(
          IF(
            JSON_TYPE(
              JSON_EXTRACT(
                `product.translation.customFields`,
                '$.system4_configurator_customer_number'
              )
            ) != "NULL",
            CONVERT(
              JSON_UNQUOTE(
                JSON_EXTRACT(
                  `product.translation.customFields`,
                  '$.system4_configurator_customer_number'
                )
              ) USING "utf8mb4"
            ) COLLATE utf8mb4_unicode_ci,
            NULL
          ),
          IF(
            JSON_TYPE(
              JSON_EXTRACT(
                `product.parent.translation.customFields`,
                '$.system4_configurator_customer_number'
              )
            ) != "NULL",
            CONVERT(
              JSON_UNQUOTE(
                JSON_EXTRACT(
                  `product.parent.translation.customFields`,
                  '$.system4_configurator_customer_number'
                )
              ) USING "utf8mb4"
            ) COLLATE utf8mb4_unicode_ci,
            NULL
          )
        ) <=> ?
        AND COALESCE(
          IF(
            JSON_TYPE(
              JSON_EXTRACT(
                `product.translation.customFields`,
                '$.system4_configurator_session_id'
              )
            ) != "NULL",
            CONVERT(
              JSON_UNQUOTE(
                JSON_EXTRACT(
                  `product.translation.customFields`,
                  '$.system4_configurator_session_id'
                )
              ) USING "utf8mb4"
            ) COLLATE utf8mb4_unicode_ci,
            NULL
          ),
          IF(
            JSON_TYPE(
              JSON_EXTRACT(
                `product.parent.translation.customFields`,
                '$.system4_configurator_session_id'
              )
            ) != "NULL",
            CONVERT(
              JSON_UNQUOTE(
                JSON_EXTRACT(
                  `product.parent.translation.customFields`,
                  '$.system4_configurator_session_id'
                )
              ) USING "utf8mb4"
            ) COLLATE utf8mb4_unicode_ci,
            NULL
          )
        ) <=> ?
      )
      AND (
        (
          `product.visibilities`.`visibility` >= ?
        )
        AND `product.visibilities`.`sales_channel_id` = ?
        AND IFNULL(
          `product`.`active`, `product.parent`.`active`
        ) = ?
      )
      AND `product.categoriesRo`.`id` = ?
      AND NOT (`product`.`display_group` <=> ?)
    )
  )

# product-listing-route::loading::aggregation::price
SELECT MIN(IFNULL(COALESCE((ROUND((ROUND(CAST((JSON_UNQUOTE(JSON_EXTRACT(`product`.`cheapest_price_accessor`, "$.rule4a9217f95c68413eaa21544ec53e5482.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross")) * 1) as DECIMAL(30, 20)), 2)) * 100, 0) / 100),(ROUND((ROUND(CAST((JSON_UNQUOTE(JSON_EXTRACT(`product`.`cheapest_price_accessor`, "$.rule943f79f0e9da46258b8491e5bab9dab7.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross")) * 1) as DECIMAL(30, 20)), 2)) * 100, 0) / 100),(ROUND((ROUND(CAST((JSON_UNQUOTE(JSON_EXTRACT(`product`.`cheapest_price_accessor`, "$.ruleafaf48c4c3c54ef1acf5664ebb345d05.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross")) * 1) as DECIMAL(30, 20)), 2)) * 100, 0) / 100),(ROUND((ROUND(CAST((JSON_UNQUOTE(JSON_EXTRACT(`product`.`cheapest_price_accessor`, "$.rule4712b0abde13450789672dacb7864a9c.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross")) * 1) as DECIMAL(30, 20)), 2)) * 100, 0) / 100),(ROUND((ROUND(CAST((JSON_UNQUOTE(JSON_EXTRACT(`product`.`cheapest_price_accessor`, "$.rule6b8db4b5d42d4e8195c64073a3e6aa9c.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross")) * 1) as DECIMAL(30, 20)), 2)) * 100, 0) / 100),(ROUND((ROUND(CAST((JSON_UNQUOTE(JSON_EXTRACT(`product`.`cheapest_price_accessor`, "$.rule95c3d16c891f4ed68d06352d893b006d.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross")) * 1) as DECIMAL(30, 20)), 2)) * 100, 0) / 100),(ROUND((ROUND(CAST((JSON_UNQUOTE(JSON_EXTRACT(`product`.`cheapest_price_accessor`, "$.rulea1f3643482be416dbd70add87d07cc44.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross")) * 1) as DECIMAL(30, 20)), 2)) * 100, 0) / 100),(ROUND((ROUND(CAST((JSON_UNQUOTE(JSON_EXTRACT(`product`.`cheapest_price_accessor`, "$.ruleab415488c86f4494803369c9e6e76aa1.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross")) * 1) as DECIMAL(30, 20)), 2)) * 100, 0) / 100),(ROUND((ROUND(CAST((JSON_UNQUOTE(JSON_EXTRACT(`product`.`cheapest_price_accessor`, "$.rulec1145f73d5c2450e97aca5fa3d5f08aa.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross")) * 1) as DECIMAL(30, 20)), 2)) * 100, 0) / 100),(ROUND((ROUND(CAST((JSON_UNQUOTE(JSON_EXTRACT(`product`.`cheapest_price_accessor`, "$.rulef01a3ca4ea264cf78d1a52246aabfac5.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross")) * 1) as DECIMAL(30, 20)), 2)) * 100, 0) / 100),(ROUND((ROUND(CAST((JSON_UNQUOTE(JSON_EXTRACT(`product`.`cheapest_price_accessor`, "$.ruledefault.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross")) * 1) as DECIMAL(30, 20)), 2)) * 100, 0) / 100)), COALESCE((ROUND((ROUND(CAST((JSON_UNQUOTE(JSON_EXTRACT(`product.parent`.`cheapest_price_accessor`, "$.rule4a9217f95c68413eaa21544ec53e5482.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross")) * 1) as DECIMAL(30, 20)), 2)) * 100, 0) / 100),(ROUND((ROUND(CAST((JSON_UNQUOTE(JSON_EXTRACT(`product.parent`.`cheapest_price_accessor`, "$.rule943f79f0e9da46258b8491e5bab9dab7.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross")) * 1) as DECIMAL(30, 20)), 2)) * 100, 0) / 100),(ROUND((ROUND(CAST((JSON_UNQUOTE(JSON_EXTRACT(`product.parent`.`cheapest_price_accessor`, "$.ruleafaf48c4c3c54ef1acf5664ebb345d05.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross")) * 1) as DECIMAL(30, 20)), 2)) * 100, 0) / 100),(ROUND((ROUND(CAST((JSON_UNQUOTE(JSON_EXTRACT(`product.parent`.`cheapest_price_accessor`, "$.rule4712b0abde13450789672dacb7864a9c.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross")) * 1) as DECIMAL(30, 20)), 2)) * 100, 0) / 100),(ROUND((ROUND(CAST((JSON_UNQUOTE(JSON_EXTRACT(`product.parent`.`cheapest_price_accessor`, "$.rule6b8db4b5d42d4e8195c64073a3e6aa9c.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross")) * 1) as DECIMAL(30, 20)), 2)) * 100, 0) / 100),(ROUND((ROUND(CAST((JSON_UNQUOTE(JSON_EXTRACT(`product.parent`.`cheapest_price_accessor`, "$.rule95c3d16c891f4ed68d06352d893b006d.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross")) * 1) as DECIMAL(30, 20)), 2)) * 100, 0) / 100),(ROUND((ROUND(CAST((JSON_UNQUOTE(JSON_EXTRACT(`product.parent`.`cheapest_price_accessor`, "$.rulea1f3643482be416dbd70add87d07cc44.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross")) * 1) as DECIMAL(30, 20)), 2)) * 100, 0) / 100),(ROUND((ROUND(CAST((JSON_UNQUOTE(JSON_EXTRACT(`product.parent`.`cheapest_price_accessor`, "$.ruleab415488c86f4494803369c9e6e76aa1.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross")) * 1) as DECIMAL(30, 20)), 2)) * 100, 0) / 100),(ROUND((ROUND(CAST((JSON_UNQUOTE(JSON_EXTRACT(`product.parent`.`cheapest_price_accessor`, "$.rulec1145f73d5c2450e97aca5fa3d5f08aa.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross")) * 1) as DECIMAL(30, 20)), 2)) * 100, 0) / 100),(ROUND((ROUND(CAST((JSON_UNQUOTE(JSON_EXTRACT(`product.parent`.`cheapest_price_accessor`, "$.rulef01a3ca4ea264cf78d1a52246aabfac5.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross")) * 1) as DECIMAL(30, 20)), 2)) * 100, 0) / 100),(ROUND((ROUND(CAST((JSON_UNQUOTE(JSON_EXTRACT(`product.parent`.`cheapest_price_accessor`, "$.ruledefault.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross")) * 1) as DECIMAL(30, 20)), 2)) * 100, 0) / 100)))) as `price.min`, MAX(IFNULL(COALESCE((ROUND((ROUND(CAST((JSON_UNQUOTE(JSON_EXTRACT(`product`.`cheapest_price_accessor`, "$.rule4a9217f95c68413eaa21544ec53e5482.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross")) * 1) as DECIMAL(30, 20)), 2)) * 100, 0) / 100),(ROUND((ROUND(CAST((JSON_UNQUOTE(JSON_EXTRACT(`product`.`cheapest_price_accessor`, "$.rule943f79f0e9da46258b8491e5bab9dab7.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross")) * 1) as DECIMAL(30, 20)), 2)) * 100, 0) / 100),(ROUND((ROUND(CAST((JSON_UNQUOTE(JSON_EXTRACT(`product`.`cheapest_price_accessor`, "$.ruleafaf48c4c3c54ef1acf5664ebb345d05.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross")) * 1) as DECIMAL(30, 20)), 2)) * 100, 0) / 100),(ROUND((ROUND(CAST((JSON_UNQUOTE(JSON_EXTRACT(`product`.`cheapest_price_accessor`, "$.rule4712b0abde13450789672dacb7864a9c.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross")) * 1) as DECIMAL(30, 20)), 2)) * 100, 0) / 100),(ROUND((ROUND(CAST((JSON_UNQUOTE(JSON_EXTRACT(`product`.`cheapest_price_accessor`, "$.rule6b8db4b5d42d4e8195c64073a3e6aa9c.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross")) * 1) as DECIMAL(30, 20)), 2)) * 100, 0) / 100),(ROUND((ROUND(CAST((JSON_UNQUOTE(JSON_EXTRACT(`product`.`cheapest_price_accessor`, "$.rule95c3d16c891f4ed68d06352d893b006d.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross")) * 1) as DECIMAL(30, 20)), 2)) * 100, 0) / 100),(ROUND((ROUND(CAST((JSON_UNQUOTE(JSON_EXTRACT(`product`.`cheapest_price_accessor`, "$.rulea1f3643482be416dbd70add87d07cc44.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross")) * 1) as DECIMAL(30, 20)), 2)) * 100, 0) / 100),(ROUND((ROUND(CAST((JSON_UNQUOTE(JSON_EXTRACT(`product`.`cheapest_price_accessor`, "$.ruleab415488c86f4494803369c9e6e76aa1.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross")) * 1) as DECIMAL(30, 20)), 2)) * 100, 0) / 100),(ROUND((ROUND(CAST((JSON_UNQUOTE(JSON_EXTRACT(`product`.`cheapest_price_accessor`, "$.rulec1145f73d5c2450e97aca5fa3d5f08aa.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross")) * 1) as DECIMAL(30, 20)), 2)) * 100, 0) / 100),(ROUND((ROUND(CAST((JSON_UNQUOTE(JSON_EXTRACT(`product`.`cheapest_price_accessor`, "$.rulef01a3ca4ea264cf78d1a52246aabfac5.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross")) * 1) as DECIMAL(30, 20)), 2)) * 100, 0) / 100),(ROUND((ROUND(CAST((JSON_UNQUOTE(JSON_EXTRACT(`product`.`cheapest_price_accessor`, "$.ruledefault.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross")) * 1) as DECIMAL(30, 20)), 2)) * 100, 0) / 100)), COALESCE((ROUND((ROUND(CAST((JSON_UNQUOTE(JSON_EXTRACT(`product.parent`.`cheapest_price_accessor`, "$.rule4a9217f95c68413eaa21544ec53e5482.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross")) * 1) as DECIMAL(30, 20)), 2)) * 100, 0) / 100),(ROUND((ROUND(CAST((JSON_UNQUOTE(JSON_EXTRACT(`product.parent`.`cheapest_price_accessor`, "$.rule943f79f0e9da46258b8491e5bab9dab7.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross")) * 1) as DECIMAL(30, 20)), 2)) * 100, 0) / 100),(ROUND((ROUND(CAST((JSON_UNQUOTE(JSON_EXTRACT(`product.parent`.`cheapest_price_accessor`, "$.ruleafaf48c4c3c54ef1acf5664ebb345d05.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross")) * 1) as DECIMAL(30, 20)), 2)) * 100, 0) / 100),(ROUND((ROUND(CAST((JSON_UNQUOTE(JSON_EXTRACT(`product.parent`.`cheapest_price_accessor`, "$.rule4712b0abde13450789672dacb7864a9c.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross")) * 1) as DECIMAL(30, 20)), 2)) * 100, 0) / 100),(ROUND((ROUND(CAST((JSON_UNQUOTE(JSON_EXTRACT(`product.parent`.`cheapest_price_accessor`, "$.rule6b8db4b5d42d4e8195c64073a3e6aa9c.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross")) * 1) as DECIMAL(30, 20)), 2)) * 100, 0) / 100),(ROUND((ROUND(CAST((JSON_UNQUOTE(JSON_EXTRACT(`product.parent`.`cheapest_price_accessor`, "$.rule95c3d16c891f4ed68d06352d893b006d.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross")) * 1) as DECIMAL(30, 20)), 2)) * 100, 0) / 100),(ROUND((ROUND(CAST((JSON_UNQUOTE(JSON_EXTRACT(`product.parent`.`cheapest_price_accessor`, "$.rulea1f3643482be416dbd70add87d07cc44.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross")) * 1) as DECIMAL(30, 20)), 2)) * 100, 0) / 100),(ROUND((ROUND(CAST((JSON_UNQUOTE(JSON_EXTRACT(`product.parent`.`cheapest_price_accessor`, "$.ruleab415488c86f4494803369c9e6e76aa1.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross")) * 1) as DECIMAL(30, 20)), 2)) * 100, 0) / 100),(ROUND((ROUND(CAST((JSON_UNQUOTE(JSON_EXTRACT(`product.parent`.`cheapest_price_accessor`, "$.rulec1145f73d5c2450e97aca5fa3d5f08aa.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross")) * 1) as DECIMAL(30, 20)), 2)) * 100, 0) / 100),(ROUND((ROUND(CAST((JSON_UNQUOTE(JSON_EXTRACT(`product.parent`.`cheapest_price_accessor`, "$.rulef01a3ca4ea264cf78d1a52246aabfac5.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross")) * 1) as DECIMAL(30, 20)), 2)) * 100, 0) / 100),(ROUND((ROUND(CAST((JSON_UNQUOTE(JSON_EXTRACT(`product.parent`.`cheapest_price_accessor`, "$.ruledefault.currencyb7d2554b0ce847cd82f3ac9bd1c0dfca.gross")) * 1) as DECIMAL(30, 20)), 2)) * 100, 0) / 100)))) as `price.max` FROM `product` LEFT JOIN `product` `product.parent` ON `product`.`parent_id` = `product.parent`.`id` AND `product`.`parent_version_id` = `product.parent`.`version_id` LEFT JOIN `product_visibility` `product.visibilities` ON `product`.`visibilities` = `product.visibilities`.`product_id` AND `product`.version_id = `product.visibilities`.product_version_id LEFT JOIN `product_category_tree` `product.categoriesRo.mapping` ON `product`.`id` = `product.categoriesRo.mapping`.`product_id`  AND `product`.`version_id` = `product.categoriesRo.mapping`.`product_version_id` LEFT JOIN (SELECT `product.translation`.`product_id`, `product.translation`.`product_version_id`, `product.translation`.`custom_fields` as `product.translation.customFields` FROM `product_translation` `product.translation` WHERE `product.translation`.`language_id` = 0x2FBB5FE2E29A4D70AA5854CE7CE3E20B) `product.product_translation` ON `product.product_translation`.`product_id` = `product`.`id` AND `product.product_translation`.`product_version_id` = `product`.`version_id` LEFT JOIN (SELECT `product.parent.translation`.`product_id`, `product.parent.translation`.`product_version_id`, `product.parent.translation`.`custom_fields` as `product.parent.translation.customFields` FROM `product_translation` `product.parent.translation` WHERE `product.parent.translation`.`language_id` = 0x2FBB5FE2E29A4D70AA5854CE7CE3E20B) `product.parent.product_translation` ON `product.parent.product_translation`.`product_id` = `product.parent`.`id` AND `product.parent.product_translation`.`product_version_id` = `product.parent`.`version_id` LEFT JOIN `category` `product.categoriesRo` ON `product.categoriesRo.mapping`.`category_id` = `product.categoriesRo`.`id`  AND `product.categoriesRo`.`version_id` = `product.categoriesRo.mapping`.`category_version_id` WHERE (`product`.`version_id` = 0x0FA91CE3E96A4BC2BE4BD9CE752C3425) AND (((COALESCE(IF(JSON_TYPE(JSON_EXTRACT(`product.translation.customFields`, '$.system4_configurator_customer_number')) != "NULL", CONVERT(JSON_UNQUOTE(JSON_EXTRACT(`product.translation.customFields`, '$.system4_configurator_customer_number')) USING "utf8mb4") COLLATE utf8mb4_unicode_ci, NULL),IF(JSON_TYPE(JSON_EXTRACT(`product.parent.translation.customFields`, '$.system4_configurator_customer_number')) != "NULL", CONVERT(JSON_UNQUOTE(JSON_EXTRACT(`product.parent.translation.customFields`, '$.system4_configurator_customer_number')) USING "utf8mb4") COLLATE utf8mb4_unicode_ci, NULL)) <=> NULL AND COALESCE(IF(JSON_TYPE(JSON_EXTRACT(`product.translation.customFields`, '$.system4_configurator_session_id')) != "NULL", CONVERT(JSON_UNQUOTE(JSON_EXTRACT(`product.translation.customFields`, '$.system4_configurator_session_id')) USING "utf8mb4") COLLATE utf8mb4_unicode_ci, NULL),IF(JSON_TYPE(JSON_EXTRACT(`product.parent.translation.customFields`, '$.system4_configurator_session_id')) != "NULL", CONVERT(JSON_UNQUOTE(JSON_EXTRACT(`product.parent.translation.customFields`, '$.system4_configurator_session_id')) USING "utf8mb4") COLLATE utf8mb4_unicode_ci, NULL)) <=> NULL) AND ((`product.visibilities`.`visibility` >= 30) AND `product.visibilities`.`sales_channel_id` = 0xC7B7F9F6CC1D4E0B8705FD682434397E AND IFNULL(`product`.`active`, `product.parent`.`active`) = 1) AND `product.categoriesRo`.`id` = 0xD06B5C68999A4346BC01E2A63273A57E AND NOT (`product`.`display_group` <=> NULL)));

# product-listing-route::loading::aggregation::options
SELECT
  LOWER(
    HEX(`product.options`.`id`)
  ) as `options.key`,
  COUNT(`product`.`id`) as `options.count`
FROM
  `product`
  LEFT JOIN `product` `product.parent` ON `product`.`parent_id` = `product.parent`.`id`
  AND `product`.`parent_version_id` = `product.parent`.`version_id`
  LEFT JOIN `product_visibility` `product.visibilities` ON `product`.`visibilities` = `product.visibilities`.`product_id`
  AND `product`.version_id = `product.visibilities`.product_version_id
  LEFT JOIN `product_category_tree` `product.categoriesRo.mapping` ON `product`.`id` = `product.categoriesRo.mapping`.`product_id`
  AND `product`.`version_id` = `product.categoriesRo.mapping`.`product_version_id`
  LEFT JOIN `product_option` `product.options.mapping` ON `product`.`id` = `product.options.mapping`.`product_id`
  AND `product`.`version_id` = `product.options.mapping`.`product_version_id`
  LEFT JOIN (
    SELECT
      `product.translation`.`product_id`,
      `product.translation`.`product_version_id`,
      `product.translation`.`custom_fields` as `product.translation.customFields`
    FROM
      `product_translation` `product.translation`
    WHERE
      `product.translation`.`language_id` = ?
  ) `product.product_translation` ON `product.product_translation`.`product_id` = `product`.`id`
  AND `product.product_translation`.`product_version_id` = `product`.`version_id`
  LEFT JOIN (
    SELECT
      `product.parent.translation`.`product_id`,
      `product.parent.translation`.`product_version_id`,
      `product.parent.translation`.`custom_fields` as `product.parent.translation.customFields`
    FROM
      `product_translation` `product.parent.translation`
    WHERE
      `product.parent.translation`.`language_id` = ?
  ) `product.parent.product_translation` ON `product.parent.product_translation`.`product_id` = `product.parent`.`id`
  AND `product.parent.product_translation`.`product_version_id` = `product.parent`.`version_id`
  LEFT JOIN `category` `product.categoriesRo` ON `product.categoriesRo.mapping`.`category_id` = `product.categoriesRo`.`id`
  AND `product.categoriesRo`.`version_id` = `product.categoriesRo.mapping`.`category_version_id`
  LEFT JOIN `property_group_option` `product.options` ON `product.options.mapping`.`property_group_option_id` = `product.options`.`id`
WHERE
  (`product`.`version_id` = ?)
  AND (
    (
      (
        COALESCE(
          IF(
            JSON_TYPE(
              JSON_EXTRACT(
                `product.translation.customFields`,
                '$.system4_configurator_customer_number'
              )
            ) != "NULL",
            CONVERT(
              JSON_UNQUOTE(
                JSON_EXTRACT(
                  `product.translation.customFields`,
                  '$.system4_configurator_customer_number'
                )
              ) USING "utf8mb4"
            ) COLLATE utf8mb4_unicode_ci,
            NULL
          ),
          IF(
            JSON_TYPE(
              JSON_EXTRACT(
                `product.parent.translation.customFields`,
                '$.system4_configurator_customer_number'
              )
            ) != "NULL",
            CONVERT(
              JSON_UNQUOTE(
                JSON_EXTRACT(
                  `product.parent.translation.customFields`,
                  '$.system4_configurator_customer_number'
                )
              ) USING "utf8mb4"
            ) COLLATE utf8mb4_unicode_ci,
            NULL
          )
        ) <=> ?
        AND COALESCE(
          IF(
            JSON_TYPE(
              JSON_EXTRACT(
                `product.translation.customFields`,
                '$.system4_configurator_session_id'
              )
            ) != "NULL",
            CONVERT(
              JSON_UNQUOTE(
                JSON_EXTRACT(
                  `product.translation.customFields`,
                  '$.system4_configurator_session_id'
                )
              ) USING "utf8mb4"
            ) COLLATE utf8mb4_unicode_ci,
            NULL
          ),
          IF(
            JSON_TYPE(
              JSON_EXTRACT(
                `product.parent.translation.customFields`,
                '$.system4_configurator_session_id'
              )
            ) != "NULL",
            CONVERT(
              JSON_UNQUOTE(
                JSON_EXTRACT(
                  `product.parent.translation.customFields`,
                  '$.system4_configurator_session_id'
                )
              ) USING "utf8mb4"
            ) COLLATE utf8mb4_unicode_ci,
            NULL
          )
        ) <=> ?
      )
      AND (
        (
          `product.visibilities`.`visibility` >= ?
        )
        AND `product.visibilities`.`sales_channel_id` = ?
        AND IFNULL(
          `product`.`active`, `product.parent`.`active`
        ) = ?
      )
      AND `product.categoriesRo`.`id` = ?
      AND NOT (`product`.`display_group` <=> ?)
    )
  )
GROUP BY
  `product.options`.`id`

# product-listing-route::loading::aggregation::options
SELECT LOWER(HEX(`product.options`.`id`)) as `options.key`, COUNT(`product`.`id`) as `options.count` FROM `product` LEFT JOIN `product` `product.parent` ON `product`.`parent_id` = `product.parent`.`id` AND `product`.`parent_version_id` = `product.parent`.`version_id` LEFT JOIN `product_visibility` `product.visibilities` ON `product`.`visibilities` = `product.visibilities`.`product_id` AND `product`.version_id = `product.visibilities`.product_version_id LEFT JOIN `product_category_tree` `product.categoriesRo.mapping` ON `product`.`id` = `product.categoriesRo.mapping`.`product_id`  AND `product`.`version_id` = `product.categoriesRo.mapping`.`product_version_id` LEFT JOIN `product_option` `product.options.mapping` ON `product`.`id` = `product.options.mapping`.`product_id`  AND `product`.`version_id` = `product.options.mapping`.`product_version_id` LEFT JOIN (SELECT `product.translation`.`product_id`, `product.translation`.`product_version_id`, `product.translation`.`custom_fields` as `product.translation.customFields` FROM `product_translation` `product.translation` WHERE `product.translation`.`language_id` = 0x2FBB5FE2E29A4D70AA5854CE7CE3E20B) `product.product_translation` ON `product.product_translation`.`product_id` = `product`.`id` AND `product.product_translation`.`product_version_id` = `product`.`version_id` LEFT JOIN (SELECT `product.parent.translation`.`product_id`, `product.parent.translation`.`product_version_id`, `product.parent.translation`.`custom_fields` as `product.parent.translation.customFields` FROM `product_translation` `product.parent.translation` WHERE `product.parent.translation`.`language_id` = 0x2FBB5FE2E29A4D70AA5854CE7CE3E20B) `product.parent.product_translation` ON `product.parent.product_translation`.`product_id` = `product.parent`.`id` AND `product.parent.product_translation`.`product_version_id` = `product.parent`.`version_id` LEFT JOIN `category` `product.categoriesRo` ON `product.categoriesRo.mapping`.`category_id` = `product.categoriesRo`.`id`  AND `product.categoriesRo`.`version_id` = `product.categoriesRo.mapping`.`category_version_id` LEFT JOIN `property_group_option` `product.options` ON `product.options.mapping`.`property_group_option_id` = `product.options`.`id`  WHERE (`product`.`version_id` = 0x0FA91CE3E96A4BC2BE4BD9CE752C3425) AND (((COALESCE(IF(JSON_TYPE(JSON_EXTRACT(`product.translation.customFields`, '$.system4_configurator_customer_number')) != "NULL", CONVERT(JSON_UNQUOTE(JSON_EXTRACT(`product.translation.customFields`, '$.system4_configurator_customer_number')) USING "utf8mb4") COLLATE utf8mb4_unicode_ci, NULL),IF(JSON_TYPE(JSON_EXTRACT(`product.parent.translation.customFields`, '$.system4_configurator_customer_number')) != "NULL", CONVERT(JSON_UNQUOTE(JSON_EXTRACT(`product.parent.translation.customFields`, '$.system4_configurator_customer_number')) USING "utf8mb4") COLLATE utf8mb4_unicode_ci, NULL)) <=> NULL AND COALESCE(IF(JSON_TYPE(JSON_EXTRACT(`product.translation.customFields`, '$.system4_configurator_session_id')) != "NULL", CONVERT(JSON_UNQUOTE(JSON_EXTRACT(`product.translation.customFields`, '$.system4_configurator_session_id')) USING "utf8mb4") COLLATE utf8mb4_unicode_ci, NULL),IF(JSON_TYPE(JSON_EXTRACT(`product.parent.translation.customFields`, '$.system4_configurator_session_id')) != "NULL", CONVERT(JSON_UNQUOTE(JSON_EXTRACT(`product.parent.translation.customFields`, '$.system4_configurator_session_id')) USING "utf8mb4") COLLATE utf8mb4_unicode_ci, NULL)) <=> NULL) AND ((`product.visibilities`.`visibility` >= 30) AND `product.visibilities`.`sales_channel_id` = 0xC7B7F9F6CC1D4E0B8705FD682434397E AND IFNULL(`product`.`active`, `product.parent`.`active`) = 1) AND `product.categoriesRo`.`id` = 0xD06B5C68999A4346BC01E2A63273A57E AND NOT (`product`.`display_group` <=> NULL))) GROUP BY `product.options`.`id`;

# product-listing-route::loading::aggregation::category
SELECT
  LOWER(
    HEX(`product.categories`.`id`)
  ) as `category`
FROM
  `product`
  LEFT JOIN `product` `product.parent` ON `product`.`parent_id` = `product.parent`.`id`
  AND `product`.`parent_version_id` = `product.parent`.`version_id`
  LEFT JOIN `product_visibility` `product.visibilities` ON `product`.`visibilities` = `product.visibilities`.`product_id`
  AND `product`.version_id = `product.visibilities`.product_version_id
  LEFT JOIN `product_category_tree` `product.categoriesRo.mapping` ON `product`.`id` = `product.categoriesRo.mapping`.`product_id`
  AND `product`.`version_id` = `product.categoriesRo.mapping`.`product_version_id`
  LEFT JOIN `product_category` `product.categories.mapping` ON `product`.`categories` = `product.categories.mapping`.`product_id`
  AND `product`.`version_id` = `product.categories.mapping`.`product_version_id`
  LEFT JOIN (
    SELECT
      `product.translation`.`product_id`,
      `product.translation`.`product_version_id`,
      `product.translation`.`custom_fields` as `product.translation.customFields`
    FROM
      `product_translation` `product.translation`
    WHERE
      `product.translation`.`language_id` = ?
  ) `product.product_translation` ON `product.product_translation`.`product_id` = `product`.`id`
  AND `product.product_translation`.`product_version_id` = `product`.`version_id`
  LEFT JOIN (
    SELECT
      `product.parent.translation`.`product_id`,
      `product.parent.translation`.`product_version_id`,
      `product.parent.translation`.`custom_fields` as `product.parent.translation.customFields`
    FROM
      `product_translation` `product.parent.translation`
    WHERE
      `product.parent.translation`.`language_id` = ?
  ) `product.parent.product_translation` ON `product.parent.product_translation`.`product_id` = `product.parent`.`id`
  AND `product.parent.product_translation`.`product_version_id` = `product.parent`.`version_id`
  LEFT JOIN `category` `product.categoriesRo` ON `product.categoriesRo.mapping`.`category_id` = `product.categoriesRo`.`id`
  AND `product.categoriesRo`.`version_id` = `product.categoriesRo.mapping`.`category_version_id`
  LEFT JOIN `category` `product.categories` ON `product.categories.mapping`.`category_id` = `product.categories`.`id`
  AND `product.categories`.`version_id` = `product.categories.mapping`.`category_version_id`
WHERE
  (`product`.`version_id` = ?)
  AND (
    (
      (
        COALESCE(
          IF(
            JSON_TYPE(
              JSON_EXTRACT(
                `product.translation.customFields`,
                '$.system4_configurator_customer_number'
              )
            ) != "NULL",
            CONVERT(
              JSON_UNQUOTE(
                JSON_EXTRACT(
                  `product.translation.customFields`,
                  '$.system4_configurator_customer_number'
                )
              ) USING "utf8mb4"
            ) COLLATE utf8mb4_unicode_ci,
            NULL
          ),
          IF(
            JSON_TYPE(
              JSON_EXTRACT(
                `product.parent.translation.customFields`,
                '$.system4_configurator_customer_number'
              )
            ) != "NULL",
            CONVERT(
              JSON_UNQUOTE(
                JSON_EXTRACT(
                  `product.parent.translation.customFields`,
                  '$.system4_configurator_customer_number'
                )
              ) USING "utf8mb4"
            ) COLLATE utf8mb4_unicode_ci,
            NULL
          )
        ) <=> ?
        AND COALESCE(
          IF(
            JSON_TYPE(
              JSON_EXTRACT(
                `product.translation.customFields`,
                '$.system4_configurator_session_id'
              )
            ) != "NULL",
            CONVERT(
              JSON_UNQUOTE(
                JSON_EXTRACT(
                  `product.translation.customFields`,
                  '$.system4_configurator_session_id'
                )
              ) USING "utf8mb4"
            ) COLLATE utf8mb4_unicode_ci,
            NULL
          ),
          IF(
            JSON_TYPE(
              JSON_EXTRACT(
                `product.parent.translation.customFields`,
                '$.system4_configurator_session_id'
              )
            ) != "NULL",
            CONVERT(
              JSON_UNQUOTE(
                JSON_EXTRACT(
                  `product.parent.translation.customFields`,
                  '$.system4_configurator_session_id'
                )
              ) USING "utf8mb4"
            ) COLLATE utf8mb4_unicode_ci,
            NULL
          )
        ) <=> ?
      )
      AND (
        (
          `product.visibilities`.`visibility` >= ?
        )
        AND `product.visibilities`.`sales_channel_id` = ?
        AND IFNULL(
          `product`.`active`, `product.parent`.`active`
        ) = ?
      )
      AND `product.categoriesRo`.`id` = ?
      AND NOT (`product`.`display_group` <=> ?)
    )
  )
GROUP BY
  `product.categories`.`id`

# product-listing-route::loading::aggregation::category
SELECT LOWER(HEX(`product.categories`.`id`)) as `category` FROM `product` LEFT JOIN `product` `product.parent` ON `product`.`parent_id` = `product.parent`.`id` AND `product`.`parent_version_id` = `product.parent`.`version_id` LEFT JOIN `product_visibility` `product.visibilities` ON `product`.`visibilities` = `product.visibilities`.`product_id` AND `product`.version_id = `product.visibilities`.product_version_id LEFT JOIN `product_category_tree` `product.categoriesRo.mapping` ON `product`.`id` = `product.categoriesRo.mapping`.`product_id`  AND `product`.`version_id` = `product.categoriesRo.mapping`.`product_version_id` LEFT JOIN `product_category` `product.categories.mapping` ON `product`.`categories` = `product.categories.mapping`.`product_id`  AND `product`.`version_id` = `product.categories.mapping`.`product_version_id` LEFT JOIN (SELECT `product.translation`.`product_id`, `product.translation`.`product_version_id`, `product.translation`.`custom_fields` as `product.translation.customFields` FROM `product_translation` `product.translation` WHERE `product.translation`.`language_id` = 0x2FBB5FE2E29A4D70AA5854CE7CE3E20B) `product.product_translation` ON `product.product_translation`.`product_id` = `product`.`id` AND `product.product_translation`.`product_version_id` = `product`.`version_id` LEFT JOIN (SELECT `product.parent.translation`.`product_id`, `product.parent.translation`.`product_version_id`, `product.parent.translation`.`custom_fields` as `product.parent.translation.customFields` FROM `product_translation` `product.parent.translation` WHERE `product.parent.translation`.`language_id` = 0x2FBB5FE2E29A4D70AA5854CE7CE3E20B) `product.parent.product_translation` ON `product.parent.product_translation`.`product_id` = `product.parent`.`id` AND `product.parent.product_translation`.`product_version_id` = `product.parent`.`version_id` LEFT JOIN `category` `product.categoriesRo` ON `product.categoriesRo.mapping`.`category_id` = `product.categoriesRo`.`id`  AND `product.categoriesRo`.`version_id` = `product.categoriesRo.mapping`.`category_version_id` LEFT JOIN `category` `product.categories` ON `product.categories.mapping`.`category_id` = `product.categories`.`id`  AND `product.categories`.`version_id` = `product.categories.mapping`.`category_version_id` WHERE (`product`.`version_id` = 0x0FA91CE3E96A4BC2BE4BD9CE752C3425) AND (((COALESCE(IF(JSON_TYPE(JSON_EXTRACT(`product.translation.customFields`, '$.system4_configurator_customer_number')) != "NULL", CONVERT(JSON_UNQUOTE(JSON_EXTRACT(`product.translation.customFields`, '$.system4_configurator_customer_number')) USING "utf8mb4") COLLATE utf8mb4_unicode_ci, NULL),IF(JSON_TYPE(JSON_EXTRACT(`product.parent.translation.customFields`, '$.system4_configurator_customer_number')) != "NULL", CONVERT(JSON_UNQUOTE(JSON_EXTRACT(`product.parent.translation.customFields`, '$.system4_configurator_customer_number')) USING "utf8mb4") COLLATE utf8mb4_unicode_ci, NULL)) <=> NULL AND COALESCE(IF(JSON_TYPE(JSON_EXTRACT(`product.translation.customFields`, '$.system4_configurator_session_id')) != "NULL", CONVERT(JSON_UNQUOTE(JSON_EXTRACT(`product.translation.customFields`, '$.system4_configurator_session_id')) USING "utf8mb4") COLLATE utf8mb4_unicode_ci, NULL),IF(JSON_TYPE(JSON_EXTRACT(`product.parent.translation.customFields`, '$.system4_configurator_session_id')) != "NULL", CONVERT(JSON_UNQUOTE(JSON_EXTRACT(`product.parent.translation.customFields`, '$.system4_configurator_session_id')) USING "utf8mb4") COLLATE utf8mb4_unicode_ci, NULL)) <=> NULL) AND ((`product.visibilities`.`visibility` >= 30) AND `product.visibilities`.`sales_channel_id` = 0xC7B7F9F6CC1D4E0B8705FD682434397E AND IFNULL(`product`.`active`, `product.parent`.`active`) = 1) AND `product.categoriesRo`.`id` = 0xD06B5C68999A4346BC01E2A63273A57E AND NOT (`product`.`display_group` <=> NULL))) GROUP BY `product.categories`.`id`;