Athena中解析varchar类型line_items列获取所有行项目ID并关联产品表

• 基于Athena数据库，需要解析orders表中varchar类型的line_items列，该列存储了订单下的所有产品明细
• 当前编写的SQL仅能提取第一个行项目的id，需求改为获取所有行项目的id，同时要支持后续的金额汇总操作，以及关联product_details表获取产品详情
• line_items列的数据类型无法修改，该列的示例值如下：

[{'id': 13942775087176, 'admin_graphql_api_id': 'gid://shop/LineItem/13942775087176', 'fulfillable_quantity': 0, 'fulfillment_service': 'manual', 'fulfillment_status': 'fulfilled', 'gift_card': False, 'grams': 585, 'name': 'Military Green Comfort Chino Pants - 36', 'pre_tax_price': 44.89, 'pre_tax_price_set': {'shop_money': {'amount': '44.89', 'currency_code': 'USD'}, 'presentment_money': {'amount': '44.89', 'currency_code': 'USD'}}, 'price': 44.89, 'price_set': {'shop_money': {'amount': 44.89, 'currency_code': 'USD'}, 'presentment_money': {'amount': 44.89, 'currency_code': 'USD'}}, 'product_exists': True, 'product_id': 6633367306312, 'properties': [{'name': '_igTestGroups', 'value': 'fee1aa1f534b,71aca8e2923f'}, {'name': '_igTestGroup', 'value': '33c7ca5a-faf4-452e-adba-fee1aa1f534b'}], 'quantity': 1, 'requires_shipping': True, 'sku': 'TCT4702MGRN36', 'tax_code': 'PC040100', 'taxable': True, 'title': 'Military Green Comfort Chino Pants', 'total_discount': 0.0, 'total_discount_set': {'shop_money': {'amount': 0.0, 'currency_code': 'USD'}, 'presentment_money': {'amount': 0.0, 'currency_code': 'USD'}}, 'variant_id': 39507850657864, 'variant_inventory_management': 'shop', 'variant_title': '36', 'vendor': 'True Classic', 'tax_lines': [{'channel_liable': False, 'price': 2.51, 'price_set': {'shop_money': {'amount': 2.51, 'currency_code': 'USD'}, 'presentment_money': {'amount': 2.51, 'currency_code': 'USD'}}, 'rate': 0.056, 'title': 'AZ STATE TAX'}, {'channel_liable': False, 'price': 0.31, 'price_set': {'shop_money': {'amount': 0.31, 'currency_code': 'USD'}, 'presentment_money': {'amount': 0.31, 'currency_code': 'USD'}}, 'rate': 0.007, 'title': 'AZ COUNTY TAX'}, {'channel_liable': False, 'price': 1.03, 'price_set': {'shop_money': {'amount': 1.03, 'currency_code': 'USD'}, 'presentment_money': {'amount': 1.03, 'currency_code': 'USD'}}, 'rate': 0.023, 'title': 'AZ CITY TAX'}], 'duties': [], 'discount_allocations': []}, {'id': 13942775119944, 'admin_graphql_api_id': 'gid://shop/LineItem/13942775119944', 'fulfillable_quantity': 0, 'fulfillment_service': 'manual', 'fulfillment_status': 'fulfilled', 'gift_card': False, 'grams': 585, 'name': 'Khaki Comfort Chino Pants - 36', 'pre_tax_price': 44.89, 'pre_tax_price_set': {'shop_money': {'amount': '44.89', 'currency_code': 'USD'}, 'presentment_money': {'amount': '44.89', 'currency_code': 'USD'}}, 'price': 44.89, 'price_set': {'shop_money': {'amount': 44.89, 'currency_code': 'USD'}, 'presentment_money': {'amount': 44.89, 'currency_code': 'USD'}}, 'product_exists': True, 'product_id': 6633366388808, 'properties': [{'name': '_igTestGroups', 'value': 'fee1aa1f534b,71aca8e2923f'}, {'name': '_igTestGroup', 'value': '33c7ca5a-faf4-452e-adba-fee1aa1f534b'}], 'quantity': 1, 'requires_shipping': True, 'sku': 'TCT4702KHAKI36', 'tax_code': 'PC040100', 'taxable': True, 'title': 'Khaki Comfort Chino Pants', 'total_discount': 0.0, 'total_discount_set': {'shop_money': {'amount': 0.0, 'currency_code': 'USD'}, 'presentment_money': {'amount': 0.0, 'currency_code': 'USD'}}, 'variant_id': 39507846725704, 'variant_inventory_management': 'shop', 'variant_title': '36', 'vendor': 'True Classic', 'tax_lines': [{'channel_liable': False, 'price': 2.51, 'price_set': {'shop_money': {'amount': 2.51, 'currency_code': 'USD'}, 'presentment_money': {'amount': 2.51, 'currency_code': 'USD'}}, 'rate': 0.056, 'title': 'AZ STATE TAX'}, {'channel_liable': False, 'price': 0.31, 'price_set': {'shop_money': {'amount': 0.31, 'currency_code': 'USD'}, 'presentment_money': {'amount': 0.31, 'currency_code': 'USD'}}, 'rate': 0.007, 'title': 'AZ COUNTY TAX'}, {'channel_liable': False, 'price': 1.03, 'price_set': {'shop_money': {'amount': 1.03, 'currency_code': 'USD'}, 'presentment_money': {'amount': 1.03, 'currency_code': 'USD'}}, 'rate': 0.023, 'title': 'AZ CITY TAX'}], 'duties': [], 'discount_allocations': []}]

当前使用的SQL：

select split_part(SUBSTRING(line_items, posa + 6, 20), ',', 1) as line_item_id from (
 select POSITION('''id'': ' IN line_items) as posa, substring(line_items, POSITION('''id'': ' IN line_items)+6, POSITION(',' IN line_items)) AS id_value, line_items
 from orders where id in ('45245','5463556','64874')
 )

输出仅能得到第一个行项目的id：

13942775087176

利用Athena的JSON解析函数处理类JSON格式的line_items列，步骤如下：

1. 将单引号替换为双引号，转换为标准JSON格式
2. 解析JSON数组并拆分为单独的行项目
3. 提取所需字段，同时保留订单关联信息
4. 支持后续关联产品表与金额汇总

完整SQL代码 #

WITH order_line_items AS (
    -- 转换格式并拆分行项目
    SELECT
        o.id AS order_id,
        -- 提取每个行项目的JSON对象
        JSON_EXTRACT_ARRAY_ELEMENTS(
            JSON_PARSE(REPLACE(o.line_items, '''', '"'))
        ) AS line_item_json
    FROM orders o
    WHERE o.id IN ('45245','5463556','64874')
)
SELECT
    -- 提取行项目核心字段
    JSON_EXTRACT_SCALAR(li.line_item_json, '$.id') AS line_item_id,
    JSON_EXTRACT_SCALAR(li.line_item_json, '$.product_id') AS product_id,
    JSON_EXTRACT_SCALAR(li.line_item_json, '$.price') AS price,
    JSON_EXTRACT_SCALAR(li.line_item_json, '$.quantity') AS quantity,
    -- 计算单条行项目的总金额（用于后续汇总）
    CAST(JSON_EXTRACT_SCALAR(li.line_item_json, '$.price') AS DOUBLE) * 
    CAST(JSON_EXTRACT_SCALAR(li.line_item_json, '$.quantity') AS DOUBLE) AS total_line_amount,
    -- 关联产品详情表获取产品信息
    pd.product_name,
    pd.category,
    pd.vendor
FROM order_line_items li
LEFT JOIN product_details pd 
    ON CAST(li.product_id AS BIGINT) = pd.id
-- 可选：按订单或产品汇总金额
-- GROUP BY li.order_id, pd.product_name
-- SUM(total_line_amount) AS total_order_amount
;

关键函数说明 #

• REPLACE(o.line_items, '''', '"')：将原字符串中的单引号替换为双引号，转换为Athena可解析的标准JSON格式
• JSON_PARSE(...)：将转换后的字符串解析为JSON数组
• JSON_EXTRACT_ARRAY_ELEMENTS(...)：将JSON数组拆分为单个行项目JSON对象
• JSON_EXTRACT_SCALAR(...)：从JSON对象中提取指定路径的标量值（如id、价格等）

后续扩展 #

• 金额汇总：可以通过GROUP BY子句按订单ID、产品ID等维度汇总总金额
• 字段扩展：如需提取更多字段（如sku、tax_lines等），只需添加对应的JSON_EXTRACT_SCALAR或JSON_EXTRACT语句
• 数据类型转换：根据实际需求将提取的字符串字段转换为对应的数据类型（如BIGINT、DOUBLE）

内容的提问来源于stack exchange，提问作者azaveri7