EXTRA��O DE DADOS DO JSON USANDO SQL - MYSQL E BIGQUERY

06/07/2021

Ol� pessoal boa tarde, estou com um problema a resolver preciso fazer a extra��o de alguns dados que est�o em formato JSON usando o BIGQUERY, tenho uma query pronta que � usada para essa extra��o no MySQL por�m n�o acha fun��es equivalentes para rodar no BQ, ser� que algu�m conseguiria me ajudar, vou colocar a query usada no MySQL.

select
   p.document as 'Documento (CPF/CNPJ)',
   r.created,
   replace(JSON_EXTRACT(s.doc, '$.CARTAS'), '"', '') AS Cartas,
   JSON_UNQUOTE(JSON_EXTRACT(r.informations, replace(JSON_UNQUOTE(JSON_SEARCH(r.informations, 'one', '1', null, '$**.Priority')), 'Priority', 'Catalog'))) as Lampada_1,
   JSON_UNQUOTE(JSON_EXTRACT(r.informations, replace(JSON_UNQUOTE(JSON_SEARCH(r.informations, 'one', '2', null, '$**.Priority')), 'Priority', 'Catalog'))) as Lampada_2,
   JSON_UNQUOTE(JSON_EXTRACT(r.informations, replace(JSON_UNQUOTE(JSON_SEARCH(r.informations, 'one', '3', null, '$**.Priority')), 'Priority', 'Catalog'))) as Lampada_3,
   JSON_UNQUOTE(JSON_EXTRACT(r.informations, replace(JSON_UNQUOTE(JSON_SEARCH(r.informations, 'one', '1', null, '$**.Priority')), 'Priority', 'Name'))) as Premio_1,
   JSON_UNQUOTE(JSON_EXTRACT(r.informations, replace(JSON_UNQUOTE(JSON_SEARCH(r.informations, 'one', '2', null, '$**.Priority')), 'Priority', 'Name'))) as Premio_2,
   JSON_UNQUOTE(JSON_EXTRACT(r.informations, replace(JSON_UNQUOTE(JSON_SEARCH(r.informations, 'one', '3', null, '$**.Priority')), 'Priority', 'Name'))) as Premio_3,
   JSON_UNQUOTE(JSON_EXTRACT(r.informations, replace(JSON_UNQUOTE(JSON_SEARCH(r.informations, 'one', '1', null, '$**.Priority')), 'Priority', 'Sku'))) as Sku_1,
   JSON_UNQUOTE(JSON_EXTRACT(r.informations, replace(JSON_UNQUOTE(JSON_SEARCH(r.informations, 'one', '2', null, '$**.Priority')), 'Priority', 'Sku'))) as Sku_2,
   JSON_UNQUOTE(JSON_EXTRACT(r.informations, replace(JSON_UNQUOTE(JSON_SEARCH(r.informations, 'one', '3', null, '$**.Priority')), 'Priority', 'Sku'))) as Sku_3,
   replace(JSON_EXTRACT(s.doc, '$.LAMPADA'), '"', '') AS Lampada_disponivel,
   upper(st.description) as Fase 
from
    MULTI_CAMPAIGN_VIEW.Participant p 
    inner join
       MULTI_CAMPAIGN_VIEW.Redemption r
       on r.participant_id = p.participant_id 
    inner join
       MULTI_CAMPAIGN_VIEW.Participant_Stage_Status_Json s 
       on s.campaign_stage_id = r.campaign_stage_id 
       and p.participant_id = s.participant_id 
    inner join
       MULTI_CAMPAIGN_VIEW.Campaign_Stage st 
       on st.campaign_stage_id = s.campaign_stage_id 
 where
    p.campaign_id = '55854265-c7b7-4f98-a130-ef6019303180'

se algu�m conseguisse me ajudar, pois o BQ n�o reconhece as fun��es JSON_UNQUOTE E JSON_SEARCH

Bruna

Curtir t�pico + 1

Responder