Data exploration with Postgres

Managed to load data from a python literal file by converting it to json. After loading the data into postgres it looked like this-

The file contains a variety of columns where I want to explore just four of them. This is the first time I bump into the magical “->>” operator to select data from a jsonb type column.

 select metadata->>'title' as title
    ,metadata->> 'genres' as genres
    ,metadata->>'releaseDate' as releaseDate
    ,metadata->>'popularity' as popularity
from movies_raw

Here is the simple workflow of the task

Get Saleh Faize’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Furthermore, it can be used to filter out data as well. Instead of “->>” we need to use “->” for that. The snippets show a little taste of exploration with several filtering scenarios.

/*if the value is contained in the array*/
select metadata->>'title' as title
    ,metadata->> 'genres' as genres
    ,metadata->>'releaseDate' as releaseDate
    ,metadata->>'popularity' as popularity
from movies_raw
where metadata->'genres' ? 'Crime'

/*if both of the elements of array are available*/
select metadata->>'title' as title
    ,metadata->> 'genres' as genres
    ,metadata->>'releaseDate' as releaseDate
    ,metadata->>'popularity' as popularity
from movies_raw
where metadata->'genres' ?& '{"Crime","Drama"}'

/*if one of the elements of the array is available*/
select metadata->>'title' as title
    ,metadata->> 'genres' as genres
    ,metadata->>'releaseDate' as releaseDate
    ,metadata->>'popularity' as popularity
from movies_raw
where metadata->'genres' ?| '{"Crime","Drama"}'

In this exploration, I was able to analyze movie data stored in a postgres database. By leveraging it’s powerful jsonb data type and its operators, querying nested json structures becomes easier without the need to fully normalize the data.