without-or.sql

/*
duration: 4210.015 ms
parameters: $1 = 'networking', $2 = 'networking', $3 = 'networking', $4 = 'networking', $5 = 'group', $6 = '{"\"group-name\""}'
*/
WITH inactive_nodes AS (
  SELECT 
    certname 
  FROM 
    certnames 
  WHERE 
    (
      deactivated IS NOT NULL 
      AND deactivated > '2023-08-24T09:50:54.019Z'
    ) 
    OR (
      expired IS NOT NULL 
      and expired > '2023-08-24T09:50:54.019Z'
    )
), 
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, 
      $1 :: text as key, 
      (stable || volatile)-> $2 as value 
    from 
      factsets 
    where 
      (stable || volatile) ? $3
  ) AS fs 
WHERE 
  (
    (
      (fs.key = $4)
    ) 
    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 = $5)
              ) 
              AND (
                fs.value = ANY($6)
              )
          ) AS sub
      )
    )
  ) 
  AND NOT (
    (fs.certname) IN (
      SELECT 
        certname 
      FROM 
        (
          SELECT 
            not_active_nodes.certname AS "certname" 
          FROM 
            not_active_nodes
        ) AS sub
    )
  )