Snowflake SQL → Column JSON values into table

Question

My TRR table is composed of JSON values in each row (I removed null rows from TRR), and I'd like to split these JSON rows into a table with corresponding columns

TRR with JSON values

Each row can have multiple entries: expanding one row example

What I need is the following (no need to keep the row number in the target table):

(Row)	discount	id	points_text	points	src_trl_code	src_trl_id	trn_id
(1)	0	1113302	TIERS_PTS: 1	0	CCU_L_01	108	743156
(2)	0	1112948	TIERS_PTS: 1	0	CCU_L_01	108	743430
(3)	0	2552076	NULL	0	CCU_L_01	108	402953
(4)	0	746255	STATUS: 50	0	CCU_L_01	659	503055
(4)	0	746254	SMILE: 50	0	CCU_L_01	108	503055
(...)

Answer 1

Working code:

WITH CTE AS ( SELECT  array_construct( object_construct( 'discount', 0, 'ID', 
1113302, 'points', 0, 'points text', 'TIERS_PTS:1', 'secirlcode', 'CCU_L_01', 
'srcirlid', 108, 'TRN_ID', 743156 ) ,
object_construct( 'discount', 4, 'ID', 1114302, 'points', 1, 'points text', 
'TIERS_PTS:5', 'secirlcode', 'CCU_L_01', 'srcirlid', 168, 'TRN_ID', 743156 ) 
) YOUR_ARRAY )

SELECT 
  HI.VALUE:ID
, HI.VALUE:TRN_ID
, HI.VALUE:points
, HI.VALUE:"points text"
, HI.VALUE:secirlcode 
FROM 
   CTE
 , LATERAL FLATTEN(INPUT=> YOUR_ARRAY) HI ;

Functions used:

• LATERAL()
• OBJECT_CONSTRUCT()
• Using : for GET_PATH()

Proof it works:

Same starting point :