PostgreSQL: How to import data from JSON column

HowTo
#1

When you try to import data from PostgreSQL and one of the columns is a text blob in the form of a JSON, then you might see the following error:

Error : No method asJSON S3 class: pq_json

If this is a case, you can workaround it by either of the followings:

Write a SQL that gets attributes from the JSON.

For example, if your column “info” contains customer attributes like below,

{ "customer": "John Doe", "items": {"product": "Beer","qty": 6}}

Then you extract the “customer” attribute from it by using “->>” operator like below.

SELECT info ->> 'customer' AS customer FROM orders;

https://www.postgresqltutorial.com/postgresql-json/
https://www.postgresql.org/docs/9.3/functions-json.html

Import the whole column value as text.

The other option is to import the JSON as text into Exploratory.

SELECT CAST( json_column AS TEXT ) FROM table

Then you can convert a character column with JSON data to a list column by following the instructions in this note.

1 Like