Should I use JSON?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Should I use JSON?

stan-9
Worming on a small project, and have been doing a lot of Perl scripting to
parse various types of files to populate the database. Now I need to get
data from a cloud services provider (time-keeping). They have a REST API
that returns data in a JSOSN format.

So here is the question, should I just manually parse this data, as I have
been doing to insert into appropriate entities into the database? Or should I
insert the JSON data, and use some queries in the database to populate my
tables from the JSON tables?


--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                                                -- Benjamin Franklin


Reply | Threaded
Open this post in threaded view
|

Re: Should I use JSON?

Adrian Klaver-4
On 5/21/20 8:37 AM, stan wrote:

> Worming on a small project, and have been doing a lot of Perl scripting to
> parse various types of files to populate the database. Now I need to get
> data from a cloud services provider (time-keeping). They have a REST API
> that returns data in a JSOSN format.
>
> So here is the question, should I just manually parse this data, as I have
> been doing to insert into appropriate entities into the database? Or should I
> insert the JSON data, and use some queries in the database to populate my
> tables from the JSON tables?
>

I use Python for this sort of thing. A JSON array of objects maps so
nicely to a Python list of dicts that I just do the parsing in Python
and INSERT the parsed data into the table.

>


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: Should I use JSON?

David G Johnston
In reply to this post by stan-9
On Thu, May 21, 2020 at 8:37 AM stan <[hidden email]> wrote:
So here is the question, should I just manually parse this data, as I have
been doing to insert into appropriate entities into the database? Or should I
insert the JSON data, and use some queries in the database to populate my
tables from the JSON tables?

The simplest thing that works is usually a good starting point.

I consider it a requirement that I can get to the original data as supplied by the vendor.  So, if you are capturing that elsewhere, going directly to structured tables is sufficient.  If you don't have any other place where that gets saved I would save it to a table first then convert it to the final structured tables.

My current setup is using "jq" to perform an intermediate transform of the source json to table-specific files and then using psql and jsonb_to_record insert the json data into the tables.

David J.
Reply | Threaded
Open this post in threaded view
|

Re: Should I use JSON?

pabloa98
In reply to this post by stan-9


On Thu, May 21, 2020 at 8:37 AM stan <[hidden email]> wrote:
Worming on a small project, and have been doing a lot of Perl scripting to
parse various types of files to populate the database. Now I need to get
data from a cloud services provider (time-keeping). They have a REST API
that returns data in a JSOSN format.

So here is the question, should I just manually parse this data, as I have
been doing to insert into appropriate entities into the database? Or should I
insert the JSON data, and use some queries in the database to populate my
tables from the JSON tables?


That depends of how advanced is your analysis of the solution you want to implement.
If you are still exploring, I would suggest you store JSON in JSONB columns + some id column to search it.

When your program/solution knows what properties you are going to use, perhaps you want to convert those in columns.

In any case, data could be indexed in both, columns and JSONB

So it is up to you :)

Pablo
Reply | Threaded
Open this post in threaded view
|

RE: Should I use JSON?

Zahir Lalani-2

We make lots of use JSON – but in specific contexts.

 

If we need to pull data out for listing view – always raw fields. If these are detail view only and we need dynamic content depending on record types, JSON is a life saver

 

Z

 

From: pabloa98 <[hidden email]>
Sent: 21 May 2020 20:28
Cc: [hidden email]
Subject: Re: Should I use JSON?

 

 

 

On Thu, May 21, 2020 at 8:37 AM stan <[hidden email]> wrote:

Worming on a small project, and have been doing a lot of Perl scripting to
parse various types of files to populate the database. Now I need to get
data from a cloud services provider (time-keeping). They have a REST API
that returns data in a JSOSN format.

So here is the question, should I just manually parse this data, as I have
been doing to insert into appropriate entities into the database? Or should I
insert the JSON data, and use some queries in the database to populate my
tables from the JSON tables?

 

That depends of how advanced is your analysis of the solution you want to implement.

If you are still exploring, I would suggest you store JSON in JSONB columns + some id column to search it.

 

When your program/solution knows what properties you are going to use, perhaps you want to convert those in columns.

 

In any case, data could be indexed in both, columns and JSONB

 

So it is up to you :)

 

Pablo

Reply | Threaded
Open this post in threaded view
|

Re: Should I use JSON?

Tim Cross
In reply to this post by stan-9

stan <[hidden email]> writes:

> Worming on a small project, and have been doing a lot of Perl scripting to
> parse various types of files to populate the database. Now I need to get
> data from a cloud services provider (time-keeping). They have a REST API
> that returns data in a JSOSN format.
>
> So here is the question, should I just manually parse this data, as I have
> been doing to insert into appropriate entities into the database? Or should I
> insert the JSON data, and use some queries in the database to populate my
> tables from the JSON tables?

Given you plan to store your data in 'normal' tables and you are already
using a scripting language to get the data from the remote API
and your already processing data in various forms using Perl, I
would not bother. All you will really do is add another layer of
complexity and skill requirement (i.e. JSON in the database and writing
JSON queries using PG's SQL JSON support).


--
Tim Cross