Parse Json - CTE & filtering-CodePudding

I need to remove a few records (that contain t) in order to parse/flatten the data column. The query in the CTE that creates 'tab', works independent but when inside the CTE i get the same error while trying to parse json, if I were not have tried to filter out the culprit.

with tab as (
 select * from table
 where  data like '%t%')
 select b.value::string, a.* from tab a, 
 lateral flatten( input => PARSE_JSON( a.data) ) b ;
 ;

error:

 Error parsing JSON: unknown keyword "test123", pos 8

example data:

Date           Data
1-12-12         {id: 13-43}
1-12-14         {id: 43-43}
1-11-14         {test12}
1-11-14         {test2}
1-02-14         {id: 44-43}

CodePudding user response：

It is possible to replace PARSE_JSON(a.data) with TRY_PARSE_JSON(a.data) which will produce NULL instead of error for invalid input.

More at: TRY_PARSE_JSON