Object_construct: another example

Mushrooms are seen in Big Salmon Lake Hiking Trail, Frontenac Provincial Park, ON, Oct 8, 2023

We had some discussion in a previous post for how to construct table columns back to JSON file. We used object_construct and array_agg etc. functions.

In this short post, we’re handling a JSON output re-organizing case originally reported from Snowflake community. We’ll use object_construct and array_agg to convert original output which is a array object to a JSON object that has an array as value of a given key. We also used a key name which is different from column name.

1 Code to generate original format

with date_formats as (
select '%d/%m/%Y' format1, '24/11/2023' example, 'DD/MM/YYYY' format2
union all
select '%m/%d/%Y' format1, '11/24/2023' example, 'MM/DD/YYYY' format2
union all
select '%Y/%m/%d' format1, '2023/11/24' example, 'YYYY/MM/DD' format2
)
select array_agg(object_construct(*)) from date_formats;

-- Original Output
[
  {
    "EXAMPLE": "24/11/2023",
    "FORMAT1": "%d/%m/%Y",
    "FORMAT2": "DD/MM/YYYY"
  },
  {
    "EXAMPLE": "11/24/2023",
    "FORMAT1": "%m/%d/%Y",
    "FORMAT2": "MM/DD/YYYY"
  },
  {
    "EXAMPLE": "2023/11/24",
    "FORMAT1": "%Y/%m/%d",
    "FORMAT2": "YYYY/MM/DD"
  }
]