英文:
How to flatten this field properly?
问题
I don't know how to flatten this field properly. I've anonymized the data, but the aim is to easily get the 4 Descriptions and the 12 numeric values for each. The RowType: Section repeats for different Title values, but I didn't want to paste the entire field here (I can if you want to).
[{"Cells": [{"Value": "Account"}, {"Value": "May-20"}, {"Value": "Jun-20"}, {"Value": "Jul-20"}, {"Value": "Aug-20"}, {"Value": "Sep-20"}, {"Value": "Oct-20"}, {"Value": "Nov-20"}, {"Value": "Dec-20"}, {"Value": "Jan-21"}, {"Value": "Feb-21"}, {"Value": "Mar-21"}, {"Value": "Apr-21"}], "RowType": "Header"}, {"RowType": "Section", "Rows": [{"Cells": [{"Attributes": [{"Id": "account", "Value": "b14dcf72-7e1e-434a-b162-552b42c17ce3"}], "Value": "Description 0"}, {"Value": "20000.00"}, {"Value": "0.00"}, {"Value": "0.00"}, {"Value": "0.00"}, {"Value": "0.00"}, {"Value": "0.00"}, {"Value": "0.00"}, {"Value": "0.00"}, {"Value": "0.00"}, {"Value": "0.00"}, {"Value": "0.00"}, {"Value": "0.00"}, {"Value": "0.00"}], "RowType": "Row"}, {"Cells": [{"Attributes": [{"Id": "account", "Value": "35ce10fd-4836-51a8-ae4c-22dc0806cb03"}], "Value": "Description 1"}, {"Value": "5324.00"}, {"Value": "5856.00"}, {"Value": "6512.00"}, {"Value": "7086.00"}, {"Value": "7755.00"}, {"Value": "8575.00"}, {"Value": "5433.00"}, {"Value": "0.00"}, {"Value": "0.00"}, {"Value": "0.00"}, {"Value": "0.00"}, {"Value": "0.00"}], "RowType": "Row"}, {"Cells": [{"Attributes": [{"Id": "account", "Value": "b083a747-102d-4f77-aaf5-3b5821e7fe88"}], "Value": "Description 2"}, {"Value": "57881.00"}, {"Value": "60775.00"}, {"Value": "6814.00"}, {"Value": "609.00"}, {"Value": "609.00"}, {"Value": "609.00"}, {"Value": "609.00"}, {"Value": "609.00"}, {"Value": "609.00"}, {"Value": "0.00"}, {"Value": "0.00"}, {"Value": "0.00"}], "RowType": "Row"}, {"Cells": [{"Attributes": [{"Id": "account", "Value": "687524b5-11c2-4afd-a313-1b5b855d55cc"}], "Value": "Description 3"}, {"Value": "300000.00"}, {"Value": "300000.00"}, {"Value": "2389.00"}, {"Value": "2389.00"}, {"Value": "3599.00"}, {"Value": "3599.00"}, {"Value": "3609.00"}, {"Value": "2577.00"}, {"Value": "2588.00"}, {"Value": "0.00"}, {"Value": "0.00"}, {"Value": "0.00"}], "RowType": "Row"}, {"Cells": [{"Attributes": [{"Id": "account", "Value": "c7f74dd2-456a-4335-a530-db85f2614df7"}], "Value": "Description 4"}, {"Value": "23328.00"}, {"Value": "25000.00"}, {"Value": "25000.00"}, {"Value": "28000.00"}, {"Value": "30000.00"}, {"Value": "30000.00"}, {"Value": "22000.00"}, {"Value": "10000.00"}, {"Value": "10000.00"}, {"Value": "0.00"}, {"Value": "0.00"}, {"Value": "0.00"}], "RowType": "Row"}, {"Cells": [{"Value": "Total Income"}, {"Value": "406533.00"}, {"Value": "351631.00"}, {"Value": "415256.00"}, {"Value": "415086.00"}, {"Value": "517755.00"}, {"Value": "518575.00"}, {"Value": "451433.00"}, {"Value": "3599.00"}, {"Value": "2389.00"}, {"Value": "0.00"}, {"Value": "0.00"}, {"Value": "0.00"}], "RowType": "SummaryRow"}], "Title": "Income"}]
英文:
I don't know how to flatten this field properly. I've anonymised the data but the aim is to easily get the 4 Descriptions and the 12 numeric values for each. The RowType : Section repeats for different Title values but I didn't want to paste the entire field here (I can if you want to)
[ { "Cells": [ { "Value": "Account" }, { "Value": "May-20" }, { "Value": "Jun-20" }, { "Value": "Jul-20" }, { "Value": "Aug-20" }, { "Value": "Sep-20" }, { "Value": "Oct-20" }, { "Value": "Nov-20" }, { "Value": "Dec-20" }, { "Value": "Jan-21" }, { "Value": "Feb-21" }, { "Value": "Mar-21" }, { "Value": "Apr-21" } ], "RowType": "Header" }, { "RowType": "Section", "Rows": [ { "Cells": [ { "Attributes": [ { "Id": "account", "Value": "b14dcf72-7e1e-434a-b162-552b42c17ce3" } ], "Value": "Description 0" }, { "Value": "20000.00" }, { "Value": "0.00" }, { "Value": "0.00" }, { "Value": "0.00" }, { "Value": "0.00" }, { "Value": "0.00" }, { "Value": "0.00" }, { "Value": "0.00" }, { "Value": "0.00" }, { "Value": "0.00" }, { "Value": "0.00" }, { "Value": "0.00" } ], "RowType": "Row" }, { "Cells": [ { "Attributes": [ { "Id": "account", "Value": "35ce10fd-4836-51a8-ae4c-22dc0806cb03" } ], "Value": "Description 1" }, { "Value": "5324.00" }, { "Value": "5856.00" }, { "Value": "6512.00" }, { "Value": "7086.00" }, { "Value": "7755.00" }, { "Value": "8575.00" }, { "Value": "5433.00" }, { "Value": "0.00" }, { "Value": "0.00" }, { "Value": "0.00" }, { "Value": "0.00" }, { "Value": "0.00" } ], "RowType": "Row" }, { "Cells": [ { "Attributes": [ { "Id": "account", "Value": "b083a747-102d-4f77-aaf5-3b5821e7fe88" } ], "Value": "Description 2" }, { "Value": "57881.00" }, { "Value": "60775.00" }, { "Value": "6814.00" }, { "Value": "609.00" }, { "Value": "609.00" }, { "Value": "609.00" }, { "Value": "609.00" }, { "Value": "609.00" }, { "Value": "609.00" }, { "Value": "0.00" }, { "Value": "0.00" }, { "Value": "0.00" } ], "RowType": "Row" }, { "Cells": [ { "Attributes": [ { "Id": "account", "Value": "687524b5-11c2-4afd-a313-1b5b855d55cc" } ], "Value": "Description 3" }, { "Value": "300000.00" }, { "Value": "300000.00" }, { "Value": "2389.00" }, { "Value": "2389.00" }, { "Value": "3599.00" }, { "Value": "3599.00" }, { "Value": "3609.00" }, { "Value": "2577.00" }, { "Value": "2588.00" }, { "Value": "0.00" }, { "Value": "0.00" }, { "Value": "0.00" } ], "RowType": "Row" }, { "Cells": [ { "Attributes": [ { "Id": "account", "Value": "c7f74dd2-456a-4335-a530-db85f2614df7" } ], "Value": "Description 4" }, { "Value": "23328.00" }, { "Value": "25000.00" }, { "Value": "25000.00" }, { "Value": "28000.00" }, { "Value": "30000.00" }, { "Value": "30000.00" }, { "Value": "22000.00" }, { "Value": "10000.00" }, { "Value": "10000.00" }, { "Value": "0.00" }, { "Value": "0.00" }, { "Value": "0.00" } ], "RowType": "Row" }, { "Cells": [ { "Value": "Total Income" }, { "Value": "406533.00" }, { "Value": "351631.00" }, { "Value": "415256.00" }, { "Value": "415086.00" }, { "Value": "517755.00" }, { "Value": "518575.00" }, { "Value": "451433.00" }, { "Value": "3599.00" }, { "Value": "2389.00" }, { "Value": "0.00" }, { "Value": "0.00" }, { "Value": "0.00" } ], "RowType": "SummaryRow" } ], "Title": "Income" },
答案1
得分: 1
我假设你的数据在名为 JSON 的列中，位于 "mydata" 表中。以下查询将列出所有值（包括描述和你提到的 12 个值）。
选择项目.seq 项目号，单元格.seq 行号，单元格.index 值编号，单元格.VALUE:"Value" 值
从 mydata，
lateral flatten ( json ) as items，
lateral flatten ( items.VALUE:"Rows" ) item_rows，
lateral flatten ( IFF( item_rows.VALUE NOT ILIKE '%Total Income%',item_rows.VALUE:"Cells", null )) cells;
简而言之，你需要使用 flatten 来迭代项目。从我看到的，你有一行显示总计，所以我用 IFF 进行了过滤。
英文:
I assumed your data is in a column named JSON in "mydata" table. The following query will list all the values (including the description and 12 values you mentioned).
select items.seq item_no, cells.seq row_no, cells.index value_no, cells.VALUE:"Value" val
from mydata,
lateral flatten ( json ) as items,
lateral flatten ( items.VALUE:"Rows" ) item_rows,
lateral flatten ( IFF( item_rows.VALUE NOT ILIKE '%Total Income%' ,item_rows.VALUE:"Cells", null )) cells;
In short, you need to use flatten to iterate through the items. As I see, you have a row to show totals, so I filtered out (using IFF)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论