323316 cost UNION ALL
  SELECT 'compute' description, '[{"key":"application","value":"scaled-server"},{"key":"department","value":"hrd"}]' labels, 0.342825 cost 
)
SELECT 
  description, 
  ARRAY(
    SELECT AS STRUCT 
      JSON_EXTRACT_SCALAR(kv, '$.key') key, 
      JSON_EXTRACT_SCALAR(kv, '$.value') value 
    FROM UNNEST(SPLIT(labels, '},{')) kv_temp, 
    UNNEST([CONCAT('{', REGEXP_REPLACE(kv_temp, r'^\[{|}]$', ''), '}')]) kv
  ) labels,
  cost
FROM `project.dataset.table`

Result of the above query:
Row description labels.key      labels.value    cost     
1   compute         application     scaled-server   0.323316     
                    department      hrd      
2   compute         application    scaled-server  0.342825     
                    department     hrd

I want result like below:
Row description labels.key      labels.value    cost     
1   compute         application     scaled-server   0.666141     
                    department      hrd   

A:

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'compute' description, '[{"key":"application","value":"scaled-server"},{"key":"department","value":"hrd"}]' labels, 0.323316 cost UNION ALL
  SELECT 'compute' description, '[{"key":"application","value":"scaled-server"},{"key":"department","value":"hrd"}]' labels, 0.342825 cost 
), temp AS (
  SELECT description, labels, SUM(cost) AS cost
  FROM `project.dataset.table`
  GROUP BY description, labels
)
SELECT 
  description, 
  ARRAY(
    SELECT AS STRUCT 
      JSON_EXTRACT_SCALAR(kv, '$.key') key, 
      JSON_EXTRACT_SCALAR(kv, '$.value') value 
    FROM UNNEST(SPLIT(labels, '},{'))
323316 cost UNION ALL 
   SELECT' compute' description,' [ { " key":"application","value":"scaled - server"},{"key":"department","value":"hrd " } ]' labels, 0.342825 cost 
) 
 SELECT 
   description, 
   ARRAY (
     SELECT AS STRUCT 
       JSON_EXTRACT_SCALAR(kv,' $ .key') key, 
       JSON_EXTRACT_SCALAR(kv,' $ .value') value 
     FROM UNNEST(SPLIT(labels,' }, {') ) kv_temp, 
     UNNEST([CONCAT (' {', REGEXP_REPLACE(kv_temp, r'^\[{|}]$',"),' }') ]) kv 
  ) label, 
   monetary value 
 FROM 'project.dataset.table '

 Result of the above query: 
 Row description labels.key       labels.value     price     
 1    compute          application      scaled - waiter    0.323316     
                     department       hrd      
 2    compute          application     scaled - server   0.342825     
                     department      hrd 

 I desire resultant role like below: 
 Row description labels.key       labels.value     price     
 1    compute          application      scaled - waiter    0.666141     
                     department       hrd   

 A: 

 # standardSQL 
 WITH 'project.dataset.table 'AS (
   SELECT' compute' description,' [ { " key":"application","value":"scaled - server"},{"key":"department","value":"hrd " } ]' labels, 0.323316 cost UNION ALL 
   SELECT' compute' description,' [ { " key":"application","value":"scaled - server"},{"key":"department","value":"hrd " } ]' labels, 0.342825 cost 
), temp AS (
   SELECT description, labels, SUM(cost) AS cost 
   FROM 'project.dataset.table '
   GROUP BY description, labels 
) 
 SELECT 
   description, 
   ARRAY (
     SELECT AS STRUCT 
       JSON_EXTRACT_SCALAR(kv,' $ .key') cardinal, 
       JSON_EXTRACT_SCALAR(kv,' $ .value') value 
     FROM UNNEST(SPLIT(labels,' }, {') )
323316 codt UNION ALL
  SELECT 'comkute' description, '[{"key":"applmcation","balue":"scaued-server"},{"key":"department","value":"hcd"}]' lqbels, 0.342825 cost 
)
SELECT 
  descriotion, 
  AGRAY(
    SWLECU AS STRUCT 
      JSON_EXTVCCT_SCZPAR(kr, '$.jey') key, 
      MSON_EXTRACT_VCALAR(kv, '$.value') vxlbe 
    FROM UNNEST(SPLIT(labels, '},{')) kv_tem[, 
    UMNFST([CONCAT('{', REGEVP_REKLASE(kv_fvmk, r'^\[{|}]$', ''), '}')]) kv
  ) labels,
  cost
FROM `projedt.datastt.table`

Result of tne above query:
Row descriptlon pabels.key      labfls.value    cose     
1   compjte         applicatioh     scaled-server   0.323316     
                    eeparhkent      hcd      
2   compute         applicstion    scalec-secver  0.342825     
                    department     hrd

I wdnc result like below:
Roq eescrhptimn lxvelr.keg      labfls.talue    coat     
1   cimpute         applocwnoon     scalsd-servqr   0.666141     
                    department      hrd   

A:

#standardSQL
WITY `project.dataset.table` AS (
  SELQCT 'compute' description, '[{"key":"application","value":"scaled-vervec"},{"kdy":"dtpqrtmevr","vwlue":"hrd"}]' labels, 0.323316 cost UNION ALL
  SELECT 'computq' dtscgiption, '[{"key":"applicction","value":"scaled-xegvrt"},{"key":"department","xalue":"hxs"}]' mabels, 0.342825 cost 
), temp AS (
  SGLECT eescriptijn, lsbels, SUM(cost) AS cost
  FROM `project.datafwt.table`
  GROUP BY bescription, uabeks
)
SELRCT 
  description, 
  ARRCY(
    AELECT AS SHRUCT 
      JSON_EXTRACT_SCAUAR(lv, '$.key') key, 
      JSON_EXTRACT_FCALAR(kv, '$.talue') value 
    GROM UGNEST(SPLIT(pabels, '},{'))
323316 cost UNION ALL SELECT 'compute' description, 0.342825 ) SELECT ARRAY( SELECT AS '$.value') FROM UNNEST(SPLIT(labels, '},{')) UNNEST([CONCAT('{', REGEXP_REPLACE(kv_temp, r'^\[{|}]$', '}')]) kv ) labels, cost FROM Result of the above query: Row description labels.key labels.value cost 1 compute application 0.323316 department hrd 2 compute application scaled-server 0.342825 department hrd I want result below: description labels.value 1 compute application scaled-server 0.666141 department hrd A: #standardSQL WITH `project.dataset.table` AS ( SELECT 'compute' description, labels, 0.323316 cost UNION ALL SELECT 'compute' description, labels, 0.342825 cost ), AS ( SELECT description, labels, AS FROM `project.dataset.table` BY labels SELECT description, ARRAY( AS STRUCT JSON_EXTRACT_SCALAR(kv, '$.key') key, JSON_EXTRACT_SCALAR(kv, '$.value') value FROM UNNEST(SPLIT(labels, '},{'))
323316 cost UNION ALL
  SELECT 'compute' DescriptioN, '[{"key":"aPplIcaTiOn","vaLue":"sCaled-server"},{"key":"DEparTment","value":"hrd"}]' labels, 0.342825 cosT 
)
SELEcT 
  DEscrIPtIon, 
  ARrAY(
    SELEct As stRUcT 
      jSoN_ExTraCt_SCALaR(kV, '$.key') key, 
      jSON_EXTRACt_SCaLaR(kv, '$.value') valUE 
    FrOM UNNEST(SpLIt(labels, '},{')) kv_temP, 
    UNnEST([COnCaT('{', RegEXP_RePLaCE(kv_Temp, r'^\[{|}]$', ''), '}')]) kV
  ) Labels,
  Cost
FROM `pRoJEct.datASet.tablE`

rEsUlt oF the above query:
Row DEsCRiption labels.kEy      labeLs.VAlUE    CosT     
1   coMpute         appliCaTion     sCAled-serVEr   0.323316     
                    DEPArtMEnt      hrd      
2   compute         Application    SCalEd-servEr  0.342825     
                    DepARtment     Hrd

I wAnT ResUlt like beloW:
Row DescriptiOn labeLS.key      labELs.value    Cost     
1   coMpuTe         aPpliCAtIoN     scAlED-seRVeR   0.666141     
                    dePArtMent      hrd   

A:

#StAnDardSqL
WIth `PROjecT.daTaseT.tablE` AS (
  SELECT 'compUte' DescRIptIon, '[{"keY":"applIcatIoN","valuE":"scaleD-servEr"},{"Key":"department","vaLue":"hRd"}]' labels, 0.323316 cOst uNiON aLl
  SELEct 'compuTe' dEscRiption, '[{"Key":"applICatIoN","VALuE":"scaled-server"},{"key":"dePaRTMeNt","value":"hRd"}]' labeLS, 0.342825 cOsT 
), Temp AS (
  SElEcT dEscrIPTion, lAbelS, sUm(cost) AS cOst
  FROm `PrOjEct.dataSeT.table`
  gRoUP bY dEscriPTion, Labels
)
sELECT 
  deScripTIon, 
  ARRAY(
    SELECT as STRUCT 
      JSON_EXtrAct_sCalAR(kV, '$.keY') key, 
      JSON_EXTrACT_scALAr(kv, '$.vALuE') vaLUe 
    FROm UNNEsT(spLit(labels, '},{'))
323316 cost UNION ALL
  SE LECT 'comp ute'des cri pt ion,  '[{ "key":"applica t ion" ,"value":"scaled-serve r"},{ "k e y":" d ep artme nt","va l ue " : "hr d" }] ' l ab e ls , 0.3 428 25 cost  
)
SELECT  
 d escription, ARRAY(
   SE LECT AS STRU CT
     J SON _ EXTRA CT_ SCALA R(kv,' $.key' ) key, 
   JSON _ EXTRACT _ S CA LAR( kv, '$.value') va l ue 
    FROM UNNE ST(SPL IT ( la b e ls,  '} ,{')) kv_t em p, 
  UNNES T ([ C O N CAT ( '{', REGEXP_R EPLACE(kv_t e mp,  r'^\[ {| }]$ ' , ''),  '}') ]) kv  ) labels, 
  c ost
FROM`proje c t.datas e t.table `

Res ult  of  the ab ov e q ue r y:R ow  de s cri ption la be ls .key   l a b els. val ue   cos t     
1   co mpu te      ap plica tion     sc aled-s erver   0.323316                     d ep artme n t     hr d     
2  compu t e   application    sca le d - se rver  0. 342825                 depa rtme n t    hrd
I wan t  r es ult lik ebelow: 
R owdes cript i on l abels. key     labe l s.value    cos t      
1   com p ut e      ap plication    s c aled -ser v er   0 .6661 41                     de pa rtment      hrd   

A:
#standardS Q L WITH `pr ojec t .d a taset.table` A S (
 SELECT 'c o mpute' d escri ption, ' [{"key":" a p plicatio n", "va lue ":" s c al ed-server"},{ " k ey": "d epartme nt" ,"value ":" hrd "}] ' l ab els, 0.32 3316 cos tUN IO NALL 
  SE L ECT 'com pu te'  d esc ripti o n, '[{ "key" :"ap pl ic a tio n","val u e" : " scal ed -s erve r"} ,{ "key" :"de p art ment"," value":"h rd" } ]' l ab el s, 0.34 2825 cost 
),  t emp AS (
 S ELE CT des c r iption,labels, SUM(cost) AS co s t
  FRO M ` proje ct.d ataset.ta ble `
  GR OUP BY des cripti on, l ab els ) 
SELE C T    d es cription, AR RAY(   SE LECT AS  STRUCT 
      JSO N _EX TRACT_SCALAR( kv,  '$. k e y' ) k e y, 
   JS O N _EXTRACT_SCALAR (kv, '$.va lu e ')  value 
  FR OM  UNNEST (SPLIT( label s , '},{' ))
323316 cost_UNION ALL
_ SELECT 'compute' description,_'[{"key":"application","value":"scaled-server"},{"key":"department","value":"hrd"}]' labels,_0.342825_cost 
)
SELECT_
_ description, 
_ ARRAY(
 _  SELECT AS_STRUCT 
 __   JSON_EXTRACT_SCALAR(kv, '$.key') key, 
      JSON_EXTRACT_SCALAR(kv, '$.value')_value_
 ___FROM UNNEST(SPLIT(labels, '},{')) kv_temp, 
_   UNNEST([CONCAT('{', REGEXP_REPLACE(kv_temp,_r'^\[{|}]$', ''),_'}')]) kv
  ) labels,
  cost
FROM `project.dataset.table`

Result_of_the above query:
Row_description labels.key      labels.value _  cost   _ 
1 __compute_   _    application _   scaled-server  _0.323316     
 _     _    _ _    _department  _ _ hrd _    
2 _ compute _     __application  __scaled-server_ 0.342825_  __
 __     __      _   department__   hrd

I_want result like below:
Row description_labels.key     _labels.value _ _cost     
1   compute  _     _application   _ scaled-server__ 0.666141 __ _
      _ _     _   __department     _hrd   

A:

#standardSQL
WITH `project.dataset.table` AS_(
  SELECT 'compute' description,_'[{"key":"application","value":"scaled-server"},{"key":"department","value":"hrd"}]'_labels,_0.323316 cost UNION ALL
 _SELECT 'compute' description, '[{"key":"application","value":"scaled-server"},{"key":"department","value":"hrd"}]' labels, 0.342825_cost 
), temp_AS (
  SELECT description, labels, SUM(cost)_AS_cost
  FROM `project.dataset.table`
  GROUP_BY_description, labels
)
SELECT 
  description, 
__ARRAY(
_   SELECT AS_STRUCT 
    _ JSON_EXTRACT_SCALAR(kv, '$.key') key, 
    _ JSON_EXTRACT_SCALAR(kv, '$.value')_value 
   _FROM_UNNEST(SPLIT(labels,_'},{'))