with-or.sql

/*
duration: 72.267 ms
parameters: $1 = 'networking', $2 = 'random-string', $3 = 'group', $4 = '{"\"group-name\""}'
*/
WITH inactive_nodes AS (
  SELECT 
    certname 
  FROM 
    certnames 
  WHERE 
    (
      deactivated IS NOT NULL 
      AND deactivated > '2023-08-24T09:52:36.205Z'
    ) 
    OR (
      expired IS NOT NULL 
      and expired > '2023-08-24T09:52:36.205Z'
    )
), 
not_active_nodes AS (
  SELECT 
    certname 
  FROM 
    certnames 
  WHERE 
    (deactivated IS NOT NULL) 
    OR (expired IS NOT NULL)
) 
SELECT 
  fs.certname AS "certname", 
  fs.value AS "value" 
FROM 
  (
    select 
      certname, 
      environment_id, 
      (
        jsonb_each(
          (stable || volatile)
        )
      ).* 
    from 
      factsets
  ) AS fs 
  LEFT JOIN environments AS env ON fs.environment_id = env.id 
WHERE 
  (
    (
      (
        (fs.key = $1)
      ) 
      OR (
        (fs.key = $2)
      )
    ) 
    AND (
      (fs.certname) IN (
        SELECT 
          certname 
        FROM 
          (
            SELECT 
              fs.certname AS "certname" 
            FROM 
              (
                select 
                  certname, 
                  environment_id, 
                  (
                    jsonb_each(
                      (stable || volatile)
                    )
                  ).* 
                from 
                  factsets
              ) AS fs 
              LEFT JOIN environments AS env ON fs.environment_id = env.id 
            WHERE 
              (
                (fs.key = $3)
              ) 
              AND (
                fs.value = ANY($4)
              )
          ) AS sub
      )
    )
  ) 
  AND NOT (
    (fs.certname) IN (
      SELECT 
        certname 
      FROM 
        (
          SELECT 
            not_active_nodes.certname AS "certname" 
          FROM 
            not_active_nodes
        ) AS sub
    )
  )