Ottenere il valore di una chiave, se esiste una matrice di elementi in una variante colonna fiocco di neve

Quindi, dato non si sa quale elemento di cui avete bisogno per svolgere tutti via APPIATTIRE, e poi filtrare quelle che non corrispondono.

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;

Con questo modello è possibile selezionare uno qualsiasi degli altri elementi che si desidera in site o stage come bene.

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;

"Ingenuo" text approccio:

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

La preparazione dei dati:

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;