How to extract JSON (json is without quotation marks) array stored as string in BigQuery-CodePudding

I have a JSON array that looks similar to this

[{sku:fgh456,price:239.22,quantity:1},{sku:ALK0069,price:110,quantity:1},{sku:dgft567,price:43.92,quantity:1},{sku:NAS0222,price:421.55,quantity:1}]

** note we do not have double quotation and the array is stored as string

Wanted to extract the each sku value. Please help. Thanks

I tried json_query function

CodePudding user response：

Wanted to extract the each sku value.

Since your data is not a valid JSON, consider below workaround instead of using json functions.

WITH sample_data AS (
  SELECT '[{sku:fgh456,price:239.22,quantity:1},{sku:ALK0069,price:110,quantity:1},{sku:dgft567,price:43.92,quantity:1},{sku:NAS0222,price:421.55,quantity:1}]' str
)
SELECT SPLIT(kv, ':')[OFFSET(1)] AS sku
  FROM sample_data,
       UNNEST(SPLIT(TRIM(str,'[]{}'), '},{')) obj,
       UNNEST(SPLIT(obj)) kv
 WHERE SPLIT(kv, ':')[OFFSET(0)] = 'sku';

CodePudding user response：

Consider below option

select sku
from your_table,
unnest(regexp_extract_all(str, r'\bsku:([^},]*)\b')) sku

if applied to sample data in your question - output id