数据帧到JSON-然后在雪花中扁平

问题描述

无法有效地将DF对象转换为JSON对象&；，然后在Snowflake中展平为表格格式。

df：

Date           User Interaction
2021-03-28     0
2021-03-28     0
2021-03-28     0
2021-03-28     0
....

因此，我运行df = df.to_json(orient ='columns')&；获得以下输出： 输出：

"{"Day":{"0":"2021-03-28","1":"2021-03-28","2":"2021-03-28","3":"2021-03-28","4":"2021-03-28","5":"2021-03-28","6":"2021-03-28","7":"2021-03-28","8":"2021-03-28","9":"2021-03-28","10":"2021-03-28","11":"2021-03-28","12":"2021-03-28","13":"2021-03-28","14":"2021-03-28","15":"2021-03-28","16":"2021-03-28","17":"2021-03-28","18":"2021-03-28","19":"2021-03-28","20":"2021-03-28","21":"2021-03-28","22":"2021-03-28","23":"2021-03-28","24":"2021-03-28","25":"2021-03-28","26":"2021-03-28","27":"2021-03-28","28":"2021-03-28","29":"2021-03-28","30":"2021-03-28","31":"2021-03-28","32":"2021-03-28","33":"2021-03-28","34":"2021-03-28","35":"2021-03-28","36":"2021-03-28","37":"2021-03-28","38":"2021-03-28","39":"2021-03-28","40":"2021-03-28","41":"2021-03-28","42":"2021-03-28","43":"2021-03-28","44":"2021-03-28","45":"2021-03-28","46":"2021-03-28","47":"2021-03-28","48":"2021-03-28","49":"2021-03-28","50":"2021-03-28","51":"2021-03-28","52":"2021-03-28","53":"2021-03-28","54":"2021-03-28","55":"2021-03-28","56":"2021-03-28","57":"2021-03-28","58":"2021-03-28","59":"2021-03-28","60":"2021-03-28","61":"2021-03-28","62":"2021-03-28","63":"2021-03-28","64":"2021-03-28","65":"2021-03-28","66":"2021-03-28","67":"2021-03-28","68":"2021-03-28","69":"2021-03-28","70":"2021-03-28","71":"2021-03-28","72":"2021-03-28","73":"2021-03-28","74":"2021-03-28","75":"2021-03-28","76":"2021-03-28","77":"2021-03-28","78":"2021-03-28","79":"2021-03-28","80":"2021-03-28","81":"2021-03-28","82":"2021-03-28","83":"2021-03-28","84":"2021-03-28","85":"2021-03-28","86":"2021-03-28","87":"2021-03-28","88":"2021-03-28","89":"2021-03-28","90":"2021-03-28","91":"2021-03-28","92":"2021-03-28","93":"2021-03-28","94":"2021-03-28","95":"2021-03-28","96":"2021-03-28","97":"2021-03-28","98":"2021-03-28","99":"2021-03-28","100":"2021-03-28","101":"2021-03-28","102":"2021-03-28","103":"2021-03-28","104":"2021-03-28","105":"2021-03-28","106":"2021-03-28","107":"2021-03-28","108":"2021-03-28","109":"2021-03-28","110":"2021-03-28","111":"2021-03-28","112":"2021-03-28","113":"2021-03-28","114":"2021-03-28","115":"2021-03-28","116":"2021-03-28","117":"2021-03-28","118":"2021-03-28","119":"2021-03-28","120":"2021-03-28","121":"2021-03-28","122":"2021-03-28","123":"2021-03-28","124":"2021-03-28","125":"2021-03-28","126":"2021-03-28","127":"2021-03-28","128":"2021-03-28","129":"2021-03-28","130":"2021-03-28","131":"2021-03-28","132":"2021-03-28","133":"2021-03-28","134":"2021-03-28","135":"2021-03-28","136":"2021-03-28","137":"2021-03-28","138":"2021-03-28","139":"2021-03-28","140":"2021-03-28","141":"2021-03-28","142":"2021-03-28","143":"2021-03-28","144":"2021-03-28","145":"2021-03-28","146":"2021-03-28","147":"2021-03-28","148":"2021-03-28","149":"2021-03-28","150":"2021-03-28","151":"2021-03-28","152":"2021-03-28","153":"2021-03-28","154":"2021-03-28","155":"2021-03-28","156":"2021-03-28","157":"2021-03-28","158":"2021-03-28","159":"2021-03-28","160":"2021-03-28","161":"2021-03-28","162":"2021-03-28","163":"2021-03-28","164":"2021-03-28","165":"2021-03-28","166":"2021-03-28","167":"2021-03-28","168":"2021-03-28","169":"2021-03-28","170":"2021-03-28","171":"2021-03-28","172":"2021-03-28","173":"2021-03-28","174":"2021-03-28","175":"2021-03-28","176":"2021-03-28","177":"2021-03-28","178":"2021-03-28","179":"2021-03-28","180":"2021-03-28","181":"2021-03-28","182":"2021-03-28","183":"2021-03-28","184":"2021-03-28","185":"2021-03-28","186":"2021-03-28","187":"2021-03-28","188":"2021-03-28","189":"2021-03-28","190":"2021-03-28","191":"2021-03-28","192":"2021-03-28","193":"2021-03-28","194":"2021-03-28","195":"2021-03-28","196":"2021-03-28","197":"2021-03-28","198":"2021-03-28","199":"2021-03-28","200":"2021-03-28","201":"2021-03-28","202":"2021-03-28","203":"2021-03-28","204":"2021-03-28","205":"2021-03-28","206":"2021-03-28","207":"2021-03-28","208":"2021-03-28","209":"2021-03-28","210":"2021-03-28","211":"2021-03-28","212":"2021-03-28","213":"2021-03-28","214":"2021-03-28","215":"2021-03-28","216":"2021-03-28","217":"2021-03-28","218":"2021-03-28","219":"2021-03-28","220":"2021-03-28","221":"2021-03-28","222":"2021-03-28","223":"2021-03-28","224":"2021-03-28","225":"2021-03-28","226":"2021-03-28"},"User \/ Activity":{"0":0,"1":0,"2":0,"3":0,"4":0,"5":0,"6":0,"7":0,"8":85110000,"9":0,"10":0,"11":0,"12":0,"13":0,"14":0,"15":0,"16":0,"17":0,"18":0,"19":0,"20":0,"21":0,"22":0,"23":27780000,"24":0,"25":0,"26":0,"27":0,"28":0,"29":0,"30":0,"31":0,"32":0,"33":0,"34":0,"35":0,"36":0,"37":0,"38":0,"39":0,"40":0,"41":0,"42":0,"43":0,"44":0,"45":0,"46":0,"47":0,"48":0,"49":0,"50":0,"51":0,"52":0,"53":0,"54":0,"55":0,"56":0,"57":0,"58":0,"59":0,"60":0,"61":0,"62":0,"63":0,"64":0,"65":0,"66":0,"67":0,"68":0,"69":0,"70":0,"71":0,"72":0,"73":0,"74":0,"75":0,"76":194350000,"77":0,"78":0,"79":0,"80":0,"81":0,"82":0,"83":0,"84":0,"85":0,"86":0,"87":0,"88":0,"89":0,"90":0,"91":0,"92":0,"93":0,"94":0,"95":0,"96":0,"97":0,"98":0,"99":0,"100":0,"101":0,"102":0,"103":0,"104":0,"105":0,"106":51370000,"107":0,"108":0,"109":0,"110":0,"111":0,"112":0,"113":0,"114":0,"115":0,"116":0,"117":0,"118":0,"119":0,"120":180797500,"121":0,"122":0,"123":0,"124":33331250,"125":0,"126":0,"127":0,"128":0,"129":0,"130":0,"131":0,"132":0,"133":0,"134":0,"135":0,"136":0,"137":0,"138":0,"139":0,"140":0,"141":0,"142":0,"143":0,"144":0,"145":0,"146":0,"147":0,"148":0,"149":0,"150":0,"151":0,"152":0,"153":23210000,"154":0,"155":0,"156":0,"157":0,"158":0,"159":0,"160":0,"161":0,"162":0,"163":0,"164":0,"165":0,"166":0,"167":0,"168":0,"169":0,"170":0,"171":32965000,"172":76040000,"173":3890000,"174":0,"175":0,"176":0,"177":0,"178":0,"179":0,"180":0,"181":0,"182":0,"183":65360000,"184":0,"185":0,"186":0,"187":0,"188":0,"189":0,"190":0,"191":0,"192":0,"193":0,"194":0,"195":0,"196":0,"197":0,"198":0,"199":0,"200":0,"201":0,"202":0,"203":0,"204":0,"205":0,"206":0,"207":0,"208":0,"209":0,"210":0,"211":0,"212":0,"213":0,"214":0,"215":365435000,"216":0,"217":0,"218":0,"219":0,"220":0,"221":0,"222":0,"223":0,"224":0,"225":0,"226":0}}"

我正在尝试从雪花中选择开始

Select $1:"Day"

但我得到的全部为空

想知道如何处理列名转换的奇怪方式，如何可能将更多参数传递到to_json(..)参数以使展平更容易。或者如何使用雪花SQL将上面的json输出扁平化为奇怪的列名，我猜这些列名实际上就是键名。

---

解决方案

基本上，与仅将数据作为行进行处理相比，这是一种相当粗略的数据处理方式。

此外，您的列"；user/active"；与"；User Interaction"；不匹配，但是如果重写json以删除\/，则可以访问To对象：

select 
parse_json('{"Day":{"0":"2021-03-28","1":"2021-03-28","2":"2021-03-28","3":"2021-03-28","4":"2021-03-28","5":"2021-03-28","6":"2021-03-28","7":"2021-03-28","8":"2021-03-28","9":"2021-03-28","10":"2021-03-28","11":"2021-03-28","12":"2021-03-28","13":"2021-03-28","14":"2021-03-28","15":"2021-03-28","16":"2021-03-28","17":"2021-03-28","18":"2021-03-28","19":"2021-03-28","20":"2021-03-28","21":"2021-03-28","22":"2021-03-28","23":"2021-03-28","24":"2021-03-28","25":"2021-03-28","26":"2021-03-28","27":"2021-03-28","28":"2021-03-28","29":"2021-03-28","30":"2021-03-28","31":"2021-03-28","32":"2021-03-28","33":"2021-03-28","34":"2021-03-28","35":"2021-03-28","36":"2021-03-28","37":"2021-03-28","38":"2021-03-28","39":"2021-03-28","40":"2021-03-28","41":"2021-03-28","42":"2021-03-28","43":"2021-03-28","44":"2021-03-28","45":"2021-03-28","46":"2021-03-28","47":"2021-03-28","48":"2021-03-28","49":"2021-03-28","50":"2021-03-28","51":"2021-03-28","52":"2021-03-28","53":"2021-03-28","54":"2021-03-28","55":"2021-03-28","56":"2021-03-28","57":"2021-03-28","58":"2021-03-28","59":"2021-03-28","60":"2021-03-28","61":"2021-03-28","62":"2021-03-28","63":"2021-03-28","64":"2021-03-28","65":"2021-03-28","66":"2021-03-28","67":"2021-03-28","68":"2021-03-28","69":"2021-03-28","70":"2021-03-28","71":"2021-03-28","72":"2021-03-28","73":"2021-03-28","74":"2021-03-28","75":"2021-03-28","76":"2021-03-28","77":"2021-03-28","78":"2021-03-28","79":"2021-03-28","80":"2021-03-28","81":"2021-03-28","82":"2021-03-28","83":"2021-03-28","84":"2021-03-28","85":"2021-03-28","86":"2021-03-28","87":"2021-03-28","88":"2021-03-28","89":"2021-03-28","90":"2021-03-28","91":"2021-03-28","92":"2021-03-28","93":"2021-03-28","94":"2021-03-28","95":"2021-03-28","96":"2021-03-28","97":"2021-03-28","98":"2021-03-28","99":"2021-03-28","100":"2021-03-28","101":"2021-03-28","102":"2021-03-28","103":"2021-03-28","104":"2021-03-28","105":"2021-03-28","106":"2021-03-28","107":"2021-03-28","108":"2021-03-28","109":"2021-03-28","110":"2021-03-28","111":"2021-03-28","112":"2021-03-28","113":"2021-03-28","114":"2021-03-28","115":"2021-03-28","116":"2021-03-28","117":"2021-03-28","118":"2021-03-28","119":"2021-03-28","120":"2021-03-28","121":"2021-03-28","122":"2021-03-28","123":"2021-03-28","124":"2021-03-28","125":"2021-03-28","126":"2021-03-28","127":"2021-03-28","128":"2021-03-28","129":"2021-03-28","130":"2021-03-28","131":"2021-03-28","132":"2021-03-28","133":"2021-03-28","134":"2021-03-28","135":"2021-03-28","136":"2021-03-28","137":"2021-03-28","138":"2021-03-28","139":"2021-03-28","140":"2021-03-28","141":"2021-03-28","142":"2021-03-28","143":"2021-03-28","144":"2021-03-28","145":"2021-03-28","146":"2021-03-28","147":"2021-03-28","148":"2021-03-28","149":"2021-03-28","150":"2021-03-28","151":"2021-03-28","152":"2021-03-28","153":"2021-03-28","154":"2021-03-28","155":"2021-03-28","156":"2021-03-28","157":"2021-03-28","158":"2021-03-28","159":"2021-03-28","160":"2021-03-28","161":"2021-03-28","162":"2021-03-28","163":"2021-03-28","164":"2021-03-28","165":"2021-03-28","166":"2021-03-28","167":"2021-03-28","168":"2021-03-28","169":"2021-03-28","170":"2021-03-28","171":"2021-03-28","172":"2021-03-28","173":"2021-03-28","174":"2021-03-28","175":"2021-03-28","176":"2021-03-28","177":"2021-03-28","178":"2021-03-28","179":"2021-03-28","180":"2021-03-28","181":"2021-03-28","182":"2021-03-28","183":"2021-03-28","184":"2021-03-28","185":"2021-03-28","186":"2021-03-28","187":"2021-03-28","188":"2021-03-28","189":"2021-03-28","190":"2021-03-28","191":"2021-03-28","192":"2021-03-28","193":"2021-03-28","194":"2021-03-28","195":"2021-03-28","196":"2021-03-28","197":"2021-03-28","198":"2021-03-28","199":"2021-03-28","200":"2021-03-28","201":"2021-03-28","202":"2021-03-28","203":"2021-03-28","204":"2021-03-28","205":"2021-03-28","206":"2021-03-28","207":"2021-03-28","208":"2021-03-28","209":"2021-03-28","210":"2021-03-28","211":"2021-03-28","212":"2021-03-28","213":"2021-03-28","214":"2021-03-28","215":"2021-03-28","216":"2021-03-28","217":"2021-03-28","218":"2021-03-28","219":"2021-03-28","220":"2021-03-28","221":"2021-03-28","222":"2021-03-28","223":"2021-03-28","224":"2021-03-28","225":"2021-03-28","226":"2021-03-28"},
"User Activity":{"0":0,"1":0,"2":0,"3":0,"4":0,"5":0,"6":0,"7":0,"8":85110000,"9":0,"10":0,"11":0,"12":0,"13":0,"14":0,"15":0,"16":0,"17":0,"18":0,"19":0,"20":0,"21":0,"22":0,"23":27780000,"24":0,"25":0,"26":0,"27":0,"28":0,"29":0,"30":0,"31":0,"32":0,"33":0,"34":0,"35":0,"36":0,"37":0,"38":0,"39":0,"40":0,"41":0,"42":0,"43":0,"44":0,"45":0,"46":0,"47":0,"48":0,"49":0,"50":0,"51":0,"52":0,"53":0,"54":0,"55":0,"56":0,"57":0,"58":0,"59":0,"60":0,"61":0,"62":0,"63":0,"64":0,"65":0,"66":0,"67":0,"68":0,"69":0,"70":0,"71":0,"72":0,"73":0,"74":0,"75":0,"76":194350000,"77":0,"78":0,"79":0,"80":0,"81":0,"82":0,"83":0,"84":0,"85":0,"86":0,"87":0,"88":0,"89":0,"90":0,"91":0,"92":0,"93":0,"94":0,"95":0,"96":0,"97":0,"98":0,"99":0,"100":0,"101":0,"102":0,"103":0,"104":0,"105":0,"106":51370000,"107":0,"108":0,"109":0,"110":0,"111":0,"112":0,"113":0,"114":0,"115":0,"116":0,"117":0,"118":0,"119":0,"120":180797500,"121":0,"122":0,"123":0,"124":33331250,"125":0,"126":0,"127":0,"128":0,"129":0,"130":0,"131":0,"132":0,"133":0,"134":0,"135":0,"136":0,"137":0,"138":0,"139":0,"140":0,"141":0,"142":0,"143":0,"144":0,"145":0,"146":0,"147":0,"148":0,"149":0,"150":0,"151":0,"152":0,"153":23210000,"154":0,"155":0,"156":0,"157":0,"158":0,"159":0,"160":0,"161":0,"162":0,"163":0,"164":0,"165":0,"166":0,"167":0,"168":0,"169":0,"170":0,"171":32965000,"172":76040000,"173":3890000,"174":0,"175":0,"176":0,"177":0,"178":0,"179":0,"180":0,"181":0,"182":0,"183":65360000,"184":0,"185":0,"186":0,"187":0,"188":0,"189":0,"190":0,"191":0,"192":0,"193":0,"194":0,"195":0,"196":0,"197":0,"198":0,"199":0,"200":0,"201":0,"202":0,"203":0,"204":0,"205":0,"206":0,"207":0,"208":0,"209":0,"210":0,"211":0,"212":0,"213":0,"214":0,"215":365435000,"216":0,"217":0,"218":0,"219":0,"220":0,"221":0,"222":0,"223":0,"224":0,"225":0,"226":0}}') as json
,json:Day
,json:"User Activity"
;

因此，要展开列数据，您需要循环一列，让我们使用Day，然后从展平

通过key访问User Activity

WITH data AS (
    select 
    parse_json('{"Day":{"0":"2021-03-28","1":"2021-03-28","2":"2021-03-28","3":"2021-03-28","4":"2021-03-28","5":"2021-03-28","6":"2021-03-28","7":"2021-03-28","8":"2021-03-28","9":"2021-03-28","10":"2021-03-28","11":"2021-03-28","12":"2021-03-28","13":"2021-03-28","14":"2021-03-28","15":"2021-03-28","16":"2021-03-28","17":"2021-03-28","18":"2021-03-28","19":"2021-03-28","20":"2021-03-28","21":"2021-03-28","22":"2021-03-28","23":"2021-03-28","24":"2021-03-28","25":"2021-03-28","26":"2021-03-28","27":"2021-03-28","28":"2021-03-28","29":"2021-03-28","30":"2021-03-28","31":"2021-03-28","32":"2021-03-28","33":"2021-03-28","34":"2021-03-28","35":"2021-03-28","36":"2021-03-28","37":"2021-03-28","38":"2021-03-28","39":"2021-03-28","40":"2021-03-28","41":"2021-03-28","42":"2021-03-28","43":"2021-03-28","44":"2021-03-28","45":"2021-03-28","46":"2021-03-28","47":"2021-03-28","48":"2021-03-28","49":"2021-03-28","50":"2021-03-28","51":"2021-03-28","52":"2021-03-28","53":"2021-03-28","54":"2021-03-28","55":"2021-03-28","56":"2021-03-28","57":"2021-03-28","58":"2021-03-28","59":"2021-03-28","60":"2021-03-28","61":"2021-03-28","62":"2021-03-28","63":"2021-03-28","64":"2021-03-28","65":"2021-03-28","66":"2021-03-28","67":"2021-03-28","68":"2021-03-28","69":"2021-03-28","70":"2021-03-28","71":"2021-03-28","72":"2021-03-28","73":"2021-03-28","74":"2021-03-28","75":"2021-03-28","76":"2021-03-28","77":"2021-03-28","78":"2021-03-28","79":"2021-03-28","80":"2021-03-28","81":"2021-03-28","82":"2021-03-28","83":"2021-03-28","84":"2021-03-28","85":"2021-03-28","86":"2021-03-28","87":"2021-03-28","88":"2021-03-28","89":"2021-03-28","90":"2021-03-28","91":"2021-03-28","92":"2021-03-28","93":"2021-03-28","94":"2021-03-28","95":"2021-03-28","96":"2021-03-28","97":"2021-03-28","98":"2021-03-28","99":"2021-03-28","100":"2021-03-28","101":"2021-03-28","102":"2021-03-28","103":"2021-03-28","104":"2021-03-28","105":"2021-03-28","106":"2021-03-28","107":"2021-03-28","108":"2021-03-28","109":"2021-03-28","110":"2021-03-28","111":"2021-03-28","112":"2021-03-28","113":"2021-03-28","114":"2021-03-28","115":"2021-03-28","116":"2021-03-28","117":"2021-03-28","118":"2021-03-28","119":"2021-03-28","120":"2021-03-28","121":"2021-03-28","122":"2021-03-28","123":"2021-03-28","124":"2021-03-28","125":"2021-03-28","126":"2021-03-28","127":"2021-03-28","128":"2021-03-28","129":"2021-03-28","130":"2021-03-28","131":"2021-03-28","132":"2021-03-28","133":"2021-03-28","134":"2021-03-28","135":"2021-03-28","136":"2021-03-28","137":"2021-03-28","138":"2021-03-28","139":"2021-03-28","140":"2021-03-28","141":"2021-03-28","142":"2021-03-28","143":"2021-03-28","144":"2021-03-28","145":"2021-03-28","146":"2021-03-28","147":"2021-03-28","148":"2021-03-28","149":"2021-03-28","150":"2021-03-28","151":"2021-03-28","152":"2021-03-28","153":"2021-03-28","154":"2021-03-28","155":"2021-03-28","156":"2021-03-28","157":"2021-03-28","158":"2021-03-28","159":"2021-03-28","160":"2021-03-28","161":"2021-03-28","162":"2021-03-28","163":"2021-03-28","164":"2021-03-28","165":"2021-03-28","166":"2021-03-28","167":"2021-03-28","168":"2021-03-28","169":"2021-03-28","170":"2021-03-28","171":"2021-03-28","172":"2021-03-28","173":"2021-03-28","174":"2021-03-28","175":"2021-03-28","176":"2021-03-28","177":"2021-03-28","178":"2021-03-28","179":"2021-03-28","180":"2021-03-28","181":"2021-03-28","182":"2021-03-28","183":"2021-03-28","184":"2021-03-28","185":"2021-03-28","186":"2021-03-28","187":"2021-03-28","188":"2021-03-28","189":"2021-03-28","190":"2021-03-28","191":"2021-03-28","192":"2021-03-28","193":"2021-03-28","194":"2021-03-28","195":"2021-03-28","196":"2021-03-28","197":"2021-03-28","198":"2021-03-28","199":"2021-03-28","200":"2021-03-28","201":"2021-03-28","202":"2021-03-28","203":"2021-03-28","204":"2021-03-28","205":"2021-03-28","206":"2021-03-28","207":"2021-03-28","208":"2021-03-28","209":"2021-03-28","210":"2021-03-28","211":"2021-03-28","212":"2021-03-28","213":"2021-03-28","214":"2021-03-28","215":"2021-03-28","216":"2021-03-28","217":"2021-03-28","218":"2021-03-28","219":"2021-03-28","220":"2021-03-28","221":"2021-03-28","222":"2021-03-28","223":"2021-03-28","224":"2021-03-28","225":"2021-03-28","226":"2021-03-28"},
    "User Activity":{"0":0,"1":0,"2":0,"3":0,"4":0,"5":0,"6":0,"7":0,"8":85110000,"9":0,"10":0,"11":0,"12":0,"13":0,"14":0,"15":0,"16":0,"17":0,"18":0,"19":0,"20":0,"21":0,"22":0,"23":27780000,"24":0,"25":0,"26":0,"27":0,"28":0,"29":0,"30":0,"31":0,"32":0,"33":0,"34":0,"35":0,"36":0,"37":0,"38":0,"39":0,"40":0,"41":0,"42":0,"43":0,"44":0,"45":0,"46":0,"47":0,"48":0,"49":0,"50":0,"51":0,"52":0,"53":0,"54":0,"55":0,"56":0,"57":0,"58":0,"59":0,"60":0,"61":0,"62":0,"63":0,"64":0,"65":0,"66":0,"67":0,"68":0,"69":0,"70":0,"71":0,"72":0,"73":0,"74":0,"75":0,"76":194350000,"77":0,"78":0,"79":0,"80":0,"81":0,"82":0,"83":0,"84":0,"85":0,"86":0,"87":0,"88":0,"89":0,"90":0,"91":0,"92":0,"93":0,"94":0,"95":0,"96":0,"97":0,"98":0,"99":0,"100":0,"101":0,"102":0,"103":0,"104":0,"105":0,"106":51370000,"107":0,"108":0,"109":0,"110":0,"111":0,"112":0,"113":0,"114":0,"115":0,"116":0,"117":0,"118":0,"119":0,"120":180797500,"121":0,"122":0,"123":0,"124":33331250,"125":0,"126":0,"127":0,"128":0,"129":0,"130":0,"131":0,"132":0,"133":0,"134":0,"135":0,"136":0,"137":0,"138":0,"139":0,"140":0,"141":0,"142":0,"143":0,"144":0,"145":0,"146":0,"147":0,"148":0,"149":0,"150":0,"151":0,"152":0,"153":23210000,"154":0,"155":0,"156":0,"157":0,"158":0,"159":0,"160":0,"161":0,"162":0,"163":0,"164":0,"165":0,"166":0,"167":0,"168":0,"169":0,"170":0,"171":32965000,"172":76040000,"173":3890000,"174":0,"175":0,"176":0,"177":0,"178":0,"179":0,"180":0,"181":0,"182":0,"183":65360000,"184":0,"185":0,"186":0,"187":0,"188":0,"189":0,"190":0,"191":0,"192":0,"193":0,"194":0,"195":0,"196":0,"197":0,"198":0,"199":0,"200":0,"201":0,"202":0,"203":0,"204":0,"205":0,"206":0,"207":0,"208":0,"209":0,"210":0,"211":0,"212":0,"213":0,"214":0,"215":365435000,"216":0,"217":0,"218":0,"219":0,"220":0,"221":0,"222":0,"223":0,"224":0,"225":0,"226":0}}') as json
)
select d.value as day
    ,json:"User Activity"[d.key] as "User Activity"
from data, table (flatten(input=>json:Day)) d;    

提供前~30行：

DAY User Activity
"2021-03-28"    0
"2021-03-28"    0
"2021-03-28"    0
"2021-03-28"    0
"2021-03-28"    0
"2021-03-28"    0
"2021-03-28"    0
"2021-03-28"    0
"2021-03-28"    0
"2021-03-28"    51370000
"2021-03-28"    0
"2021-03-28"    0
"2021-03-28"    0
"2021-03-28"    0
"2021-03-28"    0
"2021-03-28"    0
"2021-03-28"    0
"2021-03-28"    0
"2021-03-28"    0
"2021-03-28"    0
"2021-03-28"    0
"2021-03-28"    0
"2021-03-28"    0
"2021-03-28"    0
"2021-03-28"    0
"2021-03-28"    180797500
"2021-03-28"    0
"2021-03-28"    0
"2021-03-28"    0
"2021-03-28"    33331250
"2021-03-28"    0