Source tables from JSON API extraction

Question

Trying to implement an API extraction that dumps the results from JSON apis daily in a postgres db. The goal for these data is to be used in analysis. Afterwards I will handle those using dbt to do the modeling part (extracting the JSON values).

Example JSON values from an object representing a task on a task management system:

{
 gid: 1234, // Unique id for the resource
 updated_at: "Wed, 22 Mar 2023 08:53:46 GMT",
 ... rest of the attributes
}

My current strategy is storing those in tables with this schema

gid	info::jsonb	updated_at::timestamp
12345	{...}	Wed, 22 Mar 2023 08:53:46 GMT

If the extraction is to happen once a day every day, does the strategy of surrogate key of (gid, updated_at) sound ok to judge if the insert should happen or not ?

Answer 1

Yes, that seems reasonable.
Since you'll be scanning the jsonb on each pass each time for gid and updated_at, you'll want to create a GIN index for this purpose, e.g.

CREATE INDEX api_gid ON api USING GIN ((info-> 'gid'));

Ideally, you could create an index on both gid and updated_at, but that requires use of an extension called btree_gin. Without the extension, you could try creating a second index on info->'updated_at', but it would probably not get used.. depends on what else the query needs to do.

If you can install the btree_gin extension, then this syntax would work:

CREATE INDEX index_name ON items USING GIN (account_id, keywords);