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

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.