在红移中使用相同键提取钥匙值对
我有一个要求，在列的每个单元格上都有一个JSON数组。我正在尝试找到一种方法来提取相同键值对的每个实例。例如，我想从以下数组中提取'product_id'的每次出现：
[{“ id”：11999993176146155，“ exhullable_quantity”：0，“ product_id”：7538905317611，“ 1，“ sightes_shipping”：true，“应税”：false，“ total_discount”：“ 0.00”，“ total_discount_set”：{“ shop_money”：{“量”：{“量”：“ 0.00”：“ 0.00”，“ crulence_code”，“ pkr”：“ pkr”}，“}，”，“ presentment_money”：{“量”：“ 0.00”，“ crurine_code”：“ pkr”}}}，{“ id”：119993176178923， “ PRISE_SET”：{“ SHOP_MONEY”：{“量”：“ 450.00”，“ Currency_Code”：“ PKR”}，“ presentment_money”：{“量”：{“量”：“ 450.00”，“ crulence_code”，“ currency_code”：“ pkr”}}，pkr“}}，， “ product_exists”：true，“ product_id”：7018040164543}]
我想创建一个名为'product_id'的新列，并将提取的值存储在此列中。我的最终结果应该像这样：
我已经在红移上查看了一些功能，但不幸的是，这些功能无济于事。如果有人可以提供解决方案，那就太好了。
I have a requirement where I have a json array on each cell of the column. I am trying to find a way to extract each instance of the same key value pair. For example, I would like to extract each occurrence of 'product_id' from the following array:
[{"id": 11993176146155, "fulfillable_quantity": 0, "product_id": 7538905317611, "properties": [], "quantity": 1, "requires_shipping": true, "taxable": false, "total_discount": "0.00", "total_discount_set": {"shop_money": {"amount": "0.00", "currency_code": "PKR"}, "presentment_money": {"amount": "0.00", "currency_code": "PKR"}}}, {"id": 11993176178923,
"price_set": {"shop_money": {"amount": "450.00", "currency_code": "PKR"}, "presentment_money": {"amount": "450.00", "currency_code": "PKR"}}, "product_exists": true, "product_id": 7018040164543}]
I want to create a new column called 'Product_ID' and store the extracted values in this column. My final result should look like this:
Is there a way in redshift to iterate over the entire array for each row and extract the desired values (in this case it is product IDs)? I have looked at some functions on redshift but unfortunately those didn't help. Would be great if someone could provide a solution.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您需要解开数组，然后选择所需的键。
这里有一个很棒的教程，可在这里进行红移中的nesting阵列： httpps：/ /blog.getdbt.com/how-to-unnest-rays-in-redshift/
You need to unnest the array and then select the keys you want.
There is a great tutorial for unnesting arrays in Redshift here: https://blog.getdbt.com/how-to-unnest-arrays-in-redshift/