Function to return element of JSON or Variant field-CodePudding

I need help in creating a function in which i pass on table and column(containing array) and it returns required element of array.

CREATE OR REPLACE TABLE json_table (v variant); INSERT INTO json_table SELECT parse_json( '{ "fullName":"Robert Downey", "age":55, "gender":"Male", "address": { "areaCode":"91506", "suite":"916 West Burbank Blvd" }, "movies": [ {"name":"Iron Man", "budget":"$150M","producer":"Avi Arad"}, {"name":"Sherlock Holmes","budget":"$200M","producer":"Joel Silver"}, {"name":"Dolittle", "budget":"$175M","producer":"Joe Roth"} ] }');

sp(tablename, input_column, input_level optoinal, out_column); call sp('json_table', 'v', 'fullName'); return: "Robert Downey"

sp(tablename, input_column, input_level optoinal, out_column); call sp('json_table', 'v', 'name', 'movies'); return: "Iron Man" "Sherlock Holmes" "Dolittle"

NOT SURE WHAT SHOULD WE RETURN STRING, ARRAY OR TABLE ...

CodePudding user response：

To implement the first case, here is the example.

CREATE OR REPLACE TABLE json_table (v variant);

INSERT INTO json_table
SELECT parse_json( '{ "fullName":"Robert Downey", "age":55, "gender":"Male", "address": { "areaCode":"91506", "suite":"916 West Burbank Blvd" }, "movies": [ {"name":"Iron Man",       "budget":"$150M","producer":"Avi Arad"}, {"name":"Sherlock Holmes","budget":"$200M","producer":"Joel Silver"}, {"name":"Dolittle",       "budget":"$175M","producer":"Joe Roth"} ] }'); 

INSERT INTO json_table
SELECT parse_json( '{ "fullName":"Goun Na", "age":55, "gender":"Male", "address": { "areaCode":"91506", "suite":"916 West Burbank Blvd" }, "movies": [ {"name":"Iron Man",       "budget":"$150M","producer":"Avi Arad"}, {"name":"Sherlock Holmes","budget":"$200M","producer":"Joel Silver"}, {"name":"Dolittle",       "budget":"$175M","producer":"Joe Roth"} ] }'); 


select * from  json_table;
select v:fullName::String from json_table;

create or replace procedure sp(tablename varchar, input_column varchar, out_column varchar)
returns table (res varchar)
language sql
as
$$
declare
    qry string;
    res resultset;
begin
    qry := 'select v:'||:out_column||'::String'||' from '||:tablename;
    res := (execute immediate qry);
    return table(res);
end;
$$
;

call sp('json_table', 'v', 'fullName');

CodePudding user response：

Another example for the second case:

select f.value:name::String from json_table, table(flatten(v:movies)) f;

create or replace procedure sp2(tablename varchar, input_column varchar, input_level varchar, out_column varchar)
returns table (res varchar)
language sql
as
$$
declare
  qry string;
  res resultset;
begin
  qry := 'select f.value:'||:input_level||'::String from '||:tablename||', table(flatten('||:input_column||':movies)) f';
  res := (execute immediate qry);
  return table(res);
end;
$$
;

call sp2('json_table', 'v', 'name', 'movies');