Snowflake : array_agg inside an array_agg

Question

I would like to perform this transformation : below is the input and the output is the json with the "d": [ "c_value_1", "c_value_2"]. As you can see, I am working with an array of nested objects, and I would like to flatten c. I know this involves two array_aggs, but I always end up with this error : SQL compilation error: Unsupported subquery type cannot be evaluated

with table_1 as (
    select parse_json(
      '[
          {
              "a": "a_value_1",
              "b": [
                  {
                      "c": "c_value_1", 
                  },
                  {
                      "c": "c_value_2", 
                  }
              ]
          },
          {
              "a": "a_value_2",
              "b": [
                  {
                      "c": "c_value_2", 
                  },
                  {
                      "c": "c_value3", 
                  }
              ]
          }
      ]'
    ) as json_object
)
select parse_json(
    '[
      {
          "a": "a_value_1",
          "d": [ "c_value_1", "c_value_2"]
      },
      {
          "a": "a_value_2",
          "d": [ "c_value_2", "c_value_3"]
      }
  ]'
);

The goal is to have a table with the second array of objects as a column

Answer 1

• Get Path (explains how the ':' work)
• Object Agg()
• Object Keys (never leave home without em)

with table_1 as (
select
    parse_json(
        '[
      {
          "a": "a_value_1",
          "b": [
              {
                  "c": "c_value_1", 
              },
              {
                  "c": "c_value_2", 
              }
          ]
      },
      {
          "a": "a_value_2",
          "b": [
              {
                  "c": "c_value_2", 
              },
              {
                  "c": "c_value3", 
              }
          ]
      }
  ]'
    ) as json_object
)
SELECT
DISTINCT OBJECT_AGG(
    h.value::string, iff(
        h.value = 'a',
        object_construct(h.value::string,g.value:a) ,object_construct(h.value::string,array_construct(g.value:b[0].c,g.value:b[1].c
    ) ) ) ) over (partition by h.seq) bob
FROM
table_1,
lateral flatten(input => json_object) g,
lateral flatten(input => object_keys(g.value)) h;

Summary.

1. Rip it down to the atomic(lowest) layer using the flattens ect
2. Build it up using the Semi Structured functions
3. Possible improvements - removing the IFF in the middle - just ran out of time... sorry.