Uzyskaj wartość klucza, jeśli istnieje w jednym elemencie tablicy w kolumnie opcja w snowflake

Dlatego, biorąc pod uwagę, że nie wiesz, który element jest potrzebny, aby wdrożyć je wszystkie za pomocą WYRÓWNANIA, a następnie odfiltrować te, które nie pasują.

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;

Za pomocą tego szablonu można wybrać dowolny z innych elementów, które chcesz, np. site lub stage również.

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;

"Naiwny" tekstowy podejście:

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

Przygotowanie danych:

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;