MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

PostgreSQL Write JSON File

without comments

In the process of working through issues to support ETL transformations from JSON with Python. The first step was extracting a the data from a PostgreSQL table into a JSON file. The syntax wasn’t exactly clear and the PostgreSQL Tutorial was a bit misleading but I worked it out. My initial sample deals with writing the data from the item table of my videodb database to a item.json file in the /tmp directory.

The query retrieves the data but casts the creation_date and last_update_date timestamp data types into date data types, as follows:

SELECT row_to_json(
 (SELECT ROW
  FROM (SELECT item_id
        ,      item_barcode
        ,      item_type
        ,      item_title
        ,      item_subtitle
        ,      item_rating
        ,      item_release_date
        ,      created_by
        ,      creation_date::DATE
        ,      last_updated_by
        ,      last_update_date::DATE) ROW)) AS DATA
        FROM   item;

The internal SELECT-list creates the name element of name:value pairs in the JSON structure. Naturally, it does exclude the type casting ::date portion of the query from the name elements. The row_to_json() maps the name list to the list of value results from in each row, and returns a result set.

The query returns the following 21 JSON objects:

{"item_id":1001,"item_barcode":"9736-05640-4","item_type":1016,"item_title":"The Hunt for Red October","item_subtitle":"Special Collector's Edition","item_rating":"PG","item_release_date":"1990-03-02","created_by":1001,"creation_date":"2019-12-13","last_updated_by":1001,"last_update_date":"2019-12-13"}
{"item_id":1002,"item_barcode":"24543-02392","item_type":1016,"item_title":"Star Wars I","item_subtitle":"Phantom Menace","item_rating":"PG","item_release_date":"1999-05-04","created_by":1001,"creation_date":"2019-12-13","last_updated_by":1001,"last_update_date":"2019-12-13"}
{"item_id":1003,"item_barcode":"24543-5615","item_type":1015,"item_title":"Star Wars II","item_subtitle":"Attack of the Clones","item_rating":"PG","item_release_date":"2002-05-16","created_by":1001,"creation_date":"2019-12-13","last_updated_by":1001,"last_update_date":"2019-12-13"}
{"item_id":1004,"item_barcode":"24543-05539","item_type":1016,"item_title":"Star Wars II","item_subtitle":"Attack of the Clones","item_rating":"PG","item_release_date":"2002-05-16","created_by":1001,"creation_date":"2019-12-13","last_updated_by":1001,"last_update_date":"2019-12-13"}
{"item_id":1005,"item_barcode":"24543-20309","item_type":1016,"item_title":"Star Wars III","item_subtitle":"Revenge of the Sith","item_rating":"PG13","item_release_date":"2005-05-19","created_by":1001,"creation_date":"2019-12-13","last_updated_by":1001,"last_update_date":"2019-12-13"}
{"item_id":1006,"item_barcode":"86936-70380","item_type":1016,"item_title":"The Chronicles of Narnia","item_subtitle":"The Lion, the Witch and the Wardrobe","item_rating":"PG","item_release_date":"2002-05-16","created_by":1001,"creation_date":"2019-12-13","last_updated_by":1001,"last_update_date":"2019-12-13"}
{"item_id":1007,"item_barcode":"91493-06475","item_type":1019,"item_title":"RoboCop","item_subtitle":"","item_rating":"Mature","item_release_date":"2003-07-24","created_by":1001,"creation_date":"2019-12-13","last_updated_by":1001,"last_update_date":"2019-12-13"}
{"item_id":1008,"item_barcode":"93155-11810","item_type":1019,"item_title":"Pirates of the Caribbean","item_subtitle":"","item_rating":"Teen","item_release_date":"2003-06-30","created_by":1001,"creation_date":"2019-12-13","last_updated_by":1001,"last_update_date":"2019-12-13"}
{"item_id":1009,"item_barcode":"12725-00173","item_type":1019,"item_title":"The Chronicles of Narnia","item_subtitle":"The Lion, the Witch and the Wardrobe","item_rating":"Everyone","item_release_date":"2003-06-30","created_by":1001,"creation_date":"2019-12-13","last_updated_by":1001,"last_update_date":"2019-12-13"}
{"item_id":1010,"item_barcode":"45496-96128","item_type":1017,"item_title":"MarioKart","item_subtitle":"Double Dash","item_rating":"Everyone","item_release_date":"2003-11-17","created_by":1001,"creation_date":"2019-12-13","last_updated_by":1001,"last_update_date":"2019-12-13"}
{"item_id":1011,"item_barcode":"08888-32214","item_type":1018,"item_title":"Splinter Cell","item_subtitle":"Chaos Theory","item_rating":"Teen","item_release_date":"2003-04-08","created_by":1001,"creation_date":"2019-12-13","last_updated_by":1001,"last_update_date":"2019-12-13"}
{"item_id":1012,"item_barcode":"14633-14821","item_type":1018,"item_title":"Need for Speed","item_subtitle":"Most Wanted","item_rating":"Everyone","item_release_date":"2004-11-15","created_by":1001,"creation_date":"2019-12-13","last_updated_by":1001,"last_update_date":"2019-12-13"}
{"item_id":1013,"item_barcode":"10425-29944","item_type":1019,"item_title":"The DaVinci Code","item_subtitle":"","item_rating":"Teen","item_release_date":"2006-05-19","created_by":1001,"creation_date":"2019-12-13","last_updated_by":1001,"last_update_date":"2019-12-13"}
{"item_id":1014,"item_barcode":"52919-52057","item_type":1019,"item_title":"Cars","item_subtitle":"","item_rating":"Everyone","item_release_date":"2006-04-28","created_by":1001,"creation_date":"2019-12-13","last_updated_by":1001,"last_update_date":"2019-12-13"}
{"item_id":1015,"item_barcode":"9689-80547-3","item_type":1020,"item_title":"Beau Geste","item_subtitle":"","item_rating":"PG","item_release_date":"1992-03-01","created_by":1001,"creation_date":"2019-12-13","last_updated_by":1001,"last_update_date":"2019-12-13"}
{"item_id":1016,"item_barcode":"53939-64103","item_type":1020,"item_title":"I Remember Mama","item_subtitle":"","item_rating":"NR","item_release_date":"1998-01-05","created_by":1001,"creation_date":"2019-12-13","last_updated_by":1001,"last_update_date":"2019-12-13"}
{"item_id":1017,"item_barcode":"24543-01292","item_type":1020,"item_title":"Tora! Tora! Tora!","item_subtitle":"The Attack on Pearl Harbor","item_rating":"G","item_release_date":"1999-11-02","created_by":1001,"creation_date":"2019-12-13","last_updated_by":1001,"last_update_date":"2019-12-13"}
{"item_id":1018,"item_barcode":"43396-60047","item_type":1020,"item_title":"A Man for All Seasons","item_subtitle":"","item_rating":"G","item_release_date":"1994-06-28","created_by":1001,"creation_date":"2019-12-13","last_updated_by":1001,"last_update_date":"2019-12-13"}
{"item_id":1019,"item_barcode":"43396-70603","item_type":1020,"item_title":"Hook","item_subtitle":"","item_rating":"PG","item_release_date":"1991-12-11","created_by":1001,"creation_date":"2019-12-13","last_updated_by":1001,"last_update_date":"2019-12-13"}
{"item_id":1020,"item_barcode":"85391-13213","item_type":1020,"item_title":"Around the World in 80 Days","item_subtitle":"","item_rating":"G","item_release_date":"1992-12-04","created_by":1001,"creation_date":"2019-12-13","last_updated_by":1001,"last_update_date":"2019-12-13"}
{"item_id":1021,"item_barcode":"85391-10843","item_type":1020,"item_title":"Camelot","item_subtitle":"","item_rating":"G","item_release_date":"1998-05-15","created_by":1001,"creation_date":"2019-12-13","last_updated_by":1001,"last_update_date":"2019-12-13"}

Moving on to the next step you enclose the query in the copy command syntax, like:

COPY
(SELECT row_to_json(
  (SELECT ROW
   FROM (SELECT item_id
         ,      item_barcode
         ,      item_type
         ,      item_title
         ,      item_subtitle
         ,      item_rating
         ,      item_release_date
         ,      created_by
         ,      creation_date::DATE
         ,      last_updated_by
         ,      last_update_date::DATE) ROW)) AS DATA
         FROM   item) TO '/tmp/sample.json';

When you run it, it will fail with the following error message:

ERROR:  must be superuser or a member of the pg_write_server_files role to COPY to a file
HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.

You can fix this problem by connecting as the postgres user and granting the privilege to the student user:

GRANT pg_write_server_files TO student;

It then runs successfully as the student user and creates the sample.json file in the /tmp directory. As always, I hope this helps those looking for a solution.

Written by maclochlainn

January 5th, 2020 at 12:24 am