Converting SQL table to nested JSON in SQL

Question

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": []
  }

Lukasz Szozda · Accepted Answer · 2021-05-10 19:02:48Z

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": []
  }
}

answered May 10, 2021 at 19:02

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

sam · Accepted Answer · 2021-05-10 11:08:00Z

0

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

Refer to Convert Pandas Dataframe to nested JSON

answered May 10, 2021 at 11:08

sam

1,9521 gold badge18 silver badges34 bronze badges

2 Comments

maxisme Over a year ago

I would like to keep it as SQL

sam Over a year ago

@maxisme - ok ! Please update the question title and also add details about DB & your requirement as well

Collectives™ on Stack Overflow

Converting SQL table to nested JSON in SQL

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related