Skip to main content

How to Unnest JSON Data

Jira​

Suppose you have a JSON document from Jira and you want to split it into individual fields to make analysis and querying easier.

Dictionaries​

 "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
      }
]

To flatten the dictionary and array shown above, you need to reference the keys directly in the query. Using the Query module and SQL, the code would look like this:

Dictionaries​

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

This SQL flattens the information stored in the customfield_10415 and attachment JSON fields into individual columns, making the data easier to manipulate and analyze in Snowflake. Adjust the query as needed for your own structure.

The same process can also be performed with the Processing module.