JSON to Relational Table is it possible to insert with the condition into the existing columns of my 2 column table

Question

I am trying to see if I can create a relational table from a staged table created from a semi-structured file that has one variant datatype column.

The intent is to create a relational table with individual columns from each of the elements under an array of the JSON data. For example, if my data is something like this -

{service:[{type:1,value:300},

{type:2,value:400}]}

I would like to create a table with 2 columns as show below with the following values -

TYPE_1_VAL = 300

TYPE_2_VAL = 400

Essentially, my insert query should be based on a condition while inserting data into these two columns. In this case,

 INSERT 100 INTO TYPE_1_VAL if type=1

and so on.

I know we can use lateral flatten to create 2 rows/columns but I am looking to see if the above mentioned approach is possible or not -

TYPE VALUE

1 300

2 400

Nat Taylor Nat Taylor · Accepted Answer · 2019-12-13T20:28:48

I think it is best to implement a Javascript UDF that takes the array and the type as a parameter. Something like this that is less lazy about edge cases

CREATE OR REPLACE FUNCTION gp(a array, type variant)
  RETURNS variant
  LANGUAGE JAVASCRIPT
AS
$$
  return A.filter(item => item.type == TYPE).map(item => item.value)[0];
$$
;

with tbl as (select parse_json($1) json from values ('{service:[{type:1,value:100},{type:2,value:200}]}'))
select gp(json:service,1) type_1, gp(json:service,2) type_2 from tbl;

TYPE_1  TYPE_2
100 200

JSON to Relational Table is it possible to insert with the condition into the existing columns of my 2 column table

2 Answers