Create table based on JSON file in PostgreSQL

Question

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?

I want to use sql queries on that table later on...It's to big to parse JSON each time and I can't use json_enhancements in PSQL becouse I have 9.1 — Prosto Trader
– Prosto Trader, Commented May 12, 2014 at 13:01
Seems like a perfect reason to upgrade to 9.3 - although I think you could install JavaScript (plv8js) into your 9.1 installation and build some functions in JavaScript to access the JSON stored in a text column. — user330315
– user330315, Commented May 12, 2014 at 13:02
You won't be able to do that. Let's assume the provided structure is for today. Let's assume tomorrow, there will be a new column. What type would you use for that column? Because if you want to write a script today, you should know that. BTW if you do not want to query on all columns, you can pull your most wanted fields to columns, and store the whole response as text (but only use that in your application). — pozs
– pozs, Commented May 12, 2014 at 13:11
You'll probably want to write a simple script to do this, with Perl/Python/Ruby/whatever. — Craig Ringer
– Craig Ringer, Commented May 12, 2014 at 14:29

klin · Accepted Answer · 2014-05-13 01:49:03Z

4

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 
$$;

answered May 13, 2014 at 1:49

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create table based on JSON file in PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related