CODEWITHSUNDEEP
jsonpostgresqlpostgresql-9.1plpgsql
Prosto Trader
Prosto Trader

Reputation: 3527

Create table based on JSON file in PostgreSQL

I'm receiving some data in JSON format. I want to store data in database (PSQL 9.1), but structure of data is changing each day.

Data example: 

{
"No.":"1",
"Ticker":"A",
"Market Cap":"18468.13",
"P\/E":"25.53",
"Forward P\/E":"16.17",
"PEG":"2.69",
"P\/S":"2.72",
"P\/B":"3.39",
"P\/Cash":"6.74",
"P\/Free Cash Flow":"24.46",
"EPS growth this year":"-35.80%",
"EPS growth next year":"10.91%",
"EPS growth past 5 years":"2.30%",
"EPS growth next 5 years":"9.48%",
"Sales growth past 5 years":"3.30%",
"Price":"55.39",
"Change":"0.16%",
"Volume":"1287900",
"Dividend Yield":"0.96%",
"Return on Assets":"7.00%",
"Return on Equity":"14.20%",
"Return on Investment":"10.20%",
"Current Ratio":"3.30",
"Quick Ratio":"2.60",
"LT Debt\/Equity":"0.50",
"Total Debt\/Equity":"0.50",
"Gross Margin":"52.20%",
"Operating Margin":"15.70%",
"Profit Margin":"10.90%",
"Earnings Date":"5\/14\/2014 4:30:00 PM",
"Company":"Agilent Technologies Inc.",
"Sector":"Healthcare",
"Industry":"Medical Laboratories & Research",
"Country":"USA"
}

I want to store all received data in new table each day. What is the easiest way to create new table with same structure as received JSON?

Upvotes: 4

Views: 6137

Answers (1)

klin
klin

Reputation: 121474

The question is probably from regex exam. SQL Fiddle

create or replace function create_table_from_json(json text, tablename text)
returns void language plpgsql
as $$
begin
  execute
    replace(
      replace(
        regexp_replace(
          json,
          '("[^"]*"):("[^"]*")',
          '    \1 text', 'g'),
        '{', 
        format('create table %s (', tablename)),
      '}',
      ');');
end 
$$;

create or replace function insert_from_json(json text, tablename text)
returns void language plpgsql
as $$
begin
  execute
    replace(
      replace(
        regexp_replace(
          json,
          '("[^"]*"):"([^"]*)"',
          '''\2''', 'g'),
        '{', 
        format('insert into %s values (', tablename)),
      '}',
      ');');
end 
$$;

Upvotes: 4

Related Questions