Tools to migrate data from Json files to PostgreSQL DB.

On 3/7/19 1:21 PM, github kran wrote:

Hello PostgreSQL Team,

Are there are any tools to migrate data present in the json files ? to
the postgreSQL database.
We have data in flat files about 2 billion records across multiple files.

1) What is the easiest way I can transfer this data to relational
database ?.
2) Any tools I can use ?. and any better ways do it ?

An older blog:

https://www.citusdata.com/blog/2013/05/30/run-sql-on-json-files-without-any-data-loads/

Not sure if all the parts still work or not.

Otherwise I could see using the JSON types and JSON functions built into
Postgres:

https://www.postgresql.org/docs/10/datatype-json.html

https://www.postgresql.org/docs/10/functions-json.html

Appreciate your help.

Thanks
Kran,

--
Adrian Klaver
adrian.klaver@aklaver.com

Hi

čt 7. 3. 2019 v 22:21 odesílatel github kran <githubkran@gmail.com> napsal:

Hello PostgreSQL Team,

Are there are any tools to migrate data present in the json files ? to the
postgreSQL database.
We have data in flat files about 2 billion records across multiple files.

1) What is the easiest way I can transfer this data to relational database
?.
2) Any tools I can use ?. and any better ways do it ?

I wrote pgimportdoc

https://github.com/okbob/pgimportdoc

Regards

Pavel

You may want to assess how you want to store and access the data in
Postgres before deciding on an import strategy.

I have a system with a mix of relational and JSON data. The data was
originally sourced in flat file format.
I wrote a few Python programs to take the data, then format to JSON, which
I then submitted to pg functions.
To make life easier, I submitted it as 1 JSON field to be decomposed by
Postgres into relational data, and another to be loaded straight into pg
JSON columns.

The functions then either strip out the data from JSON using the PG JSON
functions and store it relationally, or plug it straight into a JSON data
element.

Not terribly difficult to do, especially if you are not navigating complex
JSON structures in pl/pgsql to strip out the data.

Plenty of python JSON examples out there. Less so for PG :)

On Thu, 7 Mar 2019 at 23:21, github kran <githubkran@gmail.com> wrote:

Thank you all for your tips. I will try to see if I can make use of these
useful tips using my java application.

On Fri, Mar 8, 2019 at 1:33 AM Tony Shelver <tshelver@gmail.com> wrote: