PSQL with JSON values

January 22, 2015 12:52 PM

I have a table with one column that contains data in JSON format, organized like:

{“Time”:”1 to 2”,”Room”:”Conference”,”Person”:”Foo”}
{“Time”:”4 to 5”,”Room”:”Lecture”,”Person”:”Bar”}
{“Time”:”7 to 8”,”Room”:”Lecture”,”Person”:”Bar”}

I want to create a new table of two columns that will any pair each unique value option with its respective class type. So something like this, in two columns:

Time: 1 to 2
Time: 4 to 5
Time: 7 to 8
Room: Conference
Room: Lecture
Person: Foo
Person: Bar

Thanks for any help.

posted by null14 to Computers & Internet (4 answers total)

I don't understand what you mean by "a new table of two columns". Can you describe the schema of that table in a little more detail? i.e., what are the column names and data types? I can't really imagine a scenario where your example ("something like this, in two columns") would be sensible.

At any rate, I'm not aware of any straightforward way to parse JSON in SQL, so you would probably need to do some scripting to accomplish this (e.g., in Python, or whatever scripting language you're familiar with).

You'll probably have better luck with questions like this over at Stack Overflow.
posted by escape from the potato planet at 12:59 PM on January 22, 2015

Best answer: If by 'psql' you mean PostgreSQL, you can use its built-in JSON handling, as long as you're running a very recent version (9.3+). I tested this on 9.4:


template1=# CREATE TABLE sample (json_data TEXT);
CREATE TABLE

template1=# INSERT INTO sample (json_data) VALUES ('{"Time":"1 to 2","Room":"Conference","Person":"Foo"}');
INSERT 0 1

template1=# INSERT INTO sample (json_data) VALUES ('{"Time":"4 to 5","Room":"Lecture","Person":"Bar"}');
INSERT 0 1

template1=# INSERT INTO sample (json_data) VALUES ('{"Time":"7 to 8","Room":"Lecture","Person":"Bar"}');
INSERT 0 1

template1=# select item.* from sample, json_each(sample.json_data::json) item;
key | value
--------+--------------
Time | "1 to 2"
Room | "Conference"
Person | "Foo"
Time | "4 to 5"
Room | "Lecture"
Person | "Bar"
Time | "7 to 8"
Room | "Lecture"
Person | "Bar"
(9 rows)

template1=# select distinct item.key, item.value::text from sample, json_each(sample.json_data::json) item order by item.key;
key | value
--------+--------------
Person | "Foo"
Person | "Bar"
Room | "Lecture"
Room | "Conference"
Time | "1 to 2"
Time | "7 to 8"
Time | "4 to 5"
(7 rows)

Sorry for the chewed-up formatting. The json_each function does the actual work of extracting the data. The wacky SELECT invocation comes from this StackOverflow question. If your data is already stored as the PostgreSQL json type, you can omit the ::json cast in the SELECT statement. Also note that this leaves the value column stored as json, so you have to cast it to text to do much with it.

If your version of PostgreSQL is 9.3, this might work with some tweaks; 9.2 and older are missing the expanded JSON handling and you'd be better off either setting up a temporary 9.4 server or running your data through an external script.
posted by pocams at 1:24 PM on January 22, 2015 [3 favorites]

Best answer: You almost certainly want 3 columns in your new table. The third should be some kind of identifier that ties related values together, because otherwise it decouples properties from their object. So:

   id   |    key   |   value
   1    |  Person  |    Foo
   1    |   Time   |   1 to 2
   1    |   Room   |  Lecture
   2    |  Person  |    Bar
   2    |   Time   |   4 to 5
   2    |   Room   | Conference
...etc.

So expanding on pocams' answer:

CREATE TABLE sample (id serial primary key, json_data TEXT);
CREATE TABLE sample_objs (id int, key text, value text);
INSERT INTO sample (json_data) VALUES ('{"Time":"1 to 2","Room":"Conference","Person":"Foo"}');
INSERT INTO sample (json_data) VALUES ('{"Time":"4 to 5","Room":"Lecture","Person":"Bar"}');
INSERT INTO sample (json_data) VALUES ('{"Time":"7 to 8","Room":"Lecture","Person":"Bar"}');
INSERT INTO sample_objs SELECT s.id, item.key, trim(item.value::text,'"') as value FROM sample s, json_each(s.json_data::json) item;

If you don't have Postgres 9.3+, and your JSON is really that well-formed and regular, try the following hack in place of the last INSERT above:

insert into sample_objs select s.id, trim(split_part(s.time,':',1),'"') as key, trim(split_part(s.time,':',2),'"') as value from (
select id, split_part(ltrim(json_data,'{'),',',1)  as time
from sample) s;
insert into sample_objs select s.id, trim(split_part(s.time,':',1),'"') as key, trim(split_part(s.time,':',2),'"') as value from (
select id, split_part(ltrim(json_data,'{'),',',2)  as time
from sample) s;
insert into sample_objs select s.id, trim(split_part(s.time,':',1),'"') as key, trim(split_part(s.time,':',2),'"') as value from (
select id, split_part(rtrim(json_data,'}'),',',3)  as time
from sample) s;

posted by axiom at 5:40 PM on January 22, 2015

Response by poster: Thanks so much!
posted by null14 at 11:05 AM on January 23, 2015