SQL 查找 varchar 类型列及其值中多次出现的子字符串

我使用雅典娜作为数据库表.我想以表顺序解析名为‘line_Items’的列.因此，Orders表中的每一行都包含一个客户订单，并且LINE_ITEMS包含客户订单中包含的所有产品的详细信息.我希望在line_Items中找到每个项目的product_id，然后基于这些product_id，我需要将每个product_id与其在product_Details表中的详细信息连接在一起.

LINE_ITEMS的数据类型为varchar，不能更改.

其中一个订单的样本值如下:

   [{'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': []}]

我编写了一个查询，它返回First Line_Items的ID，但我需要line_Items中的所有ID.请告诉我这方面的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')
 )

我的查询输出为: 13942775087176

我想要每个行项目的id，然后稍后，还需要对line_itemes中的Amount字段进行求和.

请指点一下.