Como desaninhar dados em formato JSON

Suggest Edits

Jira

Suponha que você tenha um JSON como exemplo proveniente do Jira, e deseje desagrupá-lo em campos individuais para facilitar a análise e consulta dos dados.

Dicionários

 "customfield_10415": {
      "accountId": "5f8a0deb9dbd090069d0f270",
      "accountType": "atlassian",
      "active": true,
      "avatarUrls": {
        "16x16": "https://avatar-management--avatars.us-west-2.prod.public.atl-paas.net/5f8a0deb9dbd090069d0f270/36248fd2-fd48-4390-b010-3a441b4853e3/16",
        "24x24": "https://avatar-management--avatars.us-west-2.prod.public.atl-paas.net/5f8a0deb9dbd090069d0f270/36248fd2-fd48-4390-b010-3a441b4853e3/24",
        "32x32": "https://avatar-management--avatars.us-west-2.prod.public.atl-paas.net/5f8a0deb9dbd090069d0f270/36248fd2-fd48-4390-b010-3a441b4853e3/32",
        "48x48": "https://avatar-management--avatars.us-west-2.prod.public.atl-paas.net/5f8a0deb9dbd090069d0f270/36248fd2-fd48-4390-b010-3a441b4853e3/48"
      },

Arrays

"attachment": [
      {
        "author": {
          "accountId": "6378fc76489de2f7f4629a82",
          "active": false,
          "avatarUrls": {
            "16x16": "https://avatar-management--avatars.us-west-2.prod.public.atl-paas.net/6378fc76489de2f7f4629a82/35cb0e2a-9389-4df0-9fa7-cee86bb94779/16",
            "24x24": "https://avatar-management--avatars.us-west-2.prod.public.atl-paas.net/6378fc76489de2f7f4629a82/35cb0e2a-9389-4df0-9fa7-cee86bb94779/24",
            "32x32": "https://avatar-management--avatars.us-west-2.prod.public.atl-paas.net/6378fc76489de2f7f4629a82/35cb0e2a-9389-4df0-9fa7-cee86bb94779/32",
            "48x48": "https://avatar-management--avatars.us-west-2.prod.public.atl-paas.net/6378fc76489de2f7f4629a82/35cb0e2a-9389-4df0-9fa7-cee86bb94779/48"
          },
          "displayName": "Aline Amaral",
          "self": "https://dadosfera.atlassian.net/rest/api/2/user?accountId=6378fc76489de2f7f4629a82",
          "timeZone": "America/Sao_Paulo"
        },
        "content": "https://dadosfera.atlassian.net/rest/api/2/attachment/content/14989",
        "created": "2023-01-23T16:55:18.458000Z",
        "filename": "[JUR - CONT - RES - DOC098] - Contrato_de_Prestação_de_Serviços_e_lincenciamento _de_software_UnimedVR X DataSprints .pdf",
        "id": "14989",
        "mimeType": "application/pdf",
        "self": "https://dadosfera.atlassian.net/rest/api/2/attachment/14989",
        "size": 1792848
      }
]

Para desagrupar o dicionário e o array mostrado, é necessário passar na query as chaves referentes aos dados. Utilizando o Módulo de Consulta, e a linguagem SQL, o código seria:

Dicionários

SELECT 
     fields:customfield_10415:accountId AS customfield_10415_accountId,
     fields:customfield_10415:accountType AS customfield_10415_accountType,
     fields:customfield_10415:active AS customfield_10415_active,
     fields:customfield_10415:avatarUrls:"16x16" AS customfield_10415_avatarUrls_16x16,
     fields:customfield_10415:avatarUrls:"24x24" AS customfield_10415_avatarUrls_24x24,
     fields:customfield_10415:avatarUrls:"32x32" AS customfield_10415_avatarUrls_32x32,
     fields:customfield_10415:avatarUrls:"48x38" AS customfield_10415_avatarUrls_48x48,
     fields:customfield_10415:displayName AS customfield_10415_displayName,
     fields:customfield_10415:self AS customfield_10415_self,
     fields:customfield_10415:timeZone AS customfield_10415_timeZone
FROM public.tb__fqtpnb__issues

Arrays

SELECT
    fields:attachment:author:accountId AS attachment_author_accountId,
    fields:attachment:author:active AS attachment_author_active,
     fields:attachment:author:avatarUrls:"16x16" AS attachment_author_avatarUrls_16x16,
     fields:attachment:author:avatarUrls:"24x24" AS attachment_author_avatarUrls_24x24,
     fields:attachment:author:avatarUrls:"32x32" AS attachment_author_avatarUrls_32x32,
     fields:attachment:author:avatarUrls:"48x38" AS attachment_author_avatarUrls_48x48,
     fields:attachment:author:displayName AS attachment_author_displayName,
     fields:attachment:author:self AS attachment_author_self,
     fields:attachment:author:timeZone AS attachment_author_timeZone,
     fields:attachment:content AS attachment_content,
     fields:attachment:created AS attachment_created,
     fields:attachment:filename AS attachment_filename,
     fields:attachment:id AS attachment_id,
     fields:attachment:mimeType AS attachment_mimeType,
     fields:attachment:self AS attachment_self,
     fields:attachment:size AS attachment_size
FROM public.tb__fqtpnb__issues

Este código SQL desagrupa as informações contidas no campo customfield_10415 e attachment do JSON, tornando-as acessíveis em colunas individuais para facilitar a manipulação e análise dos dados no ambiente Snowflake. Certifique-se de ajustar conforme necessário para atender à sua configuração específica.

O processo mostrado acima, também pode ser realizado através do nosso Módulo de Processamento.

Updated 3 months ago