SNOWFLAKE : read a VARIANT column as a table?-CodePudding

We have the following table

WITH fake_data(columnA, columnB, columnC) as (
select * from values
(1, 'hello1', 'world18'),
(1, 'hello2', 'world27'),
(2, 'hello9', 'world36')
(3,  NULL, 'world35')
(10, 'hello13', 'world5')

)

We convert the entire table into a single column that has a JSON-like structure

CREATE OR REPLACE TEMPORARY TABLE LISTE_JSON (V variant)
AS
WITH COLONNE_KEY
AS (
     SELECT
          ROW_NUMBER () OVER (ORDER BY  columnA DESC) KEY_AUTO 
           ,A.*
     FROM fake_data A
),
COLONNE_OBJECT
AS (
   SELECT  
   object_agg(
              TO_CHAR(KEY_AUTO )  ,
                             object_construct(
                                            'columnA', IFNULL(columnA,''),
                                            'columnB', IFNULL(columnB,''),
                                            'columnC', IFNULL(columnC,''),
                                            )
            )AS  COLONNE_OBJECT                                

   FROM COLONNE_KEY                                                                            
)
SELECT *
FROM  COLONNE_OBJECT;

So far everything is going well.

Now how do I read the variant column through a SELECT and see it as a table, as it was at the beginning?

Ex:

SELECT *
FROM LISTE_JSON 

COLUMNA COLUMNB COLUMNC 
  1   hello1   world18
  1   hello2   world27
  2   hello9   world36
  3   ''       world35
  10  hello13  world5

CodePudding user response：

You can ether use PIVOT to pull with parts, or you can hand roll the pivot via GROUP BY

SELECT 
  columna
  ,max(iff(columnb='hello1', columnc, null)) as hello1
  ,max(iff(columnb='hello2', columnc, null)) as hello2
  ,max(iff(columnb='hello3', columnc, null)) as hello3
from table
group by 1 order by 1;

So lets start with working "example code"

WITH fake_data(columnA, columnB, columnC) as (
    select * from values
    (1, 'hello1', 'world18'),
    (1, 'hello2', 'world27'),
    (2, 'hello9', 'world36'),
    (3,  NULL, 'world35'),
    (10, 'hello13', 'world5')
), COLONNE_KEY AS (
     SELECT
          ROW_NUMBER () OVER (ORDER BY  columnA DESC) KEY_AUTO 
           ,A.*
     FROM fake_data A
), COLONNE_OBJECT AS (
   SELECT  
       object_agg( KEY_AUTO::text ,
                   object_construct('columnA', IFNULL(columnA::text,''), 
                                   'columnB', IFNULL(columnB::text,''), 
                                   'columnC', IFNULL(columnC::text,'')
                                   )
            )AS  COLONNE_OBJECT                                
   FROM COLONNE_KEY                                                                            
)
SELECT *
FROM  COLONNE_OBJECT;

gives:

COLONNE_OBJECT
{ "1": { "columnA": "10", "columnB": "hello13", "columnC": "world5" }, "2": { "columnA": "3", "columnB": "", "columnC": "world35" }, "3": { "columnA": "2", "columnB": "hello9", "columnC": "world36" }, "4": { "columnA": "1", "columnB": "hello1", "columnC": "world18" }, "5": { "columnA": "1", "columnB": "hello2", "columnC": "world27" } }

which you would like to get back into it original table form

thus

SELECT
    f.value:"columnA"::number as columna,
    f.value:"columnB"::text as columnb,
    f.value:"columnC"::text as columnc
FROM  COLONNE_OBJECT, table(flatten(input=>colonne_object)) f;

gives you back

COLUMNA	COLUMNB	COLUMNC
10	hello13	world5
3	<empty string>	world35
2	hello9	world36
1	hello1	world18
1	hello2	world27

and the empty string can be swapped back in via

nullif(f.value:"columnB"::text,'') as columnb,