英文:
Multiple unnest
问题
select moi.ORDERNO,
unnest(regexp_extract_all(itemdetails,'itemId\\\\":\\\\"(.*?)\\\\",')) as sku,
unnest(regexp_extract_all(itemdetails,'mrp\\\\":(.*?),')) as mrp,
unnest(regexp_extract_all(itemdetails,'discountPrice\\\\":(.*?)}')) as discountedprice
from `CUSTOMERS.MEDICINE_ORDER_INVOICE` moi
limit 100
你的SQL查询中出现了错误，原因是 "unnest" 关键词的使用不正确。我已经帮你修正了该部分的代码。
英文:
select moi.ORDERNO,
unnest(regexp_extract_all(itemdetails,'itemId\\\\":\\\\"(.*?)\\\\",')) as sku,
unnest(regexp_extract_all(itemdetails,'mrp\\\\":(.*?),')) as mrp,
unnest(regexp_extract_all(itemdetails,'discountPrice\\\\":(.*?)}')) as discountedprice,
-- ITEMDETAILS
from `CUSTOMERS.MEDICINE_ORDER_INVOICE` moi
limit 100
Item details is of the form
"[{\"itemName\":\"Colgate Super Rakshak Toothpowder, 100 gm\",\"itemId\":\"COL0129\",\"batchId\":\"B02A1/H\",\"issuedQty\":3,\"mou\":1,\"mrp\":60,\"isSubstitute\":false,\"discountPrice\":0},{\"itemName\":\"Colgate Super Rakshak Toothpowder, 100 gm\",\"itemId\":\"COL0129\",\"batchId\":\"B23/H\",\"issuedQty\":1,\"mou\":1,\"mrp\":62,\"isSubstitute\":false,\"discountPrice\":0}]"
The error I am getting is unexpected keyword unnest.
答案1
得分: 0
你应该在BigQuery的FROM子句中展开数组，而不是在SELECT列表子句中。
with `CUSTOMERS.MEDICINE_ORDER_INVOICE` as (
select
-- 在此处放入*itemdetails*的示例数据
as itemdetails
)
select sku, mrp, discountedprice
from `CUSTOMERS.MEDICINE_ORDER_INVOICE` moi,
unnest (regexp_extract_all(itemdetails, r'itemId\":\"(.*?)\",')) as sku with offset o1,
unnest (regexp_extract_all(itemdetails, r'mrp\":(.*?),')) as mrp with offset o2,
unnest (regexp_extract_all(itemdetails, r'discountPrice\":(.*?)}')) as discountedprice with offset o3
where o1 = o2 AND o2 = o3;
-- 查询结果
+---------+-----+-----------------+
| sku | mrp | discountedprice |
+---------+-----+-----------------+
| COL0129 | 60 | 0 |
| COL0129 | 62 | 0 |
+---------+-----+-----------------+
英文:
You should flatten an array in FROM clause in BigQuery, not SELECT list clause.
with `CUSTOMERS.MEDICINE_ORDER_INVOICE` as (
select
-- put *itemdetails* sample data here
as itemdetails
)
select sku, mrp, discountedprice
from `CUSTOMERS.MEDICINE_ORDER_INVOICE` moi,
unnest (regexp_extract_all(itemdetails, r'itemId\":\"(.*?)\",')) as sku with offset o1,
unnest (regexp_extract_all(itemdetails, r'mrp\":(.*?),')) as mrp with offset o2,
unnest (regexp_extract_all(itemdetails, r'discountPrice\":(.*?)}')) as discountedprice with offset o3
where o1 = o2 AND o2 = o3;
-- query result
+---------+-----+-----------------+
| sku | mrp | discountedprice |
+---------+-----+-----------------+
| COL0129 | 60 | 0 |
| COL0129 | 62 | 0 |
+---------+-----+-----------------+
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论