Loading an Array tag from a .JSON file into snowflake

Question

I have a json file that I am loading into snowflake. One of the keys in the file has a value that is an array. The question is how do I load this tag into a separate column of type ARRAY in snowflake? It's already an array in json. Do I still need to use an array_construct(tag_name_here) function to load it up? What happens if in subsequent records, the 'industry' tag is missing altogether? Please advise.

Below is a sample of the json...

  [
   [
    {
      "title": "Avino Silver &amp; Gold Mines Ltd. Fourth Quarter and Year End Results to be Released on....",
      "pubDate": "Tue, 25 Feb 2020 00:49:00 +0000",
      "description": " Avino Silver & Gold Mines Ltd. plans to announce its Fourth Quarter and Year End 2019 financial results after the market closes. In addition, the Company...",
      "industry": [
                    "Mining & Metals ",
                    "Mining ",
                    "MNG",
                    "MIN"
                ],
       "subject": [
                    "Conference Call Announcements ",
                    "Earnings "
                ]
     }
    ]
   ]

MMV MMV · Accepted Answer · 2020-02-26T08:23:01

Take a look at the examples here: https://docs.snowflake.net/manuals/user-guide/querying-semistructured.html

generally you're looking into extracting data rather than constructing it (just use value:industry to fill in your array column). And if the proper tag is missing in some record it will just get filled with NULL.

Loading an Array tag from a .JSON file into snowflake

1 Answers