如果知道产品密钥中可能有多个符号,那么获得产品密钥完整值的正确regex是什么?
理想情况下,应该使用JSON提取(而不是REGExp提取——它会使事情复杂化)。但不幸的是,bigquery的json-extract有一些限制,不允许处理json数组
为了克服jsonpath的bigquery“限制”,可以使用
custom function
如下示例所示:
它使用jsonpath-0.8.0.js,可以从
https://code.google.com/archive/p/jsonpath/downloads
并上传到google cloud storage-gs://your\bucket/jsonpath-0.8.0.js
#standardSQL
CREATE TEMPORARY FUNCTION CUSTOM_JSON_EXTRACT(json STRING, json_path STRING)
RETURNS ARRAY<STRING>
LANGUAGE js AS """
return jsonPath(JSON.parse(json), json_path);
"""
OPTIONS (
library="gs://your_bucket/jsonpath-0.8.0.js"
);
SELECT order_id, quantity, product_id, category_id
FROM `project.dataset.table`,
UNNEST(CUSTOM_JSON_EXTRACT(cart, '$.items[*].quantity')) quantity WITH OFFSET pos1,
UNNEST(CUSTOM_JSON_EXTRACT(cart, '$.items[*].product._id')) product_id WITH OFFSET pos2,
UNNEST(CUSTOM_JSON_EXTRACT(cart, '$.items[*].product.categoryIds')) category_ids WITH OFFSET pos3,
UNNEST(SPLIT(category_ids)) category_id
WHERE pos1 = pos2 AND pos1 = pos3
您可以使用您提供的示例数据测试、播放上述内容:
#standardSQL
CREATE TEMPORARY FUNCTION CUSTOM_JSON_EXTRACT(json STRING, json_path STRING)
RETURNS ARRAY<STRING>
LANGUAGE js AS """
return jsonPath(JSON.parse(json), json_path);
"""
OPTIONS (
library="gs://your_bucket/jsonpath-0.8.0.js"
);
WITH t AS (
SELECT "order1234" AS order_id, '''{ "_id" : "cart1234" , "taxRate" : 0.0 , "items" : [
{ "quantity" : 1 , "product" : { "_id" : "prod1" , "categoryIds" : [ "cat1", "cat2", "cat3"] , "name" : "Product 1" , "imagedata" : { "imageLink" : { "_id" : "img1" , "createdOn" : { "$date" : "2019-01-19T19:55:19.782Z"} , "revision" : 1} , "title" : "" , "description" : "" , "altText" : ""} , "variants" : [ ] , "productVariants" : [ { "_id" : "var1" , "sku" : { "value" : "sku1" , "modifier" : 0} , "variants" : [ ] , "quantity" : 0 , "imageLinkIds" : [ ] , "skuImageLinkIds" : [ ] , "fulfillmentData" : { "sourceName" : null , "sourceId" : null , "sourceSku" : null , "sourceMethod" : null , "sourceRedirectUrl" : null , "sourceRedirectAppKey" : null }}] , "Shipping" : true}} ,
{ "quantity" : 2 , "product" : { "_id" : "prod2" , "categoryIds" : [ "cat2", "cat4"] , "name" : "Product 2" , "imagedata" : { "imageLink" : { "_id" : "img2" , "createdOn" : { "$date" : "2019-01-19T19:58:11.484Z"} , "revision" : 1} , "title" : "" , "description" : "" , "altText" : ""} , "variants" : [ ] , "productVariants" : [ { "_id" : "var2" , "sku" : { "value" : "sku2" , "modifier" : 0} , "variants" : [ ] , "quantity" : 0 , "imageLinkIds" : [ ] , "skuImageLinkIds" : [ ] , "fulfillmentData" : { "sourceName" : null , "sourceId" : null , "sourceSku" : null , "sourceMethod" : null , "sourceRedirectUrl" : null , "sourceRedirectAppKey" : null }}] , "Shipping" : true}} ,
{ "quantity" : 3 , "product" : { "_id" : "prod3" , "categoryIds" : [ "cat2","cat4","cat5"] , "name" : "Product 3" , "imagedata" : { "imageLink" : { "_id" : "img3" , "createdOn" : { "$date" : "2019-01-15T05:34:17.556Z"} , "revision" : 3} , "title" : "" , "description" : "" , "altText" : ""} , "variants" : [ ] , "productVariants" : [ { "_id" : "var3" , "sku" : { "value" : "sku3" , "modifier" : 0} , "variants" : [ ] , "quantity" : 0 , "imageLinkIds" : [ ] , "skuImageLinkIds" : [ ] , "fulfillmentData" : { "sourceName" : null , "sourceId" : null , "sourceSku" : null , "sourceMethod" : null , "sourceRedirectUrl" : null , "sourceRedirectAppKey" : null }}], "Shipping" : true }}
]}''' AS cart
)
SELECT order_id, quantity, product_id, category_id
FROM t,
UNNEST(CUSTOM_JSON_EXTRACT(cart, '$.items[*].quantity')) quantity WITH OFFSET pos1,
UNNEST(CUSTOM_JSON_EXTRACT(cart, '$.items[*].product._id')) product_id WITH OFFSET pos2,
UNNEST(CUSTOM_JSON_EXTRACT(cart, '$.items[*].product.categoryIds')) category_ids WITH OFFSET pos3,
UNNEST(SPLIT(category_ids)) category_id
WHERE pos1 = pos2 AND pos1 = pos3
以结果
Row order_id quantity product_id category_id
1 order1234 1 prod1 cat1
2 order1234 1 prod1 cat2
3 order1234 1 prod1 cat3
4 order1234 2 prod2 cat2
5 order1234 2 prod2 cat4
6 order1234 3 prod3 cat2
7 order1234 3 prod3 cat4
8 order1234 3 prod3 cat5
注:
product_list_price
和
product_sale_price
不存在于您的样本DAAT中,因此不在上述结果中。但现在的查询非常干净和简单,所以希望您能够轻松地添加这些内容。
