Convert an entire table to JSON?

I have the following table [table1]:

I would like to convert all this into a new table and a single column with JSON format, is it possible?

Desired result new table [table2] :

[ table] | NewColumnJSON|

{
  "[1]": {
    "Column B ": " hello 1 ",
    "Column C ": " world1",
  },
  "[2]": {
    "Column B ": " hello 2 ",
    "Column C ": " world2",
  },
  "[3]": {
    "Column B ": " hello 3 ",
    "Column C ": " world3",
  },
}

Thank you for your help.

CodePudding user response：

With this great CTE to act as our Table:

WITH fake_data(columnA, columnB, columnC) as (
    select * from values
    (1, 'hello1', 'world1'),
    (2, 'hello2', 'world2'),
    (3, 'hello3', 'world3')
)

we can use this SQL:

SELECT columnA, object_construct('column b', columnb, 'column c', columnc) as obj
FROM fake_data;

then we can use OBJECT_CONSTRUCT to us the sub-objects:

which we can wrap in OBJECT_AGG

SELECT object_agg(columnA, object_construct('column b', columnb, 'column c', columnc)) as obj
FROM fake_data;