How do I flatten a nested json key/value pair into a single array of values?

Question

In SNOWFLAKE, I have a data structure like:

ORGANIZATION TABLE
------------------
Org:variant
------------------
{
    relationships: [{
        { name: 'mother', value: a },
        { name: 'siblings', value: [ 'c', 'd' ] }
    }]
}

PEOPLE TABLE
-------------------
Person:variant
-------------------
{
    id: a
    name: Mary
}
-------------------
{
    id: b
    name: Joe
}
-------------------
{
    id: c
    name: John
}

I want to have a result of:

ORGANIZATION                                       | PEOPLE
---------------------------------------------------|----------------------------
{                                                  |[
    relationships: [{                              |  {
        { name: 'mother', value: a },              |    id: a,
        { name: 'siblings', value: [ 'c', 'd' ] }  |    name: Mary
    }]                                             |  },
}                                                  |  {
                                                   |    id: b,
                                                   |    name: Joe
                                                   |  },
                                                   |  {
                                                   |    id: c,
                                                   |    name: john
                                                   |  }
                                                   |]

I'm sure ARRAY_AGG is involved somehow but I'm at a loss how I would aggregate the results up into a single array of values.

My current query:

SELECT Org, ARRAY_AGG(Person) as People
FROM Organizations
INNER JOIN People ON People.id IN Org.relationships...?? (I'm lost here)
GROUP BY Org

Answer 1

The below queries illustrate how to use FLATTEN and ARRAY_AGG to get the output you want.

• FLATTEN unnests each array so that you can join on the values within.
• ARRAY_AGG aggregates the values grouped by org.
• The CASE statement accounts for org.relationships not always being an array.

CREATE OR REPLACE TABLE organizations (org variant) AS
SELECT parse_json('{relationships: [{ name: "mother", value: "a" }, { name: "siblings", value: [ "b", "c" ] } ] } ');


CREATE OR REPLACE TABLE people (person variant) AS
SELECT parse_json($1)
FROM
VALUES ('{id:"a", name: "Mary"}'),
       ('{id:"b", name: "Joe"}'), 
       ('{id:"c", name: "John"}');

WITH org_people AS
  (SELECT o.org,
          relationship.value AS relationship,
          CASE is_array(relationship:value)
              WHEN TRUE THEN person_in_relationship.value
              ELSE relationship:value
          END AS person_in_relationship
   FROM organizations o,
        LATERAL FLATTEN(o.org:relationships) relationship ,
        LATERAL FLATTEN(relationship.value:value, OUTER=>TRUE) person_in_relationship
  )
SELECT op.org,
       ARRAY_AGG(p.person) AS people
FROM org_people op
JOIN people p ON p.person:id = op.person_in_relationship
GROUP BY op.org;