Inserting JSON data to postgresql query successful, but output is unexpected

Question

I am trying to insert some JSON data to a table in postgresql.

JSON DATA:
{
 "wsgi.multiprocess": true,
 "HTTP_REFERER": "http://localhost:9000/"
}

So, to do this, I am doing these steps:

CREATE TABLE TEST (MULTIPROCESS VARCHAR(20), HTTP_REFERER VARCHAR(50));

INSERT INTO TEST SELECT * FROM json_populate_record(NULL::test, '{"wsgi.multiprocess": true,"HTTP_REFERER": "http://localhost:9000/"}');

The first step creates a table, while the next one should insert JSON data into the table. The query completes successfully, but when I try to see the data inside the table, it just a single pipe.

Here is the output:

Anybody knows why is the output like this? Any idea what I should do to rectify this?

Solution 1

First, in postgresql if you really want your fieldname and table name uppercase, you need to create the table like this:

CREATE TABLE "TEST" ("MULTIPROCESS" VARCHAR(20), "HTTP_REFERER" VARCHAR(50));

Then the query would be like :

SELECT * FROM json_populate_record(NULL::"TEST", '{"wsgi.multiprocess": true,"HTTP_REFERER": "http://localhost:9000/"}'); 

 MULTIPROCESS |      HTTP_REFERER      
--------------+------------------------
              | http://localhost:9000/

As you can see, multiprocess is leaved blank because the field name in json and in the sql table dont match. To get rid of that you can do an external script or a postgresql function.

For example:

CREATE OR REPLACE
  FUNCTION replace_json_keys(IN js TEXT) 
  RETURNS json 
  STABLE 
  AS
$$
BEGIN
  js := replace(js, '"wsgi.multiprocess"', '"MULTIPROCESS"');
  return js::json;
END;
$$ LANGUAGE 'plpgsql';

Then, you can do :

SELECT * FROM json_populate_record(NULL::"TEST", replace_json_keys('{"wsgi.multiprocess": true,"HTTP_REFERER": "http://localhost:9000/"}'));

 MULTIPROCESS |      HTTP_REFERER      
--------------+------------------------
 true         | http://localhost:9000/

However, the best solution wouldn't be to use replace() but to use json_each() to split the keys and values in two arrays, do a loop with a "case when" in it to map the json keys to the sql keys and then return a new json object (with json_object(keys[], values[])) ready to insert.

Solution 2

json keys should match fields in the created table (prefix ended with "." is treated as a table name, w/o it)

CREATE TABLE TEST (MULTIPROCESS VARCHAR(20), HTTP_REFERER VARCHAR(50));
INSERT INTO TEST SELECT MULTIPROCESS, HTTP_REFERER FROM json_populate_record(NULL::test, '{"multiprocess": true,"http_referer": "http://localhost:9000/"}');

http_referer should be in small letters in json_populate_record()

http://sqlfiddle.com/#!15/ec202/1

Comments

[Ambidextrous]

So, is there no other way to do this except changing my json field names? I don't want to change the json fields unless it is the only way out