1

I have rows like this in my snowflake database:

+-----+-----+-----+
| Foo | Bar | Baz |
+-----+-----+-----+
| A   | a   | []  |
| A   | b   | []  |
| B   | a   | []  |
| B   | b   | []  |
+-----+-----+-----+

I want to convert this into:

  "A": {
    "a": [],
    "b": []
  },
  "B": {
    "a": [],
    "b": []
  }
maxisme
  • 3,974
  • 9
  • 47
  • 97

2 Answers2

1

Snowflake allows to achieve the desired effect with SQL:

CREATE OR REPLACE TABLE t
AS
SELECT 'A' AS foo, 'a' AS bar, PARSE_JSON('[]') AS Baz
UNION ALL SELECT 'A' AS foo, 'b' AS bar, PARSE_JSON('[]') AS Baz
UNION ALL SELECT 'B' AS foo, 'a' AS bar, PARSE_JSON('[]') AS Baz
UNION ALL SELECT 'B' AS foo, 'b' AS bar, PARSE_JSON('[]') AS Baz;


SELECT OBJECT_AGG(foo, s) AS result
FROM (SELECT foo, OBJECT_AGG(bar, baz) AS s
      FROM t
      GROUP BY foo) sub;

Output:

{
  "A": {
    "a": [],
    "b": []
  },
  "B": {
    "a": [],
    "b": []
  }
}
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
0

You can try using pandas to read sql data and convert it to nested json

Refer to Convert Pandas Dataframe to nested JSON

sam
  • 1,819
  • 1
  • 18
  • 30