Get the value of a key if exists in one array elements in a variant column in snowflake

So given you don't know which element you need to unroll them all via FLATTEN, and then filter out the ones that don't match.

SELECT f.value:data:orgId::text as ordId
FROM table t,
    TABLE(FLATTEN(input => t.mycolumn)) f
WHERE f.value:data:orgId::text IS NOT NULL;

With this pattern you can select any of the other elements you want like site or stage as well.

SELECT 
    f.value:data:orgId::text AS ordId
    f.value:data:site::text AS site
    f.value:data:staging::text AS staging
FROM table t,
    TABLE(FLATTEN(input => t.mycolumn)) f
WHERE f.value:data:orgId::text IS NOT NULL;

"Naive" text approach:

SELECT REGEXP_SUBSTR(ARRAY_TO_STRING(col,''), '"orgId":"([^"]*)"',1,1,'e',1)
FROM T;
-- iVtXg1iwVmT9Q

Data preparation:

CREATE OR REPLACE TABLE T AS 
SELECT PARSE_JSON('[{
           "data": {
              "isCustomerAuthenticated": false,
              "visibility": "open"
           },
           "schema": "jsonschema/2-0-0"
       },
       {
           "data": {
               "orgId": "iVtXg1iwVmT9Q",
               "site": "us-abcde.qa",
               "stage": "staging"
           },
           "schema": "jsonschema/1-0-0"
       },
       {
           "data": {
               "onboardingType": "form"
           },
           "schema": "jsonschema/1-0-1"
       }
   ]') AS col;